11/26 每日任務

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

其優點是：

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

堂數組合包方案

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

需要必備的知識點

  1. 子查詢(子查詢 (Subquery))章節 - 三部影片請都檢視完再作答

情境講解

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

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

  1. USER：使用者資料
  2. CREDIT_PACKAGE：組合包方案
    • credit_amount：堂數
    • price：金額
  3. CREDIT_PURCHASE：客戶購買課程堂數
    • user_id：使用者(USER) 的 id 資訊
    • purchased_credits：購買堂數
    • price_paid：購買金額
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)
);

題目

請將先 11/25 每日任務的使用者題目加入到資料庫指令後，再行答題

第二大題：組合包方案 CREDIT_PACKAGE、客戶購買課程堂數 CREDIT_PURCHASE

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

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

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 = '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 堂組合包方案'))
Select a repo