习题2_SQL查询语句测试

第二部分SQL查询强化测试

结合第一天所看的SQL查询内容。用一天时间完成以下习题,并将相应的查询语句和视图截图贴到相应题目下方。

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

SELECT 公司名称,城市 from `供应商`

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

SELECT 产品.`产品名称`,`供应商`.公司名称

FROM `供应商`,`类别`,`产品`

WHERE `供应商`.`供应商ID`= 产品.`供应商ID`

and `类别`.`类别ID`=`产品`.`类别ID`

and `供应商`.`地区`='华北'

and `类别`.`类别名称`='海鲜'

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

select 订单明细.单价*订单明细.数量*(1-订单明细.`折扣`) as 销售额 

from 订单明细,运货商,`订单`

where

`订单明细`.`订单ID`= `订单`.`订单ID`

and

`订单`.`运货商`=`运货商`.`运货商ID`

order by 销售额 desc

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

SELECT * FROM `产品`

WHERE `单位数量` LIKE '%箱%'

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

SELECT `产品`.`产品名称` FROM `供应商`,`产品`

WHERE `供应商`.`城市`='重庆'

And `产品`.`供应商ID`=`供应商`.`供应商ID`

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

SELECT `订单`.`订单ID`,

sum(`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`)) 销售额 FROM `订单`,`雇员`,`订单明细`

WHERE `订单明细`.`订单ID`=`订单`.`订单ID`

and`订单`.`雇员ID`=`雇员`.`雇员ID`

and`雇员`.`姓氏`='郑'

and`雇员`.`名字`='建杰'

GROUP BY `订单`.`订单ID`

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

SELECT 产品.产品名称,订单明细.单价*订单明细.数量*(1-订单明细.折扣)as 订单金额,`运货商`.`公司名称`

FROM 订单,订单明细,产品,`运货商`

where

`订单`.`订单ID`=`订单明细`.`订单ID`

and `订单明细`.`产品ID`=`产品`.`产品ID`

and 订单.`订单ID`='10284'

and `运货商`.`运货商ID`=`订单`.`运货商`

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

SELECT * FROM 订单,订单明细,产品

where `订单`.`订单ID`=`订单明细`.`订单ID`

and `订单明细`.`产品ID`=`产品`.`产品ID`

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

SELECT DISTINCT`订单明细`.`订单ID`,`订单`.`订购日期`,`客户`.`公司名称`,`订单`.`发货日期`,`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`) as 销售额,`订单明细`.`数量`

FROM 订单,订单明细,产品,`客户`where `订单`.`订单ID`=`订单明细`.`订单ID`

and 订单.`客户ID`=`客户`.`客户ID`

 ORDER BY `订单明细`.`数量` desc

limit 10

10.按年度统计销售额

SELECT DISTINCT

sum(`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`))  销售额,

sum(`订单明细`.`数量`)数量

FROM 订单,订单明细,产品

where `订单`.`订单ID`=`订单明细`.`订单ID`

and year(`订单`.`订购日期`) BETWEEN  '1996'  AND  '1998' 

GROUP BY year(`订单`.`订购日期`)

------以上开始时间一个半小时

11.查询供应商中能够供应的产品样数最多的供应商。

select `产品`.`供应商ID`,`供应商`.`公司名称`,count(产品.`产品ID`)

from 产品,供应商

where `产品`.`供应商ID`= `供应商`.`供应商ID`

group by `产品`.`供应商ID`

ORDER BY count(产品.`产品ID`)desc

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

select `类别`.`类别名称`,count(产品.`产品ID`)

from 产品,`类别`

where `产品`.`类别ID`=类别.`类别ID`

group by `类别`.`类别名称`

ORDER BY count(产品.`产品ID`)desc

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

select `运货商`.`公司名称`,count(订单.`订单ID`)as 订单总数

from `订单`,`运货商`

where `订单`.`运货商`=`运货商`.`运货商ID`

group by  `运货商`.`公司名称`

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

SELECT DISTINCT `类别`.`类别名称`,`产品`.`产品名称`,`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`) as 销售额,`订单明细`.`数量`

FROM 订单,订单明细,产品,`类别`

where `订单`.`订单ID`=`订单明细`.`订单ID`

and `产品`.`类别ID`=`类别`.`类别ID`

and `订单明细`.`产品ID`=`产品`.`产品ID`

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

SELECT DISTINCT `类别`.`类别名称`,`产品`.`产品名称`,`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`) as 销售额,`订单明细`.`数量`

FROM 订单,订单明细,产品,`类别`

where `订单`.`订单ID`=`订单明细`.`订单ID`

and `产品`.`类别ID`=`类别`.`类别ID`

and `订单明细`.`产品ID`=`产品`.`产品ID`

and `类别`.`类别名称`='海鲜'

ORDER BY 销售额 desc

limit 1

16.按季度统计销售量

select year(订单.订购日期)年份,

sum(case when month(订单.订购日期) between 1 and 3 then `订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`) else 0 end) 一季度销售金额,

sum(case when month(订单.订购日期) between 4 and 6 then `订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`) else 0 end) 二季度销售金额,

sum(case when month(订单.订购日期) between 7 and 9 then `订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`) else 0 end) 三季度销售金额,

sum(case when month(订单.订购日期) between 10 and 12 then `订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`) else 0 end) 四季度销售金额

from 订单,订单明细 

where 订单.订单ID=订单明细.订单ID 

group by year(订单.订购日期)

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

SELECT `订单`.`订单ID`,`客户`.`公司名称`,`客户`.`地区`,

sum(`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`)) 订单总额

FROM `订单`,`订单明细`,`客户`

WHERE `订单明细`.`订单ID`=`订单`.`订单ID`

and `订单`.`客户ID`= `客户`.`客户ID`

GROUP BY `订单`.`订单ID`

HAVING 订单总额 > 5000

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

SELECT `产品`.`产品名称`,

sum(`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`)) 订单总额

FROM `订单`,`订单明细`,`产品`

WHERE `订单明细`.`订单ID`=`订单`.`订单ID`

and `订单明细`.`产品ID`= `产品`.`产品ID`

GROUP BY `产品`.`产品ID`

HAVING 订单总额 < 2000

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

SELECT * FROM `订单`

WHERE 订单ID LIKE '102%'

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

SELECT `订单`.`订单ID`,`客户`.`公司名称` FROM `订单`,`客户`

WHERE `客户`.`公司名称` in ('中硕贸易','学仁贸易','正人资源','中通')

and `订单`.`客户ID`=`客户`.`客户ID`

-----以上考试时间一个班销售

21.查询所有订单中月份不是单数的订单

SELECT * from `订单`

where RIGHT(month(`订购日期`),1)*1 % 2!=1

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

select * from 订单 , 订单明细

where CAST(`订单明细`.`折扣` as CHAR) in ('0.15','0.2')

and `订单`.`订单ID` = `订单明细`.`订单ID`

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

select * from `雇员`

where YEAR(雇用日期)-YEAR(出生日期)>'30'

（本题数据有问题，如下图）

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

select `产品`.`产品名称`,类别.`类别ID` ,`供应商`.`公司名称` from `产品`,`类别`,`供应商`

where `单价`> '30'

and `产品`.`类别ID`=`类别`.`类别ID`

and `产品`.`供应商ID`=`供应商`.`供应商ID`

z

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

select *,`库存量`* `单价` as 总额

from `产品`

order by 总额

26.检索出职务为销售代表的所有订单中，每笔订单总额低于2000的订单明细，以及相关供应商名称。

SELECT `供应商`.`公司名称`,

sum(`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`)) 订单总额

FROM `订单`,`订单明细`,`产品`,`客户`,`供应商`

WHERE `订单明细`.`订单ID`=`订单`.`订单ID`

and `订单明细`.`产品ID`= `产品`.`产品ID`

and `客户`.`客户ID`=`订单`.`客户ID`

and `供应商`.`供应商ID`= `产品`.`供应商ID`

and `客户`.`联系人职务`='销售代表'

GROUP BY `供应商`.`公司名称`

HAVING 订单总额 < '2000'

27.检索出向艾德高科技提供产品的供应商所在的城市。

SELECT `客户`.`公司名称`,`供应商`.`城市`

FROM `订单`,`订单明细`,`产品`,`客户`,`供应商`

WHERE `订单明细`.`订单ID`=`订单`.`订单ID`

and `订单明细`.`产品ID`= `产品`.`产品ID`

and `客户`.`客户ID`=`订单`.`客户ID`

and `供应商`.`供应商ID`= `产品`.`供应商ID`

and `客户`.`公司名称`='艾德高科技'

28.计算每一笔订单的发货期（从订购到发货），运货期（从发货到到货）的时常，并按照发货期从长到短的顺序进行排序。

SELECT 订单ID,TIMESTAMPDIFF(day, `订购日期`,`发货日期`) as 发货期,

TIMESTAMPDIFF(day, `到货日期`,`发货日期`) as 运货期

FROM `订单`

order by 发货期 desc

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

SELECT `产品`.*,`运货商`.* FROM `产品`,`运货商`,`订单`,`订单明细`

where`产品`.`产品ID`= `订单明细`.`产品ID`

and `订单明细`.`订单ID` = `订单`.`订单ID`

and `运货商`.`运货商ID`= `订单`.`运货商`

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

select DISTINCT 雇员.姓氏,`雇员`.`名字`

from `订单`,`客户`,`雇员`

WHERE `订单`.`客户ID`=`客户`.`客户ID`

and `订单`.`雇员ID`= `雇员`.`雇员ID`

and `客户`.`公司名称`='福星制衣厂股份有限公司'

and `雇员`.`城市`='北京'

以上考试时间一个半小时