select t2.live_id,t2.live_nm,count(*) as enter_cnt
from ks_live_t1 t1
join ks_live_t2 t2
on t1.live_id = t2.live_id
where date_format(t1.enter_time,'%Y-%m-%d %H') = '2021-09-12 23'
group by t1.live_id,t2.live_nm
order by enter_cnt desc
limit 5;

select *
from scores
where subject in ('历史','政治','地理') and score >= 90 and exam_date = '2024-06-30'
order by score desc,student_id,subject;

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

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

select * 
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 subject in ('语文','数学','英语') and exam_date = '2024-06-30'
group by student_id
having sum(score)>330;

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

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

select student_id,max(score) as max_score,min(score) as min_score,avg(score) as avg_score
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-01-01' 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 scores
where student_id = 735011 and 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 enter_date < '2010-01-01' 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;