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

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

select * from scores where exam_date ='2024-06-30' and score >=100 and score <=110 and subject='english' order by score desc

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

select * from students where class_code in ('C219','C220','C221') order by student_id

select * from subject_score where chinese >100 or math > 100 or english > 100 group by student_id order by chinese desc

select *from subject_score group by student_id having chinese >100 or math >100 or english > 100

select 
exam_date,
SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文',
SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) as '数学',
SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) as '英语'
 from scores where student_id = '460093' group by exam_date

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

select student_id,sum(score) from scores where exam_date='2024-06-30' group by student_id having sum(score)>330

select student_id,sum(score) from scores where exam_date='2024-06-30' group by student_id

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

select student_id from scores where exists (select * from scores where score is null or score >=80) group by student_id order by student_id

select student_id from scores where score < 80 or score is not null group by student_idorder by student_id

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

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

select 
case when year(enter_date)<=2010
then '青年教师'
when year(enter_date)>2000
then '资深教师'
else '中年教师'
end
from teachers

select 
case when year(enter_date)>=2010
then '青年教师'
when year(enter_date)<=2000
then '资深教师'
else '中年教师'
end
from teachers

select 
case when year(enter_date)>2010
then '青年教师'
when year(enter_date)<2000
then '资深教师'
else '中年教师'
end
from teachers

with base_data as (
select case gender when 'm' then '男'when 'f' then '女' end as sex from students
)
select sex,count(sex) from base_data group by sex