10 道综合题覆盖全书知识:从报表查询到数据维护、再到建表建视图。像真实工作一样,题目只给需求,方案自己设计。
毕业考试:用 SQL 解决真实业务
学完了全部语法,这一章没有新知识,只有真实感十足的业务需求。查询、过滤、连接、分组、子查询、修改数据、建表建视图——全都要用上。
做题建议:
- 先想清楚需要哪些表、怎么连接,再动手写
- 复杂需求拆成小步:先 INNER JOIN 跑通,再补 LEFT JOIN / 聚合 / 排序
- 提交前先点「仅运行」检查结果合不合理(行数、金额量级)
- 题目里关于口径的说明(比如「不考虑订单状态」「列名必须叫什么」)直接影响判题,务必读清楚
数据回顾:5 张表、7 位作者、16 本图书、8 位顾客、10 张订单、20 条明细。忘了表结构就展开下方面板。
下面 10 个示例依次对应本章 10 道练习题:先点「▶ 运行示例」看看正确结果长什么样,再合上答案自己写一遍。每次运行都在一个全新的示例数据库上,随便改、随便试,不会影响你的练习。
演练 1:月度订单数报表
把订单日期裁成「年月」再分组计数,STRFTIME 的 %Y 是四位年份、%m 是两位月份:
sqlSELECT STRFTIME('%Y-%m', order_date) AS 月份, COUNT(*) AS 订单数
FROM orders
GROUP BY STRFTIME('%Y-%m', order_date)
ORDER BY 月份;GROUP BY 后面可以把分组表达式原样再写一遍;SQLite 也允许直接用 SELECT 里的别名分组。
演练 2:销售额前三的图书
明细表按图书分组求和,再按销售额倒序取前 3 名。想查书的书名就必须 JOIN books,并按 book_id 分组(同名书不会混在一起):
sqlSELECT b.title,
SUM(oi.quantity * oi.unit_price) AS 销售额
FROM order_items oi
JOIN books b ON oi.book_id = b.book_id
GROUP BY b.book_id, b.title
ORDER BY 销售额 DESC
LIMIT 3;演练 3:各城市顾客数与消费总额
三个表连起来,顾客数用 COUNT(DISTINCT) 去重,消费总额用 COALESCE 把没下过单的 NULL 兜底成 0:
sqlSELECT c.city,
COUNT(DISTINCT c.customer_id) AS 顾客数,
COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS 消费总额
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.city;演练 4:从没买过科幻书的顾客
先查出「买过科幻书」的顾客集合,再用 NOT IN 把他们排除掉;已取消的订单同样算买过,所以不必加 status 条件:
sqlSELECT name
FROM customers
WHERE customer_id NOT IN (
SELECT o.customer_id
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN books b ON oi.book_id = b.book_id
WHERE b.category = '科幻'
);演练 5:高性价比书单
评分和价格两个条件用 AND 串起来,最后按评分倒序即可:
sqlSELECT title, price, rating
FROM books
WHERE rating >= 4.5 AND price <= 80
ORDER BY rating DESC;演练 6:给订单 8 的明细涨价
UPDATE 一定要带 WHERE,否则会误伤别的订单。改完紧跟一条 SELECT,立刻看到改动效果(非 SELECT 语句本身没有输出):
sqlUPDATE order_items
SET unit_price = unit_price * 1.1
WHERE order_id = 8;
SELECT book_id, unit_price FROM order_items WHERE order_id = 8;演练 7:给库存告急的图书补货
先 SELECT 看看补货前是哪些书、库存多少,再 UPDATE 补货,最后复查 14/15/16 号图书的库存变化:
sqlUPDATE books
SET stock = stock + 30
WHERE stock < 20;
SELECT book_id, stock FROM books WHERE book_id IN (14, 15, 16);演练 8:下架滞销书
滞销书 = 没人买过(NOT IN 子查询)且评分为空(rating IS NULL)。删完用 COUNT(*) 确认剩下的图书数:
sqlDELETE FROM books
WHERE rating IS NULL
AND book_id NOT IN (
SELECT book_id FROM order_items WHERE book_id IS NOT NULL
);
SELECT COUNT(*) AS 剩余图书数 FROM books;演练 9:心愿单建表与插入
created_date 交给 DEFAULT,插入时不需要写出这一列:
sqlCREATE TABLE wishlists (
wishlist_id INTEGER PRIMARY KEY,
customer_id INTEGER,
book_id INTEGER,
created_date TEXT DEFAULT '2025-09-01'
);
INSERT INTO wishlists (wishlist_id, customer_id, book_id)
VALUES (1, 2, 4), (2, 3, 7);
SELECT wishlist_id, customer_id, book_id, created_date FROM wishlists;演练 10:顾客价值视图
把「每位顾客的订单数与消费总额」存成视图,之后就能像表一样反复查询:
sqlCREATE VIEW v_customer_stats AS
SELECT c.customer_id, c.name,
COUNT(o.order_id) AS order_cnt,
COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_amt
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.name;
SELECT * FROM v_customer_stats;小结
- 报表题:分组 + 聚合,必要时用 LEFT JOIN + COUNT(DISTINCT) + COALESCE 兜底
- 删除/更新题:条件写清楚,WHERE 一个字符都不能少
- 建表/建视图题:列名和别名必须严格照题目要求,判题就是照名字查
全部通过的同学:你已经完成了从零到能干活的 SQL 入门。下一步可以了解窗口函数、事务、性能优化,以及把 SQLite 换成 MySQL / PostgreSQL 实战。祝你在数据的世界里一路顺畅!
遇到不会的题别急着看答案:先用「提示」,再回对应章节复习。Hint 按钮永远比答案按钮更让你进步。
练习题
建议先在本地 SQL 环境或浏览器 SQLite 练习本中写一遍,再展开参考答案对照。
1. 月度订单数报表
统计每个月的订单数量:输出年月(格式 2025-01 这样的 7 位文本,列名随意)和订单数。
💡 提示:STRFTIME('%Y-%m', order_date) 提取年月,GROUP BY 它再 COUNT(*)。
查看参考答案
sqlSELECT STRFTIME('%Y-%m', order_date) AS 月份, COUNT(*) AS 订单数 FROM orders GROUP BY STRFTIME('%Y-%m', order_date);2. 销售额前三的图书
按销售额(SUM(quantity × unit_price),不考虑订单状态)从高到低,查询前 3 名图书的 title 和销售额。本题检查行顺序。
💡 提示:order_items JOIN books → GROUP BY 书 → SUM(quantity * unit_price) → ORDER BY ... DESC LIMIT 3
查看参考答案
sqlSELECT books.title, SUM(order_items.quantity * order_items.unit_price) AS 销售额 FROM order_items JOIN books ON order_items.book_id = books.book_id GROUP BY books.book_id, books.title ORDER BY 销售额 DESC LIMIT 3;3. 各城市战报
统计每个城市的顾客数和消费总额(消费 = 顾客所有订单明细的 quantity × unit_price 之和,不考虑订单状态;没下过单的顾客消费记 0,也要出现在其城市里)。输出 city、顾客数、消费总额。提示:顾客数要用 COUNT(DISTINCT ...)。
💡 提示:customers LEFT JOIN orders LEFT JOIN order_items → GROUP BY city → COUNT(DISTINCT c.customer_id) 和 COALESCE(SUM(...), 0)。
查看参考答案
sqlSELECT c.city, COUNT(DISTINCT c.customer_id) AS 顾客数, COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS 消费总额 FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id LEFT JOIN order_items oi ON o.order_id = oi.order_id GROUP BY c.city;4. 科幻绝缘体顾客
查询从未买过任何科幻类图书的顾客姓名(不考虑订单状态:已取消订单里的书也算买过)。
💡 提示:先找出买过科幻书(books.category = '科幻')的 customer_id 集合,再用 NOT IN / NOT EXISTS 排除他们。
查看参考答案
sqlSELECT name FROM customers WHERE customer_id NOT IN (SELECT o.customer_id FROM orders o JOIN order_items oi ON o.order_id = oi.order_id JOIN books b ON oi.book_id = b.book_id WHERE b.category = '科幻');5. 高性价比书单
查询评分不低于 4.5 且 价格不高于 80 元的图书的 title、price、rating,按评分从高到低排列(只看内容,不检查行序)。
💡 提示:WHERE rating >= 4.5 AND price <= 80 ORDER BY rating DESC
查看参考答案
sqlSELECT title, price, rating FROM books WHERE rating >= 4.5 AND price <= 80 ORDER BY rating DESC;6. 涨价通知
把订单 8 的所有明细单价上调 10%(unit_price 乘 1.1)。注意别把别的订单也改了。
起始语句:
sql-- 在下面写出 UPDATE 语句
💡 提示:UPDATE order_items SET unit_price = unit_price * 1.1 WHERE order_id = 8;
查看参考答案
sqlUPDATE order_items SET unit_price = unit_price * 1.1 WHERE order_id = 8;7. 库存告急补货
把库存少于 20 本的图书每种补货 30 本(stock 加 30)。判题会同时确认其他图书的库存没有被误改。
起始语句:
sql-- 在下面写出 UPDATE 语句
💡 提示:UPDATE books SET stock = stock + 30 WHERE stock < 20;
查看参考答案
sqlUPDATE books SET stock = stock + 30 WHERE stock < 20;8. 下架滞销书
删除从未被订购过且评分为空的图书,注意不要误删其他图书。判题会检查删除后的库存表状态。
起始语句:
sql-- 在下面写出 DELETE 语句
💡 提示:DELETE FROM books WHERE rating IS NULL AND book_id NOT IN (SELECT book_id FROM order_items WHERE book_id IS NOT NULL);
查看参考答案
sqlDELETE FROM books WHERE rating IS NULL AND book_id NOT IN (SELECT book_id FROM order_items WHERE book_id IS NOT NULL);9. 心愿单
创建 wishlists 表:wishlist_id(INTEGER 主键)、customer_id(INTEGER)、book_id(INTEGER)、created_date(TEXT,DEFAULT 2025-09-01)。然后插入两行:(1, 2, 4) 和 (2, 3, 7),都不指定 created_date。
起始语句:
sql-- 建表 + 插入两行
💡 提示:INSERT INTO wishlists (wishlist_id, customer_id, book_id) VALUES (1, 2, 4), (2, 3, 7); created_date 靠默认值。
查看参考答案
sqlCREATE TABLE wishlists (wishlist_id INTEGER PRIMARY KEY, customer_id INTEGER, book_id INTEGER, created_date TEXT DEFAULT '2025-09-01');
INSERT INTO wishlists (wishlist_id, customer_id, book_id) VALUES (1, 2, 4), (2, 3, 7);10. 顾客价值视图
创建名为 v_customer_stats 的视图:每位顾客的 customer_id、姓名(name)、订单数(列名必须为 order_cnt)、消费总额(列名必须为 total_amt,明细金额求和,不考虑订单状态,没下过单的顾客也要出现且为 0)。列名不一致判题会失败。
起始语句:
sql-- customers LEFT JOIN orders LEFT JOIN order_items + GROUP BY
💡 提示:COUNT(o.order_id) 数订单(LEFT JOIN 下没单就是 0),COALESCE(SUM(...), 0) 算金额,GROUP BY c.customer_id, c.name。
查看参考答案
sqlCREATE VIEW v_customer_stats AS SELECT c.customer_id, c.name, COUNT(o.order_id) AS order_cnt, COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_amt FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id LEFT JOIN order_items oi ON o.order_id = oi.order_id GROUP BY c.customer_id, c.name;涉及的数据表
authorsbookscustomersordersorder_items
由在线工具箱(www.vba.net)整理制作