SQL 练习题

-- 参考
SELECT 姓名, 班级, 年龄
FROM 学生
WHERE 班级 IN ('一班', '二班') AND 年龄 >= 16
ORDER BY 年龄 DESC, 姓名 ASC;

SELECT COUNT(*) AS 人数
FROM 学生
WHERE 班级 IN ('一班', '二班') AND 年龄 >= 16;

-- 参考
SELECT 班级,
       COUNT(*) AS 人数,
       ROUND(AVG(年龄), 2) AS 平均年龄,
       MAX(年龄) AS 最大年龄
FROM 学生
GROUP BY 班级
HAVING AVG(年龄) >= 16.5
ORDER BY 人数 DESC;

-- 参考
SELECT s.姓名, s.班级, c.科目, c.分数
FROM 学生 s
JOIN 成绩 c ON c.学生id = s.id
WHERE c.分数 < 75
ORDER BY c.分数 ASC, s.姓名;

-- 缺考数学的人
SELECT s.姓名, s.班级
FROM 学生 s
LEFT JOIN 成绩 c ON c.学生id = s.id AND c.科目 = '数学'
WHERE c.id IS NULL;

-- 参考
SELECT s.姓名, s.班级,
       COUNT(c.id) AS 科目数,
       SUM(c.分数) AS 总分,
       ROUND(AVG(c.分数), 1) AS 平均分
FROM 学生 s
JOIN 成绩 c ON c.学生id = s.id
GROUP BY s.id, s.姓名, s.班级
ORDER BY 总分 DESC;

SELECT s.姓名, c.科目, c.分数,
       ROUND((SELECT AVG(分数) FROM 成绩), 1) AS 全班平均
FROM 成绩 c
JOIN 学生 s ON s.id = c.学生id
WHERE c.分数 > (SELECT AVG(分数) FROM 成绩)
ORDER BY c.分数 DESC
LIMIT 5;

-- 参考
SELECT s.姓名, s.班级,
       MAX(CASE WHEN c.科目 = '语文' THEN c.分数 END) AS 语文,
       MAX(CASE WHEN c.科目 = '数学' THEN c.分数 END) AS 数学,
       MAX(CASE WHEN c.科目 = '英语' THEN c.分数 END) AS 英语,
       SUM(c.分数) AS 总分
FROM 学生 s
LEFT JOIN 成绩 c ON c.学生id = s.id
GROUP BY s.id, s.姓名, s.班级
ORDER BY s.id;

-- 参考
SELECT 科目, 姓名, 分数, 名次
FROM (
  SELECT c.科目, s.姓名, c.分数,
         RANK() OVER (PARTITION BY c.科目 ORDER BY c.分数 DESC) AS 名次
  FROM 成绩 c
  JOIN 学生 s ON s.id = c.学生id
)
WHERE 名次 <= 3
ORDER BY 科目, 名次, 姓名;

SELECT s.姓名, c.分数,
       ROW_NUMBER() OVER (ORDER BY c.分数 DESC) AS 行号_不并列,
       RANK()       OVER (ORDER BY c.分数 DESC) AS 排名_并列跳号,
       DENSE_RANK() OVER (ORDER BY c.分数 DESC) AS 密集排名_并列不跳号
FROM 成绩 c JOIN 学生 s ON s.id = c.学生id
WHERE c.科目 = '数学'
ORDER BY c.分数 DESC;

-- 参考
SELECT 姓名, 科目 AS 最强科目, 分数
FROM (
  SELECT s.姓名, c.科目, c.分数,
         ROW_NUMBER() OVER (PARTITION BY s.id ORDER BY c.分数 DESC, c.id) AS rn
  FROM 成绩 c
  JOIN 学生 s ON s.id = c.学生id
)
WHERE rn = 1
ORDER BY 分数 DESC, 姓名;

-- 参考
BEGIN;
INSERT INTO 学生 (id, 姓名, 班级, 年龄, 城市) VALUES (9, '周涛', '三班', 17, '武汉');
INSERT INTO 成绩 (学生id, 科目, 分数) VALUES (9, '语文', 74), (9, '数学', 83), (9, '英语', 91);
COMMIT;

SELECT s.姓名, c.科目, c.分数
FROM 学生 s JOIN 成绩 c ON c.学生id = s.id
WHERE s.id = 9
ORDER BY c.科目;

-- 反例：手滑清空了成绩表，用 ROLLBACK 撤销
BEGIN;
DELETE FROM 成绩;
ROLLBACK;

SELECT COUNT(*) AS 成绩条数还在吗 FROM 成绩;

-- 参考
DROP TABLE IF EXISTS 班级;
CREATE TABLE 班级 (
  名称     TEXT PRIMARY KEY,
  班主任   TEXT NOT NULL,
  教室     TEXT,
  人数上限 INTEGER NOT NULL DEFAULT 40 CHECK (人数上限 > 0)
);

INSERT INTO 班级 (名称, 班主任, 教室) VALUES
  ('一班', '王老师', 'A101'),
  ('二班', '李老师', 'A102'),
  ('三班', '张老师', 'B201');

-- CREATE TABLE AS SELECT：把查询结果直接存成一张新表
DROP TABLE IF EXISTS 班级人数;
CREATE TABLE 班级人数 AS
SELECT 班级 AS 名称, COUNT(*) AS 实到人数 FROM 学生 GROUP BY 班级;

SELECT b.名称, b.班主任, b.教室, n.实到人数, b.人数上限,
       b.人数上限 - n.实到人数 AS 还能再收
FROM 班级 b
LEFT JOIN 班级人数 n ON n.名称 = b.名称
ORDER BY b.名称;

DROP TABLE IF EXISTS 副本;
CREATE TABLE 副本 AS SELECT id, 姓名 FROM 学生;

-- 直接看 SQLite 存下来的建表语句：约束一个都没有
SELECT sql AS 建表语句 FROM sqlite_master WHERE name = '副本';
PRAGMA table_info(副本);            -- pk 那一列全是 0，notnull 也全是 0

-- 后果：重复的 id 和空的姓名都能塞进去，没人拦
INSERT INTO 副本 (id, 姓名) VALUES (1, NULL), (1, '又一个 id=1');
SELECT * FROM 副本 WHERE id = 1;

-- 想让它有约束，就得老老实实 CREATE TABLE 之后再用 INSERT INTO ... SELECT 灌数据
DROP TABLE IF EXISTS 正式副本;
CREATE TABLE 正式副本 (id INTEGER PRIMARY KEY, 姓名 TEXT NOT NULL);
INSERT INTO 正式副本 (id, 姓名) SELECT id, 姓名 FROM 学生;
PRAGMA table_info(正式副本);

-- 同样一条脏数据，这次被拦住了：NOT NULL constraint failed: 正式副本.姓名
INSERT INTO 正式副本 (id, 姓名) VALUES (1, NULL);

-- 参考

-- 一、每个学生应该考 3 科，少于 3 科就是有缺考
SELECT s.姓名, s.班级,
       COUNT(c.id) AS 实际科目数,
       3 - COUNT(c.id) AS 缺考科目数
FROM 学生 s
LEFT JOIN 成绩 c ON c.学生id = s.id
GROUP BY s.id, s.姓名, s.班级
HAVING COUNT(c.id) < 3;

-- 二、具体缺的是哪一科：造一张"所有科目"的小表去叉乘，再用 NOT EXISTS 反查
SELECT s.姓名, k.科目 AS 缺考科目
FROM 学生 s
CROSS JOIN (
  SELECT '语文' AS 科目
  UNION ALL SELECT '数学'
  UNION ALL SELECT '英语'
) AS k
WHERE NOT EXISTS (
  SELECT 1 FROM 成绩 c WHERE c.学生id = s.id AND c.科目 = k.科目
)
ORDER BY s.id, k.科目;

-- 三、其它几类脏数据，用 UNION ALL 汇总成一张问题清单
SELECT '分数越界' AS 问题, s.姓名, c.科目 || ' = ' || c.分数 AS 详情
FROM 成绩 c JOIN 学生 s ON s.id = c.学生id
WHERE c.分数 < 0 OR c.分数 > 100
UNION ALL
SELECT '城市为空', 姓名, '城市 = ' || COALESCE(城市, 'NULL')
FROM 学生 WHERE 城市 IS NULL OR trim(城市) = ''
UNION ALL
SELECT '重名', 姓名, COUNT(*) || ' 个人同名'
FROM 学生 GROUP BY 姓名 HAVING COUNT(*) > 1
UNION ALL
SELECT '成绩条数不对', s.姓名, '应有 3 条，实有 ' || COUNT(c.id) || ' 条'
FROM 学生 s LEFT JOIN 成绩 c ON c.学生id = s.id
GROUP BY s.id, s.姓名
HAVING COUNT(c.id) <> 3;