select name,subject,class_code,qualification
from teachers 
where fir_degr in ('北京大学','清华大学') AND head_teacher IS NOT NULL
order by name

select name,subject,class_code,qualification
from teachers 
where fir_degr in ('北京大学','清华大学')
order by name

selectsubject
from teachers
group by subject
having count(1)>2

select case when count(subject)>2 then subject else '' end subject
from teachers
group by subject

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

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 year(birth_date)=2009 and gender='f'
order by birth_date

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

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

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

WITH ranked_scores AS (
    SELECT 
        s.student_id, 
        s.name, 
        sc.score,
        RANK() OVER (ORDER BY sc.score DESC) AS ranking
    FROM 
        students s
    JOIN 
        scores sc ON s.student_id = sc.student_id
    WHERE 
        s.grade_code = 'S1' 
        AND sc.subject = '物理'
)
SELECT 
    student_id, 
    name, 
    score, 
    ranking
FROM 
    ranked_scores
WHERE 
    ranking <= 10
ORDER BY 
    ranking;

select a.student_id,a.name,b.score,rank()over( order by b.score desc) ranking
from students a join scores b on a.student_id=b.student_id
where a.grade_code='S1' and b.subject='物理'
order by ranking
limit 10

select a.student_id,a.name,b.score,rank()over( order by b.score desc) ranking
from students a join scores b on a.student_id=b.student_id
where a.grade_code='S1' and b.subject='物理'
limit 10

select a.student_id,a.name,b.score,rank()over(partition by a.grade_code order by b.score desc) ranking
from students a join scores b on a.student_id=b.student_id
where a.grade_code='S1' and b.subject='物理'
limit 10

select a.student_id,a.name,b.score,row_number()over(partition by a.grade_code order by score desc) rnk
from students a join scores b on a.student_id=b.student_id
where b.subject='物理' and a.grade_code='S1'
limit 10;

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

select left(name,1) surname ,count(1) cnt
from students
group by 1
order by cnt desc
limit 5;

select a.name,a.class_code,a.grade_code,b.name head_teacher_name
from students a join teachers b on a.class_code = b.head_teacher
order by student_id

select rcv_usr_id,sum(pkt_amt) sum_trx_amt
from tx_red_pkt_rcd 
where rcv_datetime != '1900-01-01 00:00:00'
group by rcv_usr_id
order by sum_trx_amt desc
limit 10

select rcv_usr_id,sum(pkt_amt) sum_trx_amt
from tx_red_pkt_rcd 
group by rcv_usr_id
order by sum_trx_amt desc
limit 10