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

期中考補考試題

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

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

-- 請建立一個資料庫 middle_xxx，完成後請
-- (1) 在答案紙上寫下三個表格的欄位 (並標出關鍵值)，與表格之間的關係

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

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

s_x (s_id, s_name, address) 廠商 (廠商編號，廠商名稱，地址)	p_x (p_id, name, cost) 商品 (商品編號，商品名稱，單價)	o_x (s, p, qty) 訂購單 (廠商編號，商品編號，訂購數量)

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

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

-- 新增 table

USE middle_x;

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

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

CREATE TABLE s_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 p_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 o_x (

s int NOT NULL,

p char(10) NOT NULL,

qty int,

PRIMARY KEY (s,p),

CONSTRAINT sp_fk2

FOREIGN KEY (s) REFERENCES s_x(s_id)

ON DELETE NO ACTION

ON UPDATE CASCADE,

CONSTRAINT sp_fk3

FOREIGN KEY (p) REFERENCES p_x(p_id)

ON DELETE NO ACTION

ON UPDATE CASCADE

) CHARACTER SET=utf8, ENGINE=INNODB;

-- type=innodb 相當於 engine=innodb

-- 新增 table 內的資料

INSERT INTO s_x

(s_name, address) VALUES

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

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

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

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

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

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

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

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

INSERT INTO p_x

(p_id, name, cost) VALUES

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

INSERT INTO o_x (s, p, qty) VALUES ('1', '25', 1000);

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

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

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

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

INSERT INTO o_x (s, p, qty) VALUES ('2', '25', 2000);

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

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

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

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

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

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

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

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

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

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

INSERT INTO o_x (s, p, qty) VALUES ('5', '35', 2000);

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

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

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

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

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

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

INSERT INTO o_x (s, p, qty) VALUES ('6', '35', 1000);

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

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

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

-- 1 - 10 題，每題 6 分。

-- 1. 列出 o_x 資料表(Relation)中 s 是 "2" 的所有紀錄。

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

-- 2. 列出 p_x 資料表(Relation)中 p_id 介於 "30" 到 "35"的所有紀錄，按商品編號由大到小排列。

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

-- 3. 列出 s_x 資料表(Relation)中廠商名稱 (s_name)中有 "中" 的所有紀錄，按廠商編號由大到小排列。

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

-- 4. 列出 o_x 資料表(Relation)中有訂購商品編號 (p) 是 "35" 的所有紀錄。

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

-- 5. 列出 o_x 資料表(Relation)中有訂購商品編號 (p) 是 "35" 的所有紀錄的訂購數量 (qty)加總。

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

-- 6. 查詢 s_x 資料表(Relation)中廠商地址 (address)中有 "北" 的所有紀錄，按廠商編號由大到小排列。

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

-- 7. 列出 o_x 資料表(Relation)中訂購數量 (qty)小於 350 的所有紀錄，按商品編號 (p) 由小到大排列。

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

-- 8. 列出 o_x 資料表(Relation)中每個商品編號 (p) 的訂購數量 (qty) 總和，按總和由大到小排列。

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

-- 9. 將 s_x 資料表(Relation)中廠商編號 (s_id) 為 "6" 的廠商地址 (address) 更新為 "新竹市科園區研究院路1段234號"。

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

-- 10. 將 o_x 資料表(Relation)中廠商編號 "5" 訂購商品編號 (p) "31" 的紀錄刪除。

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

全部共 1則留言

05-01 10:02:

-- 1. 列出 o_x 資料表(Relation)中 s 是 "2" 的所有紀錄。 -- 請在答案紙上寫下結果 -- SELECT * -- FROM o_x -- WHERE s='2'; -- 2. 列出 p_x 資料表(Relation)中 p_id 介於 "30" 到 "35"的所有紀錄，按商品編號由大到小排列。 -- 請在答案紙上寫下結果 -- SELECT * -- FROM p_x -- WHERE (p_id >= '30') AND (p_id <= '35') -- ORDER BY p_id; -- 3. 列出 s_x 資料表(Relation)中廠商名稱 (s_name)中有 "中" 的所有紀錄，按廠商編號由大到小排列。 -- 請在答案紙上寫下結果 -- SELECT * -- FROM s_x -- WHERE s_name LIKE "%中%" -- ORDER BY s_id; -- 4. 列出 o_x 資料表(Relation)中有訂購商品編號 (p) 是 "35" 的所有紀錄。 -- 請在答案紙上寫下結果 -- SELECT * -- FROM o_x -- WHERE p = "35"; -- 5. 列出 o_x 資料表(Relation)中有訂購商品編號 (p) 是 "35" 的所有紀錄的訂購數量 (qty)加總。 -- 請在答案紙上寫下結果 -- SELECT p, SUM(qty) -- FROM o_x -- GROUP BY p -- HAVING p='35'; -- 6. 查詢 s_x 資料表(Relation)中廠商地址 (address)中有 "北" 的所有紀錄，按廠商編號由大到小排列。 -- 請在答案紙上寫下結果 -- SELECT * -- FROM s_x -- WHERE address LIKE "%北%" -- ORDER BY s_id DESC; -- 7. 列出 o_x 資料表(Relation)中訂購數量 (qty)小於 350 的所有紀錄，按商品編號 (p) 由小到大排列。 -- 請在答案紙上寫下前三筆的結果 -- SELECT * -- FROM o_x -- WHERE qty < 350 -- ORDER BY p; -- 8. 列出 o_x 資料表(Relation)中每個商品編號 (p) 的訂購數量 (qty) 總和，按總和由大到小排列。 -- 請在答案紙上寫下結果 SELECT p, SUM(qty) AS total FROM o_x GROUP BY p ORDER BY total DESC;

登入帳號密碼代表遵守學術網路規範