select student_id,subject,score,exam_date
from scores
where exam_date = '2024-06-30' and subject = '英语'
having score between 100 and 110
order by score desc;

select student_id,score
from scores
where exam_date = '2024-06-30' and subject = '英语'
having score between 100 and 110
order by score desc;

select *
from students
where class_code in ('C219','C220','C221') AND gender = 'f'
order by student_id;

select student_id,chinese,math,english
from subject_score
where chinese > 100 or math >100 or english > 100
order by chinese;

select exam_date,max(case when subject = '语文' then score else null end) as chinese_score,max(case when subject = '数学' then score else null end) as math_score,max(case when subject = '英语' then score else null end) as english_score
from scores
where student_id = 460093 and subject in('语文','数学','英语')
group by exam_date
order by exam_date;

select student_id,sum(score) as total_score
from scores
where exam_date = '2024-06-30' and subject in ('语文','数学','英语')
group by student_id
having sum(score) > 330;

select subject
from teachers
group by subject
having count(subject) >= 3;

select student_id,max(score),min(score),avg(score)
from scores
group by student_id
having min(score) >= 80
order by student_id;

select student_id,max(score),min(score),avg(score)
from scores
where student_id in (460093 , 735011) and subject ='数学'
group by student_id;

select max(score),min(score),avg(score)
from scores
where student_id = 735011 and subject = '语文';

select class_code,min(birth_date)
from students
group by class_code
order by class_code;

select class_code,max(birth_date)
from students
group by class_code
order by class_code;

select grade_code,max(birth_date)
from students
group by grade_code
order by grade_code;

select subject,max(score),min(score)
from scores
group by subject
order by subject;

select subject,avg(score)
from scores
where exam_date = '2024-06-30'
group by subject
order by subject;

select class_code,count(*)
from students
group by class_code;

select name,subject,class_code,enter_date
from teachers
where year(enter_date) < 2010 and qualification is null
order by enter_date;

select name,subject,class_code,graduate_date
from teachers 
where (graduate_date between '1994-01-01' and '1997-12-31') and gender = 'f'
order by graduate_date;

select name,subject,class_code,qualification
from teachers
where (fir_degr = '北京大学' OR fir_degr = '清华大学') and head_teacher is not null
order by name;

select name,subject,class_code,qualification
from teachers
where subject = '数学' and qualification = 'senior'
order by name;