SQL 巩固测试题
查询订购日期在 1996 年 7 月 1 日至 1996 年 7 月 15 日之间的订单的订购日期、订单 ID、客户 ID 和雇员 ID 等字段的值
SELECT 订购日期,订单ID,客户ID,雇员IDFROM 订单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 订单 dJOIN 客户 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 订单 djoin 运货商 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 订单 djoin 订单明细 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 订单 djoin 订单明细 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、产品名称和库存量
12. 查询单价大于 20 元的所有产品的产品名称、单价以及供应商的公司名称、电话
13. 查询上海和北京的客户在 1996 年订购的所有订单的订单 ID、所订购的产品名称和数量
查询华北客户的每份订单的订单 ID、产品名称和销售金额
15. 按运货商公司名称，统计 1997 年由各个运货商承运的订单的总数量
16. 统计 1997 年上半年的每份订单上所订购的产品的总数量
17. 统计各类产品的平均价格
18. 统计各地区客户的总数量
19. 找出供应商名称，所在城市
20. 找出华北地区能够供应海鲜的所有供应商列表。
21. 找出订单销售额前五的订单是经由哪家运货商运送的。
22. 找出按箱包装的产品名称。
23. 找出重庆的供应商能够供应的所有产品列表。
24. 找出雇员郑建杰所有的订单并根据订单销售额排序。
25. 找出订单 10284 的所有产品以及订单金额，运货商。
26. 建立产品与订单的关联。
27. 计算销量前 10 位的订单明细，结果集返回订单 ID，订单日期，公司名称，发货日期，销售额，并排序
28. 按年度统计销售额
------以上开始时间一个半小时
30. 查询供应商中能够供应的产品样数最多的供应商。
31. 查询产品类别中包含的产品数量最多的类别。
32. 找出所有的订单中经由哪家运货商运货次数最多。
33. 按类别，产品分组，统计销售额。
34. 查询海鲜类别最大的一笔订单。
35. 按季度统计销售量
36. 查出订单总额超出 5000 的所有订单，客户名称，客户所在地区。
37. 查询哪些产品的年度销售额低于 2000
38. 查询所有订单 ID 开头为 102 的订单
39. 查询所有“中硕贸易”，“学仁贸易”，“正人资源”，“中通”客户的订单，（要求使用 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 `产品` cjoin `类别` 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 产品 cORDER 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 订单 djoin `订单明细` 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 订单 djoin 订单明细 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. 将产品表和运货商两个无关的表整合为一个表
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.`城市` ='北京'
复制代码
评论