SQL查询语句-练习02+答案（含截图）

SQL巩固测试题

1.找出供应商名称，所在城市

select 公司名称,城市 from 供应商

2.找出华北地区能够供应海鲜的所有供应商列表。

select 供应商.公司名称,产品.产品名称,类别.类别名称
from 供应商,产品,类别
where 类别.类别ID=产品.类别ID
and 产品.供应商ID= 供应商.供应商ID
and 地区 = ‘华北’
and 类别.类别名称 = ‘海鲜’

3.找出订单销售额前五的订单是经由哪家运货商运送的。

select 运货商.公司名称,round(订单明细.单价 * 订单明细.数量*(1-订单明细.折扣),2) as 销售额
from 订单 left join 运货商 on 运货商.运货商ID = 订单.运货商
left join 订单明细 on 订单.订单ID = 订单明细.订单ID
order by 销售额 desc
limit 5

4.找出按箱包装的产品名称。

select 产品名称 from 产品
where 单位数量 like '%箱%'

5.找出重庆的供应商能够供应的所有产品列表。

select 产品.产品ID,产品名称,供应商.供应商ID,城市,单位数量,单价,库存量,订购量,再订购量,中止
from 产品 left join 供应商 on 产品.供应商ID = 供应商.供应商ID
where 供应商.城市 ='重庆'

6.找出雇员郑建杰所有的订单并根据订单销售额排序。

select 订单.订单ID,round(订单明细.单价 * 订单明细.数量*(1-订单明细.折扣),2) as 销售额
from 雇员 left join 订单 on 雇员.雇员ID = 订单.雇员ID 
left join 订单明细 on 订单明细.订单ID = 订单.订单ID 
where 雇员.姓氏='郑' and 雇员.名字 ='建杰'
order by 销售额 desc

7.找出订单10284的所有产品以及订单金额，运货商。

select 产品.产品名称,round(订单明细.单价 * 订单明细.数量*(1-订单明细.折扣),2) as 订单金额,运货商.公司名称
from 产品 left join 订单明细 on 订单明细.产品ID= 产品.产品ID
left join 订单 on 订单.订单ID = 订单明细.订单ID
left join 运货商 on 订单.运货商 = 运货商.运货商ID
where 订单.订单ID = 10284

8.建立产品与订单的关联。

select * from 订单 
left join 订单明细 on 订单.订单ID = 订单明细.订单ID
left join 产品 on 产品.产品ID =订单明细.产品ID

9.计算销量前10位的订单明细，结果集返回订单ID，订单日期，公司名称，发货日期，销售额，并排序

select  订单.订单ID,订单.订购日期,客户.公司名称,订单.发货日期,round(订单明细.单价 * 订单明细.数量*(1-订单明细.折扣),2) as 销售额
from 订单 left join 订单明细 on 订单.订单ID =订单明细.订单ID
left join 客户 on 客户.客户ID = 订单.客户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(产品.产品名称) 产品样数
from 供应商, 产品 
where 供应商.供应商ID = 产品.供应商ID
group by 供应商.公司名称
order by 产品样数 desc
limit 1

12.查询产品类别中包含的产品数量最多的类别。

select 类别.类别名称,count(产品.产品名称)as 产品数量
from 产品,类别
where 产品.类别ID = 类别.类别ID
group by 类别.类别ID
order by 产品数量 desc
limit 1

13.找出所有的订单中经由哪家运货商运货次数最多。

select 运货商.公司名称,count(订单.运货商) as 运货次数
from 运货商,订单
where 订单.运货商 = 运货商.运货商ID
group by 运货商.运货商ID
order by 运货次数 desc
limit 1

14.按类别，产品分组，统计销售额。

select 类别名称,产品名称,round(sum(订单明细.单价 * 订单明细.数量*(1-订单明细.折扣)),2) as 销售额
from 产品,类别,订单明细
where 产品.产品ID = 订单明细.产品ID 
and 产品.类别ID = 类别.类别ID
group by 类别名称,产品名称

15.查询海鲜类别最大的一笔订单。

select 订单明细.订单ID,产品名称,类别名称,round(sum(订单明细.单价*订单明细.数量*(1-订单明细.折扣)),2) as 销售额
from 类别 left join 产品 on 类别.类别ID=产品.类别ID
					left join 订单明细 on 订单明细.产品ID=产品.产品ID
where 类别.类别名称 = '海鲜'
group by 订单明细.订单ID
order by 销售额 desc
limit 1

16.按季度统计销售量

select year(订购日期) as 年度,
quarter(订购日期) as 季度 ,
sum(数量) as 销售量
from 订单,订单明细
where 订单.订单ID=订单明细.订单ID
group by 年度,季度

17.查出订单总额超出5000的所有订单，客户名称，客户所在地区。

select 订单明细.订单ID,客户.公司名称,客户.地区,round(订单明细.单价*数量*(1-折扣),2) as 销售额
from 订单,客户,订单明细
where 订单.客户ID = 客户.客户ID and 订单.订单ID= 订单明细.订单ID
-- and (订单明细.单价*数量*(1-折扣)) >5000
group by 订单明细.订单ID,客户.公司名称,客户.地区
having 销售额>5000

18.查询哪些产品的年度销售额低于2000

select 产品.产品ID,产品名称,year(订购日期) as 年度,round(订单明细.单价*数量*(1-折扣),2) as 销售额
from 订单明细 
left join 订单 on 订单.订单ID=订单明细.订单ID
left join 产品 on 产品.产品ID=订单明细.产品ID
group by 产品.产品ID,产品名称
having  销售额 <2000

select 产品.产品ID,产品名称,year(订购日期)as 年度,round(sum(订单明细.单价*数量*(1-折扣)),2) as 销售额
from 订单明细,订单,产品 
where 订单.订单ID=订单明细.订单ID and 产品.产品ID=订单明细.产品ID
group by 产品.产品ID,产品名称
having  销售额 <2000

19.查询所有订单ID开头为102的订单

select * from 订单 where 订单ID like '102%'

20.查询所有“中硕贸易”，“学仁贸易”，“正人资源”，“中通”客户的订单，（要求使用in函数）

select * from 订单,客户
where  客户.客户ID=订单.客户ID
and 客户.公司名称 in ('学仁贸易','正人资源','中通')

-----以上考试时间一个半小时
21.查询所有订单中月份不是单数的订单。

select * 
from 订单
where month(订购日期)%2=0

22.分别各写一个查询，得到订单中折扣为15%，20%的所有订单，并将两个查询再组成一个。

select 折扣,订单.* from 订单,订单明细
where 订单.订单ID =订单明细.订单ID
and  format(折扣,2) = '0.15'
union all
select 折扣,订单.* from 订单,订单明细
where 订单.订单ID =订单明细.订单ID
and  format(折扣,2) = '0.2'

23.找出在入职时已超过30岁的所有员工信息

select * , (year(now())-year(出生日期))as 年龄 ,(year(雇用日期)-year(出生日期))  as 入职时年龄  from 雇员
where (year(雇用日期)-year(出生日期))>30
or 
(
	(year(雇用日期)-year(出生日期))=30
	and
	(month(雇用日期)-month(出生日期))>0
)

24.找出所有单价大于30的产品(附加要求，产品类别，供应商作为参数，当产品类别和供应商都为空的时候，nofilter)

select 产品名称,单价,类别名称,公司名称 from 产品 
left join 供应商 on 产品.供应商ID= 供应商.供应商ID
left join 类别 on 类别.类别ID = 产品.类别ID
where 产品.单价>30
and 供应商.公司名称 is not null
and 类别.类别名称 is not null

25.查询所有库存产品的总额，并按照总额排序

select 产品名称 ,(单价*库存量)as 库存总额
from 产品
order by 库存总额 desc

26.检索出职务为销售代表的所有订单中，每笔订单总额低于2000的订单明细，以及相关供应商名称。
select 订单明细.* , 供应商.公司名称 as 供应商
from 雇员 left join 订单 on 订单.雇员ID= 雇员.雇员ID
left join 订单明细 on 订单明细.订单ID = 订单.订单ID
left join 产品 on 产品.产品ID=订单明细.产品ID
left join 供应商 on 产品.供应商ID = 供应商.供应商ID
where 雇员.职务 = ‘销售代表’
and 订单.订单ID in
( select 订单.订单ID from 订单,订单明细
where 订单.订单ID = 订单明细.订单ID
group by 订单.订单ID
having sum(订单明细.单价数量(1-折扣))<2000
)

SELECT 订单明细.*, 供应商.公司名称 AS 供应商
FROM 雇员, 订单, 订单明细, 产品, 供应商
WHERE 雇员.职务 = '销售代表'
AND 雇员.雇员ID = 订单.雇员ID
AND 订单.订单ID = 订单明细.订单ID
AND 订单明细.产品ID = 产品.产品ID
AND 产品.供应商ID = 供应商.供应商ID
AND (订单.订单ID) IN 	(
		SELECT 订单明细.订单ID
		FROM 订单, 订单明细
		WHERE 订单.订单ID = 订单明细.订单ID
		GROUP BY 订单ID
		HAVING SUM(订单明细.单价 * 订单明细.数量 * (1 - 订单明细.折扣)) < 2000
		)

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 运输时长
from 订单 
order by 运输时长 desc

29.将产品表和运货商两个无关的表整合为一个表

select 产品.*,运货商.*
from 产品,运货商,订单,订单明细
where 产品.产品ID=订单明细.产品ID
and 订单.订单ID=订单明细.订单ID
and 运货商.运货商ID=订单.运货商

30.获取在北京工作并向福星制衣厂股份有限公司发送过订单的职工名称。

select distinct concat(雇员.姓氏,雇员.名字) as 职工名称,公司名称
from 客户,订单,雇员
where 公司名称='福星制衣厂股份有限公司'
and 订单.客户ID=客户.客户ID
and 订单.雇员ID=雇员.雇员ID