SQL50题解题思路 Q6-12
6. 查询学过「张三」老师授课的同学的信息
思路
首先从Teacher中找到张三老师的编号T#，然后从Course里找出对应编号T#教的课程编号C#，之后从SC中找出选修这些课程C#的学生编号S#，最后通过S#在Student表中得到信息。
代码
SELECT *
FROM Student
WHERE S# IN
(
SELECT S#
FROM SC
WHERE C# IN
(
SELECT C#
FROM Course
WHERE T# IN (SELECT T# FROM Teacher WHERE Tname = N'张三')
)
)7. 查询没有学全所有课程的同学的信息
思路
是否学全课程可以用选修课程的数量和课程总数对比判断。课程总数可以用select count(C#) 从Course中得到。
代码
SELECT *
FROM Student
WHERE S# IN
(
SELECT S#
FROM SC
GROUP BY S#
HAVING COUNT(C#) = (SELECT COUNT(C#) FROM Course)
)8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
思路
先从SC找到01同学学过的课程，然后再找出学过这些课程的同学。
代码
SELECT *
FROM Student
WHERE S# IN
(
SELECT S#
FROM SC
WHERE C# IN (SELECT C# FROM SC WHERE S#='01')
)9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
思路
有两种思路，常规思路大概是先找出课程数和01一样的学生，然后在里面排除掉选修过01没有选修的课程的学生。但是感觉写起来有点麻烦。所以用了另一种，把课程编号组合起来生成一个新的列，如选修过123三门课的同学就是010203，直接和01同学选修的课程对比就可以得到答案。
group_concat()可以直接组合字符串，但是SQL server里没有。所以就用for xml path代替了。xml用处是把数据输出为xml格式，path可以灵活的自定义格式实现一些骚操作。
代码
SELECT *
FROM Student
WHERE S# IN
(
SELECT S#
FROM
(
SELECT S#,
(SELECT C#+','
FROM SC c
WHERE c.S# = SC.S# AND S#!='01'
ORDER BY C#
FOR XML PATH('')
)C_tot
FROM SC
GROUP BY S#
)e
WHERE C_tot IN
(
SELECT C#+','
FROM SC c
WHERE S# ='01'
ORDER BY C#
FOR XML PATH('')
)
)10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
思路
查询没学过张三的课的学生等于先查询学过张三老师课的学生然后在所有学生里排除这些学生。学过张三老师课的学生在Q6里做过了。
代码
SELECT *
FROM Student
WHERE S# NOT IN
(
SELECT DISTINCT S#
FROM SC
WHERE C# IN
(
SELECT C#
FROM Course
WHERE T# =(SELECT T# FROM Teacher WHERE Tname = N'张三')
)
)11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
思路
首先找到两门及其以上不及格课程的同学的学号，不及格课程数可以通过where score<60得到。之后根据这些学号得到姓名和平均成绩。
代码
SELECT s.S#, s.Sname, c.avg_score
FROM Student s
JOIN
(
SELECT S#, AVG(score) avg_score
FROM SC
GROUP BY S#
)c ON s.S#=c.S#
WHERE s.S# IN
(
SELECT S#
FROM SC
WHERE score<60
GROUP BY S#
HAVING COUNT(score)>=2
)12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
思路
和上一题差不多，改一下条件加个排序就行。
代码
SELECT s.*, score
FROM Student s
JOIN
(
SELECT S#, score
FROM SC
WHERE C#='01' AND score<60
)c ON s.S#=c.S#
ORDER BY c.score DESC