10 道综合题覆盖全书知识:从报表查询到数据维护、再到建表建视图。像真实工作一样,题目只给需求,方案自己设计。

毕业考试:用 SQL 解决真实业务

学完了全部语法,这一章没有新知识,只有真实感十足的业务需求。查询、过滤、连接、分组、子查询、修改数据、建表建视图——全都要用上。

做题建议:

  1. 先想清楚需要哪些表、怎么连接,再动手写
  2. 复杂需求拆成小步:先 INNER JOIN 跑通,再补 LEFT JOIN / 聚合 / 排序
  3. 提交前先点「仅运行」检查结果合不合理(行数、金额量级)
  4. 题目里关于口径的说明(比如「不考虑订单状态」「列名必须叫什么」)直接影响判题,务必读清楚

数据回顾:5 张表、7 位作者、16 本图书、8 位顾客、10 张订单、20 条明细。忘了表结构就展开下方面板。

下面 10 个示例依次对应本章 10 道练习题:先点「▶ 运行示例」看看正确结果长什么样,再合上答案自己写一遍。每次运行都在一个全新的示例数据库上,随便改、随便试,不会影响你的练习。

演练 1:月度订单数报表

把订单日期裁成「年月」再分组计数,STRFTIME 的 %Y 是四位年份、%m 是两位月份:

sql
SELECT 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 分组(同名书不会混在一起):

sql
SELECT 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:

sql
SELECT 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 条件:

sql
SELECT 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 串起来,最后按评分倒序即可:

sql
SELECT title, price, rating FROM books WHERE rating >= 4.5 AND price <= 80 ORDER BY rating DESC;

演练 6:给订单 8 的明细涨价

UPDATE 一定要带 WHERE,否则会误伤别的订单。改完紧跟一条 SELECT,立刻看到改动效果(非 SELECT 语句本身没有输出):

sql
UPDATE 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 号图书的库存变化:

sql
UPDATE 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(*) 确认剩下的图书数:

sql
DELETE 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,插入时不需要写出这一列:

sql
CREATE 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:顾客价值视图

把「每位顾客的订单数与消费总额」存成视图,之后就能像表一样反复查询:

sql
CREATE 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(*)。

查看参考答案
sql
SELECT 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

查看参考答案
sql
SELECT 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)。

查看参考答案
sql
SELECT 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 排除他们。

查看参考答案
sql
SELECT 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

查看参考答案
sql
SELECT 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;

查看参考答案
sql
UPDATE 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;

查看参考答案
sql
UPDATE 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);

查看参考答案
sql
DELETE 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 靠默认值。

查看参考答案
sql
CREATE 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。

查看参考答案
sql
CREATE 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;

涉及的数据表

  • authors
  • books
  • customers
  • orders
  • order_items

由在线工具箱(www.vba.net)整理制作