羅德興老師的教學歷程檔案 - 112-2 資料庫管理系統 (DBMS) - 資料庫期末考試 D 卷 (進階SQL)
 

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

 歷程檔案 Portfolio

    資料庫期末考試 D 卷 (進階SQL)

    -- 期末考試題
    -- Part 1  (30%)
    -- 一、
    -- 以下代碼 _xxx 表示 -您的學號後三碼
    -- 請建立一個 資料庫     finalexam_xxx   ，完成後請  
    -- (1) 在答案紙上寫下三個表格的欄位 (並標出關鍵值)，與表格之間的關係 
    -- (2) 展示給老師評分
    -- 有三個表格 (tables) 如下，請寫出SQL解決下列問題 1-10：
     
     
    supplier (s_id, s_name, address)
    廠商 (廠商編號，廠商名稱，地址)
     
    fruit (p_id, name, cost)
     
    商品 (商品編號，商品名稱，單價)
     
    booking (s, p, qty)
     
    訂購單  (廠商編號，商品編號，訂購數量)
     
    DROP DATABASE IF EXISTS finalexam_xxx;  -- 刪除資料庫
    CREATE DATABASE `finalexam_xxx` CHARACTER SET utf8; -- 建立資料庫 採 utf8 編碼
    -- 新增 table
     
    USE finalexam_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 fruit_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 booking_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 fruit_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 
    ('大華公司', '台北'),
    ('中華公司', '新北市'),
    ('小華公司', '花蓮'),
    ('大同公司', '高雄'),
    ('大夏公司', '台南'),
    ('大東公司', '台中'),
    ('環球公司', '雲林'),
    ('長榮大公司', '台東市大學路999號'),
    ('中西公司', '宜蘭');
     
    INSERT INTO fruit_xxx
    (p_id, name, cost) VALUES 
    ('10', '蘋果', 110),
    ('11', '青蘋果', 20),
    ('12', '檸檬', 10),
    ('13', '芭樂', 150),
    ('14', '金鑽鳳梨', 179),
    ('20', '番茄', 165),
    ('21', '香蕉', 140),
    ('23', '蓮霧', 250),
    ('24', '橘子', 100),
    ('25', '火龍果', 85),
    ('26', '橘子', 10),
    ('30', '奇異果', 290),
    ('31', '梨子', 510),
    ('32', '富士蘋果', 410),
    ('33', '小蘋果', 105),
    ('35', '鳳梨', 79),
    ('34', '柳丁', 115),
    ('40', '西瓜', 290),
    ('50', '酪梨', 199);
     
    INSERT INTO booking_xxx (s, p, qty) VALUES ('1', '10', 250);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('1', '11', 150);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('1', '12', 250);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('1', '25', 100);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('2', '10', 450);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('2', '11', 5);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('2', '13', 550);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('2', '12', 650);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('2', '25', 200);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('7', '20', 150);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('7', '21', 250);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('7', '23', 550);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('7', '30', 250);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('5', '31', 120);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('5', '32', 50);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('5', '33', 250);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('5', '10', 450);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('5', '11', 250);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('5', '12', 150);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('5', '35', 200);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('5', '20', 50);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('6', '30', 10);
    INSERT INTO booking_xxx (s, p, qty) VALUES('6', '31', 750);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('6', '32', 250);
    INSERT INTO booking_xxx (s, p, qty) VALUES('6', '33', 505);
    INSERT INTO booking_xxx (s, p, qty) VALUES('6', '20', 20);
    INSERT INTO booking_xxx (s, p, qty) VALUES ('6', '35', 100);
     
     
    -- 期中考試題  Part 2 (70%)
    -- 以下代碼 _xxx 表示 -您的學號後三碼
    -- 二、請依序回答各題，或寫出各題的 SQL 程式並執行
    -- 每題 7 分。


    -- 以下代碼 _xxx 表示 -您的學號後三碼
    -- 二、請依序回答各題，或寫出各題的 SQL 程式並執行
    -- 隨機選出 10 題，每題  8 分。


    -- 1. 請做資料庫備份 (Backup)，交給老師評分。
     
    -- 2. 列出 廠商之訂購金額加總 小於 150,000者。
    -- 訂購金額 為 table booking_xxx 之訂購量 qty * table fruit_xxx 的 價格 cost
    -- 請在答案紙上寫下前三筆的結果
      
    -- 3. 列出 supplier_xxx 資料表中 有訂購 25 號產品者(P=25)的 廠商編號 (s_id), 廠商名稱 (s_name), 與 地址 (address)
    -- 請在答案紙上寫下結果
     
    -- 4. 列出 廠商之訂購金額加總 (訂購金額 為 table booking_xxx 之訂購量 qty * table fruit_xxx 的 價格 cost) 大於 100,000者。
    -- >> 寫下 其中最多的一家廠商、與其訂購金額加總
     
    -- 5. 出訂購總金額最多的一項商品
    -- >> 寫下它的 商品編號、與品名
     
    -- 6. 列出有訂購產品的廠商中，但並沒訂購 '30' 商品的  公司編號 (s_id)、名稱，和地址
    -- >> 寫下它的 公司編號  即可
     
    -- 7. 列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id) < '32' 者、品名(name)，價格 (cost)，和數量(qty)，
    -- 以及各項商品的訂購金額 (qty*cost)。
    -- >> 寫下它的 公司名稱  即可
     
    -- 8. 將 表格 (Relation) fruit_xxx 中 50 號商品的 價格 改為 150 元、品名(name) 改為 '新鮮酪梨'
    -- >> 請在答案紙上寫下 您的 SQL 程式

    -- 9. 子查詢 (Subqueries)
    -- 列出 fruit_xxx 中最 低 價格 的 所有欄位

    -- 10. 子查詢 (Subqueries)
    -- 列出 未訂購商品的 廠商 的 所有欄位


    參考解答：
    -- 2. 列出 廠商之訂購金額加總 小於 150,000者。
    -- 訂購金額 為 table booking_xxx 之訂購量 qty * table fruit_xxx 的 價格 cost
    -- 請在答案紙上寫下前三筆的結果
    SELECT s ,SUM(qty*cost) total 
    FROM booking_xxx BB 
    JOIN fruit_xxx FF 
    ON BB.p = FF.p_id 
    GROUP BY BB.s 
    HAVING total < 150000

    -- 3. 列出 supplier_xxx 資料表中 有訂購 25 號產品者(P=25)的 廠商編號 (s_id), 廠商名稱 (s_name), 與 地址 (address)
    -- 請在答案紙上寫下結果
    SELECT *
    FROM supplier_xxx SS
    WHERE SS.s_id IN (
        SELECT DISTINCT s
        FROM booking_xxx BB
        WHERE BB.p= '25')
     
    -- 4. 列出 廠商之訂購金額加總 (訂購金額 為 table booking_xxx 之訂購量 qty * table fruit_xxx 的 價格 cost) 大於 100,000者。
    -- >> 寫下 其中最多的一家廠商、與其訂購金額加總
    SELECT s ,SUM(qty*cost) total 
    FROM booking_xxx BB 
    JOIN fruit_xxx FF 
    ON BB.p = FF.p_id 
    GROUP BY BB.s 
    HAVING total > 100000
    ORDER BY total DESC;

    -- 5. 出訂購總金額最多的一項商品
    -- >> 寫下它的 商品編號、與品名

    SELECT BB.p ,FF.name, SUM(qty*cost) total 
    FROM booking_xxx BB 
    JOIN fruit_xxx FF 
    ON BB.p = FF.p_id 
    GROUP BY BB.p 
    ORDER BY total DESC;

    -- 6. 列出有訂購產品的廠商中，但並沒訂購 '30' 商品的  公司編號 (s_id)、名稱，和地址
    -- >> 寫下它的 公司編號  即可
    SELECT DISTINCT BB.s, SS.s_name, SS.address
    FROM booking_xxx BB JOIN supplier_xxx SS
    ON BB.s = SS.s_id
    WHERE BB.s NOT IN (
        SELECT DISTINCT s
        FROM booking_xxx BB
        WHERE BB.p = '30');
     
    -- 7. 列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id) < '32' 者、品名(name)，價格 (cost)，和數量(qty)，
    -- 以及各項商品的訂購金額 (qty*cost)。
    -- >> 寫下它 (最後三筆)的 公司名稱  即可
    SELECT DISTINCT BB.s, SS.s_name, SS.address, BB.p, FF.name, FF.cost, BB.qty
    FROM booking_xxx BB JOIN supplier_xxx SS
    ON BB.s = SS.s_id JOIN fruit_xxx FF
    ON BB.p = FF.p_id
    WHERE BB.s IN (
        SELECT DISTINCT s
        FROM booking_xxx BB
        WHERE BB.p < '32');
     
     
    -- 8. 將 表格 (Relation) fruit_xxx 中 50 號商品的 價格 改為 150 元、品名(name) 改為 '新鮮酪梨'
    -- >> 請在答案紙上寫下 您的 SQL 程式
    UPDATE fruit_xxx FF
    SET  FF.cost=150, FF.name='新鮮酪梨'
    WHERE FF.p_id = '50';


    -- 9. 子查詢 (Subqueries)
    -- 列出 fruit_xxx 中最 低 價格 的 所有欄位
    SELECT *
    FROM fruit_xxx FF
    WHERE FF.cost = 
      (SELECT MIN(FF.cost)
       FROM fruit_xxx FF);

    -- 10. 子查詢 (Subqueries)
    -- 列出 未訂購商品的 廠商 的 所有欄位
    SELECT *
    FROM supplier_xxx SS
    WHERE SS.s_id NOT IN
    (SELECT DISTINCT s FROM booking_xxx); 
     
     
    全部共 0則留言
    登入帳號密碼代表遵守學術網路規範

    		文章分類 Labels

    		最新文章 Top10

    中華科技大學數位化學習歷程 - 意見反應