完整的數據庫實踐題目：在線書店管理系統

數據庫表結構及示例數據

1. 書籍表(books)

CREATE TABLE books (
book_id INT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
author VARCHAR(50) NOT NULL,
publisher VARCHAR(50),
publish_year INT,
category VARCHAR(30),
price DECIMAL(10,2),
stock INT DEFAULT 0
);

INSERT INTO books VALUES
(1001, ‘三體’, ‘劉慈欣’, ‘重慶出版社’, 2008, ‘科幻’, 45.00, 50),
(1002, ‘活著’, ‘余華’, ‘作家出版社’, 2012, ‘文學’, 39.00, 30),
(1003, ‘平凡的世界’, ‘路遙’, ‘人民文學出版社’, 2005, ‘文學’, 89.00, 20),
(1004, ‘Python編程從入門到實踐’, ‘Eric Matthes’, ‘人民郵電出版社’, 2020, ‘計算機’, 89.00, 100),
(1005, ‘人類簡史’, ‘尤瓦爾·赫拉利’, ‘中信出版社’, 2017, ‘歷史’, 68.00, 40),
(1006, ‘圍城’, ‘錢鐘書’, ‘人民文學出版社’, 1991, ‘文學’, 36.00, 25),
(1007, ‘算法導論’, ‘Thomas H.Cormen’, ‘機械工業出版社’, 2013, ‘計算機’, 128.00, 15),
(1008, ‘百年孤獨’, ‘加西亞·馬爾克斯’, ‘南海出版公司’, 2011, ‘文學’, 55.00, 35),
(1009, ‘時間簡史’, ‘史蒂芬·霍金’, ‘湖南科技出版社’, 2010, ‘科普’, 45.00, 20),
(1010, ‘小王子’, ‘圣埃克蘇佩里’, ‘人民文學出版社’, 2003, ‘童話’, 25.00, 60);

2. 客戶表(customers)

CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20),
address VARCHAR(200),
register_date DATE
);

INSERT INTO customers VALUES
(2001, ‘張三’, ‘zhangsan@example.com’, ‘13800138001’, ‘北京市海淀區’, ‘2022-01-15’),
(2002, ‘李四’, ‘lisi@example.com’, ‘13800138002’, ‘上海市浦東新區’, ‘2022-03-10’),
(2003, ‘王五’, ‘wangwu@example.com’, ‘13800138003’, ‘廣州市天河區’, ‘2022-05-20’),
(2004, ‘趙六’, ‘zhaoliu@example.com’, ‘13800138004’, ‘深圳市南山區’, ‘2022-07-05’),
(2005, ‘錢七’, ‘qianqi@example.com’, ‘13800138005’, ‘成都市武侯區’, ‘2022-09-18’);

3. 訂單表(orders)

CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_amount DECIMAL(12,2),
status VARCHAR(20) CHECK(status IN (‘待付款’,‘已付款’,‘已發貨’,‘已完成’,‘已取消’)),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO orders VALUES
(3001, 2001, ‘2023-01-05’, 84.00, ‘已完成’),
(3002, 2002, ‘2023-01-12’, 133.00, ‘已發貨’),
(3003, 2003, ‘2023-01-18’, 178.00, ‘已付款’),
(3004, 2001, ‘2023-02-03’, 45.00, ‘已完成’),
(3005, 2004, ‘2023-02-15’, 113.00, ‘已完成’),
(3006, 2005, ‘2023-03-01’, 89.00, ‘已取消’),
(3007, 2002, ‘2023-03-10’, 68.00, ‘已完成’),
(3008, 2003, ‘2023-03-20’, 164.00, ‘已發貨’),
(3009, 2001, ‘2023-04-05’, 55.00, ‘已付款’),
(3010, 2005, ‘2023-04-18’, 25.00, ‘已完成’);

4. 訂單明細表(order_items)

CREATE TABLE order_items (
item_id INT PRIMARY KEY,
order_id INT,
book_id INT,
quantity INT,
price DECIMAL(10,2),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (book_id) REFERENCES books(book_id)
);

INSERT INTO order_items VALUES
(4001, 3001, 1001, 1, 45.00),
(4002, 3001, 1006, 1, 36.00),
(4003, 3002, 1003, 1, 89.00),
(4004, 3002, 1010, 1, 25.00),
(4005, 3003, 1005, 2, 68.00),
(4006, 3003, 1009, 1, 45.00),
(4007, 3004, 1001, 1, 45.00),
(4008, 3005, 1004, 1, 89.00),
(4009, 3005, 1006, 1, 36.00),
(4010, 3006, 1003, 1, 89.00),
(4011, 3007, 1005, 1, 68.00),
(4012, 3008, 1008, 2, 55.00),
(4013, 3008, 1010, 1, 25.00),
(4014, 3009, 1008, 1, 55.00),
(4015, 3010, 1010, 1, 25.00);

十個業務問題及SQL查詢

問題1：查詢庫存不足30本的書籍信息

SELECT book_id, title, author, price, stock
FROM books
WHERE stock < 30
ORDER BY stock;

問題2：統計每個圖書類別的書籍數量和平均價格

SELECT category, COUNT(*) AS book_count,
ROUND(AVG(price),2) AS avg_price
FROM books
GROUP BY category
ORDER BY book_count DESC;

問題3：查詢2023年第一季度(1-3月)的訂單總金額

SELECT SUM(total_amount) AS q1_sales
FROM orders
WHERE order_date BETWEEN ‘2023-01-01’ AND ‘2023-03-31’;

問題4：找出購買金額最高的前3名客戶

SELECT c.customer_id, c.name, SUM(o.total_amount) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC
LIMIT 3;

問題5：查詢每本書的銷售數量和銷售總額

SELECT b.book_id, b.title,
SUM(oi.quantity) AS sales_quantity,
SUM(oi.quantity * oi.price) AS sales_amount
FROM books b
LEFT JOIN order_items oi ON b.book_id = oi.book_id
GROUP BY b.book_id, b.title
ORDER BY sales_amount DESC;

問題6：找出購買了"文學"類書籍的客戶信息

SELECT DISTINCT c.customer_id, c.name, c.email
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN books b ON oi.book_id = b.book_id
WHERE b.category = ‘文學’;

問題7：計算每個月的訂單數量和銷售總額

SELECT DATE_FORMAT(order_date, ‘%Y-%m’) AS month,
COUNT(*) AS order_count,
SUM(total_amount) AS monthly_sales
FROM orders
GROUP BY DATE_FORMAT(order_date, ‘%Y-%m’)
ORDER BY month;

問題8：查詢購買了超過1本書的訂單詳情

SELECT o.order_id, o.order_date, c.name,
SUM(oi.quantity) AS total_books,
o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.order_date, c.name, o.total_amount
HAVING SUM(oi.quantity) > 1
ORDER BY total_books DESC;

問題9：找出最受歡迎的圖書（按銷售數量排名）

SELECT b.book_id, b.title, b.author,
SUM(oi.quantity) AS total_sold
FROM books b
JOIN order_items oi ON b.book_id = oi.book_id
GROUP BY b.book_id, b.title, b.author
ORDER BY total_sold DESC
LIMIT 5;

問題10：查詢客戶購買歷史（包含客戶信息和購買的所有書籍）

SELECT c.customer_id, c.name,
GROUP_CONCAT(b.title SEPARATOR ', ') AS purchased_books,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(o.total_amount) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN books b ON oi.book_id = b.book_id
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;

這個完整的在線書店管理系統題目包含了：

1. 完整的四張表結構設計

2. 每張表的示例數據

3. 十個從簡單到復雜的業務問題

4. 每個問題對應的SQL查詢解決方案

5. 涵蓋了SELECT查詢、聚合函數、多表連接、分組統計、排序等常見SQL操作

學生可以通過這個完整的案例練習各種SQL查詢技巧，理解數據庫在實際業務中的應用。