sql-50练习题11-15
- 前言
- 数据库表结构介绍
- 学生表
- 课程表
- 成绩表
- 教师表
- 1-1 查询没有学全所有课程的同学的信息
- 1-2 查询至少有一门课与学号为'01'的同学所学相同的同学的信息
- 1-3 查询和'1'号的同学学习的课程完全相同的其他同学的信息
- 1-4 查询没学过'张三'老师讲授的任一门课程的学生姓名
- 1-5 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
前言
sql真的非常灵活，一个题目可能有很多很多种解法，我记录的只是我自己的一个解题思路，如果大家有更好的不同解法欢迎在评论区一起探讨
ps：有些题可能解法会重复，比如求大于，我们下一个题可能是求小于，大家如果第一遍没有做出来，看了我写的之后有了思路，可以试一下反面的解法。
数据库表结构介绍
学生表
课程表
成绩表
教师表
1-1 查询没有学全所有课程的同学的信息
需要的表结构：course，score，student
利用score中的成绩去裁剪表，score中有些同学只有两科成绩或者一科成绩这种，而学全所有课程的同学成绩有三科
SELECTb.*, count(a.s_id)
FROMscore a
RIGHT JOIN student b ON a.s_id = b.s_id
GROUP BYa.s_id
HAVINGcount(a.s_id) < (SELECTcount(c_id)FROMcourse)
ORDER BYb.s_id ASC;
结果如下：
1-2 查询至少有一门课与学号为’01’的同学所学相同的同学的信息
需要用到的表为：score和student
SELECT DISTINCTb.*
FROMscore a,student b
WHEREa.s_id = b.s_id
AND a.c_id IN (SELECTc.c_idFROMscore cWHEREc.s_id = '1'
)
结果如下：
1-3 查询和’1’号的同学学习的课程完全相同的其他同学的信息
select s2.s_id,student.s_name
from score as s1
join score as s2
on s1.c_id = s2.c_id
join student on student.s_id = s2.s_id
and s1.s_id = '01'
and s2.s_id != '01'
group by s2.s_id,student.s_id
having count(s2.c_id) = (select count(*) from score where s_id = '01')
结果如下：
1-4 查询没学过’张三’老师讲授的任一门课程的学生姓名
select a.s_name from student a where a.s_id not in (select s_id from score where c_id = (select c_id from course where t_id =(select t_id from teacher where t_name = '张三')) group by s_id);
1-5 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECTb.*, avg(a.s_score) agescore
FROMscore a,student b
WHEREa.s_id = b.s_id
GROUP BYa.s_id
HAVINGsum(CASEWHEN a.s_score >= 60 THEN0ELSE1END) >= 2
结果如下：
