SQL 经典题型解答(2)
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT a.*,b.C,b.score
from student a
INNER JOIN (SELECT a.* FROM
(SELECT * from sc WHERE sc.C IN ('01')) a
INNER JOIN (SELECT * FROM sc WHERE sc.C IN ('02')) b
ON a.s = b.s
WHERE a.score < b.score
)b
on a.s= b.s
详解：
只要修改
a.score > b.score为a.score < b.score即可，需要注意的是第一个INNER JOIN中必须有SELECT a.* FROM，用以形成一个表格。
INNER JOIN函数的语法：
>SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;
程序运行结果：
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT
a.s,
a.Sname,
b.avgscore
FROM
student AS a
INNER JOIN ( SELECT sc.s, avg( sc.score ) AS avgscore FROM sc GROUP BY sc.s ) AS b ON a.s = b.s
WHERE
b.avgscore > 60
详解：
首先通过表 sc 算出每个同学的平均成绩，然后再与表 student 连接，得出结果
求平均成绩的函数AVG()用来返回某一个数值列的平均值。
GROUP BY语句用于结合结合函数，根据一个或多个列队结果进行分组计算
GROUP BY语句在程序中表示根据 sc 表的 s 列求平均值，即求每个同学的平均成绩.
程序运行结果：
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
SELECT
a.s,
a.Sname,
b.avgscore
FROM
student AS a
INNER JOIN ( SELECT sc.s, avg( sc.score ) AS avgscore FROM sc GROUP BY sc.s ) AS b ON a.s = b.s
WHERE
b.avgscore < 60
详解：
修改最后的
b.avgscore > 60为b.avgscore < 60即可。
程序运行结果：
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT a.s,a.sname,b.sumscore,b.countcourse
from student a
INNER JOIN (SELECT s,sum(score) as sumscore, count(score) as countcourse from sc GROUP BY s) b
on a.s = b.s
详解：
首先先从表 sc 中求出每个同学的总成绩和选课总数，然后再和 student 表连接得出结果
sum()函数返回某一列数值的总和。
count()函数返回某一列的行数。
通过这两个函数结合GROUP BY语句，即可求出选课总数和课程总成绩。
程序运行结果：
6、 查询 "李" 姓老师的数量
SELECT COUNT(tname) FROM teacher
WHERE Tname LIKE '李%'
详解:
通过
count()函数对表 teacher 中的 tname 列操作，通过WHERE语句限制条件，
LIKE语句用法 菜鸟教程 --- LIKE 操作符，SQL 通配符
程序运行结果：
7、查询学过 "张三" 老师授课的同学的信息
SELECT
a.*
FROM
student a
INNER JOIN (
SELECT
a.s
FROM
sc a
INNER JOIN (
SELECT
b.C
FROM
teacher a
INNER JOIN course b ON a.T = b.T
AND a.Tname IN ( '张三' )
) b ON a.c = b.C
) b ON a.s = b.s
详解:
首先连接表 teacher 和表 course 得到 “张三”老师的授课编号 C ,代码:
SELECT a.*,b.Cname,b.C FROM teacher a
INNER JOIN course b
ON a.T = b.T AND a.Tname IN ('张三')
然后连接表 sc 得到学习课程 C 的学生编号，最后连接表 student 得到学生信息。
程序运行结果：
8、查询没学过 "张三" 老师授课的同学的信息
SELECT
a.*
FROM
student a
WHERE
a.S NOT IN (
SELECT
a.s
FROM
sc a
INNER JOIN (
SELECT
b.C
FROM
teacher a
INNER JOIN course b ON a.T = b.T
AND a.Tname IN ( '张三' )
) b ON a.c = b.C
)N a.c = b.C
)
详解：
和上一题一样首先找到学过 “张三” 老师课程的学生编号，然后从表 student 中选出学生编号
NOT IN这些编号中的学生信息。
程序运行结果:
9、查询学过编号为 "01" 并且也学过编号为 "02" 的课程的同学的信息
- 解法一
SELECT a.* FROM student a
INNER JOIN
(SELECT s FROM sc WHERE c = '01') b
ON a.s = b.s
INNER JOIN
(SELECT s FROM sc WHERE c = '02') c
ON a.s = c.s
详解：
表 b 表示从表 sc 中选出学习课程 “01”的学生编号，表 c 表示从表 sc 中选出学习课程 “02” 的学生编号，通过
INNER JOIN函数将表 student 与 b ,c 连接，即可得到最终结果。
程序运行结果：
- 解法二：
SELECT
c.*
FROM
sc AS a,
sc AS b,
student AS c
WHERE
a.S = b.S
AND a.C = '01'
AND b.C = '02'
AND c.S = a.S
详解：
> 直接通过 WHERE 语句进行条件判断。
程序运行结果：
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT
a.*
FROM
student a
INNER JOIN ( SELECT s FROM sc WHERE C = '01' AND s NOT IN ( SELECT s FROM sc WHERE c = '02' ) ) b ON a.s = b.s
详解：
首先查找学过编号为 “02” 课程的学生编号 ,然后查找学过编号为 “01” 课程的学生编号，通过
NOT IN语句找到不在学过 “02” 课程的学生编号，最后与表 student 连接找到学生信息。
程序运行结果：
部分答案参考自：SQL 经典五十道题
