Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 30 views

    SQL 命令練習

    Uploaded by

    Will CJ
    The document contains examples of SQL commands for selecting data from tables. Some examples select all records or specific fields from a table. Others apply filters to select records matching certain criteria, such as payment amounts over $1000. Some examples sort results and calculate aggregate values like counts, sums and averages. Joins are demonstrated to combine data from multiple tables.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    SQL 命令練習

    Uploaded by

    Will CJ
    30 views4 pages
    The document contains examples of SQL commands for selecting data from tables. Some examples select all records or specific fields from a table. Others apply filters to select records matching certain criteria, such as payment amounts over $1000. Some examples sort results and calculate aggregate values like counts, sums and averages. Joins are demonstrated to combine data from multiple tables.

    Original Description:

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains examples of SQL commands for selecting data from tables. Some examples select all records or specific fields from a table. Others apply filters to select records matching certain criteria, such as payment amounts over $1000. Some examples sort results and calculate aggregate values like counts, sums and averages. Joins are demonstrated to combine data from multiple tables.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    30 views4 pages

    SQL 命令練習

    Uploaded by

    Will CJ
    The document contains examples of SQL commands for selecting data from tables. Some examples select all records or specific fields from a table. Others apply filters to select records matching certain criteria, such as payment amounts over $1000. Some examples sort results and calculate aggregate values like counts, sums and averages. Joins are demonstrated to combine data from multiple tables.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    of 4

    SQL 命令練習：

    1. 挑選 Customer 表格所有紀錄內的 CustomerID, Name, Address, ZIP 欄位


    SELECT CustomerID, Name, Address, ZIP FROM Customer;

    2. 挑選有購買紀錄的 CustomerID 欄位
    SELECT DISTINCT custID FROM OrderForm;

    3. 挑選購買總金額(TotalPayment)超過(含) 1000 元的 CustomerID, Name, Address,


    TotalPayment
    SELECT CustomerID, Name, Address, TotalPayment FROM Customer WHERE
    TotalPayment >= 1000;

    4. 挑選購買總金額(TotalPayment)介於超過 2000 元與 3000 元 的 CustomerID, Name, Address,


    TotalPayment
    SELECT CustomerID, Name, Address, TotalPayment FROM Customer WHERE
    TotalPayment >= 2000 AND TotalPayment <= 3000;

    5. 由 Cutomer 表格中挑選出最後下訂單日期介於 2004-01-01 與 2004-12-31 的所有記錄


    SELECT * FROM Customer WHERE lastOrderDate >= '2004-01-01' AND lastOrderDate
    <= '2004-12-31';

    6. 由 Cutomer 表格中挑選出居住於台北市、台中市 的所有記錄


    SELECT * FROM Customer WHERE (ZIP >= '100' AND ZIP <= '117') OR (ZIP >=
    '400' AND ZIP <= '408');

    7. 由 OrderForm 表格挑選下列客戶的訂單資訊(CustID, paidAmount,orderDate,orderClosed)


    A0001, A0021, A0022, A0031, A0036
    (1)
    SELECT CustID, paidAmount,orderDate,orderClosed FROM OrderForm
    WHERE CustID = 'A0001' OR CustID = 'A0011' OR CustID = 'A0018'
    OR CustID = 'A0031' OR CustID = 'A0036';
    (2)
    SELECT CustID, paidAmount,orderDate,orderClosed FROM OrderForm
    WHERE CustID IN ('A0001','A0011','A0018', 'A0031','A0036');

    8. 挑選購買總金額(TotalPayment)介於超過 2000 元與 3000 元 的 CustomerID, Name, Address,


    TotalPayment
    SELECT CustomerID, Name, Address, TotalPayment FROM Customer WHERE
    TotalPayment Between 2000 AND 3000;

    9. 挑選 Customer 表格內，張姓客戶的 CustomerID, Name, Address, ZIP 欄位


    SELECT CustomerID, Name, Address, ZIP FROM Customer WHERE Name LIKE '張%';

    10.挑選 Customer 表格內，姓名欄含有'玲'的所有客戶之 CustomerID, Name, Address, ZIP 欄位


    SELECT CustomerID, Name, Address, ZIP FROM Customer WHERE Name LIKE '%玲%';

    11.挑選 Customer 表格內，姓名欄內第三個字為'玲'的所有客戶之 CustomerID, Name, Address, ZIP


    欄位
    SELECT CustomerID, Name, Address, ZIP FROM Customer WHERE Name LIKE '__玲%';

    12.挑選 Customer 表格內，所有客戶之 CustomerID, Name, ZIP, TotalPayment 欄位，


    依 TotalPayment 欄由大到小排列
    SELECT CustomerID, Name, ZIP, TotalPayment FROM Customer ORDER BY TotalPayment
    DESC;

    13.挑選 Customer 表格內，所有客戶之 CustomerID, Name, ZIP, TotalPayment 欄位，


    與 ZIP 欄(由小到大)與 TotalPayment 欄(由大到小)之順序排列
    SELECT CustomerID, Name, ZIP, TotalPayment FROM Customer ORDER BY zip INSC,
    TotalPayment DESC;

    14.挑選 Customer 表格內，居住在台北市之所有客戶的 CustomerID, Name, Address, ZIP,


    TotalPayment 欄位，
    依 TotalPayment 欄由小到大排列
    SELECT CustomerID, Name, Address, ZIP, TotalPayment FROM Customer WHERE ZIP
    LIKE '1%' ORDER BY TotalPayment;

    // NG: SELECT CustomerID, Name, Address, ZIP, TotalPayment FROM Customer ORDER
    BY TotalPayment WHERE ZIP LIKE '1%' ;

    15.計算 Customer 表格內, 所有客戶的個數


    SELECT COUNT(*) FROM Customer ;

    16.挑選 Customer 表格內, 所有客戶的付款總金額


    SELECT SUM(TotalPayment) FROM Customer ;

    17.挑選 OrderForm 表格內, 單筆金額最大者


    SELECT MAX(totalAmount) FROM OrderForm ;

    18.挑選 Product 表格內, 單價最高者，庫存最小者


    SELECT MAX(referencePrice), MIN(qunatityInHAND) FROM Product

    19.挑選 Product 表格內, 單筆金額最大者與它的 ProductID


    SELECT productID, referencePrice FROM Product WHERE referencePrice =
    (SELECT MAX(referencePrice) FROM Product);

    20.挑選 Product 表格內, 庫存最小者與它的 ProductID


    SELECT productID, qunatityInHAND FROM Product WHERE qunatityInHAND =
    (SELECT MIN(qunatityInHAND) FROM Product);

    21 計算 Customer 表格內, 不同 ZIP 的個數


    SELECT COUNT(DISTINCT ZIP) FROM Customer;

    22 計算 Customer 表格內, 不曾購買商品的紀錄的個數


    SELECT COUNT(*) FROM Customer WHERE (lastOrderDate is null);

    23 計算 Customer 表格內, 曾購買過商品的紀錄的個數


    SELECT COUNT(*) FROM Customer WHERE (lastOrderDate is not null);

    24.計算 Customer 表格內, 紀錄筆數，所有付款總金額的總和，與平均付款總金額


    SELECT COUNT(*), Sum(TotalPayment), AVG(TotalPayment) FROM Customer ;

    25. 計算 OrderForm 表格內, 每個客戶的購買次數


    SELECT Custid, COUNT(*) FROM OrderForm GROUP BY CustId;

    26. 同上，依購買次數由大到小排列
    SELECT Custid, COUNT(*) FROM OrderForm GROUP BY CustId ORDER BY COUNT(*) DESC;

    27. 同上，依購買次數由大到小排列，但只顯示購買次數大於 3 的紀錄


    SELECT Custid, COUNT(*) FROM OrderForm GROUP BY CustId
    HAVING COUNT(*) > 3
    ORDER BY COUNT(*) DESC;

    28. 計算 OrderForm 表格內, 每個客戶的購買總金額與付款總金額


    SELECT Custid, sum(totalAmount), sum(Paidamount) FROM OrderForm
    GROUP BY CustId;

    29. 計算 OrderForm 表格內, 每個客戶的未付款金額


    SELECT Custid, sum(totalAmount) - sum(Paidamount) FROM OrderForm
    GROUP BY CustId;
    或
    SELECT Custid, sum(totalAmount- Paidamount) FROM OrderForm
    GROUP BY CustId;

    30. 顯示 OrderForm 表格內, 仍有未付金額的 CustID 及未付金額


    SELECT Custid, sum(totalAmount) - sum(Paidamount) FROM OrderForm
    GROUP BY CustId HAVING sum(totalAmount) - sum(Paidamount) > 0;

    31 別名顯示
    SELECT OF.Custid '客戶編號' , sum(OF.totalAmount) - sum(OF.Paidamount) '差額'
    FROM OrderForm OF GROUP BY CustId
    HAVING sum(totalAmount) - sum(Paidamount) > 0;

    32. 顯示每個客戶的客戶編號、姓名、TOTALAMOUNT，訂購日期等訂單資訊, 並依 CustomerID 排序


    SELECT CustomerID, Name, TOTALAMOUNT, OrderDate
    FROM Customer c, ORDERFORM of
    WHERE C.CustomerID = OF.CUSTID ORDER BY CustomerID ;

    33. 顯示每個客戶編號為 'A0009' 的姓名、TOTALAMOUNT，訂購日期等訂單資訊


    SELECT CustomerID, Name, TOTALAMOUNT, OrderDate
    FROM Customer c, ORDERFORM of
    WHERE C.CustomerID = OF.CUSTID AND C.CustomerID = 'A0009' ;

    34. 顯示每個客戶編號為 'A0009' 的姓名、訂購日期、所有訂購之產品名稱、訂購數量、與單價


    等訂單資訊
    SELECT CustomerID, OrderDate, PRODUCTName, amount, Price
    FROM Customer c, ORDERFORM of, OrderLine, Product
    WHERE CustomerID = 'A0009' AND CustomerID = CUSTID
    AND orderIDOL = ORDERID AND productIDOL = ProductID;

    35. 顯示所有的客戶編號、ORDERID、訂購日期、totalamount、訂購數量 * 單價
    等訂單資訊
    SELECT CustomerID, ORDERID, OrderDate, totalAmount, SUM(amount* Price)
    FROM Customer c, ORDERFORM of, OrderLine, Product
    WHERE CustomerID = CUSTID
    AND orderIDOL = ORDERID AND productIDOL = ProductID
    GROUP BY OrderID;

    36. 顯示所有購買過產品名稱為 'A4 紙 80 磅' 的客戶編號、訂單編號、訂購日期、訂購數量,


    單價, 建議售價等資訊
    SELECT CustomerID, ORDERID, OrderDate, amount, Price
    FROM Customer c, ORDERFORM of, OrderLine, Product
    WHERE CustomerID = CUSTID
    AND orderIDOL = ORDERID AND productIDOL = ProductID
    AND ProductName = 'A4 紙 80 磅'
    GROUP BY OrderID;

    37. 顯示所有購買過產品名稱為 '點陣印表機色帶 50 尺 570C' 或 '雷射印表機 碳粉 258XP'


    的客戶編號、訂單編號、訂購日期、訂購數量,
    單價, 建議售價等資訊
    SELECT CustomerID, ORDERID, OrderDate, amount, Price
    FROM Customer c, ORDERFORM of, OrderLine, Product
    WHERE CustomerID = CUSTID
    AND orderIDOL = ORDERID AND productIDOL = ProductID
    AND ProductName IN ('Q003', '噴墨印表機墨水(彩色) 80cc COLOR 760', '雷射印表機 碳
    粉 258XP')
    GROUP BY OrderID;

    38. 顯示單筆訂單金額(OrderForm.totalAmount) >= 2000 的客戶資訊


    SELECT CustomerID, Name, zip, Address
    FROM Customer WHERE CustomerID IN
    (SELECT CUSTID FROM ORDERFORM WHERE OrderForm.totalAmount >= 2000) ;

    39. 顯示單筆訂單金額(OrderForm.totalAmount) 大於平均金額(OrderForm.totalAmount) 的客戶資


    訊
    SELECT CustomerID, Name, zip, Address
    FROM Customer WHERE CustomerID IN
    (SELECT CUSTID FROM ORDERFORM WHERE OrderForm.totalAmount >= (SELECT
    AVG(TotalAMount) FROM OrderForm)) ;

    40. JOIN
    SELECT * FROM COFFEES CROSS JOIN Suppliers;

    41.
    SELECT COF_Name, PRICE, SUP_Name FROM COFFEES c CROSS JOIN Suppliers s;

    42. 由某個 Key 值開始挑選 n 筆記錄(筆數不能太大)


    (1) SELECT COUNT(*) FROM coffees t2 WHERE t2.cof_code < 5;
    目的：算出 欄位值(cof_code) < 5 的紀錄個數
    SELECT COUNT(*) FROM coffees t2 WHERE t2.sup_id < 1050;
    目的：算出 欄位值(sup_id) < 1050 的紀錄個數

    (2) SELECT * FROM Coffees t1 WHERE


    (SELECT COUNT(*) FROM Coffees t2 WHERE t2.Cof_Code < t1.Cof_Code ) >= 10;
    目的：挑出第 11 筆開始的所有紀錄
    說明：由第一筆開始，逐筆算出小於該筆記錄的個數，如果小於該筆記錄的個數大於等於 10，則挑出此紀錄

    (3) SELECT * FROM Coffees t1 WHERE (SELECT COUNT(*) FROM Coffees t2 WHERE t2.sup_id
    < t1.sup_id ) >= 10 AND
    (SELECT COUNT(*) FROM Coffees t2 WHERE t2.sup_id
    < t1.sup_id ) < 20;
    目的：挑出第 11 筆開始的 10 筆紀錄
    說明：由第一筆開始，逐筆算出小於該筆記錄的個數，如果個數大於等於 10，且小於 20, 則挑出此紀錄

    You might also like