# 第三堂：前台專案實戰 - 客戶端資料庫規劃

* 記得錄影
* 任務二已開放觀看，週四可提交
* 提交作業教學也已錄製[影音課程](https://courses.hexschool.com/courses/20242/lectures/58618049)

## 子查詢教學
* [Gamma 簡報](https://gamma.app/docs/SQL--23heyix4r93u5u0?mode=doc)

-- 建立部門資料表
CREATE TABLE teams (
    id SERIAL PRIMARY KEY,  -- 部門編號，主鍵
    name VARCHAR(50)         -- 部門名稱
);

-- 建立員工資料表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,     -- 員工編號，主鍵
    name VARCHAR(50),          -- 姓名
    email VARCHAR(100),
    salary INTEGER,            -- 薪資
    team_id INTEGER,           -- 部門編號，外來鍵
    FOREIGN KEY (team_id) REFERENCES teams(id)  -- 設定外來鍵關聯
);

-- 新增部門資料
INSERT INTO teams (name) VALUES 
    ('開發部'),
    ('人事部');

-- 新增員工資料
INSERT INTO users (name,email, salary, team_id) VALUES 
    ('張小明','[e-mail]', 45000, 1),
    ('王大明','[e-mail]',  48000, 1),
    ('李小華','[e-mail]', 43000, 2),
    ('陳小玉','[e-mail]', 55000, 2),
    ('林小豪','[e-mail]', 47000, 1);

## 一對一、一對多、多對多情境
* [Miro 講義](https://miro.com/app/board/uXjVLMcM_hQ=/)
### 部門與員工關係
流程：從欄位角度去規劃
* 以「員工」角度：一個「員工」會有 1 個部門
* 以「部門」角度：**一個「部門」會有 多 個員工**

-- 建立部門資料表
CREATE TABLE teams (
    id SERIAL PRIMARY KEY,  -- 部門編號，主鍵 
    name VARCHAR(50)         -- 部門名稱
);

-- 建立員工資料表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,     -- 員工編號，主鍵 
    name VARCHAR(50),          -- 姓名
    salary INTEGER,            -- 薪資
    team_id INTEGER,           -- 部門編號，外來鍵
    FOREIGN KEY (team_id) REFERENCES teams(id)  -- 設定外來鍵關聯
);

-- 新增部門資料
INSERT INTO teams (name) VALUES 
    ('開發部'),
    ('人事部');

-- 新增員工資料
INSERT INTO users (name, salary, team_id) VALUES 
    ('張小明', 45000, 1),
    ('王大明', 48000, 1),
    ('李小華', 52000, 2),
    ('陳小玉', 55000, 2),
    ('林小豪', 47000, 1);

### 班級與學生資料表關係

-- 建立班級資料表
CREATE TABLE classes (
    id SERIAL PRIMARY KEY,
    class_name VARCHAR(50)
);

-- 建立學生資料表
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    gender VARCHAR(10),
    age INTEGER,
    class_id INTEGER,
    FOREIGN KEY (class_id) REFERENCES classes(id)
);

-- 新增班級資料
INSERT INTO classes (class_name) VALUES 
('三年一班'),
('三年二班');

-- 新增學生資料
INSERT INTO students (name, gender, age, class_id) VALUES 
('小明', '男', 8, 1),
('小華', '女', 9, 2),
('小美', '男', 8, 1),
('小強', '女', 8, 1),
('小智', '男', 9, 2);

## 中場休息

## 分享任務二情境
有一間廠商即將建置「**健身教練線上直播課平台**」

**其優點是：**
- **小班制健身直播課制**：教練會在平台開課，學生前往教練開設的課程頁面報名，課程時間到了後，就可點擊課程直播室進行上課
- 堂數售價：每位健身教練的收費都一致，一堂 50 分鐘**小班制健身直播課**都是 200 元

#### 堂數組合包方案

- 7 堂組合包方案：
- 價格：1,400 元
- 14 堂方案：
- 價格：2,520 元
- 21 堂方案：
- 價格：4,800 元
## 任務二資訊
* [wireframe 線稿圖](https://miro.com/app/board/uXjVLbiDml0=/?share_link_id=404709829822)
* [建立 Table 資料表指令](https://hackmd.io/@hexschool/SkpaT_jfye)
* [資料表全覽](https://images.hexschool.com/common/MTc1Mzc2NDQzNDQ4MzQ1MjMxMzc=_2024-11-26T15:23:35Z.png)

## 1\. 用戶資料，資料表為 `USER`

**1\. 新增**：新增六筆用戶資料，資料如下：

- 用戶名稱為 `李燕容`，Email 為 `[e-mail]`，Role 為 `USER`
- 用戶名稱為 `王小明`，Email 為 `[e-mail]`，Role 為 `USER`
- 用戶名稱為 `肌肉棒子`，Email 為 `[e-mail]`，Role 為 `USER`
- 用戶名稱為 `好野人`，Email 為 `[e-mail]`，Role 為 `USER`
- 用戶名稱為 `Q太郎`，Email 為 `[e-mail]`，Role 為 `USER`
- 用戶名稱為 `透明人`，Email 為 `[e-mail]`，Role 為 `USER`

insert into "USER" (name, email, role) values 
('李燕容', '[e-mail]', 'USER'),
('王小明', '[e-mail]', 'USER'),
('肌肉棒子', '[e-mail]', 'USER'),
('好野人', '[e-mail]', 'USER'),
('Q太郎', '[e-mail]', 'USER'),
('透明人', '[e-mail]', 'USER');

**2\. 修改**：用 Email 找到 `李燕容`、`肌肉棒子`、`Q太郎`，如果他的 Role 為 `USER` 將他的 Role 改為 `COACH`

**3\. 刪除**：刪除 `USER` 資料表中，用 Email 找到 `透明人`，並刪除該筆資料

**4\. 查詢**：取得 `USER` 資料表目前所有用戶數量（提示：使用count函式）

**5\. 查詢**：取得 `USER` 資料表所有用戶資料，並列出前 3 筆（提示：使用limit語法）

## 2\. 組合包方案 `CREDIT_PACKAGE`、客戶購買課程堂數 `CREDIT_PURCHASE`

**1\. 新增**：在`CREDIT_PACKAGE` 資料表新增三筆資料，資料需求如下：

- 名稱為 `7 堂組合包方案`，價格為`1,400` 元，堂數為`7`
- 名稱為`14 堂組合包方案`，價格為`2,520` 元，堂數為`14`
- 名稱為 `21 堂組合包方案`，價格為`4,800` 元，堂數為`21`

insert into "CREDIT_PACKAGE" (name, credit_amount, price) values
('7 堂組合包方案', 7, 1400);

**2\. 新增**：在 `CREDIT_PURCHASE` 資料表，新增三筆資料：（請使用 name 欄位做子查詢）

- `王小明` 購買 `14 堂組合包方案`
- `王小明` 購買 `21 堂組合包方案`
- `好野人` 購買 `14 堂組合包方案`

insert into "CREDIT_PURCHASE" (user_id, credit_package_id, purchased_credits, price_paid) values
((select id from "USER" where email = '[e-mail]'),
  (select id from "CREDIT_PACKAGE" where name = '14 堂組合包方案'),
  (select credit_amount from "CREDIT_PACKAGE" where name = '14 堂組合包方案'),
  (select price from "CREDIT_PACKAGE" where name = '14 堂組合包方案'));

## 3\. 教練資料 ，資料表為 `COACH` ,`SKILL`,`COACH_LINK_SKILL`

**1\. 新增**：在 `COACH` 資料表新增三筆資料，資料需求如下：

- 將用戶 `李燕容` 新增為教練，並且年資設定為2年（提示：使用 `李燕容` 的email ，取得 `李燕容` 的 `id` ）
- 將用戶`肌肉棒子`新增為教練，並且年資設定為2年
- 將用戶`Q太郎`新增為教練，並且年資設定為2年

insert into "COACH" (user_id, experience_years) values 
((select id from "USER" where email = '[e-mail]'), 2)

2. 新增：承1，為三名教練新增專長資料至連結表 `COACH_LINK_SKILL` ，資料需求如下：

- 所有教練都有 `重訓` 專長
- 教練`肌肉棒子` 需要有 `瑜伽` 專長
- 教練`Q太郎` 需要有`有氧運動` 與`復健訓練`專長

insert into "COACH_LINK_SKILL" (coach_id, skill_id) values 
(
  (select id from "COACH" where user_id = (select id from "USER" where email = '[e-mail]')),
  (select id from "SKILL" where name = '重訓')
);

**3\. 修改**：更新教練的經驗年數，資料需求如下：

- 教練`肌肉棒子` 的經驗年數為3年
- 教練`Q太郎` 的經驗年數為5年

update "COACH" 
set experience_years = 3 
where user_id = (
    select id 
    from "USER" 
    where email = '[e-mail]');

**4\. 刪除**：新增一個專長 `空中瑜伽` 至`SKILL`資料表，之後刪除此專長。

insert into "SKILL" (name) values ('空中瑜伽');
delete from "SKILL" where name = '空中瑜伽';

## 4\. 課程管理 `COURSE`

**1\. 新增**：在`COURSE` 新增一門課程，資料需求如下：

- 教練設定為用戶`李燕容`
- 在課程專長 `skill_id` 上設定為「`重訓`」
- 在課程名稱上，設定為「`重訓基礎課`」
- 授課開始時間`start_at`設定為2024-11-25 14:00:00
- 授課結束時間`end_at`設定為2024-11-25 16:00:00
- 最大授課人數`max_participants` 設定為10
- 授課連結設定`meeting_url`為 [https://test-meeting.test.io](https://test-meeting.test.io/)

insert into "COURSE" (user_id, skill_id, name, start_at, end_at, max_participants, meeting_url) values
((select id from "USER" where email = '[e-mail]'),
  (select id from "SKILL" where name = '重訓'),
  '重訓基礎課',
  '2024-11-25 14:00:00',
  '2024-11-25 16:00:00',
  10,
  'https://test-meeting.test.io'
);