第七週SELECT GROUP BY HAVING WHERE 與函數整合應用SELECT GROUP BY HAVING WHERE 與函數整合應用
CREATE TABLE writer (
author VARCHAR(50) DEFAULT NULL COMMENT '作者',
title VARCHAR(40) DEFAULT NULL COMMENT '書名',
copies TINYINT(4) DEFAULT NULL COMMENT '印刷數量'
);
INSERT INTO writer VALUES
('史維','黑心建商的告白',3),
('史維','黑心投資客炒房告白',8),
('史維','黑心房仲的告白',2),
('侯文詠','白色巨塔',21),
('侯文詠','帶我去月球',2); 資料如下 author title copies
史維 黑心建商的告白 3
史維 黑心投資客炒房告白 8
史維 黑心房仲的告白 2
侯文詠 白色巨塔 21
侯文詠 帶我去月球 2 select * from writer; "author" "title" "copies"
"史維" "黑心建商的告白" "3"
"史維" "黑心投資客炒房告白" "8"
"史維" "黑心房仲的告白" "2"
"侯文詠" "白色巨塔" "21"
"侯文詠" "帶我去月球" "2" SELECT author,SUM(copies) FROM writer GROUP BY author; "author" "SUM(copies)"
"侯文詠" "23"
"史維" "13" SELECT author,copies FROM writer GROUP BY author; "author" "copies"
"侯文詠" "21"
"史維" "3" SELECT author,MAX(copies) max,MIN(copies) min,AVG(copies) avg,
SUM(copies) sum FROM writer GROUP BY author; "author" "max" "min" "avg" "sum"
"侯文詠" "21" "2" "11.5000" "23"
"史維" "8" "2" "4.3333" "13" SELECT author,MAX(copies) AS max,MIN(copies) AS min, AVG(copies)
AS avg,SUM(copies) AS sum FROM writer GROUP BY author; "author" "max" "min" "avg" "sum"
"侯文詠" "21" "2" "11.5000" "23"
"史維" "8" "2" "4.3333" "13" SELECT author,MAX(copies) AS max,MIN(copies) AS min,AVG(copies)
AS avg,SUM(copies) AS sum FROM writer WHERE copies > 5 GROUP BY author; "author" "max" "min" "avg" "sum"
"侯文詠" "21" "21" "21.0000" "21"
"史維" "8" "8" "8.0000" "8" SELECT author,MAX(copies) AS max,MIN(copies) AS min,AVG(copies)
AS avg,SUM(copies) AS sum FROM writer GROUP BY author HAVING max > 5; "author" "max" "min" "avg" "sum"
"侯文詠" "21" "2" "11.5000" "23"
"史維" "8" "2" "4.3333" "13"
作業:將前述資料表 writer 增加二個欄位, 月份 mon 與 銷售量 sale, 至少輸入五個月份, 每位作者至少三本著作
將前述問題作一遍, 並回答下列問題
1, 查詢所有資料
2. 查詢每位作者每個月的資料
3. 查詢每位作者每本著作的印刷數量總和與銷售數量總和
4. 查詢每位作者每本著作的印刷數量總和與銷售數量總和, 按銷售量由大至小排序
5. 查詢每位作者每本著作的印刷數量總和與銷售數量總和, 按印刷量由小至大排序
6. 查詢銷售數量大於10本的作者資料
7. 查詢銷售數量小於10本的作者資料
8. 查詢銷售數量介於5-10本的作者資料
| 
