期末考加分題

期末考不理想的同學, 請在此留下你做錯的題目, 程式的意義與執行結果 (記得留學號, 註明卷別)

資訊技術 (Database) 期中測驗 (A卷) 學號：姓名：

三個資料表 (Relation)與內容值如下, 其中，畫底線的欄位為主鍵，

sporder中的sp_s與sp_p分別是supplier2與product的外來鍵

supplier2

s	address
1	台北市
2	台中市
3	台南市

product

p	cost
10	100
20	150
30	200
40	150
50	50
60	10

sporder

sp_s	sp_p	sp_qty
1	10	100
1	20	200
1	30	300
2	20	400
2	30	250
3	10	150
3	40	50

1. SELECT sp_s, sp_p, sp_qty AS 供應量 from sporder WHERE sp_s >'2';

2. SELECT sp_p,SUM(sp_qty) AS "供應量加總" FROM sporder GROUP BY sp_p ORDER BY sp_p;

3. SELECT sp_s,SUM(sp_qty*cost) AS Total FROM sporder sp JOIN product p ON sp.sp_p = p.p GROUP BY sp_s HAVING SUM(sp_qty*cost) > 120000 ORDER BY Total DESC;

4. Select * from product Where p NOT In (Select sp_p from sporder Where product.p = sporder.sp_p);

5. SELECT sporder.sp_s,SUM(sp_qty*cost) AS Total FROM sporder , product WHERE sporder.sp_p = product.p GROUP BY sporder.sp_s ORDER BY Total DESC;

6. SELECT sp_s, SUM (sp_qty*cost) FROM sporder LEFT JOIN product ON sporder.sp_p = product.p GROUP BY sp_s;

7. SELECT sp_s, AVG (sp_qty) AS "平均值" FROM sporder LEFT JOIN product ON sporder.sp_p = product.p GROUP BY sp_s;

8. SELECT sp_s, MIN(sp_qty), MAX(sp_qty) FROM sporder LEFT JOIN product ON sporder.sp_p = product.p GROUP BY sp_s;

9. CREATE VIEW "供應總金額檢視表" AS SELECT sp_s, SUM (sp_qty*cost) FROM sporder LEFT JOIN product ON sporder.sp_p = product.p WHERE sp_s>= '2' GROUP BY sp_s;

10. SELECT COUNT(*) FROM supplier2;

資訊技術 (Database) 期中測驗 (B卷) 學號：姓名：

三個資料表 (Relation)與內容值如下, 其中，畫底線的欄位為主鍵，

sporder中的sp_s與sp_p分別是supplier2與product的外來鍵

supplier2

s	address
1	高雄市
2	台中市
3	台北

product

p	cost
10	10
20	50
30	150
40	200
50	100
60	300

sporder

sp_s	sp_p	sp_qty
1	10	50
1	20	150
1	30	250
2	20	400
2	30	300
3	20	200
3	40	50