网络热门sql50题，每天5道（2月17号）(1-5)

#SQL 01——查询"01"课程比"02"课程成绩高的学生的信息及课程分数

#1.使用自连接
#解释：在score表中，第一次拿出01的成绩为a表，
#     第二次拿出02的成绩为b表，通过s_id连接两张表
#     同理通过s_id连接student表和a表
select
    c.*,
    a.s_score,
    b.s_score
from
    score as a,score as b,student as c
where
    a.c_id = '01'
    and b.c_id = '02'
    and a.s_id = b.s_id
    and a.s_score > b.s_score
    and c.s_id = a.s_id;

#2.长型数据变成宽型数据
#解释：将每位同学的01，02课程取出放在一个表t中（长型数据变成宽型数据）,t表再去连接student表
select
    s.*,
    t.s01,
    t.s02
from
    (select
         a.s_id,
         max(case when a.c_id='01' then a.s_score else null end) as s01,
         max(case when a.c_id='02' then a.s_score else null end) as s02
     from
         score a
     group by
         a.s_id) as t,
    student as s
where
    t.s01 > t.s02
    and s.s_id = t.s_id;

#SQL 02——查询"01"课程比"02"课程成绩低的学生的信息及课程分数

#1.使用自连接
select
    c.*,
    a.s_id,
    a.s_score,
    b.s_score
from
    score as a,
    score as b,
    student as c
where
    a.c_id = '01'
    and b.c_id = '02'
    and a.s_id = b.s_id
    and a.s_score < b.s_score
    and c.s_id = a.s_id;

#2.长型数据变成宽型数据
select
    s.*,
    t.s01,
    t.s02
from
    (select
         a.s_id,
         max(case when a.c_id = '01' then a.s_score else null end) as s01,
         max(case when a.c_id = '02' then a.s_score else null end) as s02
     from
         score as a
     group by
         a.s_id) as t,
    student as s
where
    t.s01 < t.s02
    and s.s_id = t.s_id;

#SQL 03——查询平均成绩大于等于 60 分的同学的学生编号、学生姓名和平均成绩

select
    a.s_id,
    s.s_name,
    avg(a.s_score) as avg_score
from
    score as a,
    student as s
where
    a.s_id = s.s_id
group by
    a.s_id
having
    avg_score >= 60;

#SQL 04——查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩（包括有成绩的和无成绩的)

select
    a.s_id,
    s.s_name,
    avg(a.s_score) as avg_score
from
    score as a,
    student as s
where
    a.s_id = s.s_id
group by
    a.s_id
having
    avg_score <60;

#SQL 05——查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select
    b.s_id,
    b.s_name,
    count(a.c_id) as count_course,
    sum(a.s_score) as sum_score
from
    score as a
right join
    student as b
on
    a.s_id = b.s_id
group by
    b.s_id;