企業資訊與管理系
助理教授/日導
羅德興

歷程檔案 Portfolio

期中考複習

期中考複習共有 Part 1-3.
請每位同學按分配的題目繳交。
分配方式為：學號末碼為單數者請做單數題，末碼為偶數者請做偶數題。
練習完成後請在本頁面「留言回覆」。

-- 期中考試題複習 Part 1 (30%)

-- 一、完成

Unit 2-MySQL-TEXT-PHP-新增-查詢

陸、作業
擴充 text.php程式，增加另外三個欄位 '班級代碼', '姓名', '地址',

後，請在本頁面用「留言回覆」的方式繳交作業連結網址。

-- 期中考試題複習 Part 2 (20%)

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

-- 二、請建立一個資料庫 test_xxx，完成後請 (15%)
-- (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 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);

-- 期中考試題複習 Part 3 (50%)

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

-- 三、請依序回答各題，或寫出各題的 SQL 程式並執行
-- 1 - 10 題為必答題，每題 5 分

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

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

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

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

-- 3. 列出廠商之訂購金額加總大於 80,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可完成什麼？請在答案紙上寫下答案。 ]

SELECT ordering_xxx.s, sum(qty*cost) AS total FROM ordering_xxx, product_xxx

WHERE ordering_xxx.p=product_xxx.p_id

GROUP BY ordering_xxx.s

ORDER BY Total DESC;

-- 7. 列出 supplier_xxx 資料表中有訂購 30 號產品者(P=30)的廠商編號 (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 號商品的價格改為 199 元

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

全部共 19則留言

04-29 15:07:

留言...

04-29 15:08:

第一題解答 SELECT S '廠商', P '品名', QTY '數量' FROM ordering_024 LIMIT 5 第十一題解答 SELECT s.s_id, s.address, SUM(o.qty * p.cost) AS total_cost FROM supplier_024 s INNER JOIN ordering_024 o ON s.s_id = o.s INNER JOIN product_024 p ON o.p = p.p_id GROUP BY s.s_id, s.address HAVING total_cost < 250000 ORDER BY total_cost DESC LIMIT 1;

04-29 15:08:

1、12

04-29 15:11:

8.SELECT o.s, o.p, o.qty, p.cost, SUM(o.qty*p.cost) AS total_cost FROM ordering_008 o JOIN product_008 p ON o.p = p.p_id GROUP BY o.s, o.p ORDER BY total_cost ASC LIMIT 5; 16.UPDATE product_008 SET cost = 199 WHERE p_id = '40';

04-29 15:23:

-- 第 6 題 SELECT ordering_xxx.s, sum(qty*cost) AS total FROM ordering_xxx, product_xxx WHERE ordering_xxx.p=product_xxx.p_id GROUP BY ordering_xxx.s ORDER BY Total DESC; -- 結合 ordering_xxx, product_xxx 計算每個廠商訂購總金額，依訂購總金額由大到小排列

04-29 15:30:

第二題SELECT P, SUM(quantity) AS total_quantity FROM ordering_xxx GROUP BY P;

04-29 15:31:

第十二題SELECT P_id, SUM(Quantity) as total_quantity FROM ordering_xxx GROUP BY P_id HAVING total_quantity > 900;

04-29 15:36:

第五題：24,100;34,115;40,199，第十三題：31 梨子

04-29 15:42:

-- 第 6 題 SELECT product_xxx.p_id, product_xxx.name, ordering_xxx.s, ordering_xxx.p, ordering_xxx.qty,product_xxx.cost, sum(qty*cost) AS total FROM ordering_xxx, product_xxx WHERE ordering_xxx.p=product_xxx.p_id GROUP BY product_xxx.p_id, ordering_xxx.s ORDER BY product_xxx.p_id, ordering_xxx.s; -- 結合 ordering_xxx, product_xxx 計算每個產品、廠商訂購總金額，依產品編號、廠商編號排列

04-29 16:21:

-- 7. 列出 supplier_xxx 資料表中有訂購 30 號產品者(P=30)的廠商編號 (s_id), 廠商名稱 (s_name), 與地址 (address) SELECT * FROM supplier_xxx WHERE supplier_xxx.s_id IN (SELECT DISTINCT ordering_xxx.s FROM ordering_xxx WHERE ordering_xxx.p='30');

04-29 16:23:

-- 9. 列出未有訂購產品的廠商編號 (s_id)、名稱(s_name)，與地址 (address)。 SELECT * FROM supplier_xxx WHERE supplier_xxx.s_id NOT IN (SELECT DISTINCT ordering_xxx.s FROM ordering_xxx);

04-29 16:52:

-- 10. 列出有訂購產品的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)、品名(name)，價格 (cost)，和數量(qty)， -- 以及各項商品的訂購金額 (qty*cost)。 SELECT OOO.s, SSS.address, OOO.p, PPP.name, OOO.qty, PPP.cost, SUM(OOO.qty*PPP.cost) AS total FROM (supplier_xxx SSS INNER JOIN ordering_xxx OOO ON SSS.s_id = OOO.s) INNER JOIN product_xxx PPP ON OOO.p= PPP.p_id GROUP BY OOO.s, PPP.p_id;

04-29 23:38:

第3題：列出廠商之訂購金額加總大於 80,000者。 SELECT s_name AS Supplier, SUM(qty * cost) AS Total_Price FROM supplier_026 s JOIN ordering_026 o ON s.s_id = o.s JOIN product_026 p ON o.p = p.p_id GROUP BY s_name HAVING SUM(qty * cost) > 80000; 第14題；列出有訂購產品的廠商中，但並沒訂購 '10' 商品的公司編號 (s_id) SELECT DISTINCT s_id FROM supplier_026 INNER JOIN ordering_026 ON supplier_026.s_id = ordering_026.s INNER JOIN product_026 ON ordering_026.p = product_026.p_id WHERE NOT EXISTS ( SELECT * FROM ordering_026 INNER JOIN product_026 ON ordering_026.p = product_026.p_id WHERE ordering_026.s = supplier_026.s_id AND product_026.p_id = '10' );

04-30 12:36:

-- 4. 按廠商編號由大到小排序，列出 ordering_xxx 資料表中訂購 p 小於等於 '12' 的所有資料列 -- 另存到一個資料表 temp_xxx -- 請在答案紙上寫下 temp_xxx 前三筆的結果 CREATE TABLE temp_025 AS SELECT * FROM ordering_025 WHERE P <= 12 ORDER BY S DESC -- 15. 列出有訂購產品的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)> '32' 者、品名(name)，價格 (cost)，和數量(qty)， -- 以及各項商品的訂購金額 (qty*cost)。 -- >> 寫下它的公司名稱即可 SELECT supplier_025.s_id, supplier_025.address, supplier_025.s_name, product_025.p_id, product_025.name, product_025.cost, ordering_025.qty, ordering_025.qty * product_025.cost AS total FROM supplier_025 JOIN ordering_025 ON supplier_025.s_id = ordering_025.s JOIN product_025 ON ordering_025.p = product_025.p_id WHERE product_025.p_id = '32' ORDER BY supplier_025.s_id;

06-05 13:07:

代理劉佳榮作業http://dsluo.rf.gd/?i=2

06-05 13:23:

-- 代劉佳榮作業 -- 1. 列出 ordering_xxx 資料表(Relation)中 S (以廠商作為別名 alias)、P、QTY (以訂購量作為別名)。 -- 請在答案紙上寫下前三筆的結果 select OO.s '廠商', OO.p , OO.qty '訂購量' from ordering_004 OO

06-05 13:29:

-- 代劉佳榮作業 -- 2. ordering_xxx 資料表中，各P (產品編號)之訂購量加總 -- 請在答案紙上寫下前三筆的結果 SELECT OO.p, SUM(qty) AS 總訂購量 FROM ordering_004 OO GROUP BY OO.p;

06-05 13:46:

-- 代劉佳榮作業 -- 3. 列出廠商之訂購金額加總大於 80,000者。 -- 訂購金額為 table ordering_xxx 之訂購量 qty * table product_xxx 的價格 cost -- 請在答案紙上寫下前三筆的結果 SELECT OO.s, SUM(OO.qty*PP.cost) AS total -- SELECT OO.s, OO.p, OO.qty AS 訂購量, PP.p_id, PP.cost, SUM(OO.qty*PP.cost) AS 訂購金額 FROM ordering_004 OO JOIN product_004 PP ON OO.p=PP.p_id GROUP BY OO.s HAVING total > 80000;

06-05 13:54:

-- 代劉佳榮作業 -- 4. 按廠商編號由大到小排序，列出 ordering_xxx 資料表中訂購 p 小於等於 '12' 的所有資料列 -- 另存到一個資料表 temp_xxx -- 請在答案紙上寫下 temp_xxx 前三筆的結果 SELECT OO.s, OO.p, OO.qty -- SELECT OO.s, OO.p, OO.qty AS 訂購量, PP.p_id, PP.cost, SUM(OO.qty*PP.cost) AS 訂購金額 FROM ordering_004 OO WHERE OO.p <=12 ORDER BY OO.s DESC;

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

文章分類 Labels

最新文章 Top10