# 🏅 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) |
-->| | | |
Sign in with Wallet
Connect another wallet