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

SELECT GROUP BY HAVING WHERE 與函數整合應用

資料如下

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 copies > 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, 至少輸入五個月份, 每位作者至少三本著作
將前述問題作一遍, 並回答下列問題

1, 查詢每位作者每個月的資料

2. 查詢每位作者每本著作的印刷數量總和與銷售數量總和

3. 查詢每位作者每本著作的印刷數量總和與銷售數量總和, 按銷售量由大至小排序

4. 查詢每位作者每本著作的印刷數量總和與銷售數量總和, 按印刷量由小至大排序

5.  查詢銷售數量大於10本的作者資料 

再利用先前的資料表stmd完成下列各題

6. 查詢班級人數大於5人之班級代碼及人數

7. 查詢班級人數小於10人之班級代碼及人數

8. 查詢班級數大於5人且都住在台北市的班級代碼及人數

9. 查詢班級人數大於5人之二年級班級及人數

10. 查詢班級人數大於5人之二年級且住在台北市之班級與人數

 

全部共 0則留言
留言...
登入帳號密碼代表遵守學術網路規範