本題目主要來自下列網站，原始題目及資料請到下列網站查詢
https://www.bonnie-chou.com/posts/sql-challenges-30-coffee-shop
但部份題目資料有問題所以無法實作出來
基礎難度
1. 將 customer 表前 5 筆資料列出來
SELECT*
FROM customer
LIMIT 5
2. 查詢所有熱飲 (hot = 1) 的飲品名稱與價格
SELECT dname ,price
FROM drink
WHERE hot=1
3. 找出年齡 ≥ 30 歲的會員姓名與年齡
SELECT *
FROM customer
WHERE age >=30
4. 列出 2025-01-19 全部訂單編號與對應會員編號
SELECT oid,
cid,
DATE(order_dt)
FROM orders
WHERE DATE(order_dt) = "2025-01-19"
5. 計算性別為 'M' 的會員人數
SELECT COUNT(*)
FROM customer
WHERE gender="F"
6. 使用 LIKE 找出會員姓名以 'C' 開頭者的會員編號與姓名
SELECT *
FROM customer
WHERE cname LIKE "C%"
7. 查詢 2025-01-23 當天的訂單總筆數
SELECT COUNT(*)
FROM orders
WHERE date(order_dt)="2025-01-23"
8. 查詢 price > 120 的飲品編號與名稱，依價格由高到低排序
SELECT did,dname
from drink
WHERE price >120
ORDER BY price DESC
9. 列出 order_item 中 qty >= 2 的紀錄 (顯示訂單編號、飲品編號、數量)
SELECT *
From order_item
WHERE qty >=2
10. 找出 shift 表裡排 Evening 班的店員姓名 (不重複)
SELECT DISTINCT bname
from shift
LEFT JOIN barista USING(bid)
WHERE shift="Evening"
1. 計算各飲品的總銷售杯數
SELECT did,dname,SUM(qty) AS 銷售量
FROM order_item
LEFT JOIN drink USING(did)
GROUP BY did
2. 列出每位店員的接單金額 (金額＝qty * price)，並依金額降序
SELECT bid,bname ,sum(qty*price) AS 銷售金額
FROM orders
LEFT JOIN order_item USING(oid)
LEFT JOIN barista USING(bid)
LEFT JOIN drink USING(did)
GROUP BY bid
ORDER BY 銷售金額 desc
3. 找出會員 c002 累積消費總額
SELECT cid,cname ,sum(qty*price )AS 銷售金額
FROM orders
LEFT JOIN order_item USING(oid)
LEFT JOIN customer USING(cid)
LEFT JOIN drink USING(did)
WHERE cid ="c002"
4. 列出沒有被點過的飲品編號與名稱(資料不存,每項目都有點過)
SELECT did,dname
FROM drink
WHERE did NOT IN
(
SELECT DISTINCT did
FROM order_item)
5. 對每位會員統計第一次銷費點單日期
SELECT cid ,MIN(order_dt)
FROM orders
GROUP BY cid
6. 找出一天內下 2 筆以上訂單的會員編號與下單日期(沒有這個資料)
7. 查詢平均杯數 > 1 的訂單編號與平均杯數 (提示: AVG(qty))
SELECT oid,
AVG(qty) AS AVGS
FROM orders
LEFT JOIN order_item USING(oid)
GROUP BY oid
HAVING avgs >1
8. 列出每種飲品的單日最高銷售杯數與日期
WITH dailysales
AS (
-- 步驟 1：先計算每種飲品在每天的總銷售杯數
SELECT order_dt,
did,
Sum(qty) AS total_qty
FROM orders
LEFT JOIN order_item using(oid)
GROUP BY order_dt,
did),
rankedsales
AS (
-- 步驟 2：在每種飲品的分組內，依據銷量由高到低排名
SELECT order_dt,
did,
total_qty,
Row_number()
OVER (
partition BY did
ORDER BY total_qty DESC, order_dt DESC ) AS rank_num
FROM dailysales)
SELECT *
FROM rankedsales
WHERE rank_num = 1
9. 使用 GROUP_CONCAT 把同一張訂單中的飲品名稱用逗號串起來顯示 (顯示訂單編號與飲品清單)
SELECT oid,GROUP_CONCAT(CONCAT(did," ",dname)) AS drink_list
FROM orders
LEFT JOIN order_item USING(oid)
LEFT JOIN drink USING(did)
GROUP BY oid
10. 找出 2025-01-19 到 2025-01-21 之間，每天營業額最高的店員姓名與金額
WITH daysale AS
(-- 步驟 1：先計算每個員工在每天的每筆訂單銷售額
SELECT order_dt,
bid,
oid,
Sum(qty*price) AS total_qty
FROM orders
LEFT JOIN order_item USING(oid)
LEFT JOIN drink USING(did) GROUP BY order_dt,
bid,
oid),
rankdaysale AS
(-- 步驟 2：員工在每天的每筆訂單，依據銷量由高到低排名
SELECT order_dt,
bid,
oid,
total_qty,
Row_number() OVER (PARTITION BY bid
ORDER BY total_qty DESC, order_dt DESC) AS rank_num
FROM daysale)
SELECT *
FROM rankdaysale
WHERE rank_num=1