期中考 SQL 程式

DROP DATABASE IF EXISTS test;  -- 刪除資料庫

CREATE DATABASE `test` CHARACTER SET utf8; -- 建立資料庫 採 utf8 編碼

-- 新增 table

 

USE test;

 

CREATE TABLE supplier (

    s_id char(5),

    s_name char(100),

    address char(120) NOT NULL,

    PRIMARY KEY (s_id)

) CHARACTER SET=utf8, ENGINE=INNODB; 

 

 

CREATE TABLE product (

    p_id char(10) NOT NULL,

    name char(50),

    cost int NOT NULL,

    PRIMARY KEY (p_id)

) CHARACTER SET=utf8, ENGINE=INNODB; 

 

CREATE TABLE ordering (

    s char(5) NOT NULL,

    p char(10) NOT NULL,

    qty int,

    PRIMARY KEY (s,p),

 

   CONSTRAINT sp_fk2

        FOREIGN KEY (s) REFERENCES supplier(s_id)

        ON DELETE NO ACTION

        ON UPDATE CASCADE,

 

   CONSTRAINT sp_fk3

        FOREIGN KEY (p) REFERENCES product(p_id)

        ON DELETE NO ACTION

        ON UPDATE CASCADE

 

 ) CHARACTER SET=utf8, ENGINE=INNODB; 

-- type=innodb 相當於 engine=innodb

 

-- 新增 table 內的資料

 

INSERT INTO supplier

(s_id, s_name, address) VALUES 

('1', '大華公司', '台北'),

('2', '中華公司', '新北市'),

('3', '小華公司', '花蓮'),

('7', '大同公司', '高雄'),

('8', '大夏公司', '台南'),

('9', '大東公司', '台中'),

('10', '中西公司', '宜蘭');

 

INSERT INTO product

(p_id, name, cost) VALUES 

('10', '蘋果', 110),

('11', '青蘋果', 20),

('12', '檸檬', 10),

('13', '芭樂', 150),

('20', '番茄', 165),

('21', '香蕉', 140),

('23', '蓮霧', 250),

('24', '橘子', 100),

('30', '奇異果', 290),

('31', '梨子', 510),

('32', '富士蘋果', 410),

('33', '小蘋果', 105),

('34', '柳丁', 115),

('40', '西瓜', 290);

 

INSERT INTO ordering (s, p, qty) VALUES ('1', '10', 50);

 

INSERT INTO ordering (s, p, qty) VALUES ('1', '11', 150);

 

INSERT INTO ordering (s, p, qty) VALUES ('1', '12', 250);

 

INSERT INTO ordering (s, p, qty) VALUES ('2', '10', 450);

 

INSERT INTO ordering (s, p, qty) VALUES ('2', '11', 50);

 

INSERT INTO ordering (s, p, qty) VALUES ('2', '13', 550);

 

INSERT INTO ordering (s, p, qty) VALUES ('2', '12', 650);

 

INSERT INTO ordering (s, p, qty) VALUES ('7', '20', 150);

 

INSERT INTO ordering (s, p, qty) VALUES ('7', '21', 250);

 

INSERT INTO ordering (s, p, qty) VALUES ('7', '23', 550);

 

INSERT INTO ordering (s, p, qty) VALUES ('7', '30', 250);

 

INSERT INTO ordering (s, p, qty) VALUES ('8', '31', 120);

 

INSERT INTO ordering (s, p, qty) VALUES ('8', '32', 50);

 

INSERT INTO ordering (s, p, qty) VALUES ('8', '33', 250);

 

INSERT INTO ordering (s, p, qty) VALUES ('8', '10', 450);

 

INSERT INTO ordering (s, p, qty) VALUES ('8', '11', 250);

 

INSERT INTO ordering (s, p, qty) VALUES ('8', '12', 150);

 

INSERT INTO ordering (s, p, qty) VALUES ('8', '20', 50);

 

INSERT INTO ordering (s, p, qty) VALUES ('9', '30', 10);

 

INSERT INTO ordering (s, p, qty) VALUES('9', '31', 750);

 

INSERT INTO ordering (s, p, qty) VALUES ('9', '32', 250);

 

INSERT INTO ordering (s, p, qty) VALUES('9', '33', 505);

 

INSERT INTO ordering (s, p, qty) VALUES('10', '20', 20);






以下是參考答案：

-- 1.      列出 ordering 資料表(Relation)中 S (以廠商作為別名 alias)、P、QTY (以訂購量作為別名)。

SELECT s AS '廠商', p, QTY AS '訂購量'

FROM ordering;

 

-- 2. ordering 資料表中，各P (產品編號)之訂購量加總

SELECT p AS '產品編號',SUM(qty) AS '訂購量加總'

FROM ordering

GROUP BY p;

 

-- 3. 列出 廠商之訂購金額加總 (訂購金額 為 table ordering 之訂購量 qty * table product 的 價格 cost) 大於 80,000者。

SELECT S,SUM(qty*cost) AS Total

FROM ordering sp JOIN product p

ON sp.p = p.p_id

GROUP BY s

HAVING SUM(qty*cost) > 80000

ORDER BY Total DESC;

 

-- 4. 按廠商編號 由大到小排序，列出 ordering 資料表中 訂購 p 小於等於 '12' 的所有資料列

SELECT *

FROM ordering sp

WHERE p <= '12'

ORDER BY s DESC;

 

 

-- 5. 將 Relation product 中 40 號商品的 價格 改為 199 元

UPDATE product p

SET cost = 199

WHERE p_id = '40';

 

-- 6. 列出 product 資料表中，未被訂購者的 P_id (產品編號)與 cost(單價)。

Select * from product p

Where p_id NOT IN (Select DISTINCT p FROM ordering sp);

 

-- 7. 下列SQL可完成什麼？ 請回答於後：[                                                   ]

SELECT sp.s, SUM(qty*cost) AS Total

FROM ordering sp, product p

WHERE sp.p = p.p_id

GROUP BY sp.s

ORDER BY Total DESC;

 

-- 8. 列出 supplier 資料表中 有訂購 30 號產品者(P=30)的 廠商編號 (s_id), 廠商名稱 (s_name), 與 地址 (address)

SELECT *

FROM supplier s

WHERE s_id IN (SELECT DISTINCT s FROM ordering sp WHERE p = '30');

 

-- 9. 列出訂購總金額加總為後五名者 (最少的5家) 的S,P,qty,cost,與訂購總金額加總

SELECT *, qty*cost AS Total

FROM ordering sp JOIN product p

ON sp.p = p.p_id

ORDER BY Total

LIMIT 5;

 

-- 10.  列出 未有訂購產品 的廠商編號 (s_id)、名稱(s_name)，與地址 (address)。

SELECT *

FROM supplier s

WHERE s.s_id NOT IN (SELECT DISTINCT s FROM ordering sp);

 

-- 11.  列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)、品名(name)，價格 (cost)，和數量(qty)，

-- 以及各項商品的訂購金額 (qty*cost)。

SELECT *

FROM

supplier AS tb1

JOIN ordering AS tb2 ON tb1.s_id = tb2.s

JOIN product AS tb3 ON tb2.p = tb3.p_id;

Quizzes (小考)
1. 列出 廠商之訂購金額加總 (訂購金額 為 table ordering 之訂購量 qty * table product 的 價格 cost) 小於 250,000者。
   >> 寫下 其中最多的一家廠商、與其訂購金額加總 
2. 列出 ordering 資料表中，被訂購總數量 大於 900 者的 P_id (產品編號)與 總數量
   >> 寫下來即可
3. 列出訂購總金額最多的一項商品
   >> 寫下它的 商品編號、與品名
4. 列出有訂購產品的廠商中彈沒有訂購 '10' 商品的  公司編號 (s_id)
   >> 寫下它的 公司編號  即可
5. 列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)> '32' 者、品名(name)，價格 (cost)，和數量(qty)，

以及各項商品的訂購金額 (qty*cost)。

   >> 寫下它的 公司名稱  即可