SQL经典练习（7~10）
2019-04-08 本文已影响0人
蜗牛登塔尖
-
查询没有学全所有课程的同学的信息
分析：课程表总一共有3个课程，分别是01语文，02数学，03英语。存在有的同学只学了其中两门或者一门的课程的情况。想到用聚集函数COUNT对成绩表分组之后的行数记录，小于3的就是没有学全所有课程的，再根据查询出的学号在学生表中查找出详细信息。
方法一（子查询）：
SELECT s.*
FROM students s
WHERE s.`S#` IN (SELECT `S#`
FROM SC
GROUP BY `S#`
HAVING COUNT(*)<3);
方法二（联结表）：
SELECT s.*
FROM students s,(SELECT `S#` FROM SC GROUP BY `S#` HAVING COUNT(*)<3) AS t
WHERE s.`S#` = t.`S#`;
查询结果
-
查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
分析：问题分解，首先在成绩表中查询出01号同学所学的课程编号，其次在成绩表中，查出包含在01号同学课程编号中的学生编号，最后，再利用学生编号在学生信息表中返回学生的详细信息。因此嵌套两层子查询。
方法一（子查询）:
SELECT s.*
FROM students s
WHERE s.`S#` IN (SELECT `S#`
FROM SC
WHERE `C#` IN (SELECT `C#`
FROM SC
WHERE `S#` = '01'));
查询结果
方法二（自联结）:
分析：最开始两步查询都是在成绩表进行的，让我想起了表的“自联结”，就是复制一张与自身相同的表，把它当做另外一张表，再利用联结条件联结，这里要注意用别名将两个表区别开，不然系统就会因为无法判断列的归属导致报错。
SELECT s.*
FROM students s
WHERE s.`S#` IN (SELECT p1.`S#`
FROM SC p1,SC p2
WHERE p1.`C#` = p2.`C#`AND p2.`S#` = '01');
上面代码中给SC表分别起了两个别名p1和p2，相当于复制了一个相同的表，它们之间利用课程编号联结，在p2表中给出过滤条件，从p1表中查询出所需要的学生编号。得出的结果是一致的。
注：由于联结查询比子查询速度更快，使得查询性能提高，因此能使用联结尽量使用。
结果一致
-
查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
分析：已知01号同学三门课程（01,02,03）都学习了，因此只要从其他同学中找出（至少）同时学了这三门课程的同学编号。（这里用“至少”是考虑到其他同学有可能不但学了这三门课程，还学习了其他课程，但其实我们只要保证这三门课程都学习了，就满足题目要求。如果把题目要求理解成不多也不少的也学了这三门课程，那还要加上一个筛选条件，比如学习的课程数等于3。）
把课程表中学过01课程，02课程，03课程的分别建立虚拟表，这三个表中，有些同学三门课程都学习了，有些同学只学习了两门，甚至只学习了一门，如果用学号将三表联结，得出的新表（虚拟表）就是三门课程都学习过的学生。因此
SELECT s.*
FROM students AS s
WHERE `S#` IN (SELECT SC_01.`S#`
FROM (SELECT `S#` FROM SC WHERE `C#` = '01')AS SC_01,
(SELECT `S#` FROM SC WHERE `C#` = '02')AS SC_02,
(SELECT `S#` FROM SC WHERE `C#` = '03')AS SC_03
WHERE SC_01.`S#` = SC_02.`S#` AND SC_02.`S#` = SC_03.`S#`)
AND `S#` <> '01';
查询结果
-
查询没学过"张三"老师讲授的任一门课程的学生姓名
分析：其中一个一般想到用IN，包含在其中。任意一个都没有，就要用NOT IN，可利用子查询，也可利用表联结查询。
SELECT s.Sname
FROM students AS s
WHERE s.`S#` NOT IN (SELECT SC.`S#`
FROM SC,courses,teachers AS t
WHERE t.`T#` = courses.`T#` AND courses.`C#` = SC.`C#` AND t.Tname = "张三");
查询结果
