select sco.student_id , sum(score)
fromscores scoleft join students stu on sco.student_id=stu.student_id
where exam_date='2024-06-30' and subject in ('语文','数学','英语')
group by sco.student_id
having sum(score)>330

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

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

select student_id
from scores
group by student_id
having min(score>=80)
order by student_id

select case when enter_date>='2010-01-01' then '青年教师' when enter_date<='2000-12-31' then '资深教师' else '中年教师' end tea, count(enter_date) num
from teachers
group by tea

select case when gender='m' then '男' when gender='f'then '女' endnum, count(gender) count
from students
group by num

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

select max(score) max,min(score) min ,avg(score) avg
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 subject ,max(score) maxsc, min(score) min
from scores
group by subject
order by subject

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

select class_code,count(student_id) sum
from students
group by class_code
order by sum desc

select name, subject, class_code, graduate_date
from teachers
where gender='f' and graduate_date between '1994-01-01' and '1997-12-31'
 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

select student_id,name,birth_date
from students
where gender='f'and substring(birth_date,1,4)='2009'
order by birth_date

select 
case when score>=110 then '[110, 120]' 
when score>=90 then '[90,110)'
when score>=60 then '[60,90)'
else '[0, 60)'end as score_range
,count(student_id)num_students 
from scores
where subject='数学' and exam_date='2024-06-30'
group by score_range

select 
case when score>=110 then '[110, 120]' 
when score>=90 then '[90,110)'
when score>=60 then '[60,90)'
else '[0, 60)'end as score_range
,count(*)num_students 
from scores
where subject='数学' and exam_date='2024-06-30'
group by score_range

select 
case when score>=110 then '[110, 150]' 
when score>=90 then '[90,110)'
when score>=60 then '[60,90)'
else '[0, 60)'end as score_range
,count(*)num_students 
from scores
where subject='数学' and exam_date='2024-06-30'
group by score_range
order by score_range desc

select 
case when score>=110 then '[110, 150]' 
when score>=90 then '[90,110)'
when score>=60 then '[60,90)'
else '[0, 60)'end as score_range
,count(*)num_students 
from scores
where subject='数学' and exam_date='2024-06-30'
group by score_range