[Hexschool Backend experience camp] — 資料庫體驗營 每日任務串

Introduction & 前言

這邊會存放每日任務的回答，一個章節會是一天的任務內容筆記

整篇文章是筆者自己的心得，如果文內有錯誤的地方，還請盡情指出。

本篇會從零開始記錄筆記，目前課程影片的心得在此處以及 [Hexschool Backend experience camp] — 資料庫體驗營

---

Summary & 摘要

小節作業如果你也想一起練習，可以使用 線上 DB 操作練習網站 進行操作

1. 2024/11/25每日任務
2. 2024/11/26每日任務
3. 2024/11/27每日任務
4. 2024/11/28每日任務
5. 最終作業

---

2024/11/25每日任務

任務連結

先使用以下 SQL 建立資料表

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, -- 角色，分別有 "USER"、"COACH"
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "updated_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

Q1：新增：新增六筆用戶資料，資料如下：

• 用戶名稱為 李燕容，Email 為 lee2000@hexschooltest.io，Role 為 USER
• 用戶名稱為 王小明，Email 為 wXlTq@hexschooltest.io，Role 為 USER
• 用戶名稱為 肌肉棒子，Email 為 muscle@hexschooltest.io，Role 為 USER
• 用戶名稱為 好野人，Email 為 richman@hexschooltest.io，Role 為 USER
• 用戶名稱為 Q太郎，Email 為 starplatinum@hexschooltest.io，Role 為 USER
• 用戶名稱為 透明人，Email 為 opacity0@hexschooltest.io，Role 為 USER

A1：

INSERT INTO
 	"USER"(name, email, role)
VALUES('李燕容', 'lee2000@hexschooltest.io', 'USER'),
  (
    '王小明',
    'wXlTq@hexschooltest.io',
    'USER'
  ),
  (
    '肌肉棒子',
    'muscle@hexschooltest.io',
    'USER'
  ),
  (
    '好野人',
    'richman@hexschooltest.io',
    'USER'
  ),
  (
    'Q太郎',
    'starplatinum@hexschooltest.io',
    'USER'
  ),
  (
    '透明人',
    'opacity0@hexschooltest.io',
    'USER'
  );

再進行搜尋 USER 表 SELECT * FROM "USER”;，查看是否成功塞入資料

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

A2：

UPDATE
  "USER"
SET
  ROLE = 'COACH'
WHERE
  name IN('李燕容', '肌肉棒子', 'Q太郎');

Q3：刪除：刪除 USER 資料表中，用 Email 找到 透明人，並刪除該筆資料

A3：

DELETE FROM
  "USER"
WHERE
  email = 'opacity0@hexschooltest.io';

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

A4：

SELECT
  COUNT(*) AS 目前所有用戶數量
FROM
  "USER";

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

A5：

SELECT
  *
FROM
  "USER"
LIMIT
  3;

---

2024/11/26每日任務

任務連結

先使用以下 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)
);

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

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

A1：

INSERT INTO
  "CREDIT_PACKAGE"(name, price, credit_amount)
VALUES('7 堂組合包方案', 1400, 7),('14 堂組合包方案', 2520, 14),('21 堂組合包方案', 4800, 21);

Q2：新增：在 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
        name = '王小明'
    ),
    (
      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
        name = '王小明'
    ),
    (
      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
        name = '好野人'
    ),
    (
      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 堂組合包方案'
    )
  );

---

2024/11/27每日任務

任務連結

Q1：新增：在 COACH 資料表新增三筆資料，資料需求如下：

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

A1：

INSERT INTO
  "COACH"(user_id, experience_years)
VALUES
  (
    (
      SELECT
        id
      FROM
        "USER"
      WHERE
        email = 'lee2000@hexschooltest.io'
    ),
    2
  ),
  (
    (
      SELECT
        id
      FROM
        "USER"
      WHERE
        email = 'muscle@hexschooltest.io'
    ),
    2
  ),
  (
    (
      SELECT
        id
      FROM
        "USER"
      WHERE
        email = 'starplatinum@hexschooltest.io'
    ),
    2
  );

Q2：何謂一對多、一對一

A2:

• 一對多可以用 班級 對應 學生，一個班級可以有多個學生，就是一對多。
• 一對一可以用 民眾 及 身分證字號，一個民眾對應一個身分證字號。

Q3：使用者與部落格文章的資料表關係

以「」角度：一個「」有 ? 個 「」
以「」角度：一個「」有 ? 個 「」

A3：

以「使用者」角度：一個「使用者」有 多 個 「文章」
以「文章」角度：一個「文章」有 1 個 「使用者」

Q4：依照上述描述，他是一對一關係、還是一對多？
A4：一對多的關係

Q5：下方 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 "BLOG_POST" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT (gen_random_uuid()),
  "user_id" uuid NOT NULL REFERENCES "USER"(id),
  "title" varchar(255) NOT NULL,
  "content" text NOT NULL,
  "featured_image_url" varchar(2048),
  "category" varchar(20) NOT NULL,
  "spend_minutes" smallint NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "updated_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

A5：

1對多(1>N) 的關係

---

任務連結

• subject ：科目名稱
• exam_score：科目成績
• student：學生資料

先使用以下 SQL 建立資料表

-- 建立資料表
CREATE TABLE student (
 id serial PRIMARY KEY,
 name varchar(50) NOT NULL,
 class varchar(20) NOT NULL,
 created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE subject (
 id serial PRIMARY KEY,
 name varchar(50) NOT NULL,
 created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE exam_score (
 id serial PRIMARY KEY,
 student_id integer NOT NULL,
 subject_id integer NOT NULL,
 score integer NOT NULL,
 exam_date date NOT NULL,
 created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (student_id) REFERENCES student(id),
 FOREIGN KEY (subject_id) REFERENCES subject(id)
);

-- 插入測試資料
INSERT INTO student (name, class) VALUES
('小明', '三年一班'),
('小華', '三年一班'),
('小美', '三年一班'),
('小龍', '三年一班'),
('小智', '三年一班');

INSERT INTO subject (name) VALUES
('國文'),
('英文'),
('數學');

INSERT INTO exam_score (student_id, subject_id, score, exam_date) VALUES
(1, 1, 85, '2024-03-01'),
(1, 2, 92, '2024-03-01'),
(1, 3, 78, '2024-03-01'),
(2, 1, 76, '2024-03-01'),
(2, 2, 88, '2024-03-01'),
(2, 3, 95, '2024-03-01'),
(3, 1, 92, '2024-03-01'),
(3, 2, 85, '2024-03-01'),
(3, 3, 87, '2024-03-01'),
(4, 1, 78, '2024-03-01'),
(4, 2, 82, '2024-03-01'),
(4, 3, 90, '2024-03-01'),
(5, 1, 88, '2024-03-01'),
(5, 2, 79, '2024-03-01'),
(5, 3, 93, '2024-03-01');

Q1：查看所有科目

A1：

SELECT * FROM subject;

Q2：計算所有成績平均
A2：

SELECT
  AVG(score) AS avg_score
FROM exam_score;

Q3：找出 90 分以上的成績
A3：

SELECT
  *
FROM
  exam_score
WHERE
  score >= 90;

Q4：顯示小明的國文成績，篩選條件請用國文的 subject_id = 1 進行篩選
A4：

SELECT
  student.name,
  exam_score.score
FROM
  exam_score
  INNER JOIN student ON exam_score.student_id = student.id
  INNER JOIN subject ON exam_score.subject_id = subject.id
WHERE
  student.name = '小明'
  AND subject.name = '國文';

---

2024/11/29每日任務

任務連結

• subject ：科目名稱
• exam_score：科目成績
• student：學生資料

先使用以下 SQL 建立資料表

-- 建立資料表
CREATE TABLE student (
 id serial PRIMARY KEY,
 name varchar(50) NOT NULL,
 class varchar(20) NOT NULL,
 created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE subject (
 id serial PRIMARY KEY,
 name varchar(50) NOT NULL,
 created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE exam_score (
 id serial PRIMARY KEY,
 student_id integer NOT NULL,
 subject_id integer NOT NULL,
 score integer NOT NULL,
 exam_date date NOT NULL,
 created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (student_id) REFERENCES student(id),
 FOREIGN KEY (subject_id) REFERENCES subject(id)
);

-- 插入測試資料
INSERT INTO student (name, class) VALUES
('小明', '三年一班'),
('小華', '三年一班'),
('小美', '三年一班'),
('小龍', '三年一班'),
('小智', '三年一班');

INSERT INTO subject (name) VALUES
('國文'),
('英文'),
('數學');

INSERT INTO exam_score (student_id, subject_id, score, exam_date) VALUES
(1, 1, 85, '2024-03-01'),
(1, 2, 92, '2024-03-01'),
(1, 3, 78, '2024-03-01'),
(2, 1, 76, '2024-03-01'),
(2, 2, 88, '2024-03-01'),
(2, 3, 95, '2024-03-01'),
(3, 1, 92, '2024-03-01'),
(3, 2, 85, '2024-03-01'),
(3, 3, 87, '2024-03-01'),
(4, 1, 78, '2024-03-01'),
(4, 2, 82, '2024-03-01'),
(4, 3, 90, '2024-03-01'),
(5, 1, 88, '2024-03-01'),
(5, 2, 79, '2024-03-01'),
(5, 3, 93, '2024-03-01');

Q1：計算每個學生的總分
A1：

SELECT
  name,
  SUM(score) AS total
FROM
  student
  INNER JOIN exam_score ON student.id = exam_score.student_id
GROUP BY
  student.name;

Q2：計算每科的平均分數並依分數排序
A2：

SELECT
  subject.name,
  AVG(exam_score.score) AS average
FROM
  exam_score
  INNER JOIN subject ON exam_score.subject_id = subject.id
GROUP BY
  subject.name
ORDER BY total DESC;

Q3：顯示英文成績排名
A3：

SELECT
  student.name,
  exam_score.score
FROM
  student
  INNER JOIN exam_score ON student.id = exam_score.student_id
  -- INNER JOIN subject ON exam_score.subject_id = subject.id
WHERE
  -- subject.name = '英文'
  -- 可以把下面這段子查詢註解掉，並打開上面兩個註解掉的 INNER JOIN 及 subject.name = '英文'，兩種方式皆可
  exam_score.subject_id = (
    SELECT
      id
    FROM
      subject
    WHERE
      name = '英文'
  )
ORDER BY
  exam_score.score DESC;

Q4：找出考最好的科目
A4：

SELECT
  subject.name,
  MAX(exam_score.score) AS highest
FROM
  exam_score
  INNER JOIN subject ON subject.id = exam_score.subject_id
GROUP BY
  subject.name
ORDER BY
  highest DESC
LIMIT
  1;

Q5：計算每位學生的總分和全班平均的差距，依總分排序
A5：

SELECT
  student.name,
  SUM(exam_score.score) AS student_total,
  (
    SELECT
      AVG(total_table.total)
    FROM
      (
        SELECT
          SUM(exam_score.score) AS total
        FROM
          exam_score
        GROUP BY
          exam_score.student_id
      ) AS total_table
  ) AS class_avg,
  SUM(exam_score.score) - (
    SELECT
      AVG(total_table.total)
    FROM
      (
        SELECT
          SUM(exam_score.score) AS total
        FROM
          exam_score
        GROUP BY
          exam_score.student_id
      ) AS total_table
  ) AS difference
FROM
  exam_score
  INNER JOIN student ON exam_score.student_id = student.id
GROUP BY
  student.name
ORDER BY
  student_total DESC;

上面這邊也可以在 AS total_table 的部分使用 t 替代，如下：

SELECT
  student.name,
  SUM(exam_score.score) AS student_total,
  (
    SELECT
      AVG(total_table.total)
    FROM
      (
        SELECT
          SUM(exam_score.score) AS total
        FROM
          exam_score
        GROUP BY
          exam_score.student_id
      ) t
  ) AS class_avg,
-- ...下略

---

最終作業

最終作業有兩種解題方式，一種是透過之前的 線上 DB 操作練習網站 先去匯入資料，然後進行問題的操作

另一種方式則是透過六角學院已經有在 Github 上包裹好 Docker，方便我們在本地搭建 PostgreSQL，然後再透過 DBeaver 去進行操作

作業講解

這次的作業學院有先釋出一張資料庫的結構圖

而任務的內容則是以下

任務繳交的方式有分三個等級

• LV 1｜完成題目 1.用戶資料 到 4.課程管理
• LV 2｜完成全部題目（不含挑戰題）
• LV 3｜完成全部題目（含挑戰題）

身為六角學院的學生，再難都要挑戰看看 LV 3 啊！！！

解題方式一

這邊如果是使用前者可以先進行匯入資料，可以參考 任務二-資料庫建立指令，或是貼上以下的 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 "SKILL" (
  "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  "name" varchar(50) UNIQUE NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

INSERT INTO "SKILL" (name) VALUES ('重訓'), ('瑜伽'), ('有氧運動'), ('復健訓練');

CREATE TABLE "COACH" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
  "user_id" uuid NOT NULL REFERENCES "USER"(id),
  "experience_years" integer,
  "description" text,
  "profile_image_url" varchar(2048),
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "updated_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  UNIQUE("user_id")
);

CREATE TABLE "COACH_LINK_SKILL" (
  "coach_id" uuid NOT NULL REFERENCES "COACH"(id),
  "skill_id" uuid NOT NULL REFERENCES "SKILL"(id),
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  PRIMARY KEY ("coach_id", "skill_id")
);

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 TABLE "COURSE" (
  "id" serial PRIMARY KEY,
  "user_id" uuid NOT NULL REFERENCES "USER"(id),
  "skill_id" uuid NOT NULL REFERENCES "SKILL"(id),
  "name" varchar(100) NOT NULL,
  "description" text,
  "start_at" timestamp NOT NULL,
  "end_at" timestamp NOT NULL,
  "max_participants" integer NOT NULL,
  "meeting_url" varchar(2048) NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

CREATE TABLE "COURSE_BOOKING" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT (gen_random_uuid()),
  "user_id" uuid NOT NULL REFERENCES "USER"(id),
  "course_id" integer NOT NULL REFERENCES "COURSE"(id),
  "booking_at" timestamp NOT NULL,
  "status" varchar(20) NOT NULL,
  "join_at" timestamp,
  "leave_at" timestamp,
  "cancelled_at" timestamp,
  "cancellation_reason" varchar(255),
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

CREATE TABLE "BLOG_POST" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT (gen_random_uuid()),
  "user_id" uuid NOT NULL REFERENCES "USER"(id),
  "title" varchar(255) NOT NULL,
  "content" text NOT NULL,
  "featured_image_url" varchar(2048),
  "category" varchar(20) NOT NULL,
  "spend_minutes" smallint NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "updated_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

CREATE TABLE "COMMENT" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT (gen_random_uuid()),
  "blog_post_id" uuid NOT NULL REFERENCES "BLOG_POST"(id),
  "user_id" uuid NOT NULL REFERENCES "USER"(id),
  "content" text NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "updated_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

解題方式二

這邊筆者使用後者進行本地的建置，如果有興趣也可以跟著一起做（其實學院的影片檔都有了，還是筆記一下過程）

如果要照這這個方法走有幾點事前建置請先具備，沒有安裝的都直接到相對應的官方網站進行下載安裝即可

• 擁有 Git 帳號以及操作經驗
• 已經安裝 Docker Desktop
• 已經安裝 DBeaver

先至 Github Repo 進行 clone

首先到六角學院的 Github Repo - backend-database-camping-task-startkit 把專案 Fork 到自己的 Repo 內

Fork 的部分就不多介紹，有興趣網路上都有許多教學，例如：第 28 天：了解 GitHub 的 fork 與 pull request 版控流程

接著到已經 Fork 的專案，把專案 clone 下來

安裝專案

切換到 clone 的專案底下，執行 npm i，直到看見 node_module 就算完成

執行遷移指令(選擇性操作)

因為 Github 指令上有寫到 新增一組遷移，雖然後面好像沒用到，但這邊還是照著執行了。

跟著指令一起輸入 npm run create {your_file_name}，後面的 {your_file_name} 可以整個替換成你想要的名稱

完成會看到多出檔案

執行 Docker，建置環境

因為指令學院都幫我們包好了，所以直接執行 npm run start 即可，如果有興趣想要自行體驗 Docker 的同學可以考慮參考看看之前筆者寫的 [CI/CD Note] — 透過 Docker 快速建立及部署環境，自行修改 Docker File 或者 env。

建置完成後，打開 Docker Desktop 就能看到執行中的 Container，裡面有一個不會亮是正常的，那個是拿來跑第一次的 migrate，可以理解為幫你跑了上面剛剛那串很長的 SQL 指令，一些 Table 以及資料都先被塞入了，學院方便我們後續操作。

操作 DBeaver

打開已經安裝的 DBeaver，打開後如果有更新什麼的可以點擊進行更新

點擊左上角，然後選擇 PostgreSQL 在點擊 Next

接著找到專案底下的 .env.example，然後把裡面的設定值相對應的填到 Database、Username 及 Password

創建成功後，左側會有出現一頭大象的 Icon 以及名為 test 的 Database，點擊左鍵兩下連線，至此成功建立連結，這時候你的 DB 其實就是存放在剛剛啟動的 Docker 內，如果 Docker 關閉，這邊就會連線不到，需要注意！

進行 SQL 操作

接著我們點擊 test 右鍵，然後選擇 SQL 編輯器 > 開啟SQL腳本

然後再打開的視窗中，輸入 SELECT * FROM "USER"

這時候下面如果有出現表格，就代表是正長的，沒有資料也是正確的，因為我們還沒有開始塞入資料。

然後我們將專案底下 migrations/task/sqls/20241021064214-task-up.sql 的內容複製貼到視窗內，就可以開始解題啦！

在我們貼上 SQL 後，左側會有好幾顆按鈕可以點擊，建議一次貼一段要執行的就好，然後點最上面的播放按鈕，成功下面也會出現相對應提示。

確認有沒有成功塞入資料的方式一樣就是再次進行 SELECT * FROM "USER"

作業參考

因為學院有幫寫一個測試 Migrate 的部分，請記得推 Github 上去前先去 Github 打開，第一次需要自己進去點擊按鈕，之後 git push 才會偵測到

這邊筆者已經有先提交一版上去給助教檢查，如果有興趣想參考解答的話，可以參考筆者的 backend-database-camping-task-startkit - Repo Sql

小記

Action 崩潰紀

在跑 Action Test 的時候有發現一直爆出錯誤，找了很久，結果發現是因為沒有加分號 ;，以及有個地方塞入值錯誤使用雙引號 "

insert
	into
	"SKILL"(name)
values("空中瑜伽");

上面的問題導致資料根本就沒有成功塞入，還好有 Test 檢查，不然完全不會發現～

原本中間一度以為要一個一個任務慢慢提交，差點崩潰。

關於 SQL 語法

其中練習後有一些東西是可以少寫的，例如好幾次的 SQL 查詢或塞入

-- 3-3 修改：更新教練的經驗年數，資料需求如下：
    -- 1. 教練`肌肉棒子` 的經驗年數為3年
    -- 2. 教練`Q太郎` 的經驗年數為5年

update
    "COACH"
set
    experience_years = 3
where
    "COACH".id = (
    select
            "sub_COACH".id
    from
            "USER" as "sub_USER"
    inner join "COACH" as "sub_COACH" on
        "sub_USER".id = "sub_COACH".user_id
    where
        "sub_USER".name = '肌肉棒子'
    );

update
    "COACH"
set
    experience_years = 5
where
    "COACH".id = (
    select
            "sub_COACH".id
    from
            "USER" as "sub_USER"
    inner join "COACH" as "sub_COACH" on
        "sub_USER".id = "sub_COACH".user_id
    where
        "sub_USER".name = 'Q太郎'
    );

其實可以透過 CASE 語句來進行一次的操作

update
    "COACH"
set
    experience_years = case
        when "sub_USER".name = '肌肉棒子' then 3
        when "sub_USER".name = 'Q太郎' then 5
        else experience_years
    end
from
    "USER" as "sub_USER"
inner join "COACH" as "sub_COACH" on
    "sub_USER".id = "sub_COACH".user_id
where
    "sub_USER".name in ('肌肉棒子', 'Q太郎')
and
    "COACH".id = "sub_COACH".id;

還有 Task 5 的插入資料，原本是多次操作

-- 5. 客戶預約與授課 COURSE_BOOKING
-- 5-1. 新增：請在 `COURSE_BOOKING` 新增兩筆資料：
    -- 1. 第一筆：`王小明`預約 `李燕容` 的課程
        -- 1. 預約人設為`王小明`
        -- 2. 預約時間`booking_at` 設為2024-11-24 16:00:00
        -- 3. 狀態`status` 設定為即將授課
    -- 2. 新增： `好野人` 預約 `李燕容` 的課程
        -- 1. 預約人設為 `好野人`
        -- 2. 預約時間`booking_at` 設為2024-11-24 16:00:00
        -- 3. 狀態`status` 設定為即將授課

insert
    into
    "COURSE_BOOKING"(user_id,
    course_id,
    booking_at,
    status)
values
    (
        (
select
    id
from
    "USER"
where
    name = '王小明'
        ),
        (
select
    "COURSE".id
from
    "COURSE"
inner join "USER" on
    "COURSE".user_id = "USER".id
where
    "USER".name = '李燕容'
        ),
        '2024-11-24 16:00:00',
        '即將授課'
    ),
    (
        (
select
    id
from
    "USER"
where
    name = '好野人'
        ),
        (
select
    "COURSE".id
from
    "COURSE"
inner join "USER" on
    "COURSE".user_id = "USER".id
where
    "USER".name = '李燕容'
        ),
        '2024-11-24 16:00:00',
        '即將授課'
    );

也可以透過以下方式簡寫

insert into "COURSE_BOOKING" (user_id, course_id, booking_at, status)
select
    "USER".id,
    "COURSE".id,
    '2024-11-24 16:00:00',
    '即將授課'
from
    "USER"
cross join "COURSE"
inner join "USER" as "COACH_USER" on "COURSE".user_id = "COACH_USER".id
where
    "USER".name in ('王小明', '好野人')
    and "COACH_USER".name = '李燕容';

優化 SQL 又是另一個課題了，希望打開了後端之門後，能慢慢地上手。

---

參考網站

• 【後端工程師 - 資料庫體驗營】 進入後端前的必備知識
• 線上 DB 操作練習網站