select 课程号,课程名称
from course
union
select 课程号,课程名称
from course_1

select 课程号,课程名称
from course
union all
select 课程号,课程名称
from course_1

select a.学号, a.姓名, b.课程号
from student as a inner join score as b
on a.学号 = b.学号

-- 左联结
select a.学号, a.姓名, b.课程号
from student as a left join score as b
on a.学号 = b.学号

-- 左联结去除和右边的共同部分
select a.学号, a.姓名, b.课程号
from student as a left join score as b
on a.学号 = b.学号
where b.学号 = null;

/*查询所有学生的学号、姓名、选课数、总成绩*/
select a.学号, a.姓名, count(b.课程号) as 选课数,sum(b.成绩) as 总成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号;

-- 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select a.学号, a.姓名, avg(b.成绩) as 平均成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号
having avg(b.成绩)>85;

-- 查询学生的选课情况：学号，姓名，课程号，课程名称
select a.学号, a.姓名, c.课程号, c.课程名称
from student as a inner join score as b
on a.学号 = b.学号
inner join course as c 
on b.课程号 = c.课程号;

-- 查询出每门课程的及格人数和不及格人数
select 课程号,
sum(case when 成绩 >= 60 then 1 else 0
end) as 及格人数,
sum(case when 成绩 < 60 then 1 else 0
end) as 不及格人数
from score
group by 课程号;

/*使用分段【100-85】，【85-70】，【70-60】，【<60】来统计各科成绩，分别统计：
各分段人数（成绩表score），课程号和课程名称（课程表course）*/
select b.课程号, a.课程名称,
sum(case when 成绩<60 then 1 else 0
end) as '[<60]',
sum(case when 成绩>=60 and 成绩<70 then 1 else 0
end) as '[70-60]',
sum(case when 成绩>=70 and 成绩<85 then 1 else 0
end) as '[85-70]',
sum(case when 成绩>=85 and 成绩<100 then 1 else 0
end) as '[100-85]'
from course as a left join score as b
on a.课程号 = b.课程号
group by a.课程号;