羅德興老師的教學歷程檔案 - 107-2 資料庫管理系統 - 1. DB SQL (期中複習作業)
 

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

 歷程檔案 Portfolio

    1. DB SQL (期中複習作業)

    一、期中複習作業一

    DB SQL測驗

    請建立一個 資料庫test2
    有三個    Tables 如下，請寫出SQL解決下列問題：

     

    supplier(id#,addr)

    product(p#, cost)

    sp (s#,p#,qty)

    1 Taipei

    10 210

    1 10 50

    2 宜蘭

    20 300

    1 30 20

     

    30 105

    2 30 10

     

    40 90

    2 20 20

     

     

    3 10 10

     
    1.      列出 SP 資料表(Relation)中S#(以供應商做為別名)、P#、QTY(以供應量做為別名)，產生到一張新的資料表tempa。
    2.      列出 SP 資料表中，各P#(產品編號)之供應量加總。
    3.      列出 供應商之供應總金額加總(為P#之供應量*成本價Cost)大於10,000者之供應商。
    4.      按供應商 由大到小排序，列出 SP 資料表中所有資料列。
     
    5.      因為 Relation (資料表) SP 中，有一個tuple (記錄) 之 S#為 3，參考到S relation (資料表) 中為 null (缺`值)的`，
    故不合referential integrity (參考完整性)，請寫SQL更正此一情形。

     
    6.      列出 P 資料表中，未被訂購者的P#(產品編號)與Cost(單價)。
    7.      下列SQL可完成什麼？
    SELECT SP.S#,SUM(qty*cost) AS Total FROM SP , P
    WHERE SP.P#=P.P# GROUP BY SP.S# ORDER BY Total DESC
    8.      列出有訂購30號產品者(P#=30)的S# 與addr(住址)。
    9.      列出供應總金額加總為前三名者的S#,P#,qty,cost,與供應總金額加總。
    10. 列出 未有訂購產品 的廠商編號與地址。
     



     
    SQL 測驗參考解答



    -- DB SQL 測驗練習 on 2018/10/01
    DROP DATABASE IF EXISTS test0;
    CREATE DATABASE test0;
    USE test0;

    -- 利用 AUTO_INCREMENT 自動累加，給該筆記錄一個獨一無二的編號，像是給它一個ID，或是一個Index等等，自動從一開始設定的值 (預設是1) 一直累加上去。 
    CREATE TABLE supplier (
        id int NOT NULL AUTO_INCREMENT,
        addr varchar(255),
    PRIMARY KEY (id)
    );
     
    CREATE TABLE product (
        p varchar(255) NOT NULL,
        cost int,
    PRIMARY KEY (p)
    );
     
    CREATE TABLE sp (
      s int NOT NULL,
      p varchar(255) NOT NULL,
      qty int,
    PRIMARY KEY (s,p)
    );
     
     
    INSERT INTO supplier
    (addr) VALUES
    ('TAIPEI'),
    ('宜蘭');
     
    INSERT INTO product
    (p, cost) VALUES(10, 210),
    (20,300),
    (30,105),
    (40,90);
     
    INSERT INTO sp 
    (s, p, qty) VALUES
    (1,10,50),
    (1,30,20),
    (2,30,10),
    (2,20,20),
    (3,10,10);

    1.
    SELECT S# AS 供應商,P# ,QTY AS 供應量
    INTO tempa2
    FROM SP
    SELECT *
    FROM tempa2
    2.
    -- SP 資料表中，各P#(產品編號)之供應量加總
    SELECT P#,SUM(qty) AS '供應量加總'
    FROM SP
    GROUP BY P#
     
    -- 3 列出 供應商之供應總金額加總(為P#之供應量*成本價Cost)大於10,000者之供應商。USE test0;
    SELECT s, SUM(qty*cost) AS Total
    FROM sp JOIN product pp
    ON sp.p = pp.p
    GROUP BY s
    HAVING SUM(qty*cost) > 10000
    ORDER BY Total DESC;

     
    --4.按供應商 由大到小排序，列出 SP 資料表中所有資料列
    SELECT *
    FROM SP
    ORDER BY S# DESC
     
    --5.因為 Relation SP 中，有一個tuple之 S#為3，參考到S relation 中為null，
    --故不合referential integrity，請寫SQL更正此一情形。
    - 先檢查

    USE test0;
    SELECT * FROM sp
    WHERE s NOT IN  
    (SELECT id FROM supplier);

    - 再刪除
    USE test0;
    DELETE FROM sp
    WHERE s NOT IN  
    (SELECT id FROM supplier);
     




     
     
    SELECT P.P#,SUM((qty*cost)) AS Total,COUNT(*) AS 筆數
    FROM SP JOIN P
    ON P.P#=SP.P#
    GROUP BY P.P#
    6.
    -- 列出 product  資料表中，未被訂購者的P#(產品編號)與Cost(單價)。
    USE test0;
    SELECT * FROM product
    WHERE p NOT IN 
    (SELECT DISTINCT p FROM sp);
     
    7.自己想吧！
    --  下列SQL可完成什麼？
    SELECT sp.s, SUM(qty*cost) AS Total FROM sp, product
    WHERE sp.p = product.p
    GROUP BY sp.s
    ORDER BY Total DESC;

     
    8.
    -- 8.      列出有訂購 30 號產品者 (p#=30) 的id 與 addr(住址)。
    SELECT id, addr AS '住址'
    FROM supplier
    WHERE id IN
    (SELECT s FROM sp WHERE p = '30');                                             
                             
                          
    9.
    SELECT TOP 3 *,qty*cost AS Total
    FROM SP JOIN P
    ON SP.P# = P.P#
    ORDER BY Total DESC


    -- 9.      列出供應總金額加總為前三名者的S#,P#,qty,cost,與供應總金額加總。
    -- 先列出供應總金額加總為前三名者的S#,P#,qty,cost,與供應總金額加總。
    USE test0;
    CREATE TABLE temp2
    SELECT s, sp.p, qty, cost, qty*cost AS total
    FROM sp JOIN product ON sp.p = product.p
    ORDER BY total DESC
    LIMIT 3;
     
    -- 再列出前項供應總金額加總。
    SELECT SUM(total) FROM temp2;
     
     
    -- 10.列出 未有訂購產品 的廠商編號與地址。
    USE test0;
    SELECT *
    FROM supplier ss
    WHERE ss.id NOT IN (SELECT sp.s FROM sp);
     




     

    全部共 5則留言
    09-29 17:38:資三甲 10111D013 包軒瑋 1.使資料庫具有完整性、延伸性 2.DDL，定義概念和實體階層的內容與其存在關係，也就是描述資料庫中的資料，包括欄位、型態和資料結構 3.SQL，是一種用來從資料庫讀取與儲存資料的電腦語言 4.DCL，提供資料庫安全性 5.ODVC 6. 7. 8. (1)主鍵，每一筆資料都是表格表中的唯一值) (2)超級鍵，單一屬秀性或屬值的集合，但SUPER KEY須滿足其唯一性 (3)候選鍵，在每一個關聯表至少有一個候選鍵，不只要滿足唯一性，還要滿足最小性 (4)替代鍵，在候選鍵中不是主鍵的其他候選鍵都是替代鍵 (5)外來鍵，關聯表的單一或多個屬性的集合，它的屬性值是參考其他關連表的主鍵
    09-29 17:39:資三甲 10111D013 包軒瑋 1.使資料庫具有完整性、延伸性 2.DDL，定義概念和實體階層的內容與其存在關係，也就是描述資料庫中的資料，包括欄位、型態和資料結構 3.SQL，是一種用來從資料庫讀取與儲存資料的電腦語言 4.DCL，提供資料庫安全性 5.ODVC 6. 7. 8. (1)主鍵，每一筆資料都是表格表中的唯一值) (2)超級鍵，單一屬秀性或屬值的集合，但SUPER KEY須滿足其唯一性 (3)候選鍵，在每一個關聯表至少有一個候選鍵，不只要滿足唯一性，還要滿足最小性 (4)替代鍵，在候選鍵中不是主鍵的其他候選鍵都是替代鍵 (5)外來鍵，關聯表的單一或多個屬性的集合，它的屬性值是參考其他關連表的主鍵
    10-06 06:37:留言...
    10-06 06:42:學習單元2：設計一個簡單資料庫的工作 已post到ePortfolio
    10-13 17:18:SELECT *,qty*cost AS Total FROM SP JOIN P ON SP.P# = P.P# ORDER BY Total DESC Limit 3;
    登入帳號密碼代表遵守學術網路規範

    		文章分類 Labels

    		最新文章 Top10

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