羅德興老師的教學歷程檔案 - 中華科技大學數位化學習歷程

-- 期中考補考試題

-- 以下代碼 _x 表示 -您的學號後一碼

-- 二、請建立一個資料庫 company_x，完成後請

-- (1) 在答案紙上寫下三個表格的欄位 (並標出關鍵值)，與表格之間的關係

-- (2) 展示給老師評分

- 有三個表格 (tables) 如下，請寫出SQL解決下列問題 1-10：

supplier (s_id, s_name, address)

廠商 (廠商編號，廠商名稱，地址)

product (p_id, name, cost)

商品 (商品編號，商品名稱，單價)

ordering (s, p, qty)

訂購單 (廠商編號，商品編號，訂購數量)

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

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

-- 新增 table

USE company_x;

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

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

CREATE TABLE supplier_x (

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

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

s int NOT NULL,

p char(10) NOT NULL,

qty int,

PRIMARY KEY (s,p),

CONSTRAINT sp_fk2

FOREIGN KEY (s) REFERENCES supplier_x(s_id)

ON DELETE NO ACTION

ON UPDATE CASCADE,

CONSTRAINT sp_fk3

FOREIGN KEY (p) REFERENCES product_x(p_id)

ON DELETE NO ACTION

ON UPDATE CASCADE

) CHARACTER SET=utf8, ENGINE=INNODB;

-- type=innodb 相當於 engine=innodb

-- 新增 table 內的資料

INSERT INTO supplier_x

(s_name, address) VALUES

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

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

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

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

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

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

('長榮大公司', '台東市大學路1號'),

('大海公司', '台北市大海路2020號'),

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

INSERT INTO product_x

(p_id, name, cost) VALUES

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

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

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

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

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

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

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

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

('25', '火龍果', 85),

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

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

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

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

('35', '鳳梨', 79),

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

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

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

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

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

INSERT INTO ordering_x (s, p, qty) VALUES ('1', '25', 100);

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

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

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

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

INSERT INTO ordering_x (s, p, qty) VALUES ('2', '25', 200);

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

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

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

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

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

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

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

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

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

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

INSERT INTO ordering_x (s, p, qty) VALUES ('5', '35', 200);

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

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

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

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

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

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

INSERT INTO ordering_x (s, p, qty) VALUES ('6', '35', 100);

-- 期中考試題 Part 2 (40%)

-- 以下代碼 _x 表示 -您的學號後一碼

-- 三、請依序回答各題，或寫出各題的 SQL 程式並執行

-- 1 - 10 題，每題 5 分

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

-- 另存到一個資料表 temp_x

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

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

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

-- 3. 列出訂購總金額加總為前三名者 (最多的 3 家) 的 s 與訂購總金額加總

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

-- 4. 列出未有訂購產品的廠商編號 (s_id) 以公司編號作為別名、名稱(s_name)，與地址 (address)。

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

-- 5. 列出廠商之訂購金額加總 (訂購金額為 table ordering_x 之訂購量 qty * table product_x 的價格 cost) 大於 170,000者。

-- >> 寫下其中最少的一家廠商、與其訂購金額加總

-- 6. 列出 ordering_x 資料表中，被訂購總數量小於 290 者的 P_id (產品編號)與總數量

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

-- 7. 列出訂購總金額最多的一項商品

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

-- 8. 列出有訂購產品的廠商中，但並沒訂購 '10' 商品的公司編號與名稱

-- >> 寫下它的結果

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

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

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

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

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

資料庫系統實作補考試卷