# 第四堂：後台專案實戰 - 管理者端資料庫規劃
* 記得錄影
* [Gamma 簡報](https://gamma.app/docs/SQL--23heyix4r93u5u0?mode=doc)
## group by 分組資料
* [簡報](https://gamma.app/docs/SQL--23heyix4r93u5u0?mode=present#card-sipi7d9pjizbszb)
員工部門模擬資料
```sql
-- 建立部門資料表
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),
('李小華', 43000, 2),
('陳小玉', 55000, 2),
('林小豪', 47000, 1),
('王小銷', 55000, 3),
('林中美', 47000, 3);
```
## Join
* [簡報](https://gamma.app/docs/SQL--23heyix4r93u5u0?mode=present#card-qppyrqlm15xjiqu)
## 中場休息
## 任務二資訊
* [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)
* [5-6 大題圖解](https://miro.com/app/board/uXjVL-Selqk=/?share_link_id=363174833180)
## 解題五六題
## 5\. 客戶預約與授課 `COURSE_BOOKING`
王小明 Email：`wXlTq@hexschooltest.io`
好野人 Email：`richman@hexschooltest.io`
**1\. 新增**：請在 `COURSE_BOOKING` 新增兩筆資料：
第一筆：新增 `王小明`預約 `李燕容` 的課程
- 預約人設為`王小明`
- 預約時間`booking_at` 設為2024-11-24 16:00:00
- 狀態`status` 設定為即將授課
第二筆：新增： `好野人` 預約 `李燕容` 的課程
- 預約人設為 `好野人`
- 預約時間`booking_at` 設為2024-11-24 16:00:00
- 狀態`status` 設定為即將授課
```sql
insert into "COURSE_BOOKING" (user_id, course_id, booking_at, status) values
((select id from "USER" where email = 'wXlTq@hexschooltest.io'),
(select id from "COURSE" where user_id = (select id from "USER" where email = 'lee2000@hexschooltest.io')),
'2024-11-24 16:00:00',
'即將授課'),
((select id from "USER" where email = 'richman@hexschooltest.io'),
(select id from "COURSE" where user_id = (select id from "USER" where email = 'lee2000@hexschooltest.io')),
'2024-11-24 16:00:00',
'即將授課');
```
**2\. 修改**：`王小明`取消預約 `李燕容` 的課程，請在`COURSE_BOOKING`更新該筆預約資料：
- 取消預約時間`cancelled_at` 設為2024-11-24 17:00:00
- 狀態`status` 設定為課程已取消
```sql=
update "COURSE_BOOKING" set cancelled_at = '2024-11-24 17:00:00',status = '課程已取消'
where user_id = (select id from "USER" where email = 'wXlTq@hexschooltest.io')
and course_id = (select id from "COURSE" where user_id = (select id from "USER" where email = 'lee2000@hexschooltest.io'));
```
**3\. 新增**：`王小明`再次預約 `李燕容` 的課程，請在`COURSE_BOOKING`新增一筆資料：
- 預約人設為`王小明`
- 預約時間`booking_at` 設為2024-11-24 17:10:25
- 狀態`status` 設定為即將授課
```sql
insert into "COURSE_BOOKING" (user_id, course_id, booking_at, status) values
((select id from "USER" where email = 'wXlTq@hexschooltest.io'),
(select id from "COURSE" where user_id = (select id from "USER" where email = 'lee2000@hexschooltest.io')),
'2024-11-24 17:10:25',
'即將授課');
```
**4\. 查詢**：取得`王小明`所有的預約紀錄，包含取消預約的紀錄
```sql
select *
from "COURSE_BOOKING"
where user_id = (
select id
from "USER"
where email = 'wXlTq@hexschooltest.io'
);
```
**5\. 修改**：`王小明` 現在已經加入 `李燕容` 的課程直播室了，請在 `COURSE_BOOKING` 更新該筆預約資料（請注意，不要更新到已經取消的紀錄）：
- 請在該筆預約記錄他的加入直播室時間 `join_at` 設為2024-11-25 14:01:59
- 狀態`status` 設定為上課中
```sql
update "COURSE_BOOKING" set join_at = '2024-11-25 14:01:59', status= '上課中'
where user_id = (select id from "USER" where email = 'wXlTq@hexschooltest.io')
and course_id = (select id from "COURSE" where user_id = (select id from "USER" where email = 'lee2000@hexschooltest.io'))
and status = '即將授課';
```
**6\. 查詢**：計算用戶王小明的購買堂數。 (需使用到 SUM 函式與 Group By)，顯示須包含以下欄位： `user_id` , `total`
```sql
select
"CREDIT_PURCHASE".user_id,
sum("CREDIT_PURCHASE".purchased_credits) as total
from "CREDIT_PURCHASE"
where "CREDIT_PURCHASE".user_id = (select id from "USER" where email = 'wXlTq@hexschooltest.io')
group by "CREDIT_PURCHASE".user_id;
```
**7\. 查詢**：計算用戶王小明的已使用堂數。 (需使用到 Count 函式與 Group By)，顯示須包含以下欄位： `user_id` , `total`
```sql
select
"COURSE_BOOKING".user_id,
count(*) as total
from "COURSE_BOOKING"
where "COURSE_BOOKING".user_id = (select id from "USER" where email = 'wXlTq@hexschooltest.io')
and status != '課程已取消'
group by "COURSE_BOOKING".user_id;
```
## 6\. 後台報表
**1\. 查詢**：查詢專長為 `重訓` 的教練，並按經驗年數排序，由資深到資淺（需使用 inner join 與 order by 語法) ，顯示須包含以下欄位： 教練名稱 , 經驗年數, 專長名稱
```sql
select
"USER"."name" as "教練名稱",
"COACH".experience_years as "經驗年數",
"SKILL".name as "專長名稱"
from "COACH_LINK_SKILL"
inner JOIN "SKILL" on "SKILL".id = "COACH_LINK_SKILL".skill_id
inner JOIN "COACH" on "COACH".id = "COACH_LINK_SKILL".coach_id
inner JOIN "USER" on "USER".id = "COACH".user_id
where "COACH_LINK_SKILL".skill_id = (select id from "SKILL" where name = '重訓')
order by "COACH".experience_years desc;
```
**2\. 查詢**：查詢每種專長的教練數量，並只列出教練數量最多的專長（需使用 group by, inner join 與 order by 與 limit 語法），顯示須包含以下欄位： 專長名稱, coach\_total
```sql
select
"SKILL".name as "專長名稱",
count(*) as coach_total
from "COACH_LINK_SKILL"
inner JOIN "SKILL" on "SKILL".id = "COACH_LINK_SKILL".skill_id
group by "SKILL".name
order by coach_total desc
limit 1;
```
**3\. 查詢**：計算 11 月份組合包方案的銷售數量，顯示須包含以下欄位： 組合包方案名稱, 銷售數量
```sql
select
"CREDIT_PACKAGE".name as "組合包方案名稱",
count(*) as "銷售數量"
from "CREDIT_PURCHASE"
inner join "CREDIT_PACKAGE" on "CREDIT_PACKAGE".id = "CREDIT_PURCHASE".credit_package_id
where "CREDIT_PURCHASE".created_at >= '2024-11-01 00:00:00' and "CREDIT_PURCHASE".created_at <= '2024-11-30 23:59:59'
group by "CREDIT_PACKAGE".name;
```
**4\. 查詢**：計算 11 月份總營收（使用 purchase\_at 欄位統計），顯示須包含以下欄位： 總營收
```sql
select sum(price_paid) as "總營收"
from "CREDIT_PURCHASE"
where "CREDIT_PURCHASE".purchase_at >= '2024-11-01 00:00:00'
and "CREDIT_PURCHASE".purchase_at <= '2024-11-30 23:59:59';
```
**5\. 查詢**：計算 11 月份有預約課程的會員人數（需使用 Distinct，並用 created\_at 和 status 欄位統計），顯示須包含以下欄位： 預約會員人數
```sql
select count(Distinct("COURSE_BOOKING".user_id)) as "預約會員人數"
from "COURSE_BOOKING"
where "COURSE_BOOKING".created_at >= '2024-11-01 00:00:00'
and "COURSE_BOOKING".created_at <= '2024-11-30 23:59:59'
and "COURSE_BOOKING".status != '課程已取消';
```
Sign in with Wallet
Connect another wallet