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
order by total_score desc;

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
order by total_score;

select subject
from teachers
group by subject
having count(distinct 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(distinct teacher_id) as teacher_count
from teachers
group by teacher_type

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

select
	student_id,
max(score) as max_score,
min(score) as min_score,
avg(score) as avg_score
from scores a
where a.student_id in ('460093','735011')
	and a.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 
	name,
subject,
class_code,
enter_date
from teachers 
where year(enter_date) < 2010
	and qualification is null
order by enter_date asc;

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 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;

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

WITH student_total_scores AS (
    SELECT 
        s.class_code, 
        s.student_id, 
        SUM(sc.score) AS total_score
    FROM 
        students s
    JOIN 
        scores sc ON s.student_id = sc.student_id
    WHERE 
        sc.exam_date = '2024-06-30'
    GROUP BY 
        s.class_code, s.student_id
),
class_averages AS (
    SELECT 
        sts.class_code, 
        COUNT(DISTINCT sts.student_id) AS num_students, 
        SUM(sts.total_score) AS class_total_score
    FROM 
        student_total_scores sts
    GROUP BY 
        sts.class_code
)
SELECT 
    ca.class_code, 
    ca.num_students, 
    ROUND(ca.class_total_score / ca.num_students, 2) AS avg_score, 
    t.name AS head_teacher_name
FROM 
    class_averages ca
JOIN 
    teachers t ON t.class_code LIKE CONCAT('%', ca.class_code, '%')
WHERE 
    t.head_teacher = ca.class_code
ORDER BY 
    avg_score DESC

WITH total_students AS (
    SELECT 
        t.name AS teacher_name, 
        COUNT(DISTINCT s.student_id) AS total_students
    FROM 
        teachers t
    JOIN 
        students s ON t.class_code LIKE CONCAT('%', s.class_code, '%')
    WHERE 
        t.subject = '化学'
    GROUP BY 
        t.name
),
failed_students AS (
    SELECT 
        t.name AS teacher_name, 
        COUNT(DISTINCT s.student_id) AS failed_students
    FROM 
        teachers t
    JOIN 
        students s ON t.class_code LIKE CONCAT('%', s.class_code, '%')
    JOIN 
        scores sc ON s.student_id = sc.student_id
    WHERE 
        t.subject = '化学'
        AND sc.subject = '化学'
        AND sc.score < 60
    GROUP BY 
        t.name
)
SELECT 
    ts.teacher_name, 
    ts.total_students, 
    fs.failed_students, 
    ROUND((fs.failed_students / ts.total_students) * 100, 2) AS failure_rate
FROM 
    total_students ts
JOIN 
    failed_students fs ON ts.teacher_name = fs.teacher_name
ORDER BY 
    failure_rate DESC;

select
	a.class_code,
count(distinct a.student_id) as num_students_90_plus,
round(avg(b.score),2) as avg_score_90_plus,
c.name as physics_teacher
from students a
join scores b on a.student_id = b.student_id
join teachers c on c.class_code likeCONCAT('%', a.class_code, '%')
where b.exam_date = '2024-06-30'
		and b.subject = '物理'
and b.score >= 90
and c.subject = '物理'
group by a.class_code,c.name
order by avg_score_90_plus desc;

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

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

select
	a.class_code,
count(1) as num_students_90_plus,
round(avg(b.score),2) as avg_score_90_plus,
c.name as physics_teacher
from students a
join scores b on a.student_id = b.student_id
join teachers c on c.class_code likeCONCAT('%', a.class_code, '%')
where b.exam_date = '2024-06-30'
		and b.subject = '物理'
and b.score >= 90
group by a.class_code,c.name
order by avg_score_90_plus desc;

SELECT 
CASE 
WHEN sc.score >= 110 THEN '[110, 120]'
WHEN sc.score >= 90 THEN '[90, 110)'
WHEN sc.score >= 60 THEN '[60, 90)'
ELSE '[0, 60)'
END AS score_range,
COUNT(*) AS num_students
FROM 
students s
INNER JOIN
scores sc ON s.student_id = sc.student_id
WHERE 
sc.subject = '数学' 
AND sc.exam_date = '2024-06-30'
GROUP BY 
score_range
ORDER BY 
CASE score_range 
WHEN '[110, 120]' THEN 4
WHEN '[90, 110)' THEN 3
WHEN '[60, 90)' THEN 2
ELSE 1
END DESC;