select
    *
from
    cmb_usr_trx_rcd
where
    date(trx_time) 
    between '2024-09-01' and '2024-09-30' 
    and hour(trx_time) between 1 and 5 
    and usr_id = '5201314520' 
order by trx_time

select
    *
from
    cmb_usr_trx_rcd
where 
    date(trx_time) between '2024-09-01' and '2024-09-30'
    and usr_id='5201314520'
order by 
    trx_time

select * 
from cmb_usr_trx_rcd
where usr_id = '5201314520'
and trx_time between '2024-09-01' and '2024-09-30'
order by 
    trx_time

select * 
from cmb_usr_trx_rcd
where usr_id = 5201314520
and trx_time between '2024-09-01' and '2024-09-30'

SELECT name, subject, class_code, qualification
FROM teachers
WHERE fir_degr = '北京大学'AND head_teacher IS NOT NULL OR fir_degr = '清华大学' AND head_teacher IS NOT NULL

SELECT name, subject, class_code, qualification
FROM teachers
WHERE fir_degr = '北京大学' OR fir_degr = '清华大学' AND head_teacher IS NOT NULL

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

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

select * 
from scores 
where exam_date = '2024-06-30'
and subject = '英语' 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> 100or math> 100 or english>100 
order by chinese

select
exam_date as 考试日期,
min(case when subject = '语文' then score else null end)as 语文成绩,
min(case when subject = '数学' then score else null end ) as 数学成绩,
min(case when subject = '英语' then score else null end ) as 英语成绩
FROM scores
WHERE student_id = 460093 AND subject IN ('语文', '数学', '英语')
GROUP BY 考试日期
ORDER BY exam_date;

select
exam_date,
min(case when subject = '语文' then score else null end)as 语文成绩,
min(case when subject = '数学' then score else null end ) as 数学成绩,
min(case when subject = '英语' then score else null end ) as 英语成绩
FROM scores
WHERE student_id = 460093 AND subject IN ('语文', '数学', '英语')
GROUP BY exam_date
ORDER BY exam_date;

select sc.student_id, sum(score) as total_score
from students s 
join scores sc on sc.student_id = s.student_id 
where subject in ('语文', '英语','数学')AND exam_date = '2024-06-30'
group by sc.student_id
having total_score > 330

selectsum(score) as total_score, sc.student_id
from students s 
join scores sc on sc.student_id = s.student_id 
where subject in ('语文', '英语','数学')AND exam_date = '2024-06-30'
group by sc.student_id
having total_score > 330

select s.name, sum(score) as total_score, sc.student_id
from students s 
join scores sc on sc.student_id = s.student_id 
where subject in ('语文', '英语','数学')AND exam_date = '2024-06-30'
group by sc.student_id
having total_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 
case when year(enter_date)>='2010' then '青年教师'
when year(enter_date)< '2000' then '资深教师'
else '中年教师'
end as teacher_type,
count(*)
from teachers
group by teacher_type

select case 
 when gender = 'f'then '女'
else'男'
end as 性别,
count(*) 
from students
 group by gender