---
# System prepended metadata

title: "\U0001F3C5 Day 18 - SQL 相關語法 (2)"
tags: [2025 Node.js 後端工程師培訓營]

---

# 🏅 Day 18 - SQL 相關語法 (2)

有一間廠商即將建置「**健身教練線上直播課平台**」

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

#### 堂數組合包方案

- 7 堂組合包方案：
    - 價格：1,400 元
- 14 堂方案：
    - 價格：2,520 元
- 21 堂方案：
    - 價格：4,800 元


## 需要必備的知識點

1. [子查詢(子查詢 (Subquery))章節 - 三部影片請都檢視完再作答](https://courses.hexschool.com/courses/2025-node-js/lectures/58664093)


## 情境講解

1. 為滿足商業需求的組合包方案，於是建立了 `CREDIT_PACKAGE` 資料表，來記錄各組合包資訊
2. 客戶在買的訂單記錄，都會記錄在 `CREDIT_PURCHASE` 資料表中


## 資料庫預設資訊與欄位介紹

1. **USER**：使用者資料
2. **CREDIT_PACKAGE**：組合包方案
    * credit_amount：堂數
    * price：金額 
4. **CREDIT_PURCHASE**：客戶購買課程堂數 
    * user_id：使用者(USER) 的 id 資訊
    * purchased_credits：購買堂數
    * price_paid：購買金額

```sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE "USER" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT (gen_random_uuid()),
  "name" varchar(50) NOT NULL,
  "email" varchar(320) UNIQUE NOT NULL,
  "role" varchar(20) NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "updated_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

CREATE TABLE "CREDIT_PACKAGE" (
  "id" serial PRIMARY KEY,
  "name" varchar(50) NOT NULL,
  "credit_amount" integer NOT NULL,
  "price" numeric(10,2) NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

CREATE TABLE "CREDIT_PURCHASE" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT (gen_random_uuid()),
  "user_id" uuid NOT NULL REFERENCES "USER"(id),
  "credit_package_id" integer NOT NULL REFERENCES "CREDIT_PACKAGE"(id),
  "purchased_credits" integer NOT NULL,
  "price_paid" numeric(10,2) NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "purchase_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);
```

## 題目
**請將先下面內容加入到資料庫指令後，再行答題**
```
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE "USER" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT (gen_random_uuid()),
  "name" varchar(50) NOT NULL,
  "email" varchar(320) UNIQUE NOT NULL,
  "role" varchar(20) NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "updated_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

CREATE TABLE "CREDIT_PACKAGE" (
  "id" serial PRIMARY KEY,
  "name" varchar(50) NOT NULL,
  "credit_amount" integer NOT NULL,
  "price" numeric(10,2) NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

CREATE TABLE "CREDIT_PURCHASE" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT (gen_random_uuid()),
  "user_id" uuid NOT NULL REFERENCES "USER"(id),
  "credit_package_id" integer NOT NULL REFERENCES "CREDIT_PACKAGE"(id),
  "purchased_credits" integer NOT NULL,
  "price_paid" numeric(10,2) NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "purchase_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);
```
**並加入預設資料：**
```
INSERT INTO
  "USER"(name, email, role)
VALUES
  ('王小明', 'ming@hexschooltest.io', 'USER'),
  ('好野人', 'richman@hexschooltest.io', 'USER')
```

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

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

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

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

```sql
-- 王小明提示範例
insert into "CREDIT_PURCHASE" (user_id, credit_package_id, purchased_credits, price_paid) values
((select id from "USER" where email = 'wXlTq@hexschooltest.io'),
  (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 堂組合包方案'))
```

<!-- 解答：  
1. 新增：在 CREDIT_PACKAGE 資料表新增三筆資料，資料需求如下：
INSERT INTO "CREDIT_PACKAGE" (name, price, credit_amount)
VALUES ('7 堂組合包方案', 1400, 7),
  ('14 堂組合包方案', 2520, 14),
  ('21 堂組合包方案', 4800, 21);

2. 新增：在 CREDIT_PURCHASE 資料表，新增三筆資料：（請使用 name 欄位做子查詢）
-- 插入三筆資料
INSERT INTO "CREDIT_PURCHASE"(user_id, credit_package_id, purchased_credits, price_paid) 
VALUES
(
  (SELECT id FROM "USER" WHERE email = 'ming@hexschooltest.io'),
  (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 堂組合包方案')
),
(
  (SELECT id FROM "USER" WHERE email = 'ming@hexschooltest.io'),
  (SELECT id FROM "CREDIT_PACKAGE" WHERE name = '21 堂組合包方案'),
  (SELECT credit_amount FROM "CREDIT_PACKAGE" WHERE name = '21 堂組合包方案'),
  (SELECT price FROM "CREDIT_PACKAGE" WHERE name = '21 堂組合包方案')
),
(
  (SELECT id FROM "USER" WHERE email = 'richman@hexschooltest.io'),
  (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 堂組合包方案')
);
-->

回報區
---
| #   | Discord | CodePen / 答案 |
|---|---|---|
| 01    | Tau      | [CodePen](https://codepen.io/Tau-Hsu/pen/zxOQVmm?editors=0010) | 
|  02   | daffytseng      | [CodePen](https://codepen.io/Daffy-Tseng/pen/emOaqZb) | 
|  03  |  sui_hsilan    | [CodePen](https://hackmd.io/seasR9i6RVyDc74R-py8UQ?edit) | 
|  04  |  adengg    | [CodePen](https://codepen.io/Osases/pen/azogoMV?editors=0010) |
|  05  | CC(tofuuu_cat) | [CodePen](https://codepen.io/CC-cat/pen/NPKZWxx?editors=0010) | 
|  06  | 馬德 | [CodePen](https://codepen.io/maywang/pen/QwLRXJM?editors=0010) | 
|  07   | pja._.      | [CodePen](https://codepen.io/PJA0103/pen/gbYNOdP?editors=0010) | 
|  08  | bian_yang_mofa | [CodePen](https://codepen.io/cssf998811/pen/EaYBjPy?editors=0010) | 
| 09 | poyi | [CodePen](https://codepen.io/poyi-the-flexboxer/pen/yyBdORO?editors=0010) |
|  10| benson | [CodePen](https://codepen.io/DevilButler/pen/WbeqwVQ?editors=0010) | 
| 11| Tammy | [CodePen](https://codepen.io/Tammy_Tsai/pen/JoPQEym) | 
| 12| ZoeKang|[CodePen](https://codepen.io/byehywmx-the-animator/pen/azogGZx?editors=0010)|
| 13| helena |[CodePen](https://codepen.io/helena27/pen/emOwKmO)|
| 14| ss585801 |[CodePen](https://codepen.io/ss585801/pen/yyBxBde?editors=0010)|
|15| janetlai|[Codepen](https://codepen.io/eiddkqxz-the-builder/pen/gbYNdWg)
|16| sian|[Codepen](https://codepen.io/uxitysjl-the-flexboxer/pen/azogPoP?editors=0010)
|17| JC|[Codepen](https://codepen.io/lifetimingwhisper/pen/ByBXGJB)
| 18 | hsin yu | [CodePen](https://codepen.io/tina2793778/pen/EaxxjQx) | 
| 19 | HarryKuo | [CodePen](https://codepen.io/harry_kuo/pen/NPWWqVE?editors=0010)|
| 20. |  地呱  | [Codepen](https://codepen.io/wiibxzdw-the-bold/pen/xbxxGQZ?editors=0010) |
|21.|Lee|[CodePen](https://codepen.io/leemo-tseng/pen/raNVPZZ?editors=1010)|
| 22. | JerryOOO |[CodePen](https://codepen.io/fatiangel/pen/raNxXMX?editors=0010)|
|23.|Arsian Kuo|[CodePen](https://codepen.io/Arsian-Kuo/pen/KwKaLYb)|
|24.|wuyuli_21403| [CodePen](https://codepen.io/Job-Wilhelm/pen/wBveGQg?editors=1010) | 
|25.|shiang| [CodePen](https://codepen.io/shiang29/pen/EaxbmKy?editors=0010) | 
-->|     |       | | 

