羅德興老師的教學歷程檔案 - 112-2 資料庫管理系統 (DBMS) - 進階 SQL 練習
 

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

 歷程檔案 Portfolio

    進階 SQL 練習

    進階 SQL 練習
    Reference:     https://ocw.mit.edu/courses/urban-studies-and-planning/11-521-spatial-database-management-and-advanced-geographic-information-systems-spring-2003/lecture-notes/lect4.pdf

    -- 期末考試題複習  (資料庫部分同期中考練習)
    -- 以下代碼 _xxx 表示 -您的學號後三碼
    -- Part 1
    -- 一、
    -- 以下代碼 _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);


     
    -- 二、請依序回答各題，或寫出各題的 SQL 程式並執行
    -- 隨機選出 10 題，每題  8 分。
     
    -- 31. 列出 廠商之訂購金額加總大於 80,000者。
    -- 訂購金額 為 table ordering_xxx 之訂購量 qty * table product_xxx 的 價格 cost
    -- 請在答案紙上寫下前三筆的結果
     
    -- 32. 下列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;
     
    -- 33. 列出 supplier_xxx 資料表中 有訂購 30 號產品者(P=30)的 廠商編號 (s_id), 廠商名稱 (s_name), 與 地址 (address)
    -- 請在答案紙上寫下結果
     
    -- 34. 列出訂購總金額加總為後五名者 (最少的5家) 的S,P,qty,cost,與訂購總金額加總
    -- 請在答案紙上寫下前三筆的結果
     
    -- 35.  列出 未有訂購產品 的廠商編號 (s_id)、名稱(s_name)，與地址 (address)。
    -- 請在答案紙上寫下前三筆的結果
     
    -- 36.  列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)、品名(name)，價格 (cost)，和數量(qty)，
    -- 以及各項商品的訂購金額 (qty*cost)。
    -- 請在答案紙上寫下前三筆的結果
     
    -- 37. 列出 廠商之訂購金額加總 (訂購金額 為 table ordering_xxx 之訂購量 qty * table product_xxx 的 價格 cost) 小於 250,000者。
    -- >> 寫下 其中最多的一家廠商、與其訂購金額加總
     
    -- 38. 出訂購總金額最多的一項商品
    -- >> 寫下它的 商品編號、與品名
     
    -- 39. 列出有訂購產品的廠商中，但並沒訂購 '10' 商品的  公司編號 (s_id)、名稱，和地址
    -- >> 寫下它的 公司編號  即可
     
    -- 40. 列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)> '32' 者、品名(name)，價格 (cost)，和數量(qty)，
    -- 以及各項商品的訂購金額 (qty*cost)。
    -- >> 寫下它的 公司名稱  即可
     
    -- 41. 將 表格 (Relation) product_xxx 中 40 號商品的 價格 改為 199 元
    -- >> 請在答案紙上寫下 您的 SQL 程式

    -- 42. 請將您的 資料庫 做備份
    -- >> 請在答案紙上寫下 您的 SQL 程式

    -- 43. 請將您備份的 資料庫 還原
    -- >> 請在答案紙上寫下 您的 SQL 程式


    -- 51. 子查詢 (Subqueries)
    -- 列出 product_xxx 中最高價格 的 所有欄位
    SELECT *
    FROM product_xxx PP
    WHERE PP.cost = 
      (SELECT MAX(PP.cost)
       FROM product_xxx PP);


    -- 52. 子查詢 (Subqueries)
    -- include the subquery as an inline "table" in the FROM clause
    -- 在 FROM 中，將子查詢 做為 行內的 table 
    -- 列出 product_xxx 中最高價格 的 所有欄位
    SELECT *
    FROM product_xxx PP, 
    (SELECT MAX(PP.cost) MAX_COST FROM product_xxx PP) MM
    WHERE PP.cost = MM.MAX_COST; 

    -- 53. 子查詢 (Subqueries)
    -- 列出 未訂購商品的 廠商 的 所有欄位
    SELECT *
    FROM supplier_xxx SS
    WHERE SS.s_id NOT IN
    (SELECT DISTINCT s FROM ordering_xxx); 

    -- 54. 子查詢 (Subqueries)
    -- 列出 未訂購商品的 廠商 的 所有欄位
    -- 更有效率的做法
    SELECT *
    FROM supplier_xxx SS
    WHERE NOT EXISTS
    (SELECT NULL 
    FROM ordering_xxx OO
    WHERE SS.s_id = OO.s);
     
    -- 55. 自我 結合 (Self-Join)
    -- 列出 product 中 廠商  同時訂購 '10' 和 '20'  的 所有欄位
    -- 更有效率的做法
    SELECT *
    FROM ordering_xxx O2, ordering_xxx O3
    WHERE O2.s = O3.s
    AND O2.p = '10'
    AND O3.p = '20';

    -- 56. 自我 結合 (Self-Join)
    -- 列出 product 中 廠商  同時訂購 '10' 和 '20'  且廠商地址在 '台北' 的 所有欄位
    -- 更有效率的做法
    SELECT *, SS.s_name, SS.address
    FROM ordering_xxx O2, ordering_xxx O3, supplier_xxx SS
    WHERE O2.s = O3.s
    AND O2.s = SS.s_id
    AND SS.address = '台北'
    AND O2.p = '10'
    AND O3.p = '20';
    全部共 54則留言
    05-09 10:22:-- 31. 列出 廠商之訂購金額加總大於 80,000者。 -- 訂購金額 為 table ordering_xxx 之訂購量 qty * table product_xxx 的 價格 cost SELECT s ,SUM(qty*cost) CC FROM ordering_012 LILI JOIN product_012 POP ON LILI.p = POP.p_id GROUP BY LILI.s HAVING CC > 80000
    05-09 10:24:-- 32. 下列SQL可完成什麼？ 請在答案紙上寫下答案。 SELECT ordering_012.s, sum(qty*cost) AS total FROM ordering_012, product_012 WHERE ordering_012.p=product_012.p_id GROUP BY ordering_012.s ORDER BY Total DESC;
    05-09 10:25:-- 33. 列出 supplier_xxx 資料表中 有訂購 30 號產品者(P=30)的 廠商編號 (s_id), 廠商名稱 (s_name), 與 地址 (address) -- 請在答案紙上寫下結果 SELECT * FROM supplier_012 WHERE s_id IN (SELECT DISTINCT s FROM ordering_012);
    05-09 10:27:-- 34. 列出訂購總金額加總為後五名者 (最少的5家) 的S,P,qty,cost,與訂購總金額加總 -- 請在答案紙上寫下前三筆的結果 SELECT *, SUM(OO.qty*PP.cost) AS tot FROM ordering_012 OO JOIN product_012 PP ON OO.p = PP.p_id GROUP BY OO.s ORDER BY tot;
    05-09 10:28:-- 35. 列出 未有訂購產品 的廠商編號 (s_id)、名稱(s_name)，與地址 (address)。 -- 請在答案紙上寫下前三筆的結果 SELECT * FROM supplier_012 WHERE s_id NOT IN (SELECT DISTINCT s FROM ordering_012);
    05-09 10:31:-- 36. 列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)、品名(name)，價格 (cost)，和數量(qty)， -- 以及各項商品的訂購金額 (qty*cost)。 -- 請在答案紙上寫下前三筆的結果 SELECT * FROM supplier_012 SS JOIN (SELECT *, OO.qty*PP.cost AS total FROM ordering_012 OO JOIN product_012 PP ON OO.p = PP.p_id) TT ON SS.s_id = TT.s;
    05-09 10:33:-- 37. 列出 廠商之訂購金額加總 (訂購金額 為 table ordering_xxx 之訂購量 qty * table product_xxx 的 價格 cost) 小於 250,000者。 -- >> 寫下 其中最多的一家廠商、與其訂購金額加總 SELECT s, SUM(qty*cost) total FROM ordering_012 OO JOIN product_012 PP ON OO.p=PP.p_id GROUP BY s HAVING total < 250000;
    05-09 10:35:-- 38. 出訂購總金額最多的一項商品 -- >> 寫下它的 商品編號、與品名 SELECT p, name, SUM(qty*cost) total FROM product_012 JOIN ordering_012 ON product_012.p_id=ordering_012.p GROUP BY p ORDER BY total DESC LIMIT 1;
    05-09 10:36:-- 39. 列出有訂購產品的廠商中，但並沒訂購 '10' 商品的 公司編號 (s_id)、名稱，和地址 -- >> 寫下它的 公司編號 即可 SELECT s_id FROM supplier_012 WHERE s_id NOT IN (SELECT DISTINCT s FROM ordering_012 WHERE p=10);
    05-09 10:39:-- 40. 列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)> '32' 者、品名(name)，價格 (cost)，和數量(qty)， -- 以及各項商品的訂購金額 (qty*cost)。 -- >> 寫下它的 公司名稱 即可 SELECT s_id, SS.s_name, address FROM supplier_012 SS WHERE s_id IN (SELECT DISTINCT s FROM ordering_012 OO WHERE OO.p > 32);
    05-09 10:44:-- 16. 將 表格 (Relation) product_xxx 中 40 號商品的 價格 改為 199 元 -- >> 請在答案紙上寫下 您的 SQL 程式 UPDATE product_012 PP SET PP.cost = 199 WHERE PP.p_id = '40';
    05-09 11:27:31. 列出 廠1.商之訂購金額加總大於 80,000者。 -- 訂購金額 為 table ordering_xxx 之訂購量 qty * table product_xxx 的 價格 cost SELECT s ,SUM(qty*cost) CC FROM ordering_017 LILI JOIN product_017 POP ON LILI.p = POP.p_id GROUP BY LILI.s HAVING CC > 80000
    05-09 11:28: 32.下列SQL可完成什麼？ 請在答案紙上寫下答案。 SELECT ordering_017.s, sum(qty*cost) AS total FROM ordering_017, product_017 WHERE ordering_017.p=product_017.p_id GROUP BY ordering_017.s ORDER BY Total DESC;
    05-09 11:29:33.列出 supplier_xxx 資料表中 有訂購 30 號產品者(P=30)的 廠商編號 (s_id), 廠商名稱 (s_name), 與 地址 (address) -- 請在答案紙上寫下結果 SELECT * FROM supplier_017 WHERE s_id IN (SELECT DISTINCT s FROM ordering_017);
    05-09 11:30:34.列出訂購總金額加總為後五名者 (最少的5家) 的S,P,qty,cost,與訂購總金額加總 -- 請在答案紙上寫下前三筆的結果 SELECT *, SUM(OO.qty*PP.cost) AS tot FROM ordering_017 OO JOIN product_017 PP ON OO.p = PP.p_id GROUP BY OO.s ORDER BY tot;
    05-09 11:30:35.列出 未有訂購產品 的廠商編號 (s_id)、名稱(s_name)，與地址 (address)。 -- 請在答案紙上寫下前三筆的結果 SELECT * FROM supplier_017 WHERE s_id NOT IN (SELECT DISTINCT s FROM ordering_017);
    05-09 11:33:36.列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)、品名(name)，價格 (cost)，和數量(qty)， -- 以及各項商品的訂購金額 (qty*cost)。 -- 請在答案紙上寫下前三筆的結果 SELECT * FROM supplier_017 SS JOIN (SELECT *, OO.qty*PP.cost AS total FROM ordering_017 OO JOIN product_017 PP ON OO.p = PP.p_id) TT ON SS.s_id = TT.s;
    05-09 11:34:37.列出 廠商之訂購金額加總 (訂購金額 為 table ordering_xxx 之訂購量 qty * table product_xxx 的 價格 cost) 小於 250,000者。 -- >> 寫下 其中最多的一家廠商、與其訂購金額加總 SELECT s, SUM(qty*cost) total FROM ordering_017 OO JOIN product_017 PP ON OO.p=PP.p_id GROUP BY s HAVING total < 250000;
    05-09 11:34:38.出訂購總金額最多的一項商品 -- >> 寫下它的 商品編號、與品名 SELECT p, name, SUM(qty*cost) total FROM product_017 JOIN ordering_017 ON product_017.p_id=ordering_017.p GROUP BY p ORDER BY total DESC LIMIT 1;
    05-09 11:34:39.列出有訂購產品的廠商中，但並沒訂購 '10' 商品的 公司編號 (s_id)、名稱，和地址 -- >> 寫下它的 公司編號 即可 SELECT s_id FROM supplier_017 WHERE s_id NOT IN (SELECT DISTINCT s FROM ordering_017 WHERE p=10);
    05-09 11:35:40.列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)> '32' 者、品名(name)，價格 (cost)，和數量(qty)， -- 以及各項商品的訂購金額 (qty*cost)。 -- >> 寫下它的 公司名稱 即可 SELECT s_id, SS.s_name, address FROM supplier_017 SS WHERE s_id IN (SELECT DISTINCT s FROM ordering_017 OO WHERE OO.p > 32);
    05-09 11:35:41.將 表格 (Relation) product_xxx 中 40 號商品的 價格 改為 199 元 -- >> 請在答案紙上寫下 您的 SQL 程式 UPDATE product_017 PP SET PP.cost = 199 WHERE PP.p_id = '40';
    05-09 11:42:-- 44. 請將您的 資料匯入資料表 -- >> 請在答案紙上寫下 您的 SQL 程式 load data local infile "C:/Users/user/Downloads/005" into table product_012 fields terminated by ',';
    05-30 10:46:-- 51. 子查詢 (Subqueries) -- 列出 product_012 中最高價格 的 所有欄位 SELECT * FROM product_012 PP WHERE PP.cost = (SELECT MAX(PP.cost) FROM product_012 PP);
    05-30 10:46:-- 52. 子查詢 (Subqueries) -- include the subquery as an inline "table" in the FROM clause -- 在 FROM 中，將子查詢 做為 行內的 table -- 列出 product_012 中最高價格 的 所有欄位 SELECT * FROM product_012 PP, (SELECT MAX(PP.cost) MAX_COST FROM product_012 PP) MM WHERE PP.cost = MM.MAX_COST;
    05-30 10:46:-- 53. 子查詢 (Subqueries) -- 列出 未訂購商品的 廠商 的 所有欄位 SELECT * FROM supplier_012 SS WHERE SS.s_id NOT IN (SELECT DISTINCT s FROM ordering_012);
    05-30 10:46:-- 54. 子查詢 (Subqueries) -- 列出 未訂購商品的 廠商 的 所有欄位 -- 更有效率的做法 SELECT * FROM supplier_012 SS WHERE NOT EXISTS (SELECT NULL FROM ordering_012 OO WHERE SS.s_id = OO.s);
    05-30 10:47:-- 55. 自我 結合 (Self-Join) -- 列出 product 中 廠商 同時訂購 '10' 和 '20' 的 所有欄位 -- 更有效率的做法 SELECT * FROM ordering_012 O2, ordering_012 O3 WHERE O2.s = O3.s AND O2.p = '10' AND O3.p = '20';
    05-30 10:47:-- 56. 自我 結合 (Self-Join) -- 列出 product 中 廠商 同時訂購 '10' 和 '20' 且廠商地址在 '台北' 的 所有欄位 -- 更有效率的做法 SELECT *, SS.s_name, SS.address FROM ordering_012 O2, ordering_012 O3, supplier_012 SS WHERE O2.s = O3.s AND O2.s = SS.s_id AND SS.address = '台北' AND O2.p = '10' AND O3.p = '20';
    05-30 11:07:31. 列出 廠1.商之訂購金額加總大於 80,000者。 -- 訂購金額 為 table ordering_018 之訂購量 qty * table product_018 的 價格 cost SELECT s ,SUM(qty*cost) CC FROM ordering_018 LILI JOIN product_018 POP ON LILI.p = POP.p_id GROUP BY LILI.s HAVING CC > 80000
    05-30 11:07:31. 列出 廠1.商之訂購金額加總大於 80,000者。 -- 訂購金額 為 table ordering_018 之訂購量 qty * table product_018 的 價格 cost SELECT s ,SUM(qty*cost) CC FROM ordering_018 LILI JOIN product_018 POP ON LILI.p = POP.p_id GROUP BY LILI.s HAVING CC > 80000
    05-30 11:42:-- 31. 列出 廠商之訂購金額加總大於 80,000者。 -- 訂購金額 為 table ordering_xxx 之訂購量 qty * table product_xxx 的 價格 cost SELECT s ,SUM(qty*cost) CC FROM ordering_014 LILI JOIN product_014 POP ON LILI.p = POP.p_id GROUP BY LILI.s HAVING CC > 80000
    05-30 11:45: -- 32. 下列SQL可完成什麼？ 請在答案紙上寫下答案。 SELECT ordering_014.s, sum(qty*cost) AS total FROM ordering_014, product_014 WHERE ordering_014.p=product_014.p_id GROUP BY ordering_014.s ORDER BY Total DESC;
    05-30 11:47:-- 33. 列出 supplier_xxx 資料表中 有訂購 30 號產品者(P=30)的 廠商編號 (s_id), 廠商名稱 (s_name), 與 地址 (address) -- 請在答案紙上寫下結果 SELECT * FROM supplier_014 WHERE s_id IN (SELECT DISTINCT s FROM ordering_014);
    05-30 11:48:51. 子查詢 (Subqueries) -- 列出 product_017 中最高價格 的 所有欄位 SELECT * FROM product_017 PP WHERE PP.cost = (SELECT MAX(PP.cost) FROM product_017 PP);
    05-30 11:48:-- 34. 列出訂購總金額加總為後五名者 (最少的5家) 的S,P,qty,cost,與訂購總金額加總 -- 請在答案紙上寫下前三筆的結果 SELECT *, SUM(OO.qty*PP.cost) AS tot FROM ordering_014 OO JOIN product_014 PP ON OO.p = PP.p_id GROUP BY OO.s ORDER BY tot;
    05-30 11:49:52. 子查詢 (Subqueries) -- include the subquery as an inline "table" in the FROM clause -- 在 FROM 中，將子查詢 做為 行內的 table -- 列出 product_017 中最高價格 的 所有欄位 SELECT * FROM product_017 PP, (SELECT MAX(PP.cost) MAX_COST FROM product_017 PP) MM WHERE PP.cost = MM.MAX_COST;
    05-30 11:50:53. 子查詢 (Subqueries) -- 列出 未訂購商品的 廠商 的 所有欄位 SELECT * FROM supplier_017 SS WHERE SS.s_id NOT IN (SELECT DISTINCT s FROM ordering_017);
    05-30 11:51:4. 子查詢 (Subqueries) -- 列出 未訂購商品的 廠商 的 所有欄位 -- 更有效率的做法 SELECT * FROM supplier_017 SS WHERE NOT EXISTS (SELECT NULL FROM ordering_017 OO WHERE SS.s_id = OO.s);
    05-30 11:52:55. 自我 結合 (Self-Join) -- 列出 product 中 廠商 同時訂購 '10' 和 '20' 的 所有欄位 -- 更有效率的做法 SELECT * FROM ordering_017 O2, ordering_017 O3 WHERE O2.s = O3.s AND O2.p = '10' AND O3.p = '20';
    05-30 11:52:-- 35. 列出 未有訂購產品 的廠商編號 (s_id)、名稱(s_name)，與地址 (address)。 -- 請在答案紙上寫下前三筆的結果 SELECT * FROM supplier_014 WHERE s_id NOT IN (SELECT DISTINCT s FROM ordering_014);
    05-30 11:53:56. 自我 結合 (Self-Join) -- 列出 product 中 廠商 同時訂購 '10' 和 '20' 且廠商地址在 '台北' 的 所有欄位 -- 更有效率的做法 SELECT *, SS.s_name, SS.address FROM ordering_017 O2, ordering_017 O3, supplier_017 SS WHERE O2.s = O3.s AND O2.s = SS.s_id AND SS.address = '台北' AND O2.p = '10' AND O3.p = '20';
    05-30 11:53:-- 36. 列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)、品名(name)，價格 (cost)，和數量(qty)， -- 以及各項商品的訂購金額 (qty*cost)。 -- 請在答案紙上寫下前三筆的結果 SELECT * FROM supplier_014 SS JOIN (SELECT *, OO.qty*PP.cost AS total FROM ordering_014 OO JOIN product_014 PP ON OO.p = PP.p_id) TT ON SS.s_id = TT.s;
    05-30 11:54:-- 37. 列出 廠商之訂購金額加總 (訂購金額 為 table ordering_xxx 之訂購量 qty * table product_xxx 的 價格 cost) 小於 250,000者。 -- >> 寫下 其中最多的一家廠商、與其訂購金額加總 SELECT s, SUM(qty*cost) total FROM ordering_014 OO JOIN product_014 PP ON OO.p=PP.p_id GROUP BY s HAVING total < 250000;
    05-30 11:55:-- 38. 出訂購總金額最多的一項商品 -- >> 寫下它的 商品編號、與品名 SELECT p, name, SUM(qty*cost) total FROM product_014 JOIN ordering_014 ON product_014.p_id=ordering_014.p GROUP BY p ORDER BY total DESC LIMIT 1;
    05-30 11:57:-- 39. 列出有訂購產品的廠商中，但並沒訂購 '10' 商品的 公司編號 (s_id)、名稱，和地址 -- >> 寫下它的 公司編號 即可 SELECT s_id FROM supplier_014 WHERE s_id NOT IN (SELECT DISTINCT s FROM ordering_014 WHERE p=10);
    05-30 11:59:-- 40. 列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)> '32' 者、品名(name)，價格 (cost)，和數量(qty)， -- 以及各項商品的訂購金額 (qty*cost)。 -- >> 寫下它的 公司名稱 即可 SELECT s_id, SS.s_name, address FROM supplier_014 SS WHERE s_id IN (SELECT DISTINCT s FROM ordering_014 OO WHERE OO.p > 32);
    05-30 11:59:33.列出 supplier_018 資料表中 有訂購 30 號產品者(P=30)的 廠商編號 (s_id), 廠商名稱 (s_name), 與 地址 (address) -- 請在答案紙上寫下結果 SELECT * FROM supplier_018 WHERE s_id IN (SELECT DISTINCT s FROM ordering_018);
    05-30 12:03:-- 41.將 表格 (Relation) product_xxx 中 40 號商品的 價格 改為 199 元 -- >> 請在答案紙上寫下 您的 SQL 程式 UPDATE product_014 PP SET PP.cost = 199 WHERE PP.p_id = '40';
    03-04 17:57:-- 31. 列出 廠商之訂購金額加總大於 80,000者。 -- 訂購金額 為 table ordering_xxx 之訂購量 qty * table product_xxx 的 價格 cost SELECT B.s AS '訂購商', SUM(F.cost*B.qty) AS total FROM fruit_xxx F INNER JOIN booking_xxx B ON F.p_id=B.p GROUP BY B.s;
    03-04 18:01:-- 31. 列出 廠商之訂購金額加總大於 80,000者。 -- 訂購金額 為 table ordering_xxx 之訂購量 qty * table product_xxx 的 價格 cost SELECT B.s AS '訂購商', SUM(F.cost*B.qty) AS total FROM fruit_xxx F INNER JOIN booking_xxx B ON F.p_id=B.p GROUP BY B.s HAVING total > 80000;
    05-13 17:39:https://www.youtube.com/watch?v=MvcDM2nLdzI
    05-13 17:42:https://www.youtube.com/watch?v=BHwzDmr6d7s Secret To Optimizing SQL Queries - Understand The SQL Execution Order
    05-13 17:46:SQL Beginner to Advanced in One Hour | CareerFoundry Webinar https://www.youtube.com/watch?v=oreAsJTNcsA
    登入帳號密碼代表遵守學術網路規範

    		文章分類 Labels

    		最新文章 Top10

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