# 1201資料庫(實習課)

## HINT
執行順序
`5:Select`
`1:From`
`2:Where`
`3:Group`
`4:Having`
`6:Order By`

---

## 期中講解
### 1. OrderDetails中包含了每筆訂單的資訊，其中ProductID為該訂單訂購的物品<br>請統計OrderDetails每個ProductID的訂購總數(COUNT)，以及該ProductID最高的UnitPrice(MAX_PRICE)
語法執行後必須要有ProductID, COUNT, MAX_PRICE

1. 先觀察資料表
2. 因為是要針對ProductID做統計，可以先對ProductID進行排序

SELECT * FROM OrderDetails
ORDER BY ProductID 

3. 先檢視需求，需求為每個ProductID的訂購總數，以及最高的UnitPrice
4. 其中OrderID的數量即每個ProductID的訂購總數，也可以看到UnitPrice
5. 使用==COUNT()==計算筆數，==MAX()==得到最高價
6. 因為要得到的數值為根據ProductID做分類(彙總函式幾乎都要用到GROUP BY)，所以語法則如下

SELECT ProductID, COUNT(ProductID) [COUNT], MAX(UnitPrice) MAX_PRICE 
FROM OrderDetails
GROUP BY ProductID

#### GROUP BY
1.假設有一份資料表如下
| OrderID | ProductID | UnitPrice |
| ------- | --------- | --------- |
| 10628 | 1 | 18 |
| 10689 | 1 | 17 |
| 10918 | 1 | 19 |
| 10866 | 2 | 19 |
| 10856 | 2 | 21 |
| 10741 | 2 | 10 |
2.如果對ProductID做group by，可以想像成表會被整理成下列的樣子

| OrderID | ProductID | UnitPrice |
| ----------------------- |:---------:| ---------------- |
| 10628<br>10689<br>10918 | 1 | 18 <br>17<br>19 |

| OrderID | ProductID | UnitPrice |
| ----------------------- |:---------:|:--------------- |
| 10866<br>10856<br>10741 | 2 | 19 <br>21<br>10 |
##### <font color="red">實際上並不會被處理成這樣，這是幫助理解所製作的</font>
3. 所以在select時可以針對ProductID直接選取，但是其他欄位選取會出現錯誤

### 2. 有兩個table，分別稱為A、B<br>A用來記錄每筆訂單的資訊，B用來將A中各筆訂單所購買的細項拆開逐筆紀錄<br>如A紀錄了一筆

| 流水號 | 訂購單號 | 訂購者 | 訂購者位置 | 電話 |
| ------ | -------- | ------ | ---------- |:-----:|
| 1 | 00001 | 大熊 | 淡江大學 | 09123 |
### B則記錄了每個單號的購買細項

| 流水號 | 訂購單號 | 訂購項目 | 單價 | 數量 |
| ------ | -------- | -------- | ----- | ---- |
| 1 | 00001 | 電腦 | 25000 | 1 |
| 2 | 00001 | 筆電 | 22000 | 2 |
| 3 | 00001 | 音響 | 3000 | 1 |

### 2-1 請設計一段語法可以將A、B兩張table關聯性結合
1. 一般來說，一個訂購系統(如MOMO)，會將訂購訂單跟明細區分，如上A及B(經過正規化)<br>因此要對不同的表格進行鏈結，需要透過兩張表格的primary key跟foreign key
2. 表A及B的primary key都是流水號，foreign key都是訂購單號(這些都得自己判斷)
3. 因此則利用inner join的方式將兩張表作結合

SELECT * FROM A AS A 
INNER JOIN B AS B ON A.訂購單號 = B.訂購單號

### 2-2 請統計B表中各個訂購單號中，每個訂購項目的價錢總和(TOTAL)，其中單價超過20000的商品須打85折
1. 了解需求，需要得到訂購單號中每個項目的金額總和
2. 單價超過20000要打折
3. 金額總和包刮了被打折後的金額，因此要先處理打折的問題

SELECT (CASE WHEN 單價>20000 THEN 單價*0.85
        ELSE 單價 
        END) AS 打折後單價, *
FROM B

4. 上方指令已經針對打折問題進行處理，所以可以在處理原先的大問題，把價錢做總和並得到下方的資料表(執行上方語法後得到的結果)

| 打折後單價 | 流水號 | 訂購單號 | 訂購項目 | 單價 | 數量 |
| ---------- | ------ | -------- | -------- | ----- | ---- |
| 20000 | 1 | 00001 | 電腦 | 25000 | 1 |
| 17600 | 2 | 00001 | 筆電 | 22000 | 2 |
| 3000 | 3 | 00001 | 音響 | 3000 | 1 |

5. 3.中的語法所選取的資料可以是做一個資料表使用(衍生資料表)

SELECT 訂購單號, SUM(打折後單價*數量) TOTAL
FROM (SELECT (CASE WHEN 單價>20000 THEN 單價*0.85
        ELSE 單價 
        END) AS 打折後單價, *
      FROM B) AS B
GROUP BY 訂購單號

### 3. OrderDetails中包含了每筆訂單的資訊，其中ProductID為該訂單訂購的物品<br>先利用ProductID作分組後，根據每個ProductID的UnitPrice作排名(PRICE_RANK)，排名不得重複
#### e.g. ProductID 1 有兩筆訂單，其中UnitPrice分別為 3、5；ProductID 2有三筆訂單，其中UnitPrice分別為7、2、3，則結果要呈現如下

| ProductID | UnitPrice | PRICE_RANK |
| --------- | --------- | ---------- |
| 1 | 3 | 1 |
| 1 | 5 | 2 |
| 2 | 2 | 1 |
| 2 | 3 | 2 |
| 2 | 7 | 3 |

1. 了解需求，幫每一個ProductID中的UnitPrice做排序
2. 觀察原始資料

SELECT ProductID, UnitPrice 
FROM OrderDetails
ORDER BY  ProductID, UnitPrice

3. 雖然可以透過ORDER得到排序結果，但需要加上額外的排名欄位
4. 排名不得重複->ROW_NUMBER()排序
5. 由於1.，先用partition幫ProductID分割，根據order幫UnitPrice做排序，但因為partition過後，會隨著ProductID有一棟跟著重新做編號，進而得到下方結果(<font color="red">實際上並不會被處理成這樣，這是幫助理解所製作的</font>)

| ProductID | UnitPrice |
| --------- |:----------------- |
| 1 | 18 <br>18<br>18 |
| 2 | 19 <br>19<br>19 |
| 3 | 10<br>10<br>10 |
| 4 | 17.6 <br>22<br>22 |
6. 再針對裡面的UnitPrice做order排序

SELECT ProductID,
	ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY UnitPrice) PRICE_RANK, 
	UnitPrice
FROM OrderDetails

7. partition by跟group by很類似，都有分群的作用，一般在做資料彙總我會用group，排序用partition，當然partition也可以做到group的結果
8. 細節可以參考 https://learnsql.com/blog/difference-between-group-by-partition-by/

### 4. OrderDetails中包含了每筆訂單的資訊，其中ProductID為該訂單訂購的物品<br>Orders紀錄各筆訂單的序號以及訂購者的ID<br>Customers紀錄每個訂購者的資訊
### 4-1 找出每一個訂購者的總消費金額，再幫每一位訂購者的總消費金額作排序(需重複且連續)<br>(需要JOIN三個TABLE，可參考1110資料庫第三題)
1. 先觀察每個資料表的資料
**OrderDetails**
**Orders**
**Customers**
2. 三個資料表有著這樣的關係
3. 先將三個表格用inner join合併成一張表

SELECT * FROM Orders AS O
INNER JOIN OrderDetails AS OD ON O.OrderID = OD.OrderID
INNER JOIN Customers AS C ON O.CustomerID = C.CustomerID


4. 依照需求，先幫所有訂購者的消費作加總
5. 依照上圖已經被合併的表單(先依照CustomerID做排序)，觀察CustomerID有幾筆購買筆數
6. 因為要計算每個CustomerID的總購買金額(group by)，因此需要用 ==SUM()== 來做加總

SELECT O.CustomerID, O.CompanyName, SUM(OD.UnitPrice*OD.Qty) TOTAL FROM Orders AS O
INNER JOIN OrderDetails AS OD ON O.OrderID = OD.OrderID
INNER JOIN Customers AS C ON O.CustomerID = C.CustomerID
GROUP BY O.CustomerID, O.CompanyName

7. 可以想像group by後資料表會變成如下(部分示範)

**Before group**(by CustomerID)
| CustomerID | Name | ProductID | UnitPrice |
| ---------- | ---- | --------- | --------- |
| 1 | A | 28 | 45.6 |
| 1 | A | 39 | 18 |
| 1 | A | 46 | 12 |
| 1 | A | 63 | 43.9 |
| 2 | B | 11 | 21 |
| 2 | B | 13 | 6 |

**After group**(by CustomerID)(示意圖)

| CustomerID | Name | ProductID | UnitPrice |
| ---------- | ---------------- |:---------------------- | ------------------------- |
| 1 | A<br>A<br>A<br>A | 28 <br>39<br>46<br>63 | 45.6 <br>18<br>12<br>43.9 |
| 2 | B <br>B | 11 <br>13 | 21 <br>6 |

**After group**(by CustomerID and Name)<font color="red">(這樣才能select Name)</font>(示意圖)
| CustomerID | Name | ProductID | UnitPrice |
| ---------- | ---- |:---------------------- |:------------------------- |
| 1 | A | 28 <br>39<br>46<br>63 | 45.6 <br>18<br>12<br>43.9 |
| 2 | B | 11 <br>13 | 21 <br>6 |

8. 得到加總過後的資料表

SELECT O.CustomerID, SUM(OD.UnitPrice*OD.Qty) AS TOTAL FROM Orders AS O
INNER JOIN OrderDetails AS OD ON O.OrderID = OD.OrderID
INNER JOIN Customers AS C ON O.CustomerID = C.CustomerID
GROUP BY O.CustomerID

| CustomerID | Name | UnitPrice |
| ---------- | ---- |:--------- |
| 1 | A | 119.5 |
| 2 | B | 27 |
9. 要在為每個CustomerID添加排序，因此將8.得到的結果視為一張表在進行操作(衍生資料表)
10. 排名需重複且連續->==DENSE_RANK()==

SELECT DENSE_RANK() OVER(ORDER BY TOTAL) AS BUY_RANK,* 
FROM (
    8.的語法
    ) AS DT

### 4-2 將(1)中的語法轉成使用Common Table Expressions

WITH CTE AS(
    8.的語法
)
SELECT DENSE_RANK() OVER(ORDER BY TOTAL)  AS BUY_RANK,* FROM CTE;

---

## 課堂講解
### 建立資料庫

CREATE DATABSE DB_NAME

#### 檢查資料庫是否存在

IF DB_ID('TSQL2012') IS NOT NULL 
    DROP DATABASE DB_NAME;

---

### 建立資料表

CREATE TABLE CUSTOMER(
    CUST_ID INT NOT NULL ,
    CUST_IDENT CHAR(10) NOT NULL ,
    LAST_NAME NVARCHAR(255) NOT NULL,
    FIRST_NAME NVARCHAR(255),
    AGE SMALLINT,
	CONSTRAINT PK_EmployeeSSSs PRIMARY KEY(CUST_ID,CUST_IDENT)
);

CHAR型態儲存使用固定長度
VARCHAR型態儲存可變的長度，根據INPUT長度決定空間大小
開頭+N，代表使用Unicode儲存

### 練習 1
#### 新增一個資料表，欄位如下

| 學生學號 | 學生姓名 | 系級 | 選修科目 |
| --------- | -------- | ------ | -------- |
| 405410530 | 李白 | 資工8A | 資料庫 |

---

### 新增資料

INSERT INTO CUSTOMER(CUST_ID,CUST_IDENT,LAST_NAME,FIRST_NAME,AGE) 
VALUES(1,'A12345678','王','大槌','28');

### 練習 2
#### 新增以下四筆資料到資料表內
| 學生學號 | 學生姓名 | 系級 | 選修科目 |
| --------- | -------- | ------ | -------- |
| 405410530 | 李白 | 資工8A | 資料庫 |
| 405411362 | 王大槌 | 資工9A | 資料庫 |
| 405411462 | 李勤 | 資工8C | 資料庫 |
| 405411158 | 陳水餃 | 資工4C | 資料庫 |

---

### 更新資料

UPDATE CUSTOMER SET AGE = 29 WHERE CUST_ID = 1;

### 練習 3
#### 將李白的系級改成資工9A，王大槌的系級改成資工10A

---

### 刪除資料

DELETE FROM CUSTOMER WHERE CUST_ID = 1;

### 練習 4
#### 將姓李的資料移除
HINT. 使用SUBSTRING(欄位,1,1)