50道SQL经典习题与答案解析

最新推荐文章于 2025-05-23 18:35:17 发布

alanmoneyman

最新推荐文章于 2025-05-23 18:35:17 发布

阅读量9.4k

点赞数 3

CC 4.0 BY-SA版权

分类专栏： SQL 文章标签： SQL

本文链接：https://blog.youkuaiyun.com/alanmoneyman/article/details/99673804

SQL 专栏收录该内容

2 篇文章

订阅专栏

本文提供了50道SQL题目，涵盖数据查询、联接、统计等多个方面，如课程成绩比较、学生选课情况、教师信息、成绩排名等。每个题目都有详细的解答，涉及PostgreSQL验证的SQL语句。通过这些习题，读者可以深入理解SQL在实际问题中的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

题目来源自网络，SQL语句是自己写的。所有语句均在PostgreSQL进行了验证。不足之处，欢迎指正。

数据表和字段

学生表 Student
字段含义分别为：学生编号,学生姓名, 出生年月,学生性别

Sid	Sname	Sage	Ssex
01	赵雷	1990-01-01	男
02	钱电	1990-12-21	男
03	孙风	1990-12-20	男
04	李云	1990-12-06	男
05	周梅	1991-12-01	女
06	吴兰	1992-01-01	女
07	郑竹	1989-01-01	女
09	张三	2017-12-20	女
10	李四	2017-12-25	女
11	李四	2012-06-06	女
12	赵六	2013-06-13	女
13	孙七	2014-06-01	女

课程表Course
字段含义分别为：课程编号，课程名称，教师编号

Cid	Cname	Tid
01	语文	02
02	数学	01
03	英语	03

教师表Teacher
字段含义分别为：教师编号，教师姓名

Tid	Tname
01	张三
02	李四
03	王五

成绩表SC
字段含义分别为：学生编号，课程编号，分数

Sid	Cid	Score
01	01	80
01	02	90
01	03	99
02	01	70
02	02	60
02	03	80
03	01	80
03	02	80
03	03	80
04	01	50
04	02	30
04	03	20
05	01	76
05	02	87
06	01	31
06	03	34
07	02	89
07	03	98

题目

1.查询" 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
2.查询同时存在” 01 “课程和” 02 “课程的情况
3.查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )
4.查询不存在” 01 “课程但存在” 02 “课程的情况
5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
6.查询在 SC 表存在成绩的学生信息
7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
7.1.查有成绩的学生信息
8.查询「李」姓老师的数量
9.查询学过「张三」老师授课的同学的信息
10.查询没有学全所有课程的同学的信息
11.查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息
12.查询和” 01 “号的同学学习的课程完全相同的其他同学的信息
13.查询没学过"张三"老师讲授的任一门课程的学生姓名
14.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
15.检索” 01 "课程分数小于 60，按分数降序排列的学生信息
16.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
17.查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
18.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
19.按各科成绩进行排序，并显示排名， Score 重复时合并名次
20.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
21.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
22.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
23.查询各科成绩前三名的记录
24.查询每门课程被选修的学生数
25.查询出只选修两门课程的学生学号和姓名
26.查询男生、女生人数
27.查询名字中含有「风」字的学生信息
28.查询同名同性学生名单，并统计同名人数
29.查询 1990 年出生的学生名单
30.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
31.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
32.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
33.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
34.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
35.查询不及格的课程
36.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
37.求每门课程的学生人数
38.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
39.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
40.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
41.查询每门功成绩最好的前两名
42.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
43.检索至少选修两门课程的学生学号
44.查询选修了全部课程的学生信息
45.查询各学生的年龄，只按年份来算
46.按照出生日期来算，当前月日 < 出生年月的月日,则年龄减一
47.查询本周过生日的学生
48.查询下周过生日的学生
49.查询本月过生日的学生
50.查询下月过生日的学生

录入数据

--创建表格 Student学生
CREATE TABLE Student 
(Sid       CHAR(2)      NOT NULL,
Sname      VARCHAR(4),
Sage       DATE,
Ssex       VARCHAR(4),
PRIMARY KEY(Sid));
--向Student 插入数据
BEGIN TRANSACTION;
INSERT INTO Student VALUES ('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO Student VALUES ('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO Student VALUES ('03' , '孙风' , '1990-12-20' , '男');
INSERT INTO Student VALUES ('04' , '李云' , '1990-12-06' , '男');
INSERT INTO Student VALUES ('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO Student VALUES ('06' , '吴兰' , '1992-01-01' , '女');
INSERT INTO Student VALUES ('07' , '郑竹' , '1989-01-01' , '女');
INSERT INTO Student VALUES ('09' , '张三' , '2017-12-20' , '女');
INSERT INTO Student VALUES ('10' , '李四' , '2017-12-25' , '女');
INSERT INTO Student VALUES ('11' , '李四' , '2012-06-06' , '女');
INSERT INTO Student VALUES ('12' , '赵六' , '2013-06-13' , '女');
INSERT INTO Student VALUES ('13' , '孙七' , '2014-06-01' , '女');
COMMIT;

--创建表格 Course课程表
CREATE TABLE Course 
(Cid    CHAR(2)      NOT NULL,
Cname   VARCHAR(4),
Tid    CHAR(2),
PRIMARY KEY (Cid));
--向Course插入数据
BEGIN TRANSACTION;
INSERT INTO Course VALUES ('01', '语文', '02');
INSERT INTO Course VALUES ('02', '数学', '01');
INSERT INTO Course VALUES ('03', '英语', '03');
COMMIT;

--创建表格 Teacher教师表
CREATE TABLE Teacher 
(Tid     CHAR (2)     NOT NULL,
Tname    VARCHAR(4),
PRIMARY KEY(Tid));
--向Teacher插入数据
BEGIN TRANSACTION;
INSERT INTO Teacher VALUES ('01','张三');
INSERT INTO Teacher VALUES ('02','李四');
INSERT INTO Teacher VALUES ('03','王五');
COMMIT;

--创建表格 SC成绩表
CREATE TABLE SC 
(Sid   CHAR(2)      NOT NULL,
Cid    CHAR(2)      NOT NULL,
Score  INTEGER,
PRIMARY KEY(Sid, Cid));
--向SC插入数据
BEGIN TRANSACTION;
INSERT INTO SC VALUES ('01', '01', 80);
INSERT INTO SC VALUES ('01', '02', 90);
INSERT INTO SC VALUES ('01', '03', 99);
INSERT INTO SC VALUES ('02', '01', 70);
INSERT INTO SC VALUES ('02', '02', 60);
INSERT INTO SC VALUES ('02', '03', 80);
INSERT INTO SC VALUES ('03', '01', 80);
INSERT INTO SC VALUES ('03', '02', 80);
INSERT INTO SC VALUES ('03', '03', 80);
INSERT INTO SC VALUES ('04', '01', 50);
INSERT INTO SC VALUES ('04', '02', 30);
INSERT INTO SC VALUES ('04', '03', 20);
INSERT INTO SC VALUES ('05', '01', 76);
INSERT INTO SC VALUES ('05', '02', 87);
INSERT INTO SC VALUES ('06', '01', 31);
INSERT INTO SC VALUES ('06', '03', 34);
INSERT INTO SC VALUES ('07', '02', 89);
INSERT INTO SC VALUES ('07', '03', 98);
COMMIT;

答案

1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数
从SC表中选取Cid='01’的记录，再选取Cid='02’的记录，用INNER JOIN连结成一个表，用WHERE选择"01"课程比"02"课程成绩高的记录。

SELECT C1.sid, C3.sname, C3.sage, C3.ssex, C1.score AS score1, 
	C2.score AS score2 
FROM (SELECT * FROM SC WHERE Cid = '01') AS C1 
	INNER JOIN (SELECT * FROM SC WHERE Cid = '02') AS C2 
ON C1.sid = C2.sid 
	INNER JOIN Student AS C3 
    ON C1.sid = C3.sid 
WHERE C1.score > C2.score;

2.查询同时存在" 01 “课程和” 02 "课程的情况
跟上一题差不多，把WHERE中的条件改成不等于或等于。

SELECT C1.sid, C3.sname, C3.sage, C3.ssex, C1.score AS score1, 
	C2.score AS score2 
FROM (SELECT * FROM SC WHERE Cid = '01') AS C1 
	INNER JOIN (SELECT * FROM SC WHERE Cid = '02') AS C2 
ON C1.sid = C2.sid 
	INNER JOIN Student AS C3 
    ON C1.sid = C3.sid 
WHERE C1.score <> C2.score OR C1.score = C2.score;

3.查询存在" 01 “课程但可能不存在” 02 “课程的情况 (不存在时显示为null)
与第一题相似，换乘左连结，这样是有"01"课程但没有” 02 "课程的记录也被选出来了。还要用COALESCE函数，变空值为字符串’NULL’。

SELECT C1.sid, C3.sname, C3.sage, C3.ssex, C1.score AS score1, 
	COALESCE(CAST(C2.score AS VARCHAR(4)), 'NULL') AS score2 
FROM (SELECT * FROM SC WHERE Cid = '01') AS C1 
	LEFT JOIN (SELECT * FROM SC WHERE Cid = '02') AS C2 
ON C1.sid = C2.sid 
	INNER JOIN Student AS C3 
    ON C1.sid = C3.sid;

4.查询不存在" 01 “课程但存在” 02 "课程的情况
把上一题的左连结改成右连结，并用WHERE选取出"01"成绩为空值的记录。

SELECT C2.sid, C3.sname, C3.sage, C3.ssex, 
	C1.score AS score1, C2.score AS score2 
FROM (SELECT * FROM SC WHERE Cid = '01') AS C1 
	RIGHT JOIN (SELECT * FROM SC WHERE Cid = '02') AS C2 
ON C1.sid = C2.sid 
      INNER JOIN Student AS C3 
      ON C2.sid = C3.sid 
WHERE C1.score IS NULL;

5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
先用子查询从SC表中选取平均成绩大于等于60分的Sid 和平均成绩，再与Student表连结。

SELECT S1.sid, S1.sname, S2.avg AS 平均成绩 
FROM Student AS S1 INNER JOIN (
SELECT Sid, AVG(Score) AS avg 
FROM SC 
GROUP BY Sid 
HAVING AVG(Score) >= 60) AS S2 
ON S1.Sid = S2.Sid;

6.查询在 SC 表存在成绩的学生信息
从SC表中选出去除重复的Sid，要用到DISTINCT。表连结Student表，选取其中学生信息的字段。

SELECT DISTINCT(SC.Sid), Student.sname, Student.sage, Student.ssex 
FROM SC LEFT JOIN Student 
ON SC.Sid = Student.Sid;

7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)
用COUNT计算选课数，用SUM计算总成绩，用COALESCE将空值转换为字符串’NULL’。

SELECT S2.sid, S2.sname, COUNT(S1.Sid) AS 选课总数, 
	COALESCE(CAST(SUM(score) AS VARCHAR(4)), 'NULL') AS 总成绩 
FROM SC AS S1 RIGHT OUTER JOIN Student AS S2 
ON S1.Sid = S2.Sid 
GROUP BY S2.sid, S2.sname;

7.1.查有成绩的学生信息

SELECT * 
FROM Student 
WHERE Sid IN (SELECT Sid FROM SC);

8.查询「李」姓老师的数量
使用LIKE谓词，'李%'表示以“李”字开头的字符串。

SELECT COUNT(*) AS 李姓老师数量 
FROM (SELECT * FROM Teacher WHERE Tname LIKE '李%') as TeacherLi;

9.查询学过「张三」老师授课的同学的信息
这一题需要对所有的4张表进行连结，清楚其中的连结键就不难。

SELECT S2.Sid, S2.Sname, S2.Sage, S2.Ssex, T.Tname 
FROM Teacher AS T INNER JOIN Course AS C 
ON T.Tid = C.Tid 
	INNER JOIN SC AS S1 
	ON S1.Cid = C.Cid 
		INNER JOIN Student AS S2 
		ON S2.Sid = S1.Sid 
WHERE tname = '张三';

10.查询没有学全所有课程的同学的信息
子查询里是所有课程的数量，所选课程小于所有课程的就是没有学全的同学。

SELECT S2.sid, S2.sname, S2.Sage, S2.Ssex, COUNT(S1.Sid) AS 选课总数 
FROM SC AS S1 RIGHT OUTER JOIN Student AS S2 
ON S1.Sid = S2.Sid 
GROUP BY S2.sid, S2.sname 
HAVING COUNT(S1.Sid) < (SELECT COUNT(DISTINCT Cid) FROM Course);

11.查询至少有一门课与学号为’01’的同学所学相同的同学的信息
最内层的子查询是学号为’01’的同学所学的课程，WHERE子句中同时还排除了学号为’01’的同学。

SELECT S2.Sid, S2.Sname, S2.Sage, S2.Ssex 
FROM Student AS S2 INNER JOIN ( 
SELECT Sid, Cid 
FROM SC 
WHERE Cid IN( 
SELECT Cid 
FROM SC 
WHERE Sid = '01') AND Sid <> '01') AS Q 
ON Q.sid = S2.Sid 
GROUP BY S2.Sid, S2.Sname, S2.Sage, S2.Ssex;

12.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
思路：满足两点就可以保证选课完全一样：①’01’号同学没选的课程一定不能选；②和01号学生选课的数量相同。

SELECT S.Sid, S.Sname, S.Sage, S.Ssex 
FROM Student AS S INNER JOIN (SELECT Sid 
FROM SC 
GROUP BY Sid 
HAVING sid <> '01' AND 
COUNT(*) = (SELECT COUNT(*) 
FROM SC 
WHERE sid = '01') 
EXCEPT 
SELECT Sid 
FROM SC 
WHERE Cid IN (SELECT Cid 
FROM SC 
WHERE Cid NOT IN (SELECT Cid 
FROM SC 
WHERE Sid = '01'))) AS S1 
ON S.Sid = S1.Sid;

13.查询没学过"张三"老师讲授的任一门课程的学生姓名
用三层子查询相互嵌套，最内层的选出’张三’老师的教师编号，第二层的子查询选出’张三’老师教的课程编号，最外层的子查询选出选了’张三’老师的课的学生编号。

SELECT * 
FROM Student 
WHERE Sid NOT IN ( 
SELECT Sid 
FROM SC 
WHERE Cid = (SELECT Cid 
FROM Course 
WHERE Tid = (SELECT Tid 
FROM Teacher 
WHERE Tname = '张三')))

14.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
S1是两门及其以上不及格的同学的学号，S1与Student连结定义为S2，S2是S1里人员的姓名和学号，S3是所有有成绩的人的平均分，最后S2与S3连结。

SELECT S2.Sid, S2.Sname, S3.平均分 FROM 
(SELECT Student.Sid, Student.Sname FROM Student 
	INNER JOIN 
    (SELECT Sid, COUNT(*) 
    	FROM SC 
    	WHERE Score < 60 
    	GROUP BY Sid 
    	HAVING COUNT(*) >1) AS S1 
	ON S1.Sid = Student.Sid) AS S2 
INNER JOIN 
(SELECT Sid, AVG(Score) AS 平均分 
	FROM SC 
	GROUP BY Sid) AS S3 
ON S2.Sid = S3.Sid;

15.检索" 01 “课程分数小于 60，按分数降序排列的学生信息
A1选出了” 01 "课程分数小于 60的记录，再与Student表连结补全姓名、年龄、性别等字段。

SELECT S.Sid, S.sname, S.sage, S.ssex, score 
FROM 
(SELECT * 
FROM SC 
WHERE Cid = '01' AND Score < 60) AS A1 
INNER JOIN Student AS S 
ON A1.Sid = S.Sid 
ORDER BY score DESC;

16.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
题目要求暗含着没有成绩的学生也要出现在查询结果中。语句这样写，结果中行几行分别为学号、平均成绩、01科成绩、02科成绩、03科成绩。用CASE表达式，如果是指定的科目就计算该科的成绩，否则是空值。NULLS LAST让所有科目都没成绩的学生在排最后面。

SELECT S.Sid, B.平均成绩, 
	SUM(CASE WHEN Cid = '01' 
		THEN Score ELSE NULL END) AS 成绩01, 
	SUM(CASE WHEN Cid = '02' 
		THEN Score ELSE NULL END) AS 成绩02, 
	SUM(CASE WHEN Cid = '03' 
		THEN Score ELSE NULL END) AS 成绩03 
FROM SC INNER JOIN (SELECT Sid, AVG(score) AS 平均成绩 
FROM SC 
GROUP BY Sid) AS B 
ON B.Sid = SC.Sid 
RIGHT OUTER JOIN Student AS S 
ON S.Sid = SC.Sid 
GROUP BY S.Sid, B.平均成绩 
ORDER BY B.平均成绩 DESC NULLS LAST;

17.查询各科成绩最高分、最低分和平均分，及格率，中等率，优良率，优秀率
用SUM和CASE的组合，若果分数落在指定范围计1，否则计0，再除以总数量就是响应的比率。

SELECT C.Cid, C.Cname, MAX(score) AS 最高分, 
	MIN(score) AS 最低分, AVG(score) AS 平均分,
SUM(CASE WHEN Score>= 60 THEN 1 ELSE 0 END)  * 100 / COUNT(*) AS 及格率, 
SUM(CASE WHEN Score BETWEEN 70 AND 80 THEN 1 ELSE 0 END) * 100/COUNT(*) AS 中等率, 
SUM(CASE WHEN Score BETWEEN 80 AND 90 THEN 1 ELSE 0 END) * 100/COUNT(*) AS 优良率, 
SUM(CASE WHEN Score >= 90 THEN 1 ELSE 0 END) * 100 / COUNT(*)  AS 优秀率 
FROM SC INNER JOIN Course AS C 
   ON C.Cid = SC.Cid 
GROUP BY C.Cid;

18.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
这里用RANK函数排名，用PARTITION BY进行分组。RANK函数计算是，如果有相同位次的记录，则会跳过之后的位次。

SELECT Sid, Cid, Score, 
	RANK() OVER (PARTITION BY Cid ORDER BY Score DESC) AS ranking 
FROM SC;

19.按各科成绩进行排序，并显示排名， Score 重复时合并名次
与上一题相似，改为DENSE_RANK函数，即使有相同位次的记录，也不会跳过之后的位次。

SELECT Sid, Cid, Score, 
	DENSE_RANK() OVER (PARTITION BY Cid ORDER BY Score DESC) AS dense_ranking 
FROM SC;

20.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
用RANK排名，这里不指定PARTITION BY。

SELECT Sid, SUM(Score), 
	RANK () OVER (ORDER BY SUM(Score) DESC) AS ranking 
FROM SC 
GROUP BY Sid;

21.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
用DENSE_RANK不保留名次空缺

SELECT Sid, SUM(Score), 
	DENSE_RANK () OVER (ORDER BY SUM(Score) DESC) AS dense_ranking 
FROM SC 
GROUP BY Sid;

22.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
求比率的方法，与第18题相似。

SELECT Cid, 
COUNT(*) AS 选课人数, 
SUM(CASE WHEN Score>= 85 THEN 1 ELSE 0 END) * 100 / COUNT(*) AS 分数85_100比例, 
SUM(CASE WHEN Score BETWEEN  70 AND 85 THEN 1 ELSE 0 END) * 100 / 
	COUNT(*) AS 分数70_85比例, 
SUM(CASE WHEN Score BETWEEN  60 AND 70 THEN 1 ELSE 0 END) * 100 / 
	COUNT(*) AS 分数60_70比例, 
SUM(CASE WHEN Score BETWEEN  0 AND 60 THEN 1 ELSE 0 END) * 100 / 
	COUNT(*) AS 分数0_60比例 
FROM SC 
GROUP BY Cid;

23.查询各科成绩前三名的记录
跟第19题相关。直接用第19题的语句作为子查询，在其结果的基础上挑选ranking为1, 2, 3的记录。

SELECT *
FROM( 
SELECT Sid, Cid, Score, 
	RANK() OVER (PARTITION BY Cid ORDER BY Score DESC) AS ranking 
FROM SC 
GROUP BY Sid, Cid) AS A 
WHERE A.ranking IN (1, 2, 3);

24.查询每门课程被选修的学生数

SELECT Cid, COUNT(*) AS 选课人数 
FROM SC 
GROUP BY Cid;

25.查询出只选修两门课程的学生学号和姓名
A是学生ID和对应的选课数，与Student连结，选择学号和姓名，限制选课数=2

SELECT S.Sid, S.Sname, A.选课数 
FROM( 
SELECT Sid, COUNT(*) AS 选课数 
FROM SC 
GROUP BY Sid) AS A 
INNER JOIN Student AS S ON A.Sid = S.Sid 
WHERE A.选课数 = 2;

26.查询男生、女生人数
按性别分组并计数就可以了。

SELECT Ssex, COUNT(*) AS 人数 
FROM Student 
GROUP BY Ssex;

27.查询名字中含有「风」字的学生信息
'%风%'可以选出「风」字在开头、中间、结尾的所有位置。

SELECT * 
FROM Student 
WHERE Sname LIKE '%风%';

28.查询同名同性学生名单，并统计同名人数
对姓名分组计数，结果大于1的就是出现重名的名字。

SELECT Sname, COUNT(*) AS 同名人数 
FROM Student 
GROUP BY Sname 
HAVING COUNT(*) > 1;

29.查询1990年出生的学生名单
用EXTRAXCT从日期中截取年，并选取年份是1990年的记录。

SELECT Sid, Sname, Sage, Ssex, EXTRACT(YEAR FROM Sage) AS year 
FROM Student 
WHERE EXTRACT(YEAR FROM Sage) = 1990;

30.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
DESC为降序，默认为升序。

SELECT C.Cname, C.Cid, AVG(SC.Score) 
FROM SC INNER JOIN Course AS C 
   ON SC.Cid = C.Cid 
GROUP BY C.Cid 
ORDER BY AVG(SC.Score) DESC, C.Cid;

31.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
连结Student和SC两张表。按Sid分组并用AVG聚合，指定平均值>=85。

SELECT S.Sid, S.Sname, AVG(SC.Score) 
FROM SC INNER JOIN Student AS S 
   ON SC.Sid = S.Sid 
GROUP BY S.Sid 
HAVING AVG(SC.Score) >= 85;

32.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
也是要连结Student和SC两张表，两个条件用AND关联。

SELECT S.Sname, C.Cname, SC.Score 
FROM Student AS S INNER JOIN SC 
	ON S.Sid = SC.Sid 
INNER JOIN Course AS C 
	ON SC.Cid = C.Cid 
WHERE C.Cname = '数学' AND SC.Score < 60;

33.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
要把Student表作为主表，Student表放在左边时用左连结。

SELECT S.Sid, S.Sname, C.Cname, SC.Score 
FROM Student AS S LEFT OUTER JOIN SC 
	ON S.Sid = SC.Sid 
LEFT OUTER JOIN Course AS C 
	ON SC.Cid = C.Cid;

34.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
要讲Student，SC，Course三个表连结，并且Score > 70。

SELECT S.Sid, S.Sname, C.Cname, SC.Score 
FROM Student AS S LEFT OUTER JOIN SC 
	ON S.Sid = SC.Sid 
INNER JOIN Course AS C 
	ON SC.Cid = C.Cid 
WHERE SC.Score > 70;

35.查询存在不及格的课程

SELECT Cid
FROM SC 
WHERE Score < 60
GROUP BY Cid;

36.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
Student和SC表连结，查询结果有0行记录，因为没人在01科目获得80分以上的成绩。

SELECT S.Sid, S.Sname, SC.Cid, SC.Score
FROM Student AS S INNER JOIN SC
	ON SC.Sid = S.Sid
WHERE SC.Score > 80 AND SC.Cid = '01';

37.求每门课程的学生人数

SELECT C.Cid, C.Cname, COUNT(*) AS 选课人数
FROM SC INNER JOIN Course AS C
	ON SC.Cid = C.Cid
GROUP BY C.Cid;

38.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
要连结所有4个表，用ORDER BY排序后，用LIMIT 1限定只显示第一行记录。

SELECT S.Sid, S.Sname, S.sage, S.Ssex, SC.Cid, SC.Score, T.Tname
FROM Student AS S INNER JOIN SC
	ON S.Sid = SC.Sid
INNER JOIN Course
	ON Course.Cid = SC.Cid
INNER JOIN Teacher AS T
	ON Course.Tid = T.Tid
WHERE T.Tname = '张三'
ORDER BY SC.Score DESC
LIMIT 1;

39.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
方法1：选出「张三」老师所授课程的最高成绩是多少分，若果在成绩有重复的情况下（并列），这门课程与这个分数相等的都是成绩最高的。
子查询比较多，最内层选出了「张三」老师的教师编号，然后再外面一层选出了「张三」教的课程编号，再外面一层选出了这门课的最高分数，这个分数作为一个标量子查询的结果。AND前面的子查询也是一个标量，是「张三」教的课程编号。同时满足两个条件，就能选出所有成绩最高学生的信息与成绩。

SELECT S.Sid, S.Sname, S.sage, S.Ssex, SC.Cid, SC.Score
FROM SC INNER JOIN Student AS S ON SC.Sid = S.Sid
WHERE SC.Cid = (
	SELECT Cid
	FROM Course
	WHERE Tid = (
		SELECT Tid
		FROM Teacher
		WHERE Tname = '张三')) AND SC.Score = (
			SELECT MAX(SC.Score)
			FROM SC INNER JOIN Student AS S ON SC.Sid = S.Sid
			WHERE SC.Cid = (
				SELECT Cid
				FROM Course
				WHERE Tid = (
					SELECT Tid
					FROM Teacher
					WHERE Tname = '张三')));

方法2：
这个方法与上一题相似，额外需要对成绩进行排序，排名作为ranking列。在把这个结果作为子查询，在如果有并列的情况下，选出ranking=1的所有记录。窗口函数不能再WHERE或者GROUP BY 字句中使用，所有不能直接写WHERE T.Tname = ‘张三’ AND ranking = 1.

SELECT * 
FROM(
	SELECT S.Sid, S.Sname, S.sage, S.Ssex, SC.Cid, SC.Score, T.Tname, 
	    RANK() OVER(ORDER BY SC.Score DESC) AS ranking
	FROM Student AS S INNER JOIN SC
		ON S.Sid = SC.Sid
	INNER JOIN Course
		ON Course.Cid = SC.Cid
	INNER JOIN Teacher AS T
		ON Course.Tid = T.Tid
	WHERE T.Tname = '张三') AS P
WHERE ranking = 1;

40.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
按Score分数分组计数，出现次数大于1的就是重复的成绩。

SELECT*
FROM SC
WHERE Score IN (
	SELECT Score
	FROM SC
	GROUP BY Score
	HAVING COUNT(*) > 1)

41.查询每门功成绩最好的前两名
子查询A是对每门功课的成绩排名，然后选rangking是1和2的。

SELECT * 
FROM (
	SELECT Sid, Cid, Score, 
	RANK() OVER (PARTITION BY Cid ORDER BY Score DESC) AS ranking
	FROM SC) AS A
WHERE ranking IN (1, 2);

42.统计每门课程的学生选修人数（超过 5 人的课程才统计）。

SELECT SC.Cid, Course.Cname, COUNT(*)
FROM SC INNER JOIN Course
	ON SC.Cid = Course.Cid
GROUP BY SC.Cid, Course.Cname
HAVING COUNT(SC.Cid) > 5;

43.检索至少选修两门课程的学生学号

SELECT Sid
FROM SC
GROUP BY Sid
HAVING COUNT(*) >=2;

44.查询选修了全部课程的学生信息
最内层的子查询的结果表示一共有多少门课程。再外一层的结果表示选课数量等于所有课程数量的学生的学号，最后从Student表选取学号为这些的学生信息。

SELECT *
FROM Student 
WHERE Sid IN (
	SELECT Sid
	FROM SC
	GROUP BY Sid
	HAVING COUNT(DISTINCT Cid) = (
		SELECT COUNT(DISTINCT Cid) 
		from Course));

45.查询各学生的年龄，只按年份来算
接下来考察各种日期函数，用EXTRACT提取现在的年，和出生日期中的年，相减的到年龄。

SELECT Sid, Sname, Sage,
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) - EXTRACT(YEAR FROM Sage) AS 年龄
FROM Student;

46.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
AGE函数能够计算时间间隔，这个间隔是可以精确到秒的，然后再提取年。这样不满一年的部分就忽略了。

SELECT Sid, Sname, Sage,
EXTRACT(YEAR FROM AGE(CURRENT_DATE, Sage)) AS 年龄
FROM Student;

47.查询本周过生日的学生
用EXTRACT提取现在的周数和出生日期的周数，两个周数相等就可以了。

SELECT *
FROM Student
WHERE EXTRACT(WEEK FROM CURRENT_DATE) = EXTRACT(WEEK FROM sage)

48.查询下周过生日的学生
和上一题相似，用+INTERVAL '1 WEEK’表示一周后的日期。

SELECT *
FROM Student
WHERE EXTRACT(WEEK FROM (CURRENT_DATE + INTERVAL '1 WEEK')) = 
	EXTRACT(WEEK FROM sage);

49.查询本月过生日的学生
跟47题相似，把week改成month

SELECT *
FROM Student
WHERE EXTRACT(MONTH FROM CURRENT_DATE) = EXTRACT(MONTH FROM sage);

50.查询下月过生日的学生
跟48题相似，把week改成month

SELECT *
FROM Student
WHERE EXTRACT(MONTH FROM (CURRENT_DATE + INTERVAL '1 MONTH')) = 
	EXTRACT(MONTH FROM sage)

题目来源自网络，SQL语句与答案解析作者：钱奇天。

Sid	Cid	Score
01	01	80
01	02	90
01	03	99
02	01	70
02	02	60
02	03	80
03	01	80
03	02	80
03	03	80
04	01	50
04	02	30
04	03	20
05	01	76
05	02	87
06	01	31
06	03	34
07	02	89
07	03	98

Sid	Cid	Score
01	01	80
01	02	90
01	03	99
02	01	70
02	02	60
02	03	80
03	01	80
03	02	80
03	03	80
04	01	50
04	02	30
04	03	20
05	01	76
05	02	87
06	01	31
06	03	34
07	02	89
07	03	98

Sid	Cid	Score
01	01	80
01	02	90
01	03	99
02	01	70
02	02	60
02	03	80
03	01	80
03	02	80
03	03	80
04	01	50
04	02	30
04	03	20
05	01	76
05	02	87
06	01	31
06	03	34
07	02	89
07	03	98