帆软sql练习题二巩固
SQL巩固测试题
注意：首先声明下我写的可能是当时我用我自己的sql写法，我也是参照网上的一些博主的模板写的，特别是后面的十句sql写法，可能有些sql跟你自己的想法有些偏差，所以我建议还是自己多写写，不会了可以看看。
- 找出供应商名称，所在城市
SELECT * FROM `供应商`
2.找出华北地区能够供应海鲜的所有供应商列表。
SELECT
g.`公司名称`,
l.`类别名称`,
g.`地区`
FROM
`供应商` g,
类别 l,
产品 c
WHERE
g.`供应商ID` = c.`供应商ID`
AND c.`类别ID` = l.`类别ID`
AND g.`地区` = '华北'
AND l.`类别名称` = '海鲜'
3.找出订单销售额前五的订单是经由哪家运货商运送的。
SELECT
d.`订单ID`,
y.`公司名称`,
m.`单价` * m.`数量` * (1 - m.`折扣`) AS 销售额
FROM
`订单` d,
`订单明细` m,
`运货商` y
WHERE
d.`订单ID` = m.`订单ID`
AND d.`运货商` = y.`运货商ID`
ORDER BY
销售额 DESC
LIMIT 5
4.找出按箱包装的产品名称。
SELECT
c.`产品名称`,
c.`单位数量`
FROM
`产品` c
WHERE
c.`单位数量` LIKE '每箱%'
5.找出重庆的供应商能够供应的所有产品列表。
SELECT
g.`公司名称`,
c.`产品名称`
FROM
`供应商` g,
`产品` c
WHERE
g.`供应商ID` = c.`供应商ID`
AND `城市` = '重庆'
6.找出雇员郑建杰所有的订单并根据订单销售额排序。
SELECT
d.`订单ID`,
g.`姓氏`,
g.`名字`,
m.`单价` * m.`数量` * (1 - m.`折扣`) AS 销售额
FROM
`订单` d,
`订单明细` m,
`雇员` g
WHERE
d.`订单ID` = m.`订单ID`
AND d.`雇员ID` = g.`雇员ID`
AND g.`姓氏` = '郑'
AND g.`名字` = '建杰'
ORDER BY
销售额 DESC
7.找出订单10284的所有产品以及订单金额，运货商。
SELECT
d.`订单ID`,
c.`产品名称`,
y.`公司名称`,
m.`单价` * m.`数量` * (1 - m.`折扣`) AS 销售额
FROM
`订单` d,
`订单明细` m,
`运货商` y,
`产品` c
WHERE
d.`订单ID` = m.`订单ID`
AND d.`运货商` = y.`运货商ID`
AND m.`产品ID` = c.`产品ID`
AND d.`订单ID` = '10248'
8.建立产品与订单的关联。
SELECT * FROM `订单` d JOIN `订单明细` m ON d.`订单ID` = m.`订单ID` JOIN `产品` c ON m.`产品ID` = c.`产品ID`
10.计算销量前10位的订单明细，结果集返回订单ID，订单日期，公司名称，发货日期，销售额，并排序
SELECT
d.`订单ID`,
d.`订购日期`,
k.`公司名称`,
d.`发货日期`,
m.`单价` * m.`数量` * (1 - m.`折扣`) AS 销售额,
m.`数量`
FROM
`订单` d
LEFT JOIN `订单明细` m ON d.`订单ID` = m.`订单ID`
LEFT JOIN `产品` c ON m.`产品ID` = c.`产品ID`
LEFT JOIN `客户` k ON k.`客户ID` = d.`客户ID`
ORDER BY
m.`数量` DESC
LIMIT 10
11.按年度统计销售额
SELECT d.`订单ID`,d.`到货日期`,m.`单价`*m.`数量`*(1-m.`折扣`) AS 销售额 FROM `订单` d,`订单明细` m WHERE d.`订单ID` = m.`订单ID`
GROUP BY YEAR(d.`到货日期` )
12.查询供应商中能够供应的产品样数最多的供应商。
SELECT
g.`公司名称`,
COUNT(c.`产品ID`) AS 数量
FROM
`供应商` g,
`产品` c
WHERE
g.`供应商ID` = c.`供应商ID`
GROUP BY
g.`公司名称`
ORDER BY
数量 DESC
LIMIT 1
13.查询产品类别中包含的产品数量最多的类别。
SELECT
l.`类别名称`,
COUNT(c.`产品ID`) AS 类别数量
FROM
`产品` c,
`类别` l
WHERE
c.`类别ID` = l.`类别ID`
GROUP BY
l.`类别名称`
ORDER BY
类别数量 DESC
LIMIT 1
14.找出所有的订单中经由哪家运货商运货次数最多。
SELECT
d.`订单ID`,
y.`公司名称`,
COUNT(d.`订单ID`) AS 次数
FROM
`订单` d
LEFT JOIN `运货商` y ON d.`运货商` = y.`运货商ID`
GROUP BY
y.`公司名称`
ORDER BY
次数 DESC
LIMIT 1
15.按类别，产品分组，统计销售额。
SELECT
l.`类别名称`,
c.`产品名称`,
SUM(
m.`单价` * m.`数量` * (1 - m.`折扣`)
) AS 数量
FROM
`产品` c,
`类别` l,
`订单明细` m
WHERE
c.`产品ID` = m.`产品ID`
AND c.`类别ID` = l.`类别ID`
GROUP BY
c.`产品名称`,
l.`类别名称`
16.查询海鲜类别最大的一笔订单。
SELECT
d.`订单ID`,
c.`产品名称`,
SUM(
d.`单价` * d.`数量` * (1 - d.`折扣`)
) AS 销售额
FROM
`订单明细` d,
`类别` l,
`产品` c
WHERE
d.`产品ID` = c.`产品ID`
AND c.`类别ID` = l.`类别ID`
AND l.`类别名称` = '海鲜'
GROUP BY
d.`订单ID`
ORDER BY
销售额 DESC
LIMIT 1
17.按季度统计销售量
SELECT
YEAR (d.订购日期) AS 年度,
QUARTER (d.订购日期) AS 季度,
SUM(m.`数量`) AS 销售数量
FROM
`订单` d,
`订单明细` m
WHERE
d.`订单ID` = m.`订单ID`
GROUP BY
年度,
季度
18.查出订单总额超出5000的所有订单，客户名称，客户所在地区。
SELECT
d.`订单ID`,
k.`公司名称`,
k.`地区`
FROM
`订单` d,
`订单明细` m,
`客户` k
WHERE
d.`订单ID` = m.`订单ID`
AND d.`客户ID` = k.`客户ID`
GROUP BY
d.`订单ID`,
k.`公司名称`,
k.`地区`
HAVING
SUM(
m.`单价` * m.`数量` * (1 - m.`折扣`)
) > 5000
19.查询哪些产品的年度销售额低于2000
SELECT
YEAR (d.订购日期) AS 年度,
c.`产品名称`
FROM
`订单` d,
`订单明细` m,
`产品` c
WHERE
d.`订单ID` = m.`订单ID`
AND c.`产品ID` = m.`产品ID`
GROUP BY
c.`产品名称`,
年度
HAVING
SUM(
m.`单价` * m.`数量` * (1 - m.`折扣`)
) < 2000
20.查询所有订单ID开头为102的订单
SELECT d.`订单ID` FROM `订单` d WHERE d.`订单ID` LIKE '102%'
21.查询所有“中硕贸易”，“学仁贸易”，“正人资源”，“中通”客户的订单，（要求使用in函数）
SELECT
d.*,k.`公司名称`
FROM
`订单` d,
`客户` k
WHERE
d.`客户ID` = k.`客户ID`
AND k.`公司名称` IN (
'中硕贸易',
'学仁贸易',
'正人资源'
)
22.查询所有订单中月份不是单数的订单。
SELECT * FROM `订单` WHERE MONTH(`订购日期`) %2 =0
23.分别各写一个查询，得到订单中折扣为15%，20%的所有订单，并将两个查询再组成一个。
SELECT
*
FROM
`订单明细` d
WHERE
FORMAT(d.`折扣`, 2) = 0.15
UNION ALL
SELECT
*
FROM
`订单明细` d
WHERE
FORMAT(d.`折扣`, 2) = 0.2
24.找出在入职时已超过30岁的所有员工信息
SELECT * FROM `雇员` g WHERE TIMESTAMPDIFF(YEAR,g.`出生日期`,g.`雇用日期`) >=30
25.找出所有单价大于30的产品(附加要求，产品类别，供应商作为参数，当产品类别和供应商都为空的时候，nofilter)
这个因为需求的原因不是很理解所以完成了他前面的需求练习用吧
SELECT c.`产品名称`,c.`单价` FROM `产品` c WHERE c.`单价` > 30 ORDER BY c.`单价`
26.查询所有库存产品的总额，并按照总额排序
SELECT
c.`产品名称`,
SUM(c.`单价` * c.`库存量`) AS 产品总额
FROM
`产品` c
GROUP BY
c.`产品名称`
ORDER BY
产品总额 DESC
27.检索出职务为销售代表的所有订单中，每笔订单总额低于2000的订单明细，以及相关供应商名称。
SELECT
m.*, g.`公司名称`
FROM
`订单` d,
`订单明细` m,
产品 c,
`客户` k,
供应商 g
WHERE
d.`订单ID` = m.`订单ID`
AND m.`产品ID` = c.`产品ID`
AND c.`供应商ID` = g.`供应商ID`
AND k.`客户ID` = d.`客户ID`
AND k.`联系人职务` = '销售代表'
GROUP BY
g.`公司名称`
HAVING
SUM(
m.`单价` * m.`数量` * (1 - m.`折扣`)
) < 2000
28.检索出向艾德高科技提供产品的供应商所在的城市。
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.`公司名称` = '艾德高科技'
29.计算每一笔订单的发货期（从订购到发货），运货期（从发货到到货）的时长，并按照发货期从长到短的顺序进行排序。
SELECT
*, TIMESTAMPDIFF(
MINUTE,
订购日期,
发货日期
) AS 发货期,
TIMESTAMPDIFF(
MINUTE,
订购日期,
到货日期
) AS 运货期
FROM
`订单`
ORDER BY
发货期 DESC
30.将产品表和运货商两个无关的表整合为一个表
SELECT
c.*, y.*
FROM
`订单` d,
`订单明细` m,
`产品` c,
`运货商` y
WHERE
d.`订单ID` = m.`订单ID`
AND m.`产品ID` = c.`产品ID`
AND d.`运货商` = y.`运货商ID`
31.获取在北京工作并向福星制衣厂股份有限公司发送过订单的职工名称。
SELECT CONCAT(E.姓氏,E.名字) as 职工名称 FROM 订单 AS O LEFT JOIN 雇员 as E on O.雇员ID=E.雇员ID
WHERE 客户ID
in
(SELECT 客户ID FROM `客户`
WHERE 公司名称='福星制衣厂股份有限公司')
AND E.城市='北京'
