期中考試題 B 卷

-- 期中考試題 B 卷 part1

-- 以下代碼 _xxx 表示 -您的學號後三碼

-- 有三個表格 (tables) 如下 

 

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

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

-- 新增 table

 

USE test_xxx;

-- 利用 AUTO_INCREMENT 自動累加，給該筆記錄一個獨一無二的編號，

-- 像是給它一個ID，或是一個Index等等，自動從一開始設定的值 (預設是1) 一直累加上去。 

 

CREATE TABLE supplier_xxx (

    s_id int NOT NULL AUTO_INCREMENT,

    s_name char(100),

    address char(120) NOT NULL,

    PRIMARY KEY (s_id)

) CHARACTER SET=utf8, ENGINE=INNODB; 

 

CREATE TABLE product_xxx (

    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_xxx (

    s int NOT NULL,

    p char(10) NOT NULL,

    qty int,

    PRIMARY KEY (s,p),

 

   CONSTRAINT sp_fk2

        FOREIGN KEY (s) REFERENCES supplier_xxx(s_id)

        ON DELETE NO ACTION

        ON UPDATE CASCADE,

 

   CONSTRAINT sp_fk3

        FOREIGN KEY (p) REFERENCES product_xxx(p_id)

        ON DELETE NO ACTION

        ON UPDATE CASCADE

 

 ) CHARACTER SET=utf8, ENGINE=INNODB; 

-- type=innodb 相當於 engine=innodb

-- 新增 table 內的資料

 

INSERT INTO supplier_xxx

(s_name, address) VALUES 

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

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

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

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

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

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

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

 

INSERT INTO product_xxx

(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_xxx (s, p, qty) VALUES ('1', '10', 50);

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

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

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

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

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

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

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

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

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

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

INSERT INTO ordering_xxx (s, p, qty) VALUES ('5', '31', 120);

INSERT INTO ordering_xxx (s, p, qty) VALUES ('5', '32', 50);

INSERT INTO ordering_xxx (s, p, qty) VALUES ('5', '33', 250);

INSERT INTO ordering_xxx (s, p, qty) VALUES ('5', '10', 450);

INSERT INTO ordering_xxx (s, p, qty) VALUES ('5', '11', 250);

INSERT INTO ordering_xxx (s, p, qty) VALUES ('5', '12', 150);

INSERT INTO ordering_xxx (s, p, qty) VALUES ('5', '20', 50);

INSERT INTO ordering_xxx (s, p, qty) VALUES ('6', '30', 10);

INSERT INTO ordering_xxx (s, p, qty) VALUES('6', '31', 750);

INSERT INTO ordering_xxx (s, p, qty) VALUES ('6', '32', 250);

INSERT INTO ordering_xxx (s, p, qty) VALUES('6', '33', 505);

INSERT INTO ordering_xxx (s, p, qty) VALUES('6', '20', 20);

-- 1.      列出 ordering_xxx 資料表(Relation)中 S (以公司作為別名 alias)、P、QTY (以採買量作為別名)。

-- 請在答案紙上寫下前三筆的結果

 

-- 2. ordering_xxx 資料表中，各P (產品編號)之採買量加總

-- 請在答案紙上寫下前三筆的結果

 

-- 3. 列出 公司之採買金額加總小於 210,000者。

-- 採買金額 為 table ordering_xxx 之採買量 qty * table product_xxx 的 價格 cost

-- 請在答案紙上寫下前三筆的結果

 

-- 4. 按公司編號 由小到大排序，列出 ordering_xxx 資料表中 採買 p 小於等於 '12' 的所有資料列

-- 另存到一個資料表 temp_xxx

-- 請在答案紙上寫下 temp_xxx 前三筆的結果

 

-- 5. 列出 product_xxx 資料表中，未被採買者的 P_id (產品編號)與 cost(單價)。

-- 請在答案紙上寫下後三筆的結果

 

-- 6. 下列SQL可完成什麼？ 請在答案紙上寫下答案。                                                   ]

 

-- 7. 列出 supplier_xxx 資料表中 有採買 20 號產品者(P=20)的 公司編號 (s_id), 公司名稱 (s_name), 與 地址 (address)

-- 請在答案紙上寫下結果

 

-- 8. 列出採買總金額加總為前五名者 (最多的5家) 的S,P,qty,cost,與採買總金額加總

-- 請在答案紙上寫下前三筆的結果

 

-- 9.  列出 未有採買產品 的公司編號 (s_id)、名稱(s_name)，與地址 (address)。

-- 請在答案紙上寫下前三筆的結果

 

-- 10.  列出 有採買產品 的公司編號 (s_id) 與地址 (address)，及其採買的商品編號(p_id)、品名(name)，價格 (cost)，和數量(qty)，

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

-- 請在答案紙上寫下前三筆的結果

 

-- 11. 列出 公司之採買金額加總 (採買金額 為 table ordering_xxx 之採買量 qty * table product_xxx 的 價格 cost) 小於 250,000者。

-- >> 寫下 其中最多的一家公司、與其採買金額加總

 

-- 12. 列出 ordering_xxx 資料表中，被採買總數量 大於 900 者的 P_id (產品編號)與 總數量

-- >> 在答案紙上寫下來即可

 

-- 13. 出採買總金額最多的一項商品

-- >> 寫下它的 商品編號、與品名

 

-- 14. 列出有採買產品的公司中，但並沒採買 '10' 商品的  公司編號 (s_id)

-- >> 寫下它的 公司編號  即可

 

-- 15. 列出 有採買產品 的公司編號 (s_id) 與地址 (address)，及其採買的商品編號(p_id)> '32' 者、品名(name)，價格 (cost)，和數量(qty)，

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

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

 

-- 16. 將 表格 (Relation) product_xxx 中 40 號商品的 價格 改為 299 元

-- >> 請在答案紙上寫下 您的 SQL 程式