1. 找出供应商名称，所在城市
SELECT
公司名称,
城市
FROM
供应商
2. 找出华北地区能够供应海鲜的所有供应商列表。
SELECT
供应商.公司名称 华北地区海鲜供应商
FROM
供应商
LEFT JOIN 产品 ON 产品.供应商ID = 供应商.供应商ID
LEFT JOIN 类别 ON 类别.类别ID = 产品.类别ID
WHERE
供应商.地区 = '华北'
AND 类别.类别名称 = '海鲜'
3. 找出订单销售额前五的订单是经由哪家运货商运送的。
SELECT
`运货商`.公司名称,
ROUND(
SUM(
订单明细.单价 * 订单明细.数量 * (1 - 订单明细.折扣)
),
0
) 订单销售额 FROM 运货商
RIGHT JOIN 订单 ON `订单`.运货商 = `运货商`.运货商ID
RIGHT JOIN 订单明细 ON 订单明细.订单ID = 订单.订单ID
GROUP BY
订单明细.订单ID
ORDER BY
SUM(
订单明细.单价 * 订单明细.数量 * (1 - 订单明细.折扣)
) DESC
LIMIT 5
4. 找出按箱包装的产品名称。
SELECT
产品名称,
REPLACE (
LEFT (单位数量, 2),
'每',
''
) 包装单位
FROM
产品
WHERE
REPLACE (
LEFT (单位数量, 2),
'每',
''
) = '箱'
5. 找出重庆的供应商能够供应的所有产品列表。
SELECT
ANY_VALUE(`供应商`.公司名称) AS 公司名称,
GROUP_CONCAT(`产品`.产品名称) 供应产品
FROM
供应商
RIGHT JOIN 产品 ON `供应商`.供应商ID = `产品`.供应商ID
WHERE
供应商.城市 = '重庆'
6. 找出雇员郑建杰所有的订单并根据订单销售额排序。
SELECT
CONCAT(
雇员.姓氏,
雇员.名字
) 雇员姓名,
订单.订单ID,
ROUND(
订单明细.数量 * 订单明细.单价 * (1 - 订单明细.折扣),
0
) 销售金额
FROM
雇员
RIGHT JOIN 订单 ON 雇员.雇员ID = 订单.雇员ID
LEFT JOIN 订单明细 ON 订单明细.订单ID = 订单.订单ID
WHERE
CONCAT(
雇员.姓氏,
雇员.名字
) = "郑建杰"
ORDER BY
订单明细.数量 * 订单明细.单价 * (1 - 订单明细.折扣) DESC
7. 找出订单10284的所有产品以及订单金额，运货商。
SELECT
订单.订单ID,
GROUP_CONCAT(产品.产品名称) 产品明细,
SUM(
订单明细.单价 * 订单明细.数量 * (1 - 订单明细.折扣)
) 总金额,
运货商.公司名称
FROM
订单
LEFT JOIN 运货商 ON 订单.运货商 = 运货商.运货商ID
LEFT JOIN 订单明细 ON 订单.订单ID = 订单明细.订单ID
LEFT JOIN 产品 ON 产品.产品ID = 订单明细.产品ID
WHERE
订单.订单ID = 10284
8. 建立产品与订单的关联。
SELECT
*
FROM
订单
LEFT JOIN 订单明细 ON 订单.订单ID = 订单明细.订单ID
LEFT JOIN 产品 ON 订单明细.产品ID = 产品.产品ID
9. 计算销量前10位的订单明细，结果集返回订单ID，订单日期，公司名称，发货日期，销售额，并排序
SELECT
订单.订单ID,
订单.订购日期,
客户.公司名称,
订单.发货日期,
ROUND(
SUM(
订单明细.数量 * 订单明细.单价 * (1 - 订单明细.折扣)
),
2
) AS 销售额FROM订单
RIGHT JOIN 订单明细 ON 订单.订单ID = 订单明细.订单ID
LEFT JOIN 客户 ON 订单.客户ID = 客户.客户ID
GROUP BY
订单.订单ID
ORDER BY
销售额 DESC
LIMIT 10
10. 按年度统计销售额
SELECT
YEAR (`订单`.订购日期) AS 年份,
ROUND(
SUM(
订单明细.单价 * 订单明细.数量 * (1 - 订单明细.折扣)
),
2
) AS 销售额
FROM
订单
LEFT JOIN 订单明细 ON 订单明细.订单ID = `订单`.订单ID
GROUP BY
年份;
------以上开始时间一个半小时
11. 查询供应商中能够供应的产品样数最多的供应商。
SELECT
供应商.公司名称,
COUNT(`产品`.产品ID) 供应商品样数
FROM
供应商
RIGHT JOIN 产品 ON `产品`.`供应商ID` = `供应商`.`供应商ID`
GROUP BY
`供应商`.供应商ID
ORDER BY
COUNT(`产品`.产品ID) DESC
LIMIT 1;
12. 查询产品类别中包含的产品数量最多的类别。
SELECT
`类别`.`类别名称`,
SUM(产品.库存量) 产品数量
FROM
类别
RIGHT JOIN 产品 ON `类别`.类别ID = `产品`.类别ID
GROUP BY
`类别`.类别ID
ORDER BY
SUM(产品.库存量) DESC
LIMIT 1;
13. 找出所有的订单中经由哪家运货商运货次数最多。
SELECT
`运货商`.公司名称
FROM
运货商
RIGHT JOIN 订单 ON 运货商.`运货商ID` = `订单`.运货商 GROUP BY `运货商`.`运货商ID`
ORDER BY
COUNT(`运货商`.`运货商ID`) DESC
LIMIT 1;
14. 按类别，产品分组，统计销售额。
SELECT
`类别`.类别名称,
`产品`.产品名称,
ROUND(
SUM(
订单明细.单价 * 订单明细.`数量` * (1 - `订单明细`.折扣)
),
2
) AS 销售额
FROM
类别
RIGHT JOIN 产品 ON `产品`.类别ID = `类别`.类别ID
RIGHT JOIN 订单明细 ON 订单明细.产品ID = `产品`.产品ID
GROUP BY
`产品`.产品名称
15. 查询海鲜类别最大的一笔订单。
SELECT
*
FROM
订单
LEFT JOIN 订单明细 ON 订单.订单ID = 订单明细.订单ID
LEFT JOIN 产品 ON 产品.产品ID = `订单明细`.产品ID
LEFT JOIN 类别 ON `类别`.类别ID = `产品`.类别ID
WHERE
类别.类别名称 = '海鲜'
ORDER BY
订单明细.单价 * 订单明细.数量 * (1 - `订单明细`.折扣) DESC
LIMIT 1;
16. 按季度统计销售量
SELECT
ANY_VALUE(
CONCAT(
YEAR (`订单`.订购日期),
'年第',
FLOOR(
(
DATE_FORMAT(`订单`.订购日期, '%m') + 2
) / 3
),
'季度'
)
) AS 季度,
ANY_VALUE(
ROUND(
SUM(
订单明细.单价 * 订单明细.数量 * (1 - 订单明细.折扣)
),
0
)
) AS 销售额
FROM
订单
LEFT JOIN 订单明细 ON `订单`.订单ID = `订单明细`.订单ID
GROUP BY
CONCAT(
DATE_FORMAT(`订单`.订购日期, '%Y'),
FLOOR(
(
DATE_FORMAT(`订单`.订购日期, '%m') + 2
) / 3
)
);
17. 查出订单总额超出5000的所有订单，客户名称，客户所在地区。
SELECT
订单.订单ID,
ANY_VALUE (
GROUP_CONCAT(`产品`.产品名称)
) AS 订单产品,
ANY_VALUE (
ROUND(
SUM(
订单明细.单价 * 订单明细.数量 * (1 - `订单明细`.折扣)
)
)
) AS 订单额,
ANY_VALUE (`客户`.公司名称) AS 客户名称,
ANY_VALUE (客户.地区) AS 客户所在地区
FROM
订单
LEFT JOIN 订单明细 ON 订单明细.订单ID = 订单.订单ID
LEFT JOIN 产品 ON `产品`.产品ID = `订单明细`.产品ID
LEFT JOIN 客户 ON `客户`.客户ID = 订单.客户ID
GROUP BY `订单`.订单ID
HAVING
ROUND(
SUM(
订单明细.单价 * 订单明细.数量 * (1 - `订单明细`.折扣)
)
)
> 5000
18. 查询哪些产品的年度销售额低于2000
SELECT
ANY_VALUE (`产品`.产品名称) 产品名称,
ANY_VALUE (YEAR(订单.订购日期)) 年度,
ANY_VALUE (
ROUND(
SUM(
订单明细.单价 * 订单明细.数量 * (1 - 订单明细.折扣)
)
)
) 销售额
FROM
产品
LEFT JOIN 订单明细 ON `产品`.产品ID = `订单明细`.产品ID
LEFT JOIN 订单 ON 订单明细.订单ID = 订单.订单ID
GROUP BY
`产品`.产品名称,
YEAR (`订单`.订购日期)
HAVING
SUM(
`订单明细`.单价 * `订单明细`.数量 * (1 - 订单明细.折扣)
) < 2000;
19. 查询所有订单ID开头为102的订单
SELECT
*
FROM
订单
WHERE
LEFT (订单ID, 3) = '102';
20. 查询所有“中硕贸易”，“学仁贸易”，“正人资源”，“中通”客户的订单，（要求使用in函数）
SELECT
*
FROM
订单
LEFT JOIN 客户 ON 客户.客户ID = 订单.客户ID
WHERE
客户.公司名称 IN (
'中硕贸易',
'学仁贸易',
'正人资源',
'中通'
);
-----以上考试时间一个半小时
21. 查询所有订单中月份不是单数的订单。
SELECT
*
FROM
订单
WHERE
MOD (MONTH(订购日期), 2) = 0;
22. 分别各写一个查询，得到订单中折扣为15%，20%的所有订单，并将两个查询再组成一个。
SELECT
`订单`.*
FROM
订单
LEFT JOIN 订单明细 ON 订单明细.订单ID = `订单`.订单ID
WHERE
TRIM(`订单明细`.折扣) = 0.15
UNION ALL
SELECT
`订单`.*
FROM
订单
LEFT JOIN 订单明细 ON 订单明细.订单ID = `订单`.订单ID
WHERE
TRIM(`订单明细`.折扣) = 0.20;
23. 找出在入职时已超过30岁的所有员工信息
SELECT
*
FROM
雇员
WHERE
FLOOR(
(
DATEDIFF(雇用日期, 出生日期) / 365.2422
)
) > 30;
SELECT
*
FROM
雇员
WHERE
TIMESTAMPDIFF(
YEAR,
出生日期,
雇用日期
) > 30;
24. 找出所有单价大于30的产品(附加要求，产品类别，供应商作为参数，当产品类别和供应商都为空的时候，nofilter)
SELECT
`类别`.类别名称,
`供应商`.公司名称,
`产品`.*
FROM
产品
LEFT JOIN 类别 ON `产品`.类别ID = `类别`.类别ID
LEFT JOIN 供应商 ON `产品`.供应商ID = `供应商`.供应商ID
WHERE
trim(`产品`.单价) > 30 AND `类别`.类别名称 IS NOT NULL AND `供应商`.公司名称 IS NOT NULL;
25. 查询所有库存产品的总额，并按照总额排序
SELECT
产品.*, 产品.单价 * 产品.库存量 AS 总额
FROM
产品
ORDER BY
总额 DESC;
26. 检索出职务为销售代表的所有订单中，每笔订单总额低于2000的订单明细，以及相关供应商名称。
SELECT
销售.订单ID,
销售.名字,
销售.销售金额,
供应商.公司名称,
产品.产品名称
FROM
(
SELECT
订单.订单ID,
ANY_VALUE(CONCAT(
雇员.姓氏,
雇员.名字
)) AS 名字,
SUM(
订单明细.单价 * 订单明细.数量 * (1 - 订单明细.折扣)
) 销售金额
FROM
雇员
LEFT JOIN 订单 ON (
订单.雇员ID = 雇员.雇员ID
)
LEFT JOIN 订单明细 ON (
订单.订单ID = 订单明细.订单ID
)
WHERE
雇员.职务 = '销售代表'
GROUP BY
订单.订单ID
HAVING
SUM(
订单明细.单价 * 订单明细.数量 * (1 - 订单明细.折扣)
) < 2000
) 销售
LEFT JOIN 订单明细 ON (
订单明细.订单id = 销售.订单ID
)
LEFT JOIN 产品 ON (
产品.产品ID = 订单明细.产品ID
)
LEFT JOIN 供应商 ON (
供应商.供应商id = 产品.供应商ID
);
27. 检索出向艾德高科技提供产品的供应商所在的城市。
SELECT
`供应商`.城市,
`客户`.公司名称,
产品.产品名称
FROM
订单
LEFT JOIN 客户 ON `客户`.客户ID = 订单.客户ID
LEFT JOIN 订单明细 ON 订单明细.订单ID = `订单`.订单ID
LEFT JOIN 产品 ON `产品`.产品ID = 订单明细.产品ID
LEFT JOIN 供应商 ON `供应商`.供应商ID = 产品.供应商ID
WHERE
`客户`.公司名称 = '艾德高科技';
28. 计算每一笔订单的发货期（从订购到发货），运货期（从发货到到货）的时常，并按照发货期从长到短的顺序进行排序。
SELECT
`订单`.*, DATEDIFF(
`订单`.发货日期,
`订单`.订购日期
) AS 发货时间,
DATEDIFF(
`订单`.到货日期,
`订单`.发货日期
) AS 运货时间
FROM
订单
ORDER BY
发货时间 DESC;
29. 将产品表和运货商两个无关的表整合为一个表
SELECT
`产品`.*, `运货商`.*
FROM
产品
LEFT JOIN 订单明细 ON 订单明细.产品ID = `产品`.产品ID
LEFT JOIN 订单 ON `订单`.订单ID = 订单明细.订单ID
LEFT JOIN 运货商 ON `运货商`.运货商ID = `订单`.运货商;
30. 获取在北京工作并向福星制衣厂股份有限公司发送过订单的职工名称。
SELECT DISTINCT
CONCAT(
`雇员`.姓氏,
`雇员`.名字
) AS 姓名
FROM
雇员
RIGHT JOIN 订单 ON `订单`.雇员ID = `雇员`.雇员ID
RIGHT JOIN 客户 ON `客户`.客户ID = `订单`.客户ID
WHERE
`客户`.公司名称 = '福星制衣厂股份有限公司'
AND `雇员`.城市 = '北京';
|
