sql-50练习题11-15

前言

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

结果如下：