基礎難度

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

 

進階難度

1.     找出同時點過 Espresso (d001) 與 Latte (d003) 的會員編號與姓名

SELECT cid,

       cname

FROM order_item AS o1,

     order_item AS o2

JOIN orders USING(OID) JOIN customer USING(cid) WHERE o1.oid=o2.oid

  AND o1.did='d001'

  AND o2.did='d003'

2.     找出從未在 Evening 班時段下單的會員姓名

找出在

SELECT

    cid

FROM

    orders

WHERE

    RIGHT(orders.order_dt, 8) BETWEEN '16:00:00'

    AND '23:59:59'

WITH oeven AS

  (SELECT cid

   FROM orders

   WHERE RIGHT(orders.order_dt, 8) BETWEEN '16:00:00' AND '23:59:59' )

SELECT cname

FROM customer

WHERE cid NOT IN

    (SELECT cid

     FROM oeven)

下列作作法是錯的

WITH seven  AS

(

SELECT bid,sdate

     FROM shift

     WHERE shift='Evening'

)

SELECT DISTINCT  cname

from orders

JOIN barista USING (bid)

JOIN customer USING(cid)

where   orders.bid NOT IN (SELECT bid  from seven )

3.     使用視窗函數 (Window function)找出各會員最新一筆訂單日期與金額

SELECT OiD,cid,order_dt,sum(QTY*PRICE)

FROM orders

JOIN order_item USING(oid)

JOIN drink USING(Did)

GROUP BY oid

SELECT OiD,cid,order_dt,sum(QTY*PRICE)

,ROW_NUMBER() OVER(PARTITION BY cid ORDER BY order_dt DESC) AS rn

FROM orders

JOIN order_item USING(oid)

JOIN drink USING(Did)

GROUP BY oid

WITH olast AS

  (SELECT OiD,

          orders.cid,

          order_dt,

          sum(QTY*PRICE),

          ROW_NUMBER() OVER(PARTITION BY cid

                            ORDER BY order_dt DESC) AS rn

   FROM orders

   JOIN order_item USING(oid)

   JOIN drink USING(Did)

   GROUP BY oid,

            orders.cid,

            order_dt)

SELECT *

FROM olast

WHERE rn =1

4.     以 CTE 計算每位店員每日營業額，並找出高於自己平均營業額的日期與金額

使用 CTE（通用資料表運算式，Common Table Expression）處理此類問題，最有效率的方式是拆解成兩個步驟：

第一個 CTE 計算出每位店員在各日期的總營業額。

第二個 CTE（或視窗函數）計算每位店員的個人平均營業額。

最後透過主查詢比對並篩選出高於平均的紀錄。

WITH dsum AS

  (SELECT bid,

          LEFT(order_dt, 10) AS odate,

          SUM(price*qty) AS ddsum

   FROM orders

   JOIN order_item USING(oid)

   JOIN drink USING(did)

   GROUP BY bid,

            odate),

     tavg AS

  (SELECT bid,

          AVG(ddsum) AS avgs

   FROM dsum

   GROUP BY bid)

SELECT *

FROM dsum

JOIN tavg USING(bid)

WHERE avgs<ddsum

5.     將 2025-01-23 當天訂單依下單時間排序，使用 LAG 計算相鄰兩筆訂單間隔分鐘數

SELECT oid,

       order_dt,

       LAG(order_dt, 1) OVER (

                              ORDER BY order_dt) AS prev_cnt

FROM orders

WHERE LEFT(order_dt, 10)= '2025-01-23'

6.     餘額思考：若會員每消費 200 元累積 1 點數，計算各會員目前點數 (用子查詢或 CTE 都可以)

各會員消費金額

SELECT cid,

       sum(qty*price)

FROM orders

JOIN order_item USING(oid)

JOIN drink USING(did)

GROUP BY cid

SELECT cid,

       sum(qty*price) DIV 200 點數

FROM orders

JOIN order_item USING(oid)

JOIN drink USING(did)

GROUP BY cid

7.     找出單筆訂單金額排名前 3 的訂單編號、金額與店員姓名

SELECT oid,

       sum(qty*price) AS 金額

FROM orders

JOIN order_item USING(oid)

JOIN drink USING(did)

GROUP BY oid

ORDER BY 金額 DESC

LIMIT 3

8.     查詢同時在 2025-01-23 上過早班 (Morning) 又上過晚班 (Evening) 的店員姓名

9.     建立一個 view 叫 hot_seller，內容是總銷售杯數超過全店平均的飲品；再查 view，列出飲品名稱

SELECT drink.dname,

       sum(qty)

FROM order_item

JOIN drink USING(did)

GROUP BY drink.dname

HAVING sum(qty) >

  (SELECT avg(sumqty)

   FROM

     (SELECT did,

             sum(qty) sumqty

      FROM drink

      JOIN order_item USING(did)

      GROUP BY did) AS sub_total)

10.   對 orders 與 order_item 進行集合運算，找出沒有任何明細卻存在於 orders 的訂單編號 (應為空集合，檢查資料完整性)

這題沒有法子做因為每筆訂單都有明細

SELECT *

FROM orders

LEFT JOIN order_item USING(oid)

WHERE cid IS NULL