1. 数学成绩比语文成绩高的所有学生信息及数学、语文的成绩

-- 使用子查询
select
    x.*,
    a.course_name course_math,
    a.score math_score,
    b.course_name course_language ,
    b.score language_score
from
    t_student x
join 
(
    -- 所有学生的数学成绩
    select
        *
    from 
        t_score
    WHERE
        course_name = '数学'
) a
on 
    x.s_id = a.s_id
join (
    -- 所有学生的语文成绩
    select
        *
    from 
        t_score
    WHERE
        course_name = '语文'
) b 
on 
    a.s_id = b.s_id
and 
    b.score <= a.score
;

-- 2. 使用join
select
    x.*,
    y.course_name course_math,
    y.score math_score,
    z.course_name course_language ,
    z.score language_score
from
    t_student x
join 
    t_score y
ON
    x.s_id = y.s_id
and 
    course_name = '数学'
join 
    t_score z
on 
    z.course_name = '语文'
and 
    z.s_id = y.s_id 
and 
    z.score <= y.score
;

语文成绩比数学成绩好的

2. 每个学生的平均分、总分(学生ID、学生姓名、平均分（保留整数）、总分）,结果按平均分降序排列

select 
    b.s_id,
    b.s_name,
    round(avg(a.score),0) avg_score
from 
    t_score a
join 
    t_student b 
on 
    b.s_id = a.s_id
group by 
    b.s_id,
    b.s_name
order by 
    avg_score desc
;

平均分

3. 上一题的描述，加上平均分大于60分

-- 3. 平局分大于60分
select 
    b.s_id,
    b.s_name,
    round(avg(a.score),0) avg_score,
    sum(a.score) total_score
from 
    t_score a
join 
    t_student b 
on 
    b.s_id = a.s_id
group by 
    b.s_id,
    b.s_name
having avg_score>=60
order by 
    avg_score desc
;

平均分大于60分

4. 查询每个班级、每门课程的考试人数、平均分、总分

-- 查询每个班级的各科平局分
select 
    c.c_id,
    c.c_name,
    a.course_name,
    count(DISTINCT a.s_id) stu_num,
    round(avg(a.score),0) avg_score,
    sum(a.score) total_score
from t_score a 
join t_student b on b.s_id=a.s_id
join t_class c on c.c_id = b.c_id
group by 
    c.c_id,
    c.c_name,
    a.course_name
;

每个班级、课程的平均分

5. 查询英语和语文成绩都大于60的学生信息

select x.* from t_student x
join (
    select 
        a.s_id
    from 
        t_score a
    where 
        a.score > 60
    and 
        a.course_name in ('英语','语文')
    group by
        a.s_id 
    having
        count(1) = 2
) y 
on y.s_id=x.s_id
;