# DBMS Test 5 (4/8)

<style>
.markdown-body li + li
{
padding-top: 0 !important;
}
</style>

---

[TOC]

---

## 14. SQL 查詢
### SQL 的演進、分類與使用
- SQL（Strucutre Query Language）
- SQL-92（SQL2）：關聯式資料庫的共通標準語言
- SQL-99（SQL3）：適用於物件導向資料庫
- Oracle：PL/SQL
- Sybase：T-SQL
- Microsoft：T-SQL
- SQL 涵蓋：
- 資料定義語言（Data Definition Language，DDL）：定義資料庫物件
- 資料庫物件：資料庫、資料表、檢視表、索引、預存程序、觸發程序、函數

        CREATE/ALTER/DROP DATABASE/TABLE/INDEX
        CREATE PROCEDURE/TRIGGER/FUNCTION

- 資料處理語言（Data Manipulation Language，DML）：處理資料表、檢視表裡的資料
- 增刪查改

        SELECT/INSERT/UPDATE/DELETE

- 資料控制語言（Data Control Language，DCL）：控制資料表、檢視表之存取權限

        GRANT/REVOKE/COMMIT/ROLLBACK/LOCK/UNLOCK

- 主語言（Host Language）和 SQL 互動的方式：
- 主語言呼叫 SQL 預存程序（Stored Procedure）
- 在主語言中內嵌（Embedded）靜態 SQL 指令
- 在主語言中內嵌（Embedded）動態 SQL 指令

### 查詢資料 - SELECT
- SQL 語法：

    SELECT [ ALL | DISTINCT ]
           [ TOP n [ PERCENT ] [ WITH TIES ] ]
           * | < select_sublist > [ { , < select_sublist > }... ]
           [ INTO new_table ]
      FROM < table_source > [ { , < table_source > }... ]
    [ WHERE search_condition]
    [ GROUP BY group_by_expression ]
    [ HAVING search_condition ]
    [ ORDER BY order_expression [ ASC | DESC ] ]
    ---
    < select_sublist > ::=
          ( table_name | view_name | table_alias ).*
        | ( column_name | expression | IDENTITYCOL | ROWGUIDCOL )
            [ [ AS ] column_alias ]
        | column_alias = expression
    ---
    < table_source > ::=
          table_name [ [ AS ] table_alias ]
        | table_name join_type table_name
          ON join_condition

- 執行順序：
1. `FROM`
2. `WHERE`
3. `GROUP BY`
4. `HAVING`
5. `SELECT`
6. `ORDER BY`

### SELECT（查詢）

SELECT * FROM Product

- AS 皆可省略

SELECT Pno AS 產品代號,
       PName AS 產品名稱,
       Inventory AS 庫存量,
       Price * Inventory AS 庫存成本
  FROM Product
 WHERE Price > 10000
       AND Inventory >= 500

### SELECT INTO（儲存至新資料表）
- `<>` ::= `!=`

SELECT Pno AS 產品代號,
       PName AS 產品名稱,
       Inventory AS 庫存量,
       Price * Inventory AS 庫存成本
  INTO temp
  FROM Product
 WHERE Price <> 10000

SELECT *
  FROM temp

### SELECT ALL | DISTINCT

SELECT DISTINCT Publisher
  FROM Book

### SELECT TOP n | TOP n PERCENT

SELECT TOP 2 Ono
  FROM Orderlist
 WHERE Pno = 'P01'

SELECT TOP 50 PERCENT Ono
  FROM Orderlist
 WHERE Pno = 'P01'

### SELECT AVG | COUNT | MAX | MIN | SUM
- COUNT 計算不包括 NULL 值

SELECT COUNT(*) AS 產品種類,
       AVG(Price) AS 平均單價,
       SUM(Price) AS 總價,
       MIN(Price) AS 單價,
       MAX(Price) AS 最高單價
  FROM Product

SELECT COUNT(DISTINCT Publisher)
  FROM Product

### SELECT .. WHERE（設定查詢條件）
- Wildcard:
- `%` ::= `.*`
- `_` ::= `.`
- `#` ::= `[0-9]`

SELECT S_id, S_name
  FROM Student
 WHERE S_sex = '男'
       AND S_grade LIKE '大%'

SELECT S_name, S_address
  FROM Student
 WHERE S_address LIKE '%台中%'

SELECT *
  FROM Product
 WHERE Date <= #1919/8/10#

SELECT Pno, PName, Price
  FROM Product
 WHERE Price BETWEEN 10000 AND 50000
       AND Inventory >= 100

SELECT Pno, PName, Price
  FROM Product
 WHERE PName IN ('DVD', 'DV')

### SELECT .. GROUP BY（分群）
- 彙總函數以群組為單位執行

SELECT S_sex,
       COUNT(*) AS 人數
  FROM Student
 GROUP BY S_sex

SELECT Publisher AS 出版社,
       COUNT(*) AS 圖書數量
  FROM Book
 WHERE Publisher = '西華'
       OR Publisher = '巨播'
       OR Publisher = '旗標'
 GROUP BY Publisher
 ORDER BY 圖書數量

SELECT Pno, SUM(Amount) AS 出貨數量
  FROM Orderlist
 WHERE Ono Between 1 AND 3
 GROUP BY Pno
 ORDER BY Pno

### SELECT .. HAVING（設定群組或彙總的條件）
- 必須跟隨彙總函數或是分群標準

SELECT Pno, SUM(Amount) AS 出貨數量
  FROM Orderlist
 WHERE Ono Between 1 AND 3
 GROUP BY Pno
HAVING SUM(Amount) > 100
 ORDER BY 出貨數量 DESC

### SELECT .. ORDER BY（排序）
- 預設為遞增

SELECT S_name, S_sex, S_grade
  FROM Student
 ORDER BY S_sex DESC, S_grade

SELECT Pno, SUM(Amount) AS 出貨數量
  FROM Orderlist
 GROUP BY Pno
HAVING SUM(Amount) > 100
 ORDER BY 2 DESC

### SELECT .. JOIN（合併）
- Inner Join：`INNER JOIN` == `JOIN`

SELECT 台北_Sale.Name AS 台北, 台北_Sale.Amount AS 台北銷售量,
       台中_Sale.Name AS 台中, 台中_Sale.Amount AS 台中銷售量
  FROM 台北_Sale INNER JOIN 台中_Sale
       ON 台北_Sale.Name = 台中_Sale.Name

-- Equivalent to the above
SELECT 台北_Sale.Name AS 台北, 台北_Sale.Amount AS 台北銷售量,
       台中_Sale.Name AS 台中, 台中_Sale.Amount AS 台中銷售量
  FROM 台北_Sale, 台中_Sale
 WHERE 台北_Sale.Name = 台中_Sale.Name

SELECT B_name, Au_name, Publisher
  FROM Book JOIN Author
       ON Book.Au_id = Author.Au_id
 WHERE Publisher = '旗標'

- Left Join：`LEFT JOIN` == `LEFT OUTER JOIN`

SELECT 台北_Sale.Name AS 台北, 台北_Sale.Amount AS 台北銷售量,
       台中_Sale.Name AS 台中, 台中_Sale.Amount AS 台中銷售量
  FROM 台北_Sale LEFT JOIN 台中_Sale
       ON 台北_Sale.Name = 台中_Sale.Name

-- Equivalent to the above
SELECT 台北_Sale.Name AS 台北, 台北_Sale.Amount AS 台北銷售量,
       台中_Sale.Name AS 台中, 台中_Sale.Amount AS 台中銷售量
  FROM 台北_Sale, 台中_Sale
 WHERE 台北_Sale.Name *= 台中_Sale.Name

- Right Join：`RIGHT JOIN` == `RIGHT OUTER JOIN`

SELECT 台北_Sale.Name AS 台北, 台北_Sale.Amount AS 台北銷售量,
       台中_Sale.Name AS 台中, 台中_Sale.Amount AS 台中銷售量
  FROM 台北_Sale RIGHT JOIN 台中_Sale
       ON 台北_Sale.Name = 台中_Sale.Name

-- Equivalent to the above
SELECT 台北_Sale.Name AS 台北, 台北_Sale.Amount AS 台北銷售量,
       台中_Sale.Name AS 台中, 台中_Sale.Amount AS 台中銷售量
  FROM 台北_Sale, 台中_Sale
 WHERE 台北_Sale.Name =* 台中_Sale.Name

- Full Join

SELECT 台北_Sale.Name AS 台北, 台北_Sale.Amount AS 台北銷售量,
       台中_Sale.Name AS 台中, 台中_Sale.Amount AS 台中銷售量
  FROM 台北_Sale FULL JOIN 台中_Sale
       ON 台北_Sale.Name = 台中_Sale.Name

- Cross Join（卡笛生乘積）

SELECT 台北_Sale.Name AS 台北, 台北_Sale.Amount AS 台北銷售量,
       台中_Sale.Name AS 台中, 台中_Sale.Amount AS 台中銷售量
  FROM 台北_Sale CROSS JOIN 台中_Sale

-- Equivalent to the above
SELECT 台北_Sale.Name AS 台北, 台北_Sale.Amount AS 台北銷售量,
       台中_Sale.Name AS 台中, 台中_Sale.Amount AS 台中銷售量
  FROM 台北_Sale, 台中_Sale

- Natural Join

SELECT 台北_Sale.Name AS 台北_台中,
       台北_Sale.Amount AS 台北銷售量, 台中_Sale.Amount AS 台中銷售量
  FROM 台北_Sale NATURAL JOIN 台中_Sale

### UNION（聯集）
- 預設刪除重複資料列

SELECT Name FROM 台北_Sale
UNION
SELECT Name FROM 台中_Sale

SELECT Name FROM 台北_Sale
UNION ALL
SELECT Name FROM 台中_Sale

### INTERSECT（交集）

SELECT Name FROM 台北_Sale
INTERSECT
SELECT Name FROM 台中_Sale

### 子查詢（Subquery）
- 單一值比較

SELECT *
  FROM Product
 WHERE Inventory > (SELECT AVG(Inventory)
                      FROM Product)
 ORDER BY Inventory

- IN：判斷資料是否存在於其中
- `IN` == `= ANY`
- `NOT IN` == `<> ANY`

SELECT Pno, PName, Inventory
  FROM Product
 WHERE Pno NOT IN (SELECT Pno
                     FROM In_Product)

SELECT S_name
  FROM Student
 WHERE S_id IN (
       SELECT S_id
         FROM Loan
        WHERE B_code IN (
              SELECT B_code
                FROM Book
               WHERE Publisher = '旗標'))

-- Equivalent to the above
SELECT DISTINCT S_name
  FROM Student, Loan, Book
 WHERE Student.S_id = Loan.S_id
       AND Book.B_code = Loan.B_code
       AND Publisher = '旗標'

SELECT Name, A.Amount
  FROM 台北_Sale A
 WHERE A.Amount > ALL (SELECT TOP 2 Amount
                         FROM 台中_Sale
                        ORDER BY Amount DESC)

- ANY/SOME：判斷是否符合其中一值
- `ANY` == `SOME`

SELECT Name, A.Amount
  FROM 台北_Sale A
 WHERE Name = ANY (SELECT Name
                     FROM 台中_Sale B)

--- Equivalent to the above
SELECT Name, A.Amount
  FROM 台北_Sale A
 WHERE Name IN (SELECT Name
                  FROM 台中_Sale B)

- EXISTS：判斷是否有傳回資料（包括 NULL）

--- Equivalent to the above
SELECT Name, A.Amount
  FROM 台北_Sale A
 WHERE EXISTS (SELECT *
                 FROM 台中_Sale B
                WHERE A.Name = B.Name)

--- Equivalent to the above
SELECT *
  FROM 台北_Sale A
 WHERE EXISTS (SELECT Name
                 FROM 台中_Sale B
                WHERE A.Name = B.Name)

--- Equivalent to the above
SELECT A.*
  FROM 台北_Sale A JOIN 台中_Sale B
       ON A.Name = B.Name

- NOT EXIST：可實現差集

SELECT *
  FROM 台北_Sale A
 WHERE NOT EXISTS (SELECT *
                     FROM 台中_Sale B
                    WHERE A.Name = B.Name)

## 15. SQL 查詢
==（全略，部分合併至上面）==

## 16. SQL 查詢
==（全略，部分合併至上面）==