帆软SQL巩固测试题 练习结果
近日因工作需要，需要系统化的训练一下自己的SQL水平，本人属于野路子，并没有得到专业的训练，都靠日常工作过程中，摸索出来。各位不要见怪。相关的原始表可以参考帆软的论坛。
习题答案通过SQL查询分析器输出 查询的视图，将查询语句和视图的截图贴在相应习题的下方。
- 找出供应商名称，所在城市
SELECT 公司名称,城市 FROM `供应商`
- 找出华北地区能够供应海鲜的所有供应商列表。
SELECT
公司名称,城市
FROM
供应商 WHERE 供应商id IN (
SELECT
供应商id
FROM
产品 AS p
WHERE
p.类别id IN ( SELECT 类别id FROM 类别 WHERE 类别名称 = '海鲜' )
)
and 地区='华北'
- 找出订单销售额前五的订单是经由哪家运货商运送的。
SELECT
运货商id,公司名称
FROM
运货商 WHERE 运货商id in (
SELECT 运货商 FROM 订单 AS O
WHERE O.订单ID in (
SELECT T.订单ID
FROM (SELECT 订单ID FROM `订单明细`
ORDER BY 单价*数量*(1-折扣) DESC
LIMIT 5) as T
)
)
- 找出按箱包装的产品名称。
SELECT 产品名称 from 产品
where 单位数量 like '%箱%'
- 找出重庆的供应商能够供应的所有产品列表。
WHERE 供应商id in(
SELECT
供应商id
FROM
供应商 WHERE 城市='重庆')
- 找出雇员郑建杰所有的订单并根据订单销售额排序。
SELECT O.*,D.单价*D.数量*(1-折扣) as 订单金额 FROM 订单 AS O left join
`订单明细` AS D ON O.订单ID=D.订单ID
WHERE O.雇员ID in (
SELECT 雇员ID from
雇员
WHERE CONCAT(姓氏,名字)='郑建杰'
)
ORDER BY 订单金额 DESC
- 找出订单10284的所有产品以及订单金额，运货商。
-- 7. 找出订单10284的所有产品以及订单金额，运货商。
SELECT D.*,D.单价*D.数量*(1-折扣) as 订单金额,O.运货商,C.公司名称 FROM 订单明细 as D
left join 订单 AS O ON O.订单ID=D.订单ID
left join 运货商 AS C ON O.运货商=C.运货商id
WHERE D.订单ID=10284
-
建立产品与订单的关联。略
-
计算销量前10位的订单明细，结果集返回订单ID，订单日期，公司名称，发货日期，销售额，并排序
-
按年度统计销售额
-- 10. 按年度统计销售额
SELECT LEFT( O.`订购日期`, 4 ) as 年份, ROUND(sum( D.单价 * D.数量 *( 1-折扣 )),2) AS 年度销售总额
FROM 订单 AS O LEFT JOIN `订单明细` AS D ON O.订单ID = D.订单ID
GROUP BY LEFT(O.`订购日期`, 4)
年份 年度销售总额
1996 208083.97
1997 617085.20
1998 440674.57
------以上开始时间一个半小时
11. 查询供应商中能够供应的产品样数最多的供应商。
– 11. 查询供应商中能够供应的产品样数最多的供应商。
SELECT S.`公司名称` ,P.供应商ID,count(1) as 供应量 FROM `产品` P LEFT JOIN `供应商` S ON P.`供应商ID`=S.`供应商ID`
GROUP BY `供应商ID`
ORDER BY 供应量 desc
- 查询产品类别中包含的产品数量最多的类别。
SELECT C.`类别名称` ,P.`产品ID`,count(1) as 供应量 FROM `产品` P LEFT JOIN `类别` C ON P.`类别ID`=C.`类别ID`
GROUP BY P.`类别ID`
ORDER BY 供应量 desc
类别名称 产品ID 供应量
点心 16 13
饮料 1 12
海鲜 10 12
- 找出所有的订单中经由哪家运货商运货次数最多。
SELECT P.运货商,C.`公司名称`,COUNT(*) as 运货量 from 订单 P LEFT JOIN 运货商 C ON P.运货商=C.`运货商ID`
GROUP BY P.运货商
ORDER BY 运货量 desc
运货商 公司名称 运货量
2 统一包裹 326
3 联邦货运 255
1 急速快递 249
- 按类别，产品分组，统计销售额。
类别名称 类别ID 销售额
饮料 1 267892.18
调味品 2 105148.28
点心 3 168244.72
日用品 4 234339.28
谷类/麦片 5 95782.59
肉/家禽 6 163190.36
特制品 7 99984.58
海鲜 8 131261.74
SELECT C.`类别名称`,P.类别ID,ROUND(sum(T.年度销售总额),2) 销售额 from (
SELECT `产品ID`,sum( D.单价 * D.数量 *( 1-折扣 )) AS 年度销售总额
FROM
订单 AS O
LEFT JOIN `订单明细` AS D ON O.订单ID = D.订单ID
GROUP BY
产品ID) T LEFT JOIN `产品` P ON T.`产品ID`=P.`产品ID`
LEFT JOIN `类别` C ON P.`类别ID`=C.`类别ID`
GROUP BY P.类别ID
- 查询海鲜类别最大的一笔订单。
SELECT * FROM 订单
WHERE 订单ID in (SELECT T.订单ID FROM (SELECT 订单ID FROM 订单明细
where 产品ID in (
SELECT 产品ID FROM 产品
WHERE 类别ID IN (SELECT 类别ID from 类别 WHERE 类别名称=‘海鲜’))
ORDER BY 单价数量(1-折扣) DESC
LIMIT 1) T)
- 按季度统计销售量
SELECT QUARTER( O.`订购日期`) as 季度, ROUND(sum( D.单价 * D.数量 *( 1-折扣 )),2) AS 季度销售总额
FROM
订单 AS O
LEFT JOIN `订单明细` AS D ON O.订单ID = D.订单ID
GROUP BY
QUARTER( O.`订购日期`)
季度 年度销售总额
1 436831.18
2 285309.36
3 233666.34
4 310036.86
- 查出订单总额超出5000的所有订单，客户名称，客户所在地区。
SELECT 公司名称,联系人姓名,地区 from 客户
WHERE `客户ID` IN(
SELECT 客户ID FROM 订单
WHERE `订单ID` in (
SELECT 订单ID FROM `订单明细` D
where (D.单价* D.数量 *( 1-折扣 ) >5000)
) )
公司名称 联系人姓名 地区
正人资源 谢小姐 华南
仪和贸易 王先生 华北
实翼 谢小姐 华东
师大贸易 苏先生 华北
永业房屋 谢丽秋 西南
华科 吴小姐 东北
顶上系统 方先生 华东
留学服务中心 赵小姐 华北
高上补习班 徐先生 华北
学仁贸易 余小姐 华东
大钰贸易 胡继尧 西南
百达电子 徐文彬 华北
椅天文化事业 方先生 华东
- 查询哪些产品的年度销售额低于2000
产品名称 销售额 年份
苹果汁 518.40 1996
苹果汁 144.00 1997
苹果汁 54.00 1998
牛奶 304.00 1996
牛奶 912.00 1997
牛奶 182.40 1998
蕃茄酱 240.00 1996
蕃茄酱 400.00 1997
蕃茄酱 490.00 1998
盐 352.00 1996
盐 225.28 1997
盐 462.00 1998
麻油 884.00 1996
麻油 288.22 1997
麻油 640.50 1998
酱油 600.00 1996
酱油 1750.00 1997
酱油 150.00 1998
海鲜粉 360.00 1996
海鲜粉 364.80 1997
海鲜粉 540.00 1998
胡椒粉 1680.00 1996
胡椒粉 340.00 1997
胡椒粉 800.00 1998
鸡 1396.80 1997
鸡 291.00 1998
猪肉 62.40 1997
猪肉 234.00 1998
汽水 135.00 1998
巧克力 44.80 1996
巧克力 134.40 1997
巧克力 70.00 1998
棉花糖 1058.25 1996
棉花糖 709.65 1997
棉花糖 1061.82 1998
鸡精 120.00 1997
鸡精 200.00 1998
浓缩咖啡 186.00 1996
。。。 。。。 。。。
浓缩咖啡 155.00 1998
柠檬汁 183.60 1996
柠檬汁 201.60 1997
柠檬汁 765.00 1998
辣椒粉 124.80 1996
辣椒粉 364.00 1997
辣椒粉 260.00 1998
SELECT Y.产品名称,Y.销售额,Y.年份 FROM (
SELECT P.产品名称,ROUND(sum(T.单品销售量),2) 销售额,年份 from (
SELECT D.`产品ID`,D.单价 * D.数量 *( 1-折扣 ) as 单品销售量, LEFT(O.`订购日期`, 4) as 年份
FROM
订单 AS O
LEFT JOIN `订单明细` AS D ON O.订单ID = D.订单ID
GROUP BY D.`产品ID`,LEFT(O.`订购日期`, 4)
) T
LEFT JOIN `产品` P ON T.`产品ID`=P.`产品ID`
GROUP BY P.`产品ID` ,年份) as Y
WHERE Y.销售额<2000
- 查询所有订单ID开头为102的订单
SELECT * from 订单
WHERE left(`订单ID`,3)='102'
- 查询所有“中硕贸易”，“学仁贸易”，“正人资源”，“中通”客户的订单，（要求使用in函数）
-- 20. 查询所有“中硕贸易”，“学仁贸易”，“正人资源”，“中通”客户的订单，（要求使用in函数）
SELECT * from 订单
WHERE 客户ID IN(
SELECT 客户ID from 客户
WHERE `公司名称` in('中硕贸易','学仁贸易','正人资源','中通')
)
-----以上考试时间一个半小时
- 查询所有订单中月份不是单数的订单。
SELECT * from 订单
WHERE MONTH(`订购日期`) %2 =0
- 分别各写一个查询，得到订单中折扣为15%，20%的所有订单，并将两个查询再组成一个。
SELECT * from `订单明细`
WHERE FORMAT(折扣,2) =0.15
UNION all
SELECT * from `订单明细`
WHERE FORMAT(折扣,2) =0.20
- 找出在入职时已超过30岁的所有员工信息
SELECT * from 雇员
WHERE TIMESTAMPDIFF(YEAR,出生日期,雇用日期)>=30
-
找出所有单价大于30的产品(附加要求，产品类别，供应商作为参数，当产品类别和供应商都为空的时候，nofilter)
不理解需求 -
查询所有库存产品的总额，并按照总额排序
SELECT 产品名称,sum(单价*`库存量`) as 总额 FROM 产品
WHERE `库存量`>0
GROUP BY 产品名称
- 检索出职务为销售代表的所有订单中，每笔订单总额低于2000的订单明细，以及相关供应商名称。
SELECT TT.*,S.`公司名称`
FROM 订单明细 TT LEFT JOIN 产品 DD ON TT.产品ID=DD.产品ID LEFT JOIN 供应商 S ON DD.`供应商ID`=S.`供应商ID`
WHERE 订单ID IN (
SELECT 订单ID FROM (
SELECT 订单ID,SUM(D.单价 * D.数量 *( 1-折扣 )) as 订单总额 FROM 订单明细 D
WHERE 订单ID IN (
SELECT 订单ID from 订单
WHERE 雇员ID in
(SELECT 雇员ID from 雇员 WHERE 职务='销售代表'))
GROUP BY 订单ID) T
WHERE T.订单总额 <2000
)
- 检索出向艾德高科技提供产品的供应商所在的城市。
SELECT S.`公司名称`,S.城市 FROM 产品 DD LEFT JOIN 供应商 S ON DD.`供应商ID`=S.`供应商ID`
WHERE 产品ID IN (
SELECT 产品ID from 订单明细
WHERE 订单ID IN (
SELECT 订单ID from 订单
WHERE 客户ID IN(
SELECT 客户ID FROM 客户 WHERE `公司名称`='艾德高科技')
))
- 计算每一笔订单的发货期（从订购到发货），运货期（从发货到到货）的时常，并按照发货期从长到短的顺序进行排序。
SELECT *,TIMESTAMPDIFF(MINUTE,订购日期,发货日期) as 发货期,TIMESTAMPDIFF(MINUTE,订购日期,到货日期) as 运货期 from 订单
ORDER BY 发货期 DESC
-
将产品表和运货商两个无关的表整合为一个表
不理理 -
获取在北京工作并向福星制衣厂股份有限公司发送过订单的职工名称。
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.城市='北京'
5453
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
