零基础入门 PostgreSQL 练习题：20 道 SQL 从入门到进阶

本文精选 20 道 SQL 练习题，从基础单表查询到高阶开窗函数与复杂聚合，覆盖日常开发与面试中 90% 的常见场景。先列出全部题目，再附上完整可运行的 PostgreSQL 标准答案，方便读者先思考后对照。

---

前置基础

你需要准备好以下三张表（标准学生-课程-成绩模型）：

-- 学生表 CREATE TABLE student ( sid INT PRIMARY KEY, sname VARCHAR(50), ssex CHAR(2), sdept VARCHAR(50) ); -- 课程表 CREATE TABLE course ( cid INT PRIMARY KEY, cname VARCHAR(100), cteacher VARCHAR(50), ccredit NUMERIC(3,1) ); -- 成绩表 CREATE TABLE sc ( sid INT REFERENCES student(sid), cid INT REFERENCES course(cid), score NUMERIC(5,2), PRIMARY KEY (sid, cid) );

请自行插入测试数据（不少于 10 条学生、5 门课程、20 条成绩记录），以便验证答案。

---

第一部分：题目（共 20 题）

基础入门（1~7 题：单表查询、条件、排序、聚合基础）

1. 查询所有学生的全部信息
2. 查询计算机学院所有男生的姓名、学号
3. 查询学分大于等于 3.5 的所有课程名称、授课老师
4. 查询《数据库原理》这门课的所有学生成绩，按分数降序排序
5. 统计一共有多少名学生、多少门课程
6. 查询成绩不及格（<60）的学生学号、课程号、分数
7. 统计每一门课程的平均分、最高分、最低分

中等进阶（8~14 题：多表联查、子查询、分组过滤、INNER/LEFT JOIN）

1. 查询每个学生姓名、所选全部课程名、对应分数（三表联查）
2. 查询张教授授课的所有学生姓名与对应分数
3. 查询至少选了 2 门课的学生姓名、选课总门数、平均分
4. 查询没有选《数据库原理》的所有学生姓名
5. 查询每一位学生选课的总学分，只显示总学分 ≥ 7 的学生
6. 查询平均分高于 85 分的课程名称、平均分
7. 查询所有选了 Java 开发但没选操作系统的学生姓名

高阶难题（15~20 题：关联子查询、开窗函数、存在判断、TopN、复杂聚合）

1. 查询每门课分数排名前 2 的学生姓名、分数（开窗函数 rank）
2. 查询所有各科分数都高于 80 分的学生姓名
3. 查询和李四同院系的全部学生（子查询 in）
4. 找出平均分最高的学生，输出姓名、平均分
5. 查询存在不及格科目的学生姓名、不及格课程名、分数
6. 统计每个院系所有学生的平均分，并按平均分从高到低排序

---

第二部分：答案（共 20 题）

基础入门（1~7 题）

第 1 题：查询所有学生的全部信息

SELECT * FROM student;

第 2 题：查询计算机学院所有男生的姓名、学号

SELECT sid, sname FROM student WHERE sdept = '计算机学院' AND ssex = '男';

第 3 题：查询学分大于等于 3.5 的所有课程名称、授课老师

SELECT cname, cteacher FROM course WHERE ccredit >= 3.5;

第 4 题：查询《数据库原理》这门课的所有学生成绩，按分数降序排序

SELECT s.sname, sc.score FROM student s JOIN sc ON s.sid = sc.sid JOIN course c ON sc.cid = c.cid WHERE c.cname = '数据库原理' ORDER BY sc.score DESC;

第 5 题：统计一共有多少名学生、多少门课程

-- 方法一：分开查询 SELECT COUNT(sid) AS student_total FROM student; SELECT COUNT(cid) AS course_total FROM course; -- 方法二：合并为一行输出 SELECT (SELECT COUNT(sid) FROM student) AS student_total, (SELECT COUNT(cid) FROM course) AS course_total;

第 6 题：查询成绩不及格（<60）的学生学号、课程号、分数

SELECT sid, cid, score FROM sc WHERE score < 60;

第 7 题：统计每一门课程的平均分、最高分、最低分

SELECT c.cname, ROUND(AVG(sc.score), 2) AS avg_score, MAX(sc.score) AS max_score, MIN(sc.score) AS min_score FROM course c JOIN sc ON c.cid = sc.cid GROUP BY c.cid, c.cname;

中等进阶（8~14 题）

第 8 题：查询每个学生姓名、所选全部课程名、对应分数（三表联查）

SELECT s.sname, c.cname, sc.score FROM student s JOIN sc ON s.sid = sc.sid JOIN course c ON sc.cid = c.cid ORDER BY s.sid;

第 9 题：查询张教授授课的所有学生姓名与对应分数

SELECT DISTINCT s.sname, sc.score FROM student s JOIN sc ON s.sid = sc.sid JOIN course c ON sc.cid = c.cid WHERE c.cteacher = '张教授';

第 10 题：查询至少选了 2 门课的学生姓名、选课总门数、平均分

SELECT s.sname, COUNT(sc.cid) AS course_count, ROUND(AVG(sc.score), 2) AS avg_score FROM student s JOIN sc ON s.sid = sc.sid GROUP BY s.sid, s.sname HAVING COUNT(sc.cid) >= 2;

第 11 题：查询没有选《数据库原理》的所有学生姓名

SELECT DISTINCT s.sname FROM student s WHERE s.sid NOT IN ( SELECT sc.sid FROM sc JOIN course c ON sc.cid = c.cid WHERE c.cname = '数据库原理' );

第 12 题：查询每一位学生选课的总学分，只显示总学分 ≥ 7 的学生

SELECT s.sname, SUM(c.ccredit) AS total_credit FROM student s JOIN sc ON s.sid = sc.sid JOIN course c ON sc.cid = c.cid GROUP BY s.sid, s.sname HAVING SUM(c.ccredit) >= 7;

第 13 题：查询平均分高于 85 分的课程名称、平均分

SELECT c.cname, ROUND(AVG(sc.score), 2) AS avg_score FROM course c JOIN sc ON c.cid = sc.cid GROUP BY c.cid, c.cname HAVING AVG(sc.score) > 85;

第 14 题：查询所有选了 Java 开发但没选操作系统的学生姓名

SELECT DISTINCT s.sname FROM student s JOIN sc sc1 ON s.sid = sc1.sid JOIN course c1 ON sc1.cid = c1.cid WHERE c1.cname = 'Java开发' AND s.sid NOT IN ( SELECT sc2.sid FROM sc sc2 JOIN course c2 ON sc2.cid = c2.cid WHERE c2.cname = '操作系统' );

高阶难题（15~20 题）

第 15 题：查询每门课分数排名前 2 的学生姓名、分数（开窗函数 rank）

WITH course_rank AS ( SELECT c.cname, s.sname, sc.score, RANK() OVER (PARTITION BY c.cid ORDER BY sc.score DESC) AS rk FROM student s JOIN sc ON s.sid = sc.sid JOIN course c ON sc.cid = c.cid ) SELECT cname, sname, score FROM course_rank WHERE rk <= 2;

第 16 题：查询所有各科分数都高于 80 分的学生姓名

SELECT DISTINCT s.sname FROM student s WHERE NOT EXISTS ( SELECT 1 FROM sc WHERE sc.sid = s.sid AND sc.score <= 80 );

第 17 题：查询和李四同院系的全部学生（子查询 in）

SELECT sname FROM student WHERE sdept = ( SELECT sdept FROM student WHERE sname = '李四' );

第 18 题：找出平均分最高的学生，输出姓名、平均分

WITH student_avg AS ( SELECT s.sname, ROUND(AVG(sc.score), 2) AS avg_score FROM student s JOIN sc ON s.sid = sc.sid GROUP BY s.sid, s.sname ) SELECT sname, avg_score FROM student_avg ORDER BY avg_score DESC LIMIT 1;

第 19 题：查询存在不及格科目的学生姓名、不及格课程名、分数

SELECT DISTINCT s.sname, c.cname, sc.score FROM student s JOIN sc ON s.sid = sc.sid JOIN course c ON sc.cid = c.cid WHERE sc.score < 60;

第 20 题：统计每个院系所有学生的平均分，并按平均分从高到低排序

SELECT s.sdept, ROUND(AVG(sc.score), 2) AS dept_avg_score FROM student s JOIN sc ON s.sid = sc.sid GROUP BY s.sdept ORDER BY dept_avg_score DESC;

---

避坑指南

常见错误	原因	正确做法
SELECT * FROM sc GROUP BY cid报错	SELECT *包含了非分组列sid、score	只选择分组列和聚合函数
HAVING score > 80报错	score不是聚合函数也不是分组列	改用WHERE或HAVING AVG(score) > 80
NOT IN (SELECT ...)结果为空	子查询中有 NULL 值	使用NOT EXISTS或加WHERE ... IS NOT NULL
开窗函数RANK()并列第一名后第二名序号为 3	RANK()的特性：并列占用下一个名次	如需连续排名用DENSE_RANK()

💡核心口诀：先单表后多表，先筛选后聚合，先分组再过滤，先 CTE 再开窗。