本題目主要來自大陸網站,台灣有人貼，實作一下增加一下經驗。部份題目有問題 ，無法解出來或是資料有問題

相關資料可由下列找到

50題:https://zhuanlan.zhihu.com/p/389183029

30題: https://www.cnblogs.com/qijiang123/p/14330248.html

資料庫資料表主要來源可以 由 https://ithelp.ithome.com.tw/articles/10212163 來Download

 

1.  查詢訂購日期在1996年7月1日至1996年7月15日之間的訂單的訂購日期、訂單ID、客戶ID和雇員ID等欄位的值

SELECT

  訂購日期,

  訂單ID,

  客戶ID,

  雇員ID

FROM

  訂單

WHERE

  DATE(訂購日期) BETWEEN '1996-07-01'

  AND '1996-07-15'

2. 查詢供應商的ID、公司名稱、地區、城市和電話欄位的值。條件是“地區等於華北”並且“連絡人頭銜等於銷售代表”。

SELECT

    供應商ID,

    公司名稱,

    地區,

    城市,

    電話

FROM

    供應商

WHERE

    地區 = "華北"

    AND 連絡人職務 = "銷售代表"

3. 查詢供應商的ID、公司名稱、地區、城市和電話欄位的值。其中的一些供應商位於華東或華南地區，另外一些供應商所在的城市是天津

SELECT

  供應商ID,

  公司名稱,

  地區,

  城市,

  電話

FROM

  供應商

WHERE

  地區 IN ('華東', '華南')

  OR 城市 = '天津'

4. 查詢位於“華東”或“華南”地區的供應商的ID、公司名稱、地區、城市和電話欄位的值

SELECT

  供應商ID,

  公司名稱,

  地區,

  城市,

  電話

FROM

  供應商

WHERE

  地區 IN ('華東', '華南')

5. 查詢訂購日期在1996年7月1日至1996年7月15日之間的訂單的訂購日期、訂單ID、相應訂單的客戶公司名稱、負責訂單的雇員的姓氏和名字等欄位的值，並將查詢結果按雇員的“姓氏”和“名字”欄位的昇冪排列，“姓氏”和“名字”值相同的記錄按“訂單 ID”的降冪排列

SELECT

    訂購日期,

    訂單ID,

    客戶ID,

    公司名稱,

    雇員ID,

    姓氏,

    名字,

    CONCAT(姓氏, 名字) AS 姓名

FROM

    訂單

    JOIN 客戶 USING(客戶ID)

    JOIN 雇員 USING(雇員ID)

WHERE

    date (訂購日期) BETWEEN '1996-07-01'

    AND '1996-07-15'

ORDER BY

    姓氏,

    名字,

    訂單ID

6. 查詢“10248”和“10254”號訂單的訂單ID、運貨商的公司名稱、訂單上所訂購的產品的名稱

SELECT

    訂單ID,

    運貨商.公司名稱,

    產品.產品名稱

FROM

    訂單

    LEFT JOIN 運貨商 ON 運貨商 = 運貨商ID

    LEFT JOIN 訂單明細 USING (訂單ID)

    LEFT JOIN 產品 USING (產品ID)

WHERE

    訂單ID IN("10248", "10254")

7. 查詢“10248”和“10254”號訂單的訂單ID、訂單上所訂購的產品的名稱、數量、單價和折扣

SELECT

    訂單ID,

    產品.產品名稱,

    訂單明細.數量,

    訂單明細.單價,

    訂單明細.折扣

FROM

    訂單

    LEFT JOIN 訂單明細 USING (訂單ID)

    LEFT JOIN 產品 USING (產品ID)

WHERE

    訂單ID IN("10248", "10254")

8. 查詢“10248”和“10254”號訂單的訂單ID、訂單上所訂購的產品的名稱及其銷售金額

SELECT

    訂單ID,

    產品.產品名稱,

    (

       訂單明細.數量 * 訂單明細.單價

    )*(1 - 訂單明細.折扣) AS 銷售額

FROM

    訂單

    LEFT JOIN 訂單明細 USING (訂單ID)

    LEFT JOIN 產品 USING (產品ID)

WHERE

    訂單ID IN("10248", "10254")

9. 查詢所有運貨商的公司名稱和電話

SELECT

    公司名稱,

    電話

FROM

    運貨商

10. 查詢所有客戶的公司名稱、電話、傳真、位址、連絡人姓名和連絡人頭銜

SELECT

    公司名稱,

    客戶.電話,

    客戶.傳真,

    客戶.連絡人姓名,

    客戶.連絡人職務

FROM

    客戶

11. 查詢單價介於10至30元的所有產品的產品ID、產品名稱和庫存量

SELECT 產品ID,

       產品名稱,

       庫存量

FROM 產品

WHERE 單價 BETWEEN 10 AND 30

12. 查詢單價大於20元的所有產品的產品名稱、單價以及供應商的公司名稱、電話

SELECT 產品.產品名稱,

       產品.單價,

       供應商.公司名稱,

       供應商.電話

FROM 產品

JOIN 供應商 USING(供應商id)

WHERE 單價 > 20

13. 查詢上海和北京的收貨的客戶在1996年訂購的所有訂單的訂單ID、所訂購的產品名稱和數量(請使用訂單資料)

SELECT 訂單ID,

       產品名稱,

       數量

FROM 訂單

JOIN 訂單明細 USING(訂單ID)

JOIN 產品 USING(產品ID)

WHERE YEAR(訂單.訂購日期)= 1996

  AND 貨主城市 IN ("北京",

               "上海")

14. 查詢華北客戶的每份訂單的訂單ID、產品名稱和銷售金額

SELECT 訂單id,

       產品名稱,

       round((訂單明細.單價*數量)*(1-折扣), 2) AS 銷售金額

FROM 訂單

JOIN 訂單明細 USING(訂單id)

JOIN 產品 USING(產品id)

WHERE 貨主地區="華北"

15. *按運貨商公司名稱，統計1997年由各個運貨商承運的訂單的總數量

SELECT

    公司名稱,

    運貨商,

    COUNT(訂單ID)

FROM

    訂單

    JOIN 運貨商  ON 運貨商 = 運貨商ID

    AND YEAR(訂購日期)= "1997"

GROUP BY

    運貨商,

    公司名稱

16. 統計1997年上半年的每份訂單上所訂購的產品的總數量

SELECT

  訂單ID,

  SUM(訂單明細.數量)

FROM

  訂單

  LEFT JOIN 訂單明細 USING(訂單ID)

WHERE

  訂購日期 BETWEEN "1997-1-1"

  AND "1997-6-30"

GROUP BY

  訂單ID

ORDER BY

  訂單ID

17. 統計各類產品的平均價格

SELECT

  類別id,

  類別名稱,

  AVG(單價) 平均價格

FROM

  類別

  LEFT JOIN 產品 USING(類別ID)

GROUP BY

  類別id

18. 統計各地區客戶的總數量

SELECT

    地區,

    COUNT(*) AS 數量

FROM

    客戶

GROUP BY

    地區

19. 找出供應商名稱，所在城市

SELECT 公司名稱,

       城市

FROM `供應商`

20. 找出華北地區能夠供應海鮮的所有供應商列表。

SELECT DISTINCT 公司名稱,

                類別名稱

FROM 產品

JOIN 供應商 USING(供應商ID)

JOIN 類別 USING(類別ID)

WHERE 類別名稱="海鮮"

  AND 地區="華北"

21. 找出訂單銷售額前五的訂單是經由哪家運貨商運送的。

SELECT 訂單id,

       運貨商.公司名稱,

       ROUND(SUM(訂單明細.單價 * 訂單明細.數量 *(1 -折扣)), 0) AS sales

FROM 訂單

JOIN 訂單明細 USING(訂單ID)

JOIN 運貨商 ON 運貨商 = 運貨商ID

GROUP BY 訂單ID

ORDER BY sales DESC

LIMIT 5

22. 找出按箱包裝的產品名稱。

SELECT 產品名稱

FROM 產品

WHERE 單位數量 LIKE "%箱%"

23. 找出 重慶 的供應商能夠供應的所有產品清單。

方法一

WITH sid AS

  (SELECT 供應商ID

   FROM 供應商

   WHERE 城市="重慶" )

SELECT 產品.產品名稱

FROM 產品

JOIN sid USING(供應商ID)

方法二

SELECT 產品.產品名稱

FROM 供應商,

     產品

WHERE 城市="重慶"

  AND 產品.供應商ID=供應商.供應商ID

24. 找出雇員 鄭建傑 所有的訂單並根據訂單銷售額排序。

SELECT 訂單ID,

       sum(FORMAT(單價 * 數量 * (1 - 折扣), 2)) 訂單銷售額

FROM 訂單明細

WHERE 訂單ID IN

    (SELECT 訂單ID

     FROM 訂單

     JOIN 雇員 USING(雇員ID)

     WHERE CONCAT(姓氏, 名字) = '鄭建傑' )

GROUP BY 訂單ID

ORDER BY 訂單銷售額 DESC

WITH d AS

(

SELECT 訂單.訂單id

FROM   雇員,

       訂單,

       訂單明細

WHERE  Concat(雇員.姓氏, 雇員.名字) = "鄭建傑"

       AND 雇員.雇員id = 訂單.雇員id

       AND  訂單.訂單id= 訂單明細.訂單id

)

SELECT 訂單.訂單id,sum(FORMAT(單價 * 數量 * (1 - 折扣), 2)) 訂單銷售額

FROM   訂單,

       訂單明細       

WHERE  訂單.訂單id = 訂單明細.訂單id AND 訂單.訂單id IN (SELECT 訂單id FROM d)

GROUP BY 訂單.訂單id

ORDER BY 訂單銷售額 DESC

25. 找出訂單10284的所有產品以及訂單金額，運貨商。

SELECT

   產品.產品名稱,

   (

       (d.單價 * d.數量)*(1 - d.折扣)

   ) 銷售金額,

   運貨商.公司名稱

FROM

   訂單

   JOIN 訂單明細 AS d USING(訂單ID)

   JOIN 產品 USING(產品ID)

   JOIN 運貨商 ON 運貨商 = 運貨商id

WHERE

   訂單.訂單ID = 10284

26. 建立產品與訂單的關聯。

SELECT

    *

FROM

    產品

    JOIN 訂單明細 USING(產品ID)

    JOIN 訂單 USING(訂單id)

`

27. 計算銷量前10位的訂單明細，結果集返回訂單ID，訂單日期，公司名稱，發貨日期，銷售額，並排序

SELECT

    訂單ID,

    訂購日期,

    發貨日期,

    公司名稱,

    (

       單價 * 數量 *(1 - 折扣)

    ) AS 銷售額

FROM

    訂單明細

    JOIN 訂單 USING(訂單ID)

    JOIN 客戶 USING(客戶ID)

ORDER BY

    銷售額 DESC

LIMIT

    10

計算銷量前10位的訂單，查詢結果請顯示
訂單 ID，訂單日期，公司名稱，發貨日期，銷售額，並排序

SELECT 訂單.訂單id,

       訂單.訂購日期,

       客戶.公司名稱,

       訂單.到貨日期,

       Round(Sum((單價 * 數量) - (單價 * 數量) * 折扣), 0) AS 銷售額

FROM 訂單,

     訂單明細,

     客戶

WHERE 訂單.訂單id = 訂單明細.訂單id

  AND 客戶.客戶ID=訂單.客戶ID GROUP  BY 訂單id

  ORDER  BY 銷售額 DESC

LIMIT 10

28. 按年度統計銷售額

#10. 按年度統計銷售額

SELECT SUBSTR(訂單.訂購日期, 1, 4) AS 年度,

       Round(Sum((單價 * 數量) - (單價 * 數量) * 折扣), 0) AS 銷售額

FROM 訂單,

     訂單明細

WHERE 訂單.訂單id = 訂單明細.訂單id GROUP  BY 年度

30. 查詢供應商中能夠供應的產品樣數最多的供應商。

SELECT 供應商.公司名稱,COUNT(產品.產品ID) AS 數量

FROM 產品

INNER JOIN 供應商 USING(供應商ID)

GROUP BY 供應商ID

ORDER BY 數量 DESC

LIMIT 1

31. 查詢產品類別中包含的產品數量最多的類別。

SELECT 類別.類別名稱 ,COUNT(*) AS 類別數量

FROM 產品

LEFT JOIN 類別 USING(類別ID)

GROUP BY 類別.類別名稱

ORDER BY 類別數量 DESC

LIMIT 1

32. 找出所有的訂單中經由哪家運貨商運貨次數最多。

SELECT 運貨商.公司名稱,COUNT(*) AS 運貨次數

FROM 訂單

LEFT JOIN 運貨商 on 訂單.運貨商 =運貨商.運貨商ID

GROUP BY  訂單.運貨商

ORDER BY 運貨次數 DESC

LIMIT 1

33. 按類別，產品分組，統計銷售額。

SELECT

    類別.類別名稱,

    產品.產品名稱,

    ROUND(

       SUM(

           (

               訂單明細.單價 * 訂單明細.數量

           ) - (

               訂單明細.單價 * 訂單明細.數量

           ) * 訂單明細.折扣

       ),

       0

    ) AS 銷售額

FROM

    訂單

    LEFT JOIN 訂單明細 USING(訂單ID)

    LEFT JOIN 產品 USING(產品ID)

    LEFT JOIN 類別 USING(類別ID)

GROUP BY

    類別ID,

    產品.產品名稱

ORDER BY

    類別.類別名稱,

    產品.產品名稱,

    銷售額 DESC

34. 查詢海鮮類別最大的一筆訂單。

SELECT 訂單.訂單ID,

       SUM(((訂單明細.單價 * 訂單明細.數量)*(1-訂單明細.折扣))) AS 銷售額

FROM 訂單

LEFT JOIN 訂單明細 USING(訂單ID)

LEFT JOIN 產品 USING(產品ID)

LEFT JOIN 類別 USING(類別ID)

WHERE 類別.類別名稱="海鮮"

GROUP BY 訂單.訂單ID

ORDER BY 銷售額 DESC

LIMIT 1

35. 按季度統計銷售量

SELECT 訂單.訂單ID ,QUARTER(訂單.訂購日期)

FROM  訂單

SELECT YEAR(訂單.訂購日期),

       QUARTER(訂單.訂購日期) as 季,

       Round(SUM(((訂單明細.單價 * 訂單明細.數量)*(1-訂單明細.折扣))), 2) AS 銷售額

FROM 訂單

LEFT JOIN 訂單明細 USING(訂單ID)

GROUP BY YEAR(訂單.訂購日期),

         QUARTER(訂單.訂購日期)

36. 查出訂單總額超出5000的所有訂單，客戶名稱，客戶所在地區。

SELECT d.訂單ID,

       k.公司名稱 AS 客戶名稱,

       k.地區 AS 客戶所在地區,

       round(sum(dm.單價*dm.數量*(1-dm.折扣)), 2) AS 銷售額

FROM 訂單 d

JOIN 訂單明細 dm ON d.訂單ID =dm.訂單ID

JOIN 客戶 k ON d.客戶ID=k.客戶ID

GROUP BY d.訂單ID

HAVING round(sum(dm.單價*dm.數量*(1-dm.折扣)), 2) > 5000

ORDER BY round(sum(dm.單價*dm.數量*(1-dm.折扣)), 2) DESC

FORMAT把數值每三位數用逗號(,)區分時，或是想要調整小數點顯示的位數時，可以使用FORMAT函式來達成目的。

例如以下的情形：

每三位數用逗號(,)區分: 123456 -> 123,456

顯示小數點下兩位數: 123.456 -> 123.45

SELECT 訂單.訂單ID,

       客戶.公司名稱,

       客戶.地區,

       round(SUM(((訂單明細.單價 * 訂單明細.數量)*(1-訂單明細.折扣))), 0) AS 銷售額

FROM 訂單

INNER JOIN 訂單明細 USING(訂單ID)

INNER JOIN 客戶 USING(客戶ID)

GROUP BY 訂單.訂單ID

HAVING 銷售額 >5000

37. 查詢哪些產品的年度銷售額低於2000

SELECT 產品.產品名稱,

       YEAR(訂購日期),

       round(SUM(((訂單明細.單價 * 訂單明細.數量)*(1-訂單明細.折扣))), 0) AS 銷售額

FROM 訂單

LEFT JOIN 訂單明細 USING(訂單ID)

LEFT JOIN 產品 USING(產品ID)

GROUP  BY 產品名稱, YEAR(訂單.訂購日期)

HAVING 銷售額<2000

ORDER BY

  銷售額 DESC

38. 查詢所有訂單ID開頭為102的訂單

SELECT *

FROM   訂單

WHERE

訂單ID LIKE "102%"

39. 查詢所有“中碩貿易”，“學仁貿易”，“正人資源”，“中通”客戶的訂單，（要求使用in函數）

SELECT *

FROM 訂單

WHERE 客戶ID IN

    (SELECT 客戶ID

     FROM 客戶

     WHERE 公司名稱 ="中碩貿易"

       OR 公司名稱 ="學仁貿易"

       OR 公司名稱 ="正人資源"

       OR 公司名稱 ="中通")

41. 查詢所有訂單中月份不是單數的訂單。

SELECT *

FROM 訂單

WHERE MONTH(訂購日期) %2 =0

42. 分別各寫一個查詢，得到訂單中折扣為15%，20%的所有訂單，並將兩個查詢再組成一個。

SELECT *

FROM 訂單

LEFT JOIN 訂單明細 AS b USING(訂單ID)

WHERE FORMAT(b.折扣, 2)=0.15

UNION

SELECT *

FROM 訂單

LEFT JOIN 訂單明細 AS b USING(訂單ID)

WHERE FORMAT(b.折扣, 2)=0.20

43. 找出在入職時已超過30歲的所有員工資訊

SELECT *

FROM 雇員

WHERE TIMESTAMPDIFF(YEAR,出生日期,雇用日期) >=30

DATEDIFF(g.雇用日期,g.出生日期)/365 > 30?????

44. 找出所有單價大於30的產品(附加要求，產品類別，供應商作為參數，當產品類別和供應商都為空的時候，nofilter)

為空的時候，nofilter)

SELECT  *

FROM 產品

INNER JOIN 供應商 USING (供應商ID)

INNER JOIN 類別 USING(類別ID)

WHERE   單價 > 30

45. 查詢所有庫存產品的總額，並按照總額排序

SELECT 類別ID,SUM(單價*庫存量)

FROM 產品

GROUP BY 類別ID

46. 檢索出職務為銷售代表的所有訂單中，每筆訂單總額低於2000的訂單明細，以及相關供應商名稱。

方法一

SELECT 訂單id,c.產品名稱,g.公司名稱,dm.單價,dm.數量

FROM   訂單明細 dm

       JOIN 產品 c using(產品id)

       JOIN 供應商 g using(供應商id)

WHERE  dm.訂單id IN (SELECT d.訂單id

                       FROM   訂單 d

                              JOIN 訂單明細 dm

                                ON d.訂單id = dm.訂單id

                              JOIN 雇員 g

                                ON d.雇員id = g.雇員id

                       WHERE  g.職務 = "銷售代表"

                       GROUP  BY d.訂單id

                       HAVING Sum(dm.數量 * dm.單價 * ( 1 - dm.折扣 )) <

                              2000)

方法二

WITH overid    AS

(SELECT d.訂單id

         FROM   訂單 d

                JOIN 訂單明細 dm

                  ON d.訂單id = dm.訂單id

                JOIN 雇員 g

                  ON d.雇員id = g.雇員id

         WHERE  g.職務 = "銷售代表"

         GROUP  BY d.訂單id

         HAVING Sum(dm.數量 * dm.單價 * ( 1 - dm.折扣 )) < 2000

)

SELECT 訂單id,產品.產品名稱,供應商.公司名稱,訂單明細.單價,訂單明細.數量

FROM   訂單明細

       JOIN 產品  using(產品id)

       JOIN 供應商  using(供應商id)

WHERE  訂單id IN (SELECT *  FROM   overid )

47. 檢索出向艾德高科技提供產品的供應商所在的城市。

SELECT d.`訂單ID`,

       c.`產品名稱`,

       g.`公司名稱`,

       g.`城市`

FROM `訂單` d,

     `訂單明細` m,

     `客戶` k,

     `產品` c,

     `供應商` g

WHERE d.`訂單ID` = m.`訂單ID`

  AND m.`產品ID` = c.`產品ID`

  AND c.`供應商ID` = g.`供應商ID`

  AND d.`客戶ID` = k.`客戶ID`

  AND k.`公司名稱` = '艾德高科技'

48. 計算每一筆訂單的發貨期（從訂購到發貨），運貨期（從發貨到到貨）的時長，並按照發貨期從長到短的順序進行排序。

SELECT

    訂單ID,

    TIMESTAMPDIFF(DAY, 訂購日期, 發貨日期) AS 發貨期,

    TIMESTAMPDIFF(DAY, 發貨日期, 到貨日期) AS 運貨期

FROM

    訂單

WHERE

    到貨日期 IS NOT NULL

    AND 發貨日期 IS NOT NULL

    AND 訂購日期 IS NOT NULL

ORDER BY

    發貨期 DESC

TIPS：datediff 兩個日期差天數

本題這樣作會有問題，因為三個時間資料有數筆NULL或是未來訂單如11069

49.      將產品表和運貨商兩個無關的表整合為一個表(真不知為何做這一題)

SELECT p.*,t.*

FROM 訂單

LEFT JOIN 訂單明細 USING(訂單ID)

LEFT  JOIN 產品  as p USING(產品ID)

LEFT  JOIN 運貨商 AS t ON  訂單.運貨商 =t.運貨商ID

50. 獲取在北京工作並向福星制衣廠股份有限公司發送過訂單的職工名稱。

SELECT

    distinct CONCAT(雇員.姓氏, 雇員.名字) 雇員姓名,

    客戶.公司名稱

FROM

    訂單

    LEFT JOIN 客戶 USING(客戶ID)

    LEFT JOIN 雇員 USING(雇員ID)

WHERE

    客戶.公司名稱 = "福星制衣廠股份有限公司"

    AND 雇員.城市 = "北京"

ORDER BY

    訂單ID