羅德興老師的教學歷程檔案 - 100-1 計算機概論 - 期末考試 試卷 (06/08)
 

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


歷程檔案 Portfolio

    期末考試 試卷 (06/08)

    期末考時間:
    06/08 12:30-13:15

    期末考補考時間:
    06/15 12:30-13:15

    繳交作業最後期限:
    06/16 10:50-13:15

    -- 期末考試題 Part 1  (20%)
    -- 一、
    -- 以下代碼 _xxx 表示 -您的學號後三碼
    -- 請建立一個 資料庫 test_xxx,完成後請  
    -- (1) 在答案紙上標識出三個表格的關鍵值彼此之間的關係 
    -- (2) 建置資料庫與資料表後展示給老師評分
    -- 有三個表格 (tables) 如下,請寫出SQL解決下列問題:

     

    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),
    ('14', '芒果', 99),
    ('20', '番茄', 165),
    ('21', '香蕉', 140),
    ('23', '蓮霧', 250),
    ('24', '橘子', 100),
    ('25', '椰子', 150),
    ('30', '奇異果', 290),
    ('31', '梨子', 510),
    ('32', '富士蘋果', 410),
    ('33', '小蘋果', 105),
    ('34', '柳丁', 115),
    ('35', '鳳梨', 79),
    ('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 ('1', '35', 150);
    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 ('2', '14', 500);
    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 ('5', '25', 250);
    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);
    INSERT INTO ordering_xxx (s, p, qty) VALUES('6', '35', 120);

    -- 期末考試題複習  Part 2 (80%)
    -- 以下代碼 _xxx 表示 -您的學號後三碼
    -- 二、請依序回答各題,或寫出各題的 SQL 程式並執行
    -- 以下 10 題,每題  8 分。
     
    -- 1.      列出 ordering_xxx 資料表(Relation)中 S (以廠商作為別名 alias)、P、QTY (以訂購量作為別名)。
    -- 請在答案紙上寫下前三筆的結果
     
    -- 2. 列出 廠商之訂購金額加總大於 95,000者。
    -- 訂購金額 為 table ordering_xxx 之訂購量 qty * table product_xxx 的 價格 cost
    -- 請在答案紙上寫下前三筆的結果
     
    -- 3. 按廠商編號 由大到小排序,列出 ordering_xxx 資料表中 訂購 p 小於等於 '15' 的所有資料列
    -- 另存到一個資料表 temp_xxx
    -- 請在答案紙上寫下 temp_xxx 前三筆的結果
     
    -- 4. 列出 product_xxx 資料表中,未被訂購者的 P_id (產品編號)與 cost(單價)。
    -- 請在答案紙上寫下前三筆的結果
     
    -- 5. 列出 supplier_xxx 資料表中 有訂購 35 號產品者(P=30)的 廠商編號 (s_id), 廠商名稱 (s_name), 與 地址 (address)
    -- 請在答案紙上寫下結果
     
    -- 6. 列出訂購總金額加總為前五名者 (最多的5家) 的S,P,qty,cost,與訂購總金額加總
    -- 請在答案紙上寫下前三筆的結果
     
    -- 7.  列出 未有訂購產品 的廠商編號 (s_id)、名稱(s_name),與地址 (address)。
    -- 請在答案紙上寫下前三筆的結果
     
    -- 8. 列出 廠商之訂購金額加總 (訂購金額 為 table ordering_xxx 之訂購量 qty * table product_xxx 的 價格 cost) 小於 250,000者。
    -- >> 寫下 其中最多的一家廠商、與其訂購金額加總
     
    -- 9. 列出 ordering_xxx 資料表中,被訂購總數量 大於 900 者的 P_id (產品編號)與 總數量
    -- >> 在答案紙上寫下來即可
     
    -- 10. 列出有訂購產品的廠商中,但並沒訂購 '35' 商品的  公司編號 (s_id)
    -- >> 寫下它的 公司編號  即可
     
     
     
     
    全部共 21則留言
    06-08 13:42:-- 1. 列出 ordering_xxx 資料表(Relation)中 S (以廠商作為別名 alias)、P、QTY (以訂購量作為別名)。 -- 請在答案紙上寫下前三筆的結果 SELECT s '廠商', p, qty '訂購量' FROM ordering_xxx;
    06-08 13:46:-- 2. 列出 廠商之訂購金額加總大於 95,000者。 -- 訂購金額 為 table ordering_xxx 之訂購量 qty * table product_xxx 的 價格 cost -- 請在答案紙上寫下前三筆的結果 SELECT s, SUM(OO.qty*PP.cost) total FROM ordering_xxx OO JOIN product_xxx PP ON OO.p = PP.p_id GROUP BY s HAVING total > 95000;
    06-08 13:52:-- 3. 按廠商編號 由大到小排序,列出 ordering_xxx 資料表中 訂購 p 小於等於 '15' 的所有資料列 -- 另存到一個資料表 temp_xxx -- 請在答案紙上寫下 temp_xxx 前三筆的結果 CREATE TABLE temp_xxx SELECT * FROM ordering_xxx WHERE p <= '15' ORDER BY s DESC;
    06-08 13:55:-- 4. 列出 product_xxx 資料表中,未被訂購者的 P_id (產品編號)與 cost(單價)。 -- 請在答案紙上寫下前三筆的結果 SELECT p_id, cost FROM product_xxx WHERE p_id NOT IN ( SELECT DISTINCT p FROM ordering_xxx);
    06-08 13:58:-- 5. 列出 supplier_xxx 資料表中 有訂購 35 號產品者(P=30)的 廠商編號 (s_id), 廠商名稱 (s_name), 與 地址 (address) -- 請在答案紙上寫下結果 SELECT * FROM supplier_xxx WHERE s_id IN ( SELECT DISTINCT s FROM ordering_xxx WHERE p = '35');
    06-08 14:02:-- 6. 列出訂購總金額加總為前五名者 (最多的5家) 的S,P,qty,cost,與訂購總金額加總 -- 請在答案紙上寫下前三筆的結果 SELECT s, SUM(OO.qty*PP.cost) total FROM ordering_xxx OO JOIN product_xxx PP ON OO.p = PP.p_id GROUP BY s ORDER BY total DESC;
    06-08 14:04:-- 7. 列出 未有訂購產品 的廠商編號 (s_id)、名稱(s_name),與地址 (address)。 -- 請在答案紙上寫下前三筆的結果 SELECT * FROM supplier_xxx WHERE s_id NOT IN ( SELECT DISTINCT s FROM ordering_xxx);
    06-08 14:06:-- 8. 列出 廠商之訂購金額加總 (訂購金額 為 table ordering_xxx 之訂購量 qty * table product_xxx 的 價格 cost) 小於 250,000者。 -- >> 寫下 其中最多的一家廠商、與其訂購金額加總 SELECT s, SUM(OO.qty*PP.cost) total FROM ordering_xxx OO JOIN product_xxx PP ON OO.p = PP.p_id GROUP BY s HAVING total < 250000 ORDER BY total DESC;
    06-08 14:09:-- 9. 列出 ordering_xxx 資料表中,被訂購總數量 大於 900 者的 P_id (產品編號)與 總數量 -- >> 在答案紙上寫下來即可 SELECT p, SUM(qty) total FROM ordering_xxx OO GROUP BY p HAVING total > 900;
    06-08 14:12:-- 10. 列出有訂購產品的廠商中,但並沒訂購 '35' 商品的 公司編號 (s_id) -- >> 寫下它的 公司編號 即可 SELECT DISTINCT s FROM ordering_xxx WHERE s NOT IN ( SELECT s FROM ordering_xxx WHERE p = '35');
    06-15 14:18:以下為補考試卷參考答案
    06-15 14:19:-- 1. 列出 ordering_xxx 資料表(Relation)中 S (以 供應商作為別名 alias)、P、QTY (以 供應量作為別名)。 -- 請在答案紙上寫下前三筆的結果 SELECT s '供應商', p, qty '供應量' FROM ordering_xxx;
    06-15 14:20:-- 2. 列出 廠商之訂購金額加總小於 85,000者。 -- 訂購金額 為 table ordering_xxx 之訂購量 qty * table product_xxx 的 價格 cost -- 請在答案紙上寫下前三筆的結果 SELECT s, SUM(OO.qty*PP.cost) total FROM ordering_xxx OO JOIN product_xxx PP ON OO.p = PP.p_id GROUP BY s HAVING total < 85000;
    06-15 14:22:-- 3. 按廠商編號 由小到大排序,列出 ordering_xxx 資料表中 訂購 p 小於等於 '25' 的所有資料列 -- 請在答案紙上寫下 前三筆的結果 SELECT * FROM ordering_xxx WHERE p <= '25' ORDER BY s;
    06-15 14:23:-- 4. 列出 product_xxx 資料表中,未被訂購者的 P_id (產品編號)與 cost(單價)。 -- 請在答案紙上寫下前三筆的結果 SELECT p_id, cost FROM product_xxx WHERE p_id NOT IN ( SELECT DISTINCT p FROM ordering_xxx);
    06-15 14:25:-- 5. 列出 supplier_xxx 資料表中 沒有訂購 35 號產品者(P=35)的 廠商編號 (s_id), 廠商名稱 (s_name), 與 地址 (address) -- 請在答案紙上寫下結果 SELECT * FROM supplier_xxx WHERE s_id NOT IN ( SELECT DISTINCT s FROM ordering_xxx WHERE p = '35');
    06-15 14:27:-- 6. 列出訂購總金額加總為後五名者 (最少的5家) 的S,P,qty,cost,與訂購總金額加總 -- 請在答案紙上寫下前三筆的結果 SELECT s, SUM(OO.qty*PP.cost) total FROM ordering_xxx OO JOIN product_xxx PP ON OO.p = PP.p_id GROUP BY s ORDER BY total;
    06-15 14:28:-- 7. 列出 未有訂購產品 的廠商編號 (s_id)、名稱(s_name),與地址 (address)。 -- 請在答案紙上寫下前三筆的結果 SELECT * FROM supplier_xxx WHERE s_id NOT IN ( SELECT DISTINCT s FROM ordering_xxx);
    06-15 14:30:-- 8. 列出 廠商之訂購金額加總 (訂購金額 為 table ordering_xxx 之訂購量 qty * table product_xxx 的 價格 cost) 大於 150,000者。 -- >> 寫下 其中最多的一家廠商、與其訂購金額加總 SELECT s, SUM(OO.qty*PP.cost) total FROM ordering_xxx OO JOIN product_xxx PP ON OO.p = PP.p_id GROUP BY s HAVING total > 150000 ORDER BY total DESC;
    06-15 14:31:-- 9. 列出 ordering_xxx 資料表中,被訂購總數量 小於 500 者的 P_id (產品編號)與 總數量 -- >> 在答案紙上寫下來即可 SELECT p, SUM(qty) total FROM ordering_xxx OO GROUP BY p HAVING total < 500;
    06-15 14:34:-- 10. 列出有訂購產品的廠商中,但並沒訂購 '35' 商品的 公司編號 (s_id) 和地址 -- >> 寫下它的 公司編號 和地址 即可 SELECT s_id, address FROM supplier_xxx WHERE s_id IN ( SELECT DISTINCT s FROM ordering_xxx WHERE s NOT IN ( SELECT s FROM ordering_xxx WHERE p = '35'));
    登入帳號密碼代表遵守學術網路規範


    文章分類 Labels


    最新文章 Top10

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