帆软社区：零基础快速自学SQL练习题答案

创建表和插入数据的SQL，

请查看帆软社区任务详情：https://bbs.fanruan.com/task-94.html

SQL自学练习题（帆软社区版）.zip

问题和答案：

SELECT * FROM 产品
-- 查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、客户ID和雇员ID等字段的值
SELECT 订购日期,订单ID,客户ID,雇员ID
FROM `订单` 
WHERE 订购日期 BETWEEN '1996-07-01' AND '1996-07-15'
-- 查询供应商的ID、公司名称、地区、城市和电话字段的值。条件是“地区等于华北”并且“联系人头衔等于销售代表”。 
SELECT 供应商ID,公司名称,地区,城市,电话
FROM `供应商`
WHERE 地区 = '华北' AND 联系人职务 = '销售代表'
-- 查询供应商的ID、公司名称、地区、城市和电话字段的值。其中的一些供应商位于华东或华南地区，另外一些供应商所在的城市是天津
 OR 城市 = '天津'
-- 查询位于“华东”或“华南”地区的供应商的ID、公司名称、地区、城市和电话字段的值
SELECT 供应商ID,公司名称,地区,城市,电话
FROM `供应商`
WHERE 地区 = '华北' OR 地区 = '华南'
-- 查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、相应订单的客户公司名称、负责订单的雇员的姓氏和名字等字段的值，并将查询结果按雇员的“姓氏”和“名字”字段的升序排列，“姓氏”和“名字”值相同的记录按“订单 ID”的降序排列
SELECT A.订购日期,A.订单ID,B.公司名称,C.姓氏,C.名字
FROM 
`订单` A
LEFT JOIN `客户` B ON A.客户ID = B.客户ID
LEFT JOIN `雇员` C ON A.雇员ID = C.雇员ID
WHERE 
A.订购日期 BETWEEN '1996-07-01' AND '1996-07-15'
-- 查询“10248”和“10254”号订单的订单ID、运货商的公司名称、订单上所订购的产品的名称
SELECT A.订单ID,B.公司名称,D.产品名称
FROM
`订单` A
LEFT JOIN `运货商` B ON A.运货商 = B.运货商ID
LEFT JOIN `订单明细` C ON A.订单ID = C.订单ID
LEFT JOIN `产品` D ON C.产品ID = D.产品ID
WHERE
A.订单ID = 10248 OR A.订单ID = 10254
-- 查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称、数量、单价和折扣
SELECT A.订单ID,C.产品名称,B.数量,B.单价,B.折扣
FROM
`订单` A
LEFT JOIN `订单明细` B ON A.订单ID = B.订单ID
LEFT JOIN `产品` C ON B.产品ID = C.产品ID
WHERE
A.订单ID = 10248 OR A.订单ID = 10254
-- 查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称及其销售金额
SELECT A.订单ID,C.产品名称,(B.单价*B.数量) AS '销售金额'
FROM
`订单` A
LEFT JOIN `订单明细` B ON A.订单ID = B.订单ID
LEFT JOIN `产品` C ON B.产品ID = C.产品ID
WHERE
A.订单ID = 10248 OR A.订单ID = 10254
-- 查询所有运货商的公司名称和电话
SELECT 公司名称,电话
FROM
`供应商`
-- 查询所有客户的公司名称、电话、传真、地址、联系人姓名和联系人头衔
SELECT 公司名称,电话,传真,地址,联系人姓名,联系人职务
FROM
`客户`
-- 查询单价介于10至30元的所有产品的产品ID、产品名称和库存量
SELECT 产品ID,产品名称,库存量
FROM
`产品`
WHERE 单价 BETWEEN 10 AND 30
-- 查询单价大于20元的所有产品的产品名称、单价以及供应商的公司名称、电话
SELECT A.产品名称,A.单价,B.公司名称,B.电话
FROM
`产品` A
LEFT JOIN `供应商` B ON A.供应商ID = B.供应商ID
WHERE
A.单价 > 20
-- 查询上海和北京的客户在1996年订购的所有订单的订单ID、所订购的产品名称和数量
SELECT A.订单ID,C.产品名称,B.数量
FROM
`订单` A
LEFT JOIN `订单明细` B ON A.订单ID = B.订单ID
LEFT JOIN `产品` C ON B.产品ID = C.产品ID
LEFT JOIN `客户` D ON A.客户ID = D.客户ID
WHERE
(D.城市 = '上海' OR D.城市 = '北京') AND YEAR(A.订购日期) = 1996
-- 查询华北客户的每份订单的订单ID、产品名称和销售金额
	SELECT A.订单ID,GROUP_CONCAT(C.产品名称) AS '产品名称',SUM(B.单价*B.数量) AS '销售金额',D.地区
	FROM
	`订单` A
	LEFT JOIN `订单明细` B ON A.订单ID = B.订单ID
	LEFT JOIN `产品` C ON B.产品ID = C.产品ID
	LEFT JOIN `客户` D ON A.客户ID = D.客户ID
	GROUP BY A.订单ID
	HAVING
  D.地区 = '华北'
-- 按运货商公司名称，统计1997年由各个运货商承运的订单的总数量
SELECT A.公司名称,SUM(C.数量) AS 总数量,B.发货日期
FROM
`运货商` A
LEFT JOIN `订单` B ON A.运货商ID = B.运货商
LEFT JOIN `订单明细` C ON B.订单ID = C.订单ID
GROUP BY A.公司名称,B.发货日期
HAVING
YEAR(B.发货日期) = 1996
-- 统计1997年上半年的每份订单上所订购的产品的总数量
SELECT A.产品名称,SUM(B.数量) AS '总数量',C.订购日期
FROM
`产品` A
LEFT JOIN `订单明细` B ON A.产品ID = B.产品ID
LEFT JOIN `订单` C ON B.订单ID = C.订单ID
GROUP BY A.产品名称,C.订购日期
HAVING
YEAR(C.订购日期) = '1997' AND (MONTH(C.订购日期) BETWEEN 1 AND 6)
-- 统计各类产品的平均价格
SELECT 类别ID,AVG(单价) AS '均价'
FROM
`产品`
GROUP BY 类别ID
-- 统计各地区客户的总数量
SELECT 地区,COUNT(客户ID) AS '总数量'
FROM
`客户`
GROUP BY 地区
-- -------------------------第二部分-------------------------------------
-- 找出供应商名称，所在城市
SELECT 公司名称,城市
FROM
`供应商`
-- 找出华北地区能够供应海鲜的所有供应商列表。
SELECT *
FROM 
`供应商` A
INNER JOIN `产品` B ON A.供应商ID = B.供应商ID
WHERE B.类别ID = 8
-- 找出订单销售额前五的订单是经由哪家运货商运送的。
SELECT A.公司名称,SUM(C.单价*C.数量) AS '销售额'
FROM
`运货商` A
INNER JOIN `订单` B ON A.运货商ID = B.运货商
LEFT JOIN `订单明细` C ON B.订单ID = C.订单ID
GROUP BY A.公司名称
ORDER BY 销售额 DESC
LIMIT 5
-- 找出按箱包装的产品名称。
SELECT 产品名称,单位数量
FROM 
`产品`
WHERE SUBSTRING(单位数量,2,1) = '箱'
-- 找出重庆的供应商能够供应的所有产品列表。
SELECT *
FROM
`产品` A
LEFT JOIN `供应商` B ON A.供应商ID = B.供应商ID
WHERE B.城市 = '重庆'
-- 6.	找出雇员郑建杰所有的订单并根据订单销售额排序。
SELECT * ,(C.单价*C.数量) AS '销售额'
FROM
`订单` A
LEFT JOIN `雇员` B ON A.雇员ID = B.雇员ID
LEFT JOIN `订单明细` C ON A.订单ID = C.订单ID
WHERE 
B.姓氏 = '郑' AND B.名字 = '建杰'
ORDER BY 销售额
-- 7.	找出订单10284的所有产品以及订单金额，运货商。
SELECT *
FROM
`订单` A
LEFT JOIN `订单明细` B ON A.订单ID = B.订单ID
LEFT JOIN `运货商` C ON A.运货商 = C.运货商ID
WHERE
A.订单ID = 10284
-- 9.	计算销量前10位的订单明细，结果集返回订单ID，订单日期，公司名称，发货日期，销售额，并排序
SELECT A.订单ID,A.订购日期,C.公司名称,A.发货日期,(B.单价*B.数量)AS '销售额'
FROM 
`订单` A
LEFT JOIN `订单明细` B ON A.订单ID = B.订单ID
LEFT JOIN `客户` C ON A.客户ID = C.客户ID
ORDER BY 销售额 DESC
LIMIT 10
-- 10.	按年度统计销售额
SELECT YEAR(A.订购日期) AS '订购年度',SUM(B.单价*B.数量) AS '销售额'
FROM 
`订单` A
LEFT JOIN 订单明细 B ON A.订单ID = B.订单ID
GROUP BY 订购年度
-- 11.	查询供应商中能够供应的产品样数最多的供应商。
SELECT A.供应商ID,A.公司名称,COUNT(*) AS '产品样数'
FROM
`供应商` A
LEFT JOIN `产品` B ON A.供应商ID = B.供应商ID
GROUP BY A.供应商ID,A.公司名称
ORDER BY 产品样数 DESC
LIMIT 1
-- 12.	查询产品类别中包含的产品数量最多的类别。
SELECT 类别ID,COUNT(*) AS '类别数量'
FROM
`产品`
GROUP BY 类别ID
ORDER BY 类别数量 DESC
LIMIT 1
-- 13.	找出所有的订单中经由哪家运货商运货次数最多。
SELECT B.运货商ID,B.公司名称,COUNT(*) AS '运货次数'
FROM
`订单` A
LEFT JOIN `运货商` B ON A.运货商 = B.运货商ID
GROUP BY B.运货商ID,B.公司名称
ORDER BY 运货次数 DESC
LIMIT 1
-- 14.	按类别，产品分组，统计销售额。
SELECT A.类别ID,A.产品名称,SUM(B.单价*B.数量) AS '销售额'
FROM
`产品` A
LEFT JOIN `订单明细` B ON A.产品ID = B.产品ID
GROUP BY A.类别ID,A.产品名称
ORDER BY A.类别ID
-- 15.	查询海鲜类别最大的一笔订单。
SELECT B.订单ID,A.产品名称,(B.单价*B.数量) AS '销售额'
FROM
`产品` A
LEFT JOIN `订单明细` B ON A.产品ID = B.产品ID
WHERE A.类别ID = 8
ORDER BY 销售额 DESC
LIMIT 1
-- 16.	按季度统计销售量
SELECT QUARTER(A.订购日期) AS 季度,SUM(B.单价*B.数量) AS '销售额'
FROM
`订单` A
LEFT JOIN `订单明细` B ON A.订单ID = B.订单ID
GROUP BY QUARTER(A.订购日期)
ORDER BY QUARTER(A.订购日期)
-- 17.	查出订单总额超出5000的所有订单，客户名称，客户所在地区。
SELECT A.订单ID, SUM(B.单价*B.数量) AS '订单总额',C.公司名称,C.地区
FROM 
`订单` A
LEFT JOIN `订单明细` B ON A.订单ID = B.订单ID
LEFT JOIN `客户` C ON A.客户ID = C.客户ID
GROUP BY A.订单ID,C.公司名称,C.地区
HAVING 订单总额 >= 5000
ORDER BY 订单总额
-- 18.	查询哪些产品的年度销售额低于2000
SELECT A.产品名称,YEAR(C.订购日期)AS '销售年度' ,SUM(B.单价*B.数量) AS '销售额'
FROM
`产品` A
LEFT JOIN `订单明细` B ON A.产品ID = B.产品ID
LEFT JOIN `订单` C ON B.订单ID = C.订单ID
GROUP BY A.产品名称,销售年度
HAVING 销售额 < 2000
ORDER BY 销售额 DESC
-- 19.	查询所有订单ID开头为102的订单
SELECT  *
FROM
`订单`
WHERE LEFT(订单ID,3) = 102

SELECT * 
FROM
`订单`
WHERE SUBSTRING(订单ID,1,3) = 102
-- 20.	查询所有“中硕贸易”，“学仁贸易”，“正人资源”，“中通”客户的订单，（要求使用in函数）
SELECT * 
FROM
`订单` A
LEFT JOIN `客户` B ON A.客户ID = B.客户ID
WHERE 公司名称 IN ('中硕贸易','学仁贸易','正人资源','中通')
-- 21.	查询所有订单中月份不是单数的订单。
SELECT *
FROM 
`订单`
WHERE MONTH(订购日期)%2 = 0
-- 22.	分别各写一个查询，得到订单中折扣为15%，20%的所有订单，并将两个查询再组成一个。
SELECT *
FROM
`订单` A1
LEFT JOIN `订单明细` B1 ON A1.订单ID = B1.订单ID
WHERE
CAST(B1.折扣 AS CHAR) = 0.15
UNION ALL
SELECT *
FROM
`订单` A2
LEFT JOIN `订单明细` B2 ON A2.订单ID = B2.订单ID
WHERE
CAST(B2.折扣 AS CHAR) = 0.20
-- 23.	找出在入职时已超过30岁的所有员工信息
SELECT *
FROM
`雇员` 
WHERE
YEAR(雇用日期) - YEAR(出生日期) >30 
OR(YEAR(雇用日期) - YEAR(出生日期) =30 AND MONTH(雇用日期) - MONTH(出生日期) > 0)
OR(YEAR(雇用日期) - YEAR(出生日期) =30 AND MONTH(雇用日期) - MONTH(出生日期) = 0 AND DAY(雇用日期) - DAY(出生日期) > 0)
-- 24.	找出所有单价大于30的产品(附加要求，产品类别，供应商作为参数，当产品类别和供应商都为空的时候，nofilter)

-- 25.	查询所有库存产品的总额，并按照总额排序
SELECT 产品名称,(单价*库存量) AS 库存总额
FROM 
`产品`
ORDER BY 库存总额
-- 26.	检索出职务为销售代表的所有订单中，每笔订单总额低于2000的订单明细，以及相关供应商名称。
SELECT B.订单ID,SUM(C.单价*C.数量)AS '销售额',A.雇员ID,A.姓氏,A.名字,GROUP_CONCAT(E.公司名称) AS '相关供应商'
FROM 
`雇员` A
LEFT JOIN `订单` B ON A.雇员ID = B.雇员ID
LEFT JOIN `订单明细` C ON B.订单ID = C.订单ID
LEFT JOIN `产品` D ON C.产品ID = D.产品ID
LEFT JOIN `供应商` E ON D.供应商ID = E.供应商ID
GROUP BY B.订单ID,A.雇员ID,A.姓氏,A.名字
HAVING 销售额 < 2000
ORDER BY 销售额 DESC
-- 27.	检索出向艾德高科技提供产品的供应商所在的城市。
SELECT A1.客户公司名称,GROUP_CONCAT(A1.城市) AS 供应商城市
FROM
(SELECT  DISTINCT
A.公司名称 AS '客户公司名称',E.城市 
FROM 
`客户` A
LEFT JOIN `订单` B ON A.客户ID = B.客户ID 
LEFT JOIN `订单明细` C ON B.订单ID = C.订单ID
LEFT JOIN `产品` D ON C.产品ID = D.产品ID
LEFT JOIN `供应商` E ON D.供应商ID = E.供应商ID
WHERE A.公司名称 = '艾德高科技') A1
GROUP BY A1.客户公司名称
-- 28.	计算每一笔订单的发货期（从订购到发货），运货期（从发货到到货）的时常，并按照发货期从长到短的顺序进行排序。
SELECT 订单ID,IFNULL(DATEDIFF(发货日期,订购日期),'未发货') AS '发货期',IFNULL(DATEDIFF(到货日期,发货日期),'未到货') AS '运货期'
FROM 
`订单`
ORDER BY 发货期 DESC
-- 29.	将产品表和运货商两个无关的表整合为一个表
SELECT *
FROM
`产品` A
LEFT JOIN `订单明细` B ON A.产品ID = B.产品ID 
LEFT JOIN `订单` C ON B.订单ID = C.订单ID
LEFT JOIN `运货商` D ON C.运货商 = D.运货商ID
UNION 
SELECT *
FROM
`产品` A
LEFT JOIN `订单明细` B ON A.产品ID = B.产品ID 
LEFT JOIN `订单` C ON B.订单ID = C.订单ID
RIGHT JOIN `运货商` D ON C.运货商 = D.运货商ID
-- 30.	获取在北京工作并向福星制衣厂股份有限公司发送过订单的职工名称。
SELECT B.姓氏,B.名字
FROM 
`订单` A
LEFT JOIN `雇员` B ON A.雇员ID = B.雇员ID
LEFT JOIN `客户` C ON A.客户ID = C.客户ID
WHERE 
B.城市 = '北京' AND
C.公司名称 = '福星制衣厂股份有限公司'