数据库sql查询语句练习2_习题_结果(单世民)仓库_习题.docx

数据库结构如下：
仓库（仓库号, 城市, 面积）
订购单（职工号, 供应商号, 订购单号, 订购日期）
供应商（供应商号, 供应商名, 地址）
职工（仓库号, 职工号, 工资）

具体数据如下：

仓库表：
仓库号
城市
面积
WH1
北京
370
WH2
上海
500
WH3
广州
200
WH4
武汉
400

订购单表：
职工号
供应商号
订购单号
订购日期
E3
S7
OR67
06/23/01
E1
S4
OR73
07/28/01
E5
S4
OR76
05/25/01
E6
S6
OR77
05/26/01
E3
S4
OR79
06/13/01
E1
S2
OR80
08/29/01
E3
S3
OR90
09/01/01
E3
S3
OR91
07/13/01

供应商表：
供应商号
供应商名
地址
S2
名硕电子公司
苏州
S3
振华电子厂
西安
S4
华通电子公司
北京
S6
607厂
郑州
S7
爱华电子厂
北京

职工表：
仓库号
职工号
工资
WH2
E1
1220
WH1
E3
1210
WH2
E4
1250
WH3
E5
1230
WH1
E6
1250

使用SQL语句完成：
DDL
1. 写出创建上述表的语句
命令：
create table 仓库(仓库号 varchar(8) primary key, 城市 varchar(8), 面积 int);
create table 订购单(订购单号 varchar(8) primary key,职工号 varchar(8), 供应商号 varchar(8), 订购日期 nchar(8))
create table 供应商(供应商号 varchar(8) primary key, 供应商名 varchar(18), 地址 varchar(8))
create table 职工(职工号 varchar(8) primary key,仓库号 varchar(8), 工资 int)DML
2. 给出插入上述数据的insert语句
命令：
insert into 仓库 values('WH1','北京',370)
insert into 仓库 values('WH2','上海',500)
insert into 仓库 values('WH3','广州',200)
insert into 仓库 values('WH4','武汉',400)

insert into 订购单 values('OR67','E3','S7','06/23/01')
insert into 订购单 values('OR73','E1','S4','07/28/01')
insert into 订购单 values('OR76','E5','S4','05/25/01')
insert into 订购单 values('OR77','E6','S6','05/26/01')
insert into 订购单 values('OR79','E3','S4','06/13/01')
insert into 订购单 values('OR80','E1','S2','08/29/01')
insert into 订购单 values('OR90','E3','S3','09/01/01')
insert into 订购单 values('OR91','E3','S7','07/13/01')

insert into 供应商 values('S2','名硕电子公司','苏州')
insert into 供应商 values('S3','振华电子厂','西安')
insert into 供应商 values('S4','华通电子公司','北京')
insert into 供应商 values('S6','607厂','郑州')
insert into 供应商 values('S7','爱华电子厂','北京')

insert into 职工 values('E1','WH2',1220)
insert into 职工 values('E3','WH1',1210)
insert into 职工 values('E4','WH2',1250)
insert into 职工 values('E5','WH3',1230)
insert into 职工 values('E6','WH1',1250)
单表查询
3. 检索职工关系中的所有信息
命令：select * from 职工

结果：

4. 检索供应商关系中的所有信息
命令：select * from 供应商

结果：

5. 检索六月之后的所有订单
命令：
SELECT *FROM 订购单 WHERE DATEDIFF(MONTH,'2001-06-01 00:00:00.000',订购日期)>=0;
结果：

6. 检索面积大于400的仓库
命令：select * from 仓库 where 面积>400

结果：

7. 检索哪些职工的工资多于1210
命令：select * from 职工 where 工资>1210

结果：

8. 检索仓库是“WH1”或“WH2”并且面积大于400的城市
命令：select 城市 from 仓库 where 仓库号='WH1' and 面积>400 union select 城市 from 仓库 where 仓库号='WH2' and 面积>400

结果：

9. 找出仓库面积在400到600的仓库
命令：select * from 仓库 where 面积 between 400 and 600

结果：

10. 找出名中包含 “厂”的所有供应商的名
命令：select 供应商名 from 供应商 where 供应商名 like '%厂%'

结果：

11. 找出不在西安的供应商
命令：select * from 供应商 except select * from 供应商 where 地址='西安' 或者:select * from 供应商 where not 地址 = '西安'

结果：

12. 找出不在北京的仓库
命令：select * from 仓库 except select * from 仓库 where 城市='北京'

结果：

13. 按工资降序排列出所有职工的信息
命令：select * from 职工 order by 工资 desc

结果：

14. 先按仓库号升序排列，再按工资降序排列
命令：select * from 职工 order by 仓库号 asc ,工资 desc
结果：

15. 在仓库表中统计一下有几个仓库
命令：select COUNT(*) 仓库数 from 仓库

结果：

16. 在职工表中统计一下有几个仓库
命令：select COUNT(distinct 仓库号) 仓库数 from 职工

结果：

17. 求总的仓库面积
命令：select sum(面积) 总面积 from 仓库

结果：

聚合查询
18. 每个职工的订单数
命令：select 职工号,count(*) 订单数 from 订购单 group by 职工号

结果：

19. 订单数大于3的职工
命令：select 职工号,COUNT(*) 订单数 from 订购单 group by 职工号 having COUNT(*)>3

结果：

多表查询
20. 找出在面积大于400的仓库中工作的职工
命令：select 职工号 from 职工 join 仓库 on 职工.仓库号=仓库.仓库号 where 面积>400

结果：

21. 找出在北京工作的职工和他们的工资情况
命令：select 职工号,工资 from 职工 join 仓库 on 职工.仓库号=仓库.仓库号 where 仓库.城市='北京'

结果：

22. 找出工资大于1215的职工和他们所在的城市
命令：select 职工号,城市 from 职工 join 仓库 on 职工.仓库号=仓库.仓库号 where 工资>1215

结果：

子查询
23. 哪些城市至少有一个订单（从仓库角度考虑）
命令：select distinct 城市 from 订购单 join 职工 on 订购单.职工号=职工.职工号 join 仓库 on 职工.仓库号=仓库.仓库号

结果：

24. 找出没有任何订单的城市
命令：select 城市 from 仓库 except select distinct 城市 from 订购单 join 职工 on 订购单.职工号=职工.职工号 join 仓库 on 职工.仓库号=仓库.仓库号

结果：

25. 找出和E4有同样工资的所有职工
命令：select b.职工号,b.仓库号,b.工资 from 职工 a join 职工 b on a.工资=b.工资 where a.职工号='E4'

结果：

26. 找出仓库面积大于400的仓库的所有职工
命令：select 职工.职工号,职工.仓库号,职工.工资 from 职工 join 仓库 on 职工.仓库号=仓库.仓库号 where 面积>400

结果：

27. 找出供应商在西安的职工和他们的工资情况
命令：select 职工.职工号,工资 from 供应商 join 订购单 on 供应商.供应商号=订购单.供应商号 join 职工 on 订购单.职工号=职工.职工号 where 地址='西安'

结果：

28. 找出不在北京仓库里工作的职工
命令：select 职工号,工资 from 职工 join 仓库 on 职工.仓库号=仓库.仓库号 except select 职工号,工资 from 职工 join 仓库 on 职工.仓库号=仓库.仓库号 where 城市='北京'

结果：

29. 找出在北京仓库里工作的职工
命令：select 职工号,工资 from 职工 join 仓库 on 职工.仓库号=仓库.仓库号 where 城市='北京'

结果：

30. 求广州和上海仓库职工的总工资
命令：select SUM(工资) 总工资 from 职工 join 仓库 on 职工.仓库号=仓库.仓库号 where 城市='广州' or 城市='上海'

结果：

31. 求所有职工工资都大于1210的仓库的平均面积
命令：select avg(面积) 平均面积 from (select 仓库号 from 职工 except select 仓库号 from 职工 where 工资<1210) a join 仓库 on a.仓库号=仓库.仓库号

结果：

32. 求上海仓库中职工的最高工资
命令：select MAX(工资) 最高工资 from 职工 join 仓库 on 职工.仓库号=仓库.仓库号 where 城市='上海'

结果：

33. 订单数大于或等于2的职工的工资
命令：select a.工资 from (select 职工.职工号,工资 from 职工 join 订购单 on 职工.职工号=订购单.职工号 group by 职工.职工号,工资 having COUNT(*)>=2) a

结果：