SQL鞏固測試題
本題目主要來自大陸網站,台灣有人貼,實作一下增加一下經驗
相關資料可由下列找到
https://1drv.ms/u/s!AiaZqpUA1g5ShUDnohpCNCVR-_go?e=5nWHXK
|
|
|
|
|
|
|
|
|
|
|
|
SQL鞏固測試題
結合第一天所看的SQL查詢內容。用一天時間獨立完成以下習題,並將相應的查詢語句和視圖截圖貼到相應題目下方。
1. 找出供應商名稱，所在城市
SELECT 公司名稱,
城市
FROM 供應商
2. 找出華北地區能夠供應海鮮的所有供應商列表。
SELECT DISTINCT 公司名稱
FROM 供應商
INNER JOIN 產品 USING(供應商id)
INNER JOIN 類別 USING(類別id)
WHERE 地區 = "華北"
AND 類別名稱 = "海鮮"
3. 找出訂單銷售額前五的訂單是經由哪家運貨商運送的。
SELECT 訂單.訂單id,
Round(Sum(( 單價 * 數量 ) - ( 單價 * 數量 ) * 折扣), 0) AS
銷售額,
運貨商.公司名稱
FROM 訂單,
訂單明細,
運貨商
WHERE 訂單.運貨商 = 運貨商.運貨商id
AND 訂單.訂單id = 訂單明細.訂單id
GROUP BY 訂單id
ORDER BY 銷售額 DESC
LIMIT 5
4. 找出按箱包裝的產品名稱。
SELECT 產品名稱
FROM 產品
WHERE 單位數量 LIKE "%箱%"
5. 找出重慶的供應商能夠供應的所有產品清單。
SELECT 產品.產品名稱
FROM 供應商,
產品
WHERE 城市="重慶"
AND 產品.供應商ID=供應商.供應商ID
6. 找出雇員鄭建傑所有的訂單並根據訂單銷售額排序。
SELECT 訂單.訂單id,
Round(Sum((單價*數量)-(單價*數量)*折扣), 0) AS
銷售額
FROM 雇員,
訂單,
訂單明細
WHERE Concat(雇員.姓氏, 雇員.名字) = "鄭建傑"
AND 雇員.雇員id = 訂單.雇員id
AND 訂單.訂單id = 訂單明細.訂單id
GROUP BY 訂單.訂單id
ORDER BY 銷售額 DESC
7. 找出訂單10284的所有產品以及訂單金額，運貨商。
SELECT 產品名稱,
運貨商.公司名稱,
( 訂單明細.單價 * 訂單明細.數量 ) - (
訂單明細.單價 * 訂單明細.數量 ) * 折扣 AS 金額
FROM 訂單
LEFT JOIN 訂單明細 USING(訂單id)
LEFT JOIN 運貨商
ON 訂單.運貨商 = 運貨商.運貨商id
LEFT JOIN 產品 USING(產品id)
WHERE 訂單.訂單id = 10284
8. 建立產品與訂單的關聯。
#10. 按年度統計銷售額
SELECT 訂單.訂單ID,產品.產品名稱,產品.單價
FROM 訂單,
訂單明細,
產品
WHERE 訂單.訂單id = 訂單明細.訂單id
AND 訂單明細.產品ID=產品.產品ID
9. 計算銷量前10位的訂單明細，查詢結果請顯示
訂單ID，訂單日期，公司名稱，發貨日期，銷售額，並排序
SELECT 訂單.訂單id,
訂單.訂購日期,
客戶.公司名稱,
訂單.到貨日期,
Round(Sum((單價 * 數量) - (單價 * 數量) * 折扣), 0) AS 銷售額
FROM 訂單,
訂單明細,
客戶
WHERE 訂單.訂單id = 訂單明細.訂單id
AND 客戶.客戶ID=訂單.客戶ID GROUP BY 訂單id
ORDER BY 銷售額 DESC
LIMIT 10
10. 按年度統計銷售額
#10. 按年度統計銷售額
SELECT SUBSTR(訂單.訂購日期, 1, 4) AS 年度,
Round(Sum((單價 * 數量) - (單價 * 數量) * 折扣), 0) AS 銷售額
FROM 訂單,
訂單明細
WHERE 訂單.訂單id = 訂單明細.訂單id GROUP BY 年度
11. 查詢供應商中能夠供應的產品樣數最多的供應商。
SELECT 供應商.公司名稱,COUNT(產品.產品ID) AS 數量
FROM 產品
INNER JOIN 供應商 USING(供應商ID)
GROUP BY 供應商ID
ORDER BY 數量 DESC
LIMIT 1
12. 查詢產品類別中包含的產品數量最多的類別。
SELECT 類別.類別名稱 ,COUNT(*) AS 類別數量
FROM 產品
LEFT JOIN 類別 USING(類別ID)
GROUP BY 類別.類別名稱
ORDER BY 類別數量 DESC
LIMIT 1
13. 找出所有的訂單中經由哪家運貨商運貨次數最多。
SELECT 運貨商.公司名稱,COUNT(*) AS 運貨次數
FROM 訂單
LEFT JOIN 運貨商 on 訂單.運貨商 =運貨商.運貨商ID
GROUP BY 訂單.運貨商
ORDER BY 運貨次數 DESC
LIMIT 1
14. 按類別，產品分組，統計銷售額。
SELECT 類別.類別名稱,
Round(Sum((訂單明細.單價 * 訂單明細.數量) - (訂單明細.單價 * 訂單明細.數量) * 訂單明細.折扣), 0) AS 銷售額
FROM 訂單
LEFT JOIN 訂單明細 USING(訂單ID)
LEFT JOIN 產品 USING(產品ID)
LEFT JOIN 類別 USING(類別ID)
GROUP BY 類別ID
15. 查詢海鮮類別最大的一筆訂單。
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
16. 按季度統計銷售量
SELECT 訂單.訂單ID ,QUARTER(訂單.訂購日期)
FROM 訂單
SELECT YEAR(訂單.訂購日期),
QUARTER(訂單.訂購日期),
Round(SUM(((訂單明細.單價 * 訂單明細.數量)*(1-訂單明細.折扣))), 2) AS 銷售額
FROM 訂單
LEFT JOIN 訂單明細 USING(訂單ID)
GROUP BY YEAR(訂單.訂購日期),
QUARTER(訂單.訂購日期)
17. 查出訂單總額超出5000的所有訂單，客戶名稱，客戶所在地區。
SELECT 訂單.訂單ID,
客戶.公司名稱,
客戶.地區,
round(SUM(((訂單明細.單價 * 訂單明細.數量)*(1-訂單明細.折扣))), 0) AS 銷售額
FROM 訂單
INNER JOIN 訂單明細 USING(訂單ID)
INNER JOIN 客戶 USING(客戶ID)
GROUP BY 訂單.訂單ID
HAVING 銷售額 >5000
另一個解法
SELECT
d.`訂單ID`,
k.`公司名稱` as 客戶名稱,
k.`地區` as 客戶所在地區,
format(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
k.`公司名稱`,
k.`地區`,
d.`訂單ID`
HAVING format(sum(dm.`單價`*dm.`數量`*(1-dm.`折扣`)),2) > 5000
ORDER BY format(sum(dm.`單價`*dm.`數量`*(1-dm.`折扣`)),2) desc
這個會沒有答案
FORMAT把數值每三位數用逗號(,)區分時，或是想要調整小數點顯示的位數時，可以使用FORMAT函式來達成目的。
例如以下的情形：
- 每三位數用逗號(,)區分: 123456 -> 123,456
- 顯示小數點下兩位數: 123.456 -> 123.45
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
k.`公司名稱`,
k.`地區`,
d.`訂單ID`
HAVING round(sum(dm.`單價`*dm.`數量`*(1-dm.`折扣`)),2) > 5000
ORDER BY round(sum(dm.`單價`*dm.`數量`*(1-dm.`折扣`)),2) desc
18. 查詢哪些產品的年度銷售額低於2000
SELECT 產品.產品名稱,
YEAR(訂購日期),
round(SUM(((訂單明細.單價 * 訂單明細.數量)*(1-訂單明細.折扣))), 0) AS 銷售額
FROM 訂單
LEFT JOIN 訂單明細 USING(訂單ID)
LEFT JOIN 產品 USING(產品ID)
GROUP BY 產品名稱, YEAR(訂單.訂購日期)
HAVING 銷售額<2000
19. 查詢所有訂單ID開頭為102的訂單
20. 查詢所有“中碩貿易”，“學仁貿易”，“正人資源”，“中通”客戶的訂單，（要求使用in函數）
SELECT *
FROM 訂單
WHERE 客戶ID IN
(SELECT 客戶ID
FROM 客戶
WHERE 公司名稱 ="中碩貿易"
OR 公司名稱 ="學仁貿易"
OR 公司名稱 ="正人資源"
OR 公司名稱 ="中通")
21. 查詢所有訂單中月份不是單數的訂單。
22. 分別各寫一個查詢，得到訂單中折扣為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
23. 找出在入職時已超過30歲的所有員工資訊
SELECT *
FROM 雇員
WHERE TIMESTAMPDIFF(YEAR,出生日期,雇用日期) >=30
24. 找出所有單價大於30的產品(附加要求，產品類別，供應商作為參數，當產品類別和供應商都為空的時候，nofilter)
SELECT *
FROM 產品
INNER JOIN 供應商 USING (供應商ID)
INNER JOIN 類別 USING(類別ID)
WHERE 單價 > 30
25. 查詢所有庫存產品的總額，並按照總額排序
SELECT 類別ID,SUM(單價*庫存量)
FROM 產品
GROUP BY 類別ID
26. 檢索出職務為銷售代表的所有訂單中，每筆訂單總額低於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 )
27. 檢索出向艾德高科技提供產品的供應商所在的城市。
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.`公司名稱` = '艾德高科技'
28. 計算每一筆訂單的發貨期（從訂購到發貨），運貨期（從發貨到到貨）的時常，並按照發貨期從長到短的順序進行排序。
SELECT 訂單ID
,TIMESTAMPDIFF(day,訂購日期,發貨日期) AS 發貨期
,TIMESTAMPDIFF(day,發貨日期,到貨日期) AS 運貨期
FROM 訂單
WHERE 到貨日期 IS NOT NULL
AND 發貨日期 IS NOT NULL
AND 訂購日期 IS NOT NULL
本題這樣作會有問題，因為三個時間資料有數筆NULL或是未來訂單如11069
29. 將產品表和運貨商兩個無關的表整合為一個表(真不知為何做這一題)
SELECT p.*,t.*
FROM 訂單
LEFT JOIN 訂單明細 USING(訂單ID)
LEFT JOIN 產品 as p USING(產品ID)
LEFT JOIN 運貨商 AS t ON 訂單.運貨商 =t.運貨商ID
30. 獲取在北京工作並向福星制衣廠股份有限公司發送過訂單的職工名稱。(這一題真的是亂出題,每個雇員工作地方都在北京) 修改為：獲取向福星制衣廠股份有限公司發送過訂單的職工名稱
SELECT CONCAT(雇員.姓氏,雇員.名字) 雇員姓名, 訂單ID
FROM 訂單
LEFT JOIN 客戶 USING(客戶ID)
LEFT JOIN 雇員 USING(雇員ID)
WHERE 客戶.公司名稱="福星制衣廠股份有限公司"
ORDER BY訂單ID
https://www.cnblogs.com/qijiang123/p/14330248.html
https://zhuanlan.zhihu.com/p/389183029