第6週-SELECT GROUP BY HAVING與函數整合應用

SELECT GROUP BY HAVING WHERE 與函數整合應用
在mydb資料庫中另建資表

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 sum > 5;

請特別注意這題與下題的結果

 

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,
融入先前的命令檔中，
至少輸入五個月份, 每位作者至少三本著作

將前述問題作一遍, 並回答下列問題