select
	case 
when year(enter_date) < 2000 then '资深教师'
when year(enter_date) >= 2010 then '青年教师'
	else '中年教师'
end as'teacher_type',
count(*) as 
		teacher_count
from
	teachers
group by 
	teacher_type

select
	case 
when gender = 'm' then '男'
when gender = 'f' then '女'
end as gender_text,
count(*) as student_count
from
	students
group by
	gender

select
	student_id,max(score) as max_score,min(score) as min_score,avg(score) as avg_score
from
	scores
where
	(student_id = '460093' or student_id ='735011' ) and subject = '数学'
group by
student_id

select
	max(score) as max_score,min(score) as min_score,avg(score) as avg_score
from
	students
inner join
	scores
on
	students.student_id = scores.student_id and students.student_id = '735011' and scores.subject = '语文'

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

select
	subject,max(score) as max_score,min(score) as min_score
from
	scores
group by 
	subject
order by
	subject

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

select
	class_code,count(*) as student_count
from
	students
group by
	class_code
order by
	student_count desc

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
	(year(graduate_date) between 1994 and 1997)
	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
	fir_degr = '北京大学' or fir_degr = '清华大学'
order by
	name

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

select
	students.student_id,students.name,students.birth_date
from
	students
where
	gender = 'f' and year(birth_date) = '2009'
order by
	birth_date

select
	students.student_id,students.name,students.birth_date
from
	students
where
	gender = 'm' and year(birth_date) = '2009'
order by
	birth_date

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

select
	case 
when scores.score >= 0 andscores.score < 60 then '[0, 60)'
when scores.score >= 60 and scores.score < 90 then '[60, 90)'
when scores.score >= 90 and scores.score <110 then '[90, 110)'
when scores.score >=110 and scores.score <=120 then '[1110,120]'
end as 'score_range',
count(*) as num_students
from
	students
inner join
	scores
on
	students.student_id = scores.student_id
where
	scores.subject = '数学' and scores.exam_date = '2024-06-30'
group by
	score_range

with ranked_scores as (
	select
		students.student_id,
		students.name,
		scores.score,
		rank() over (partition by students.grade_code order by scores.score desc) as ranking
	from
		students
	inner join
		scores
	on 
		students.student_id = scores.student_id
	where
		scores.subject = '物理' AND students.grade_code = 'S1'
)
select
	student_id,name,score,ranking
from
	ranked_scores
where
	ranking <10

with ranked_scores as(
	select 
		students.student_id,
		students.name,
		grade_code,
		physical_score.score,
		row_number()over(partition by grade_code order by score desc) as rnk
	from
		students
	inner join
		(select scores.student_id,scores.score from scores where subject = '物理') physical_score
	on 
		students.student_id = physical_score.student_id
)
select
	student_id,name,score,rnk
from
	ranked_scores
where
	grade_code = 'S1' and rnk <= 10

select 
	students.student_id,
students.name,
b.score,
	row_number()over(order by b.score desc) as rnk
from
	students
inner join
	(select scores.score,scores.student_id from scores where subject = '物理') b
on 
	students.student_id = b.student_id
where
	students.grade_code = 'S1'
order by
	rnk,student_id