羅德興老師的教學歷程檔案 - 111-2 三甲 ASP & DB - 期末進度
 

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

 歷程檔案 Portfolio

    期末進度

    期末進度

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



    -- 期末考試題複習 Part 1  (40%)
    -- 一、完成

    Unit 8- MySQL-PHP-TEXT-新增-查詢-刪除-更新範例 

    ......

    作業

    針對前述資料表(四個欄位)寫完整的程式
    完成後，請在本頁面用「留言   回覆」的方式繳交   作業連結網址。



    -- 同期中考試題複習 Part 2 (0%)
    -- 以下代碼 _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 (60%)
    -- 以下代碼 _xxx 表示 -您的學號後三碼
    -- 三、請依序回答各題，或寫出各題的 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

    -- 期末考試題複習  (資料庫部分同期中考練習，如上面之 Part 2)
    -- 以下代碼 _xxx 表示 -您的學號後三碼
     
    -- 請依序回答各題，或寫出各題的 SQL 程式並執行
    -- 隨機選出 10 題，每題  6 分。
     
    -- 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';
    全部共 14則留言
    05-27 09:59:Part 1 以localhost 完成、新增"性別"欄位、變更顏色。
    05-27 09:59:Part 1 以localhost 完成、新增"性別"欄位、變更顏色。
    05-27 14:53:Part 1 以localhost 完成、新增"性別"欄位、變更顏色。
    06-03 15:40:-- 31. 列出 廠商之訂購金額加總大於 80,000者。 -- 訂購金額 為 table ordering_xxx 之訂購量 qty * table product_xxx 的 價格 cost SELECT OO.s, OO.qty, PP.cost, SUM(OO.qty*PP.cost) AS total FROM ordering_xxx OO, product_xxx PP WHERE OO.p= PP.p_id GROUP BY OO.s HAVING total > 80000;
    06-03 15:46:-- 41. 將 表格 (Relation) product_xxx 中 40 號商品的 價格 改為 199 元 -- >> 請在答案紙上寫下 您的 SQL 程式 UPDATE product_xxx PP SET PP.cost=199 WHERE PP.p_id='40';
    06-04 12:40:-- SQL 的進階用法 -- 根據某欄位值，判斷所要顯示的資訊 Select p_id, Case When (cost>=200) Then '高價水果' Else '普通水果' END 價位 From product_xxx;
    06-04 12:46: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; 答案: 用於計算每個供應商所賣出的產品總價格，然後按照總價格由高到低排序。 34. 列出訂購總金額加總為後五名者 (最少的5家) 的S,P,qty,cost,與訂購總金額加總 -- 請在答案紙上寫下前三筆的結果 答案: SELECT o.s AS S, o.p AS P, SUM(o.qty) AS qty, p.cost AS cost, SUM(o.qty * p.cost) AS total FROM ordering_024 o JOIN product_024 p ON o.p = p.p_id GROUP BY o.s, o.p ORDER BY total ASC LIMIT 3; -- 36. 列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)、品名(name)，價格 (cost)，和數量(qty)， -- 以及各項商品的訂購金額 (qty*cost)。 -- 請在答案紙上寫下前三筆的結果 答案: SELECT supplier_024.s_id, supplier_024.address, product_024.p_id, product_024.name, product_024.cost, ordering_024.qty, ordering_024.qty * product_024.cost AS total_cost FROM supplier_024 JOIN ordering_024 ON supplier_024.s_id = ordering_024.s JOIN product_024 ON ordering_024.p = product_024.p_id LIMIT 3; -- 38. 出訂購總金額最多的一項商品 -- >> 寫下它的 商品編號、與品名 答案: SELECT o.p AS Product_ID, p.name AS Product_Name, SUM(o.qty * p.cost) AS Total FROM ordering_024 o JOIN product_024 p ON o.p = p.p_id GROUP BY o.p ORDER BY Total DESC LIMIT 1; -- 40. 列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)> '32' 者、品名(name)，價格 (cost)，和數量(qty)， -- 以及各項商品的訂購金額 (qty*cost)。 -- >> 寫下它的 公司名稱 即可 答案: SELECT s.s_name AS Company_Name, s.address AS Company_Address, o.p AS Product_ID, p.name AS Product_Name, p.cost AS Product_Price, o.qty AS Quantity, o.qty * p.cost AS Total_Price FROM ordering_024 o JOIN product_024 p ON o.p = p.p_id JOIN supplier_024 s ON o.s = s.s_id WHERE o.p > '32' ORDER BY s.s_id; 42. 請將您的 資料庫 做備份 -- >> 請在答案紙上寫下 您的 SQL 程式 答案: CREATE DATABASE IF NOT EXISTS `unit 824` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; USE `unit 824`; 52. 子查詢 (Subqueries) -- include the subquery as an inline "table" in the FROM clause -- 在 FROM 中，將子查詢 做為 行內的 table -- 列出 product_024 中最高價格 的 所有欄位 SELECT * FROM product_024 PP, (SELECT MAX(PP.cost) MAX_COST FROM product_024 PP) MM WHERE PP.cost = MM.MAX_COST; -- 54. 子查詢 (Subqueries) -- 列出 未訂購商品的 廠商 的 所有欄位 -- 更有效率的做法 SELECT * FROM supplier_024 SS WHERE NOT EXISTS (SELECT NULL FROM ordering_024 OO WHERE SS.s_id = OO.s); -- 56. 自我 結合 (Self-Join) -- 列出 product 中 廠商 同時訂購 '10' 和 '20' 且廠商地址在 '台北' 的 所有欄位 -- 更有效率的做法 SELECT *, SS.s_name, SS.address FROM ordering_024 O2, ordering_024 O3, supplier_024 SS WHERE O2.s = O3.s AND O2.s = SS.s_id AND SS.address = '台北' AND O2.p = '10' AND O3.p = '20';
    06-04 12:47:42.備份方法2：在 命令提示字元 C: 下做 (1) 先 cd 到該目錄 (2) mysqldump --no-defaults -u root -p 要備份的資料庫(如 test_xxx) > 備份的 SQL 檔名(如 test_bk2.sql) (3) Key 密碼
    06-04 12:47:42.備份方法2：在 命令提示字元 C: 下做 (1) 先 cd 到該目錄 (2) mysqldump --no-defaults -u root -p 要備份的資料庫(如 test_xxx) > 備份的 SQL 檔名(如 test_bk2.sql) (3) Key 密碼
    06-04 12:48:-- 53. 子查詢 (Subqueries) -- 列出 未訂購商品的 廠商 的 所有欄位 SELECT * FROM supplier_xxx SS WHERE SS.s_id NOT IN (SELECT DISTINCT s FROM ordering_xxx); -- 第 6 行 先做一次子查詢 有訂購的廠商 -- 第 5 行 廠商不在這個子查詢中的 > 那就是 未訂購商品的 廠商
    06-04 12:48:54 同上， 但兩相比較 (查詢用了 ? 秒)，看誰較有效率？ 53 (總計 2 筆, 查詢用了 0.0006 秒。) 54 (總計 2 筆, 查詢用了 0.0004 秒。) 55-56 只是做更多的條件限制。 (1) 有什麼問題嗎？ (2) 也可用 JOIN 再試試！ (3) 並做比較
    06-04 12:50: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; 答案: 用於計算每個供應商所賣出的產品總價格，然後按照總價格由高到低排序。 34. 列出訂購總金額加總為後五名者 (最少的5家) 的S,P,qty,cost,與訂購總金額加總 -- 請在答案紙上寫下前三筆的結果 答案: SELECT o.s AS S, o.p AS P, SUM(o.qty) AS qty, p.cost AS cost, SUM(o.qty * p.cost) AS total FROM ordering_008 o JOIN product_008 p ON o.p = p.p_id GROUP BY o.s, o.p ORDER BY total ASC LIMIT 3; -- 36. 列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)、品名(name)，價格 (cost)，和數量(qty)， -- 以及各項商品的訂購金額 (qty*cost)。 -- 請在答案紙上寫下前三筆的結果 答案: SELECT supplier_008.s_id, supplier_008.address, product_008.p_id, product_008.name, product_008.cost, ordering_008.qty, ordering_008.qty * product_008.cost AS total_cost FROM supplier_008 JOIN ordering_008 ON supplier_008.s_id = ordering_008.s JOIN product_008 ON ordering_008.p = product_008.p_id LIMIT 3; -- 38. 出訂購總金額最多的一項商品 -- >> 寫下它的 商品編號、與品名 答案: SELECT o.p AS Product_ID, p.name AS Product_Name, SUM(o.qty * p.cost) AS Total FROM ordering_008 o JOIN product_008 p ON o.p = p.p_id GROUP BY o.p ORDER BY Total DESC LIMIT 1; -- 40. 列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)> '32' 者、品名(name)，價格 (cost)，和數量(qty)， -- 以及各項商品的訂購金額 (qty*cost)。 -- >> 寫下它的 公司名稱 即可 答案: SELECT s.s_name AS Company_Name, s.address AS Company_Address, o.p AS Product_ID, p.name AS Product_Name, p.cost AS Product_Price, o.qty AS Quantity, o.qty * p.cost AS Total_Price FROM ordering_008 o JOIN product_008 p ON o.p = p.p_id JOIN supplier_008 s ON o.s = s.s_id WHERE o.p > '32' ORDER BY s.s_id; 42. 請將您的 資料庫 做備份 -- >> 請在答案紙上寫下 您的 SQL 程式 答案: CREATE DATABASE IF NOT EXISTS `unit 824` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; USE `unit 824`; 52. 子查詢 (Subqueries) -- include the subquery as an inline "table" in the FROM clause -- 在 FROM 中，將子查詢 做為 行內的 table -- 列出 product_008 中最高價格 的 所有欄位 SELECT * FROM product_008 PP, (SELECT MAX(PP.cost) MAX_COST FROM product_008 PP) MM WHERE PP.cost = MM.MAX_COST; -- 54. 子查詢 (Subqueries) -- 列出 未訂購商品的 廠商 的 所有欄位 -- 更有效率的做法 SELECT * FROM supplier_008 SS WHERE NOT EXISTS (SELECT NULL FROM ordering_008 OO WHERE SS.s_id = OO.s); -- 56. 自我 結合 (Self-Join) -- 列出 product 中 廠商 同時訂購 '10' 和 '20' 且廠商地址在 '台北' 的 所有欄位 -- 更有效率的做法 SELECT *, SS.s_name, SS.address FROM ordering_008 O2, ordering_008 O3, supplier_008 SS WHERE O2.s = O3.s AND O2.s = SS.s_id AND SS.address = '台北' AND O2.p = '10' AND O3.p = '20';
    06-04 14:46:32. 下列SQL可完成什麼？ 使用上述的 SQL 程式可以完成以下事情： 1. 列出每個廠商的訂購金額加總。 2. 將訂購量（qty）和產品價格（cost）相乘，計算出每筆訂單的金額。 3. 根據廠商（s）分組，計算每個廠商的訂購金額總和。 4. 根據訂購金額加總（total）的降序排序。 5. 結果中包含廠商的編號（s）和訂購金額加總（total）。 這個 SQL 程式，可以快速找出訂購金額加總超過特定門檻的廠商，並按照金額加總進行排序。 34. 列出訂購總金額加總為後五名者 (最少的5家) 的S,P,qty,cost,與訂購總金額加總 SELECT o.s, o.p, o.qty, p.cost, SUM(o.qty * p.cost) AS '訂購總金額加總' FROM ordering_026 o JOIN product_026 p ON o.p = p.p_id GROUP BY o.s, o.p, o.qty, p.cost ORDER BY SUM(o.qty * p.cost) ASC LIMIT 5; 36. 列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)、品名(name)，價格 (cost)，和數量(qty)， SELECT s.s_id, s.address, o.p, p.name, p.cost, o.qty, (o.qty * p.cost) AS '訂購金額' FROM supplier_026 s JOIN ordering_026 o ON s.s_id = o.s JOIN product_026 p ON o.p = p.p_id; 38. 列出訂購總金額最多的一項商品 SELECT o.p, p.name FROM ordering_026 o JOIN product_026 p ON o.p = p.p_id GROUP BY o.p, p.name ORDER BY SUM(o.qty * p.cost) DESC LIMIT 1; 40. 列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)> '32' 者、品名(name)，價格 (cost)，和數量(qty)， SELECT s.s_id, s.address, p.name FROM supplier_026 s JOIN ordering_026 o ON s.s_id = o.s JOIN product_026 p ON o.p = p.p_id WHERE o.p > '32'; 42. 請將您的 資料庫 做備份 CREATE TABLE `ordering_026` ( `s` int(11) NOT NULL, `p` char(10) NOT NULL, `qty` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 52. 子查詢 (Subqueries) -- include the subquery as an inline "table" in the FROM clause -- 在 FROM 中，將子查詢 做為 行內的 table SELECT * FROM product_026 PP, (SELECT MAX(PP.cost) MAX_COST FROM product_026 PP) MM WHERE PP.cost = MM.MAX_COST; 54. 子查詢 (Subqueries) -- 列出 未訂購商品的 廠商 的 所有欄位 -- 更有效率的做法 SELECT * FROM supplier_026 SS WHERE NOT EXISTS (SELECT NULL FROM ordering_026 OO WHERE SS.s_id = OO.s); 56. 自我 結合 (Self-Join) -- 列出 product 中 廠商 同時訂購 '10' 和 '20' 且廠商地址在 '台北' 的 所有欄位 -- 更有效率的做法：0.07秒 SELECT *, SS.s_name, SS.address FROM ordering_026 O2, ordering_026 O3, supplier_026 SS WHERE O2.s = O3.s AND O2.s = SS.s_id AND SS.address = '台北' AND O2.p = '10' AND O3.p = '20';
    06-04 14:47:32. 下列SQL可完成什麼？ 使用上述的 SQL 程式可以完成以下事情： 1. 列出每個廠商的訂購金額加總。 2. 將訂購量（qty）和產品價格（cost）相乘，計算出每筆訂單的金額。 3. 根據廠商（s）分組，計算每個廠商的訂購金額總和。 4. 根據訂購金額加總（total）的降序排序。 5. 結果中包含廠商的編號（s）和訂購金額加總（total）。 這個 SQL 程式，可以快速找出訂購金額加總超過特定門檻的廠商，並按照金額加總進行排序。 34. 列出訂購總金額加總為後五名者 (最少的5家) 的S,P,qty,cost,與訂購總金額加總 SELECT o.s, o.p, o.qty, p.cost, SUM(o.qty * p.cost) AS '訂購總金額加總' FROM ordering_026 o JOIN product_026 p ON o.p = p.p_id GROUP BY o.s, o.p, o.qty, p.cost ORDER BY SUM(o.qty * p.cost) ASC LIMIT 5; 36. 列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)、品名(name)，價格 (cost)，和數量(qty)， SELECT s.s_id, s.address, o.p, p.name, p.cost, o.qty, (o.qty * p.cost) AS '訂購金額' FROM supplier_026 s JOIN ordering_026 o ON s.s_id = o.s JOIN product_026 p ON o.p = p.p_id; 38. 列出訂購總金額最多的一項商品 SELECT o.p, p.name FROM ordering_026 o JOIN product_026 p ON o.p = p.p_id GROUP BY o.p, p.name ORDER BY SUM(o.qty * p.cost) DESC LIMIT 1; 40. 列出 有訂購產品 的廠商編號 (s_id) 與地址 (address)，及其訂購的商品編號(p_id)> '32' 者、品名(name)，價格 (cost)，和數量(qty)， SELECT s.s_id, s.address, p.name FROM supplier_026 s JOIN ordering_026 o ON s.s_id = o.s JOIN product_026 p ON o.p = p.p_id WHERE o.p > '32'; 42. 請將您的 資料庫 做備份 CREATE TABLE `ordering_026` ( `s` int(11) NOT NULL, `p` char(10) NOT NULL, `qty` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 52. 子查詢 (Subqueries) -- include the subquery as an inline "table" in the FROM clause -- 在 FROM 中，將子查詢 做為 行內的 table SELECT * FROM product_026 PP, (SELECT MAX(PP.cost) MAX_COST FROM product_026 PP) MM WHERE PP.cost = MM.MAX_COST; 54. 子查詢 (Subqueries) -- 列出 未訂購商品的 廠商 的 所有欄位 -- 更有效率的做法 SELECT * FROM supplier_026 SS WHERE NOT EXISTS (SELECT NULL FROM ordering_026 OO WHERE SS.s_id = OO.s); 56. 自我 結合 (Self-Join) -- 列出 product 中 廠商 同時訂購 '10' 和 '20' 且廠商地址在 '台北' 的 所有欄位 -- 更有效率的做法：0.07秒 SELECT *, SS.s_name, SS.address FROM ordering_026 O2, ordering_026 O3, supplier_026 SS WHERE O2.s = O3.s AND O2.s = SS.s_id AND SS.address = '台北' AND O2.p = '10' AND O3.p = '20';
    登入帳號密碼代表遵守學術網路規範

    		文章分類 Labels

    		最新文章 Top10

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