数据源
链接：https://pan.baidu.com/s/14WQXDf9rm7rvZLTK7KWJzA
提取码：etqr
SQL巩固测试题
查询订购日期在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
d.订购日期,
d.订单ID,
d.客户ID,
k.`公司名称`,
d.雇员ID,
g.`姓氏`,
g.`名字`,
CONCAT(g.`姓氏`,g.`名字`) as 姓名
FROM
订单 d
JOIN 客户 k ON d.`客户ID` = k.`客户ID`
JOIN 雇员 g ON d.`雇员ID` = g.`雇员ID`
WHERE
date (d.订购日期) BETWEEN '1996-07-01' AND '1996-07-15'
order by g.`姓氏`,g.`名字`,d.`订单ID`
6. 查询“10248”和“10254”号订单的订单ID、运货商的公司名称、订单上所订购的产品的名称
select
d.`订单ID`,
y.`公司名称`,
c.`产品名称`
from 订单 d
join 运货商 y on d.`运货商` = y.`运货商ID`
join 订单明细 dm on d.`订单ID`=dm.`订单ID`
join 产品 c on dm.`产品ID`=c.`产品ID`
where d.`订单ID` in ('10248','10254')
7. 查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称、数量、单价和折扣
select
d.`订单ID`,
c.`产品名称`,
dm.`数量`,
dm.`单价`,
dm.`折扣`
from 订单 d
join 订单明细 dm on d.`订单ID`=dm.`订单ID`
join 产品 c on dm.`产品ID`=c.`产品ID`
where d.`订单ID` in ('10248','10254')
8. 查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称及其销售金额
select
d.`订单ID`,
c.`产品名称`,
dm.`数量`*dm.`单价` * (1-dm.`折扣`) as 销售额
from 订单 d
join 订单明细 dm on d.`订单ID`=dm.`订单ID`
join 产品 c on dm.`产品ID`=c.`产品ID`
where d.`订单ID` in ('10248','10254')
9. 查询所有运货商的公司名称和电话
select
y.`公司名称`,
y.`电话`
from 运货商 y
10. 查询所有客户的公司名称、电话、传真、地址、联系人姓名和联系人头衔
select
k.`公司名称`,
k.`电话`,
k.`传真`,
k.`地址`,
k.`联系人姓名`,
k.`联系人职务`
from 客户 k
11. 查询单价介于10至30元的所有产品的产品ID、产品名称和库存量
SELECT
`产品ID`,
`产品名称`,
`库存量`
FROM
`产品`
WHERE
单价 BETWEEN 10
AND 30
12. 查询单价大于20元的所有产品的产品名称、单价以及供应商的公司名称、电话
SELECT
c.`产品名称`,
c.`单价`,
g.`公司名称` as 供应商名称,
g.`电话`
FROM
`产品` c
join `供应商` g on c.`供应商ID` = g.`供应商ID`
WHERE
c.`单价`> 20
13. 查询上海和北京的客户在1996年订购的所有订单的订单ID、所订购的产品名称和数量
SELECT
d.`订单ID`,
-- dm.`产品ID`,
c.`产品名称`,
dm.`数量`
FROM
`订单` d
join `订单明细` dm on d.`订单ID` =dm.`订单ID`
join `产品` c on dm.`产品ID` = c.`产品ID`
WHERE
d.`货主城市` IN ('北京', '上海')
AND YEAR (d.`订购日期`) = 1996
查询华北客户的每份订单的订单ID、产品名称和销售金额
SELECT
d.`订单ID`,
c.`产品名称`,
dm.`数量`*dm.`单价` * (1-dm.`折扣`) as 销售额
FROM
`订单` d
join `订单明细` dm on d.`订单ID` =dm.`订单ID`
join `产品` c on dm.`产品ID` = c.`产品ID`
WHERE
d.`货主地区` IN ('华北')
15. 按运货商公司名称，统计1997年由各个运货商承运的订单的总数量
SELECT
y.`公司名称` ,
d.`运货商`,
count(d.`订单ID`)
FROM
订单 d
join `运货商` y on d.`运货商` = y.`运货商ID`
GROUP BY
d.`运货商`, y.`公司名称`
16. 统计1997年上半年的每份订单上所订购的产品的总数量
SELECT
d.`订单ID`,
sum(dm.`数量`)
FROM
订单 d
join `订单明细` dm on d.`订单ID`= dm.`订单ID`
where d.`订购日期` >= DATE('1997-01-01') and d.`订购日期` <= DATE('1997-06-30')
group by d.`订单ID`
order by d.`订单ID`
17. 统计各类产品的平均价格
SELECT
c.`产品名称`,
ROUND(AVG(c.`单价`),2)
FROM
产品 c
GROUP BY c.`产品名称`
ORDER BY c.`产品名称`
18. 统计各地区客户的总数量
SELECT
k.`地区`,
count(k.`客户ID`)
FROM
客户 k
GROUP BY k.`地区`
order by count(k.`客户ID`) desc
19. 找出供应商名称，所在城市
select g.`公司名称` as 供应商名称,
g.`城市`
from 供应商 g
20. 找出华北地区能够供应海鲜的所有供应商列表。
SELECT
DISTINCT
l.`类别名称` ,
g.`公司名称` as 供应商名称,
g.`地区`
from 产品 c
join 类别 l on c.`类别ID` = l.`类别ID`
join 供应商 g on c.`供应商ID` = g.`供应商ID`
where l.`类别名称` ='海鲜' and g.`地区`='华北'
21. 找出订单销售额前五的订单是经由哪家运货商运送的。
SELECT
-- d.`运货商` as 运货商id,
y.`公司名称` as 运货商名称,
dm.`订单ID`,
sum(FORMAT(dm.`单价` * dm.数量 * (1- dm.`折扣`),2)) as 订单销售额
from `订单明细` dm
join `订单` d on dm.`订单ID`=d.`订单ID`
join `运货商` y on d.`运货商` = y.`运货商ID`
GROUP BY dm.`订单ID`,y.`公司名称`
order by sum(FORMAT(dm.`单价` * dm.数量 * (1- dm.`折扣`),2)) desc
LIMIT 5
22. 找出按箱包装的产品名称。
SELECT
c.`产品名称`
from `产品` c
where `单位数量` like '%箱%'
23. 找出重庆的供应商能够供应的所有产品列表。
SELECT
c.`产品名称`
FROM
`产品` c
WHERE
c.供应商ID IN (
SELECT
g.`供应商ID`
FROM
供应商 g
WHERE
g.`城市` = '重庆'
)
24. 找出雇员郑建杰所有的订单并根据订单销售额排序。
SELECT
dm.`订单ID`,
sum(
FORMAT(
dm.`单价` * dm.数量 * (1 - dm.`折扣`),
2
)
) 订单销售额
FROM
`订单明细` dm
WHERE
dm.`订单ID` IN (
SELECT
d.`订单ID`
FROM
订单 d
JOIN 雇员 g ON d.`雇员ID` = g.`雇员ID`
WHERE
CONCAT(g.`姓氏`, g.`名字`) = '郑建杰'
)
GROUP BY
dm.`订单ID`
ORDER BY
sum(
FORMAT(
dm.`单价` * dm.数量 * (1 - dm.`折扣`),
2
)
) DESC
25. 找出订单10284的所有产品以及订单金额，运货商。
SELECT
y.`公司名称` AS 运货商名称,
d.`订单ID`,
dm.`产品ID`,
c.`产品名称`,
FORMAT(
dm.`单价` * dm.数量 * (1 - dm.`折扣`),
2
) 产品销售额
FROM
订单 d
JOIN `运货商` y ON d.`运货商` = y.`运货商ID`
JOIN `订单明细` dm ON d.`订单ID` = dm.`订单ID`
JOIN `产品` c ON dm.`产品ID` = c.`产品ID`
WHERE
d.`订单ID` = '10284'
GROUP BY
y.`公司名称`,
d.`订单ID`,
dm.`产品ID`,
c.`产品名称`
26. 建立产品与订单的关联。
SELECT
*
from `产品` c
join `订单明细` dm on dm.`产品ID` = c.`产品ID`
join `订单` d on d.`订单ID` = dm.`订单ID`
27. 计算销量前10位的订单明细，结果集返回订单ID，订单日期，公司名称，发货日期，销售额，并排序
SELECT
-- d.`运货商` as 运货商id,
y.`公司名称` as 运货商名称,
k.`公司名称` as 客户名称,
DATE_FORMAT(d.`订购日期`,'%Y-%m-%d') 订购日期2,
DATE_FORMAT(d.`发货日期`,'%Y-%m-%d') 发货日期2,
dm.`订单ID`,
sum(FORMAT(dm.`单价` * dm.数量 * (1- dm.`折扣`),2)) as 订单销售额
from `订单明细` dm
join `订单` d on dm.`订单ID`=d.`订单ID`
join `运货商` y on d.`运货商` = y.`运货商ID`
join `客户` k on d.`客户ID` = k.`客户ID`
GROUP BY dm.`订单ID`,d.`订购日期`,d.`发货日期`,k.`公司名称`,
y.`公司名称`
order by sum(FORMAT(dm.`单价` * dm.数量 * (1- dm.`折扣`),2)) desc
LIMIT 10
28. 按年度统计销售额
SELECT
year(d.`订购日期` ),
FORMAT(sum( dm.`单价` * dm.数量 * (1- dm.`折扣`)) ,2) as 订单销售额
from `订单明细` dm
join `订单` d on dm.`订单ID`=d.`订单ID`
GROUP BY year(d.`订购日期` )
order by sum(FORMAT(dm.`单价` * dm.数量 * (1- dm.`折扣`),2)) desc
------以上开始时间一个半小时
30. 查询供应商中能够供应的产品样数最多的供应商。
SELECT
g.`公司名称` as 供应商名称,
COUNT(c.`产品名称` )
from 产品 c
join 供应商 g on g.`供应商ID` = c.`供应商ID`
group by g.`公司名称`
ORDER BY COUNT(c.`产品名称` )
DESC
LIMIT 5
31. 查询产品类别中包含的产品数量最多的类别。
select
l.`类别名称`,
count(c.`产品名称`)
from `产品` c
join `类别` l on c.`类别ID` = l.`类别ID`
GROUP BY l.`类别名称`
order by count(c.`产品名称`) desc
LIMIT 1
32. 找出所有的订单中经由哪家运货商运货次数最多。
SELECT
y.`公司名称` as 运货商名称,
count(d.`订单ID`)
from `订单` d join 运货商 y on d.`运货商` =y.`运货商ID`
GROUP BY y.`公司名称`
order by count(d.`订单ID`) DESC
limit 1
33. 按类别，产品分组，统计销售额。
SELECT
l.`类别名称`,
c.`产品名称`,
format(sum(dm.`单价`*dm.`数量`*(1-dm.`折扣`)),2) as 销售额
from `订单明细` dm join `产品` c on dm.`产品ID`=c.`产品ID`
join `类别` l on c.`类别ID`= l.`类别ID`
GROUP BY l.`类别名称`,
c.`产品名称`
order by l.`类别名称`,
c.`产品名称`,
format(sum(dm.`单价`*dm.`数量`*(1-dm.`折扣`)),2)
34. 查询海鲜类别最大的一笔订单。
SELECT
l.`类别名称`,
dm.`订单ID`,
format(sum(dm.`单价`*dm.`数量`*(1-dm.`折扣`)),2) as 销售额
from `订单明细` dm join `产品` c on dm.`产品ID`=c.`产品ID`
join `类别` l on c.`类别ID`= l.`类别ID`
where l.`类别名称`='海鲜'
GROUP BY l.`类别名称`,
dm.`订单ID`
order by
sum(dm.`单价`*dm.`数量`*(1-dm.`折扣`)) DESC
LIMIT 1
35. 按季度统计销售量
year
36. 查出订单总额超出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) > 500
ORDER BY format(sum(dm.`单价`*dm.`数量`*(1-dm.`折扣`)),2) desc
37. 查询哪些产品的年度销售额低于2000
SELECT
year(d.`订购日期`)as 年度,
c.`产品名称`,
format(sum(dm.`单价`*dm.`数量`*(1-dm.`折扣`)),2) as 销售额
from 订单 d join `订单明细` dm on d.`订单ID` =dm.`订单ID`
join `产品` c on dm.`产品ID`= c.产品ID
GROUP BY
year(d.`订购日期`),
c.`产品名称`
HAVING sum(dm.`单价`*dm.`数量`*(1-dm.`折扣`)) < 2000
ORDER BY sum(dm.`单价`*dm.`数量`*(1-dm.`折扣`))
38. 查询所有订单ID开头为102的订单
SELECT
*
from `订单` d
where d.`订单ID` like '102%'
39. 查询所有“中硕贸易”，“学仁贸易”，“正人资源”，“中通”客户的订单，（要求使用in函数）
SELECT
k.`公司名称` as 客户名称,
d.*
from `订单` d
join `客户` k on d.`客户ID` = k.`客户ID`
where k.`公司名称` in ('中硕贸易','学仁贸易','正人资源','中通')
40. -----以上考试时间一个半小时
41. 查询所有订单中月份不是单数的订单。
SELECT
MONTH(d.`订购日期`),
d.*
from `订单` d
where MONTH(d.`订购日期`)%2 = 0 -- 除以2 余数等于0
42. 分别各写一个查询，得到订单中折扣为15%，20%的所有订单，并将两个查询再组成一个。
select * from `订单明细` where FORMAT(折扣,2) = 0.15
UNION
select * from `订单明细` where FORMAT(折扣,2) = 0.20
43. 找出在入职时已超过30岁的所有员工信息
SELECT
DATEDIFF(g.雇用日期,g.出生日期)/365 as 入职年龄,
g.*
FROM
雇员 g
where DATEDIFF(g.雇用日期,g.出生日期)/365 > 30
44. 找出所有单价大于30的产品(附加要求，产品类别，供应商作为参数，当产品类别和供应商都为空的时候，nofilter)
SELECT
l.`类别名称`,
g.`公司名称` as 供应商名称,
c.*
from `产品` c
join `类别` l on c.`类别ID` = l.`类别ID`
join `供应商` g on c.`供应商ID` = g.`供应商ID`
WHERE c.`单价` > 30
order by c.`单价` desc
45. 查询所有库存产品的总额，并按照总额排序
SELECT
c.`单价` * c.库存量 AS 库存金额
FROM
产品 c
ORDER BY
(c.`单价` * c.库存量) DESC
46. 检索出职务为销售代表的所有订单中，每笔订单总额低于2000的订单明细，以及相关供应商名称。
SELECT
dm.`订单ID`,
dm.`产品ID`,
c.`产品名称`,
c.`供应商ID`,
g.`公司名称` as 供应商名称,
dm.`单价`,
dm.`折扣`,
dm.`数量`
from `订单明细` dm
join `产品` c on dm.`产品ID` = c.`产品ID`
join `供应商` g on c.`供应商ID`=g.`供应商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
)
47. 检索出向艾德高科技提供产品的供应商所在的城市。
SELECT
DISTINCT
g.`城市` as 供应商所在城市
-- d.`客户ID`,
-- k.`公司名称` as 客户名称,
-- d.`订单ID`,
--
-- dm.`产品ID`,
-- c.`产品名称`,
-- c.`供应商ID`,
-- g.`公司名称` as 供应商名称,
-- g.`城市` as 供应商所在城市,
-- d.`订购日期`
--
from 订单 d
join 订单明细 dm on d.`订单ID` = dm.`订单ID`
join 产品 c on dm.`产品ID` = c.`产品ID`
join 供应商 g ON c.`供应商ID` = g.`供应商ID`
join 客户 k on d.`客户ID`= k.`客户ID`
where k.`公司名称` ='艾德高科技'
48. 计算每一笔订单的发货期（从订购到发货），运货期（从发货到到货）的时长，并按照发货期从长到短的顺序进行排序。
SELECT
DATEDIFF(d.`发货日期`,d.`订购日期`) as 订发天数,
DATEDIFF(d.`到货日期`,d.`订购日期`) as 到发天数,
d.*
from 订单 d
ORDER BY DATEDIFF(d.`发货日期`,d.`订购日期`) desc
-- 知识点：datediff 两个日期差天数
49. 将产品表和运货商两个无关的表整合为一个表
SELECT
*
from `产品`,`运货商`
50. 获取在北京工作并向福星制衣厂股份有限公司发送过订单的职工名称。
select
d.`客户ID`,
k.`公司名称`,
d.`雇员ID`,
g.`姓氏`,
g.`名字`,
g.`城市`
from `订单` d
join `客户` k on d.`客户ID` = k.`客户ID`
join `雇员` g on d.`雇员ID` = g.`雇员ID`
where k.`公司名称`='福星制衣厂股份有限公司'
and g.`城市` ='北京'
文章转载自flychen的碎语，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
