SQL 练习题二

以下是一个订货管理数据库，其中有仓库表、职工表、订购单表、供货商表。

仓库表：

仓库号	城市	面积
wh1	北京	370
wh2	上海	500
wh3	广州	200
wh4	武汉	400

职工表：

仓库号	职工号	工资
wh2	e1	1220
wh1	e3	1210
wh2	e4	1250
wh3	e6	1230
wh1	e7	1250

订购单表：

职工号	供应商号	订购单号	订购日期
e3	s7	or67	2001-6-23
e1	s4	or73	2001-7-28
e7	s4	or76	2001-5-25
e6	null	or77	-   -
e3	s4	or79	2001-6-13
e1	null	or80	-   -
e3	null	or90	-   -
e3	s3	or91	2001-7-13

供应商表：

供应商号	供应商名	地址
s3	振华电子厂	西安
s4	华通电子公司	北京
s6	607厂	郑州
s7	爱华电子厂	北京

1. 从职工关系中检索所有工资值。
2. 检索仓库关系中的所有记录
3. 检索工资多于1230元的职工号
4. 检索哪些仓库有工资多于1210元的职工。
5. 给出在仓库“wh1”或“wh2”工作，并且工资少于1250元的职工号。
6. 找出工资多于1230元的职工号和他们所在的城市。
7. 找出工作在面积大于400的仓库的职工号以及这些职工工作所在的城市。
8. 哪些城市至少有一个仓库的职工工资为1250元
9. 查询所有职工的工资都多于1210元的仓库的信息。
10. 找出和职工e4挣同样工资的所有职工。
11. 检索出工资在1220元到1240元范围内的职工信息。
12. 从供应商关系中检索出全部公司的信息，不要工厂或其他供应商的信息。
13. 找出不在北京的全部供应商信息。
14. 按职工的工资值升序检索出全部职工信息。
15. 先按仓库号排序，再按工资排序并输出全部职工信息。
16. 找出供应商所在地的数目。
17. 求支付的工资总数
18. 求北京和上海的仓库职工的工资总和
19. 求所有职工的工资都多于1210元的仓库的平均面积
20. 求在wh2仓库工作的职工的最高工资值
21. 求每个仓库的职工的平均工资
22. 求至少有两个职工的每个仓库的平均工资。
23. 找出尚未确定供应商的订购单
24. 列出已经确定了供应商的订购单信息
25. 查询供应商名
26. 在订购单表中加入一个新字段总金额，说明完成该订购单所应付出的总金额数。
27. 列出每个职工经手的具有最高总金额的订购单信息。
28. 检索哪些仓库中还没有职工的仓库的信息
29. 检索哪些仓库中至少已经有一个职工的仓库的信息
30. 检索有职工的工资大于或等于wh1仓库中任何一名职工工资的仓库号
31. 检索有职工的工资大于或等于wh1仓库中所有职工工资的仓库号。

答案仅供参考：

create database 订货管理数据库

create table 仓库表(

仓库号 varchar(50) not null,城市 varchar(50) not null,面积 int not null,primary key(仓库号))

create table 职工表(

仓库号 varchar(50) not null,职工号 varchar(50) not null,工资 money not null,

primary key(职工号),foreign key(仓库号) references  仓库表(仓库号))

create table 订购单表(

职工号 varchar(50) not null,供应商号 varchar(50) ,订购单号 varchar not null,订购日期 datetime,

foreign key(职工号) references 职工表(职工号),foreign key(供应商号) references 供应商表(供应商号))

create table 供应商表(

供应商号 varchar(50) not null,供应商名 varchar(50) not null,地址 varchar(50) not null,primary key(供应商号))

 

insert into 仓库表(仓库号,城市,面积)

values('wh1','北京',370),

           ('wh2','上海',500),

           ('wh3','广州',200),

           ('wh4','武汉',400)

 

 

insert into 职工表(仓库号,职工号,工资)

values('wh2','e1',1220),

           ('wh1','e3',1210),

           ('wh2','e4',1250),

           ('wh3','e6',1230),

           ('wh1','e7',1250)

   

    --修改变量范围

    alter   table 订购单表 alter column 订购单号 varchar(50)   

 insert into 订购单表(职工号,供应商号,订购单号,订购日期)

values('e3','s7','or67','2001-6-23'),

           ('e1','s4','or73','2001-7-28'),

           ('e7','s4','or76','2001-5-25'),

           ('e6',null,'or77',null),

           ('e3','s4','or79','2001-6-13'),

           ('e1',null,'or80', null ),

           ('e3',null,'or90',null),

           ('e3','s3','or91','2001-7-23')

  

    insert into 供应商表(供应商号,供应商名,地址)

values('s3','振华电子厂','西安'),

           ('s4','华通电子公司','北京'),

           ('s6','607厂','郑州'),

           ('s7','爱华电子厂','北京')

 

--1

select 工资 from 职工表    

--2

select * from 仓库表

--3

select 职工号 from 职工表 where 工资>1230

--4

select 仓库号 from 职工表 where 工资>1210

--5

select 职工号 from 职工表 where (仓库号='wh1' or 仓库号= 'wh2')  and 工资<1250

 --6

  select 职工号,城市 from 职工表,仓库表 where 工资>1230 and 职工表.仓库号=仓库表.仓库号

  --7

  select 职工号,城市 from 职工表,仓库表 where   职工表.仓库号=仓库表.仓库号 and 仓库表.面积>400

  --8

  select 城市 from 仓库表 where 仓库号 in (select 仓库号 from 职工表 where 工资=1250)

  --9

  select * from 仓库表 where 仓库号 not in(select 仓库号 from 职工表 where 工资<=1210) and 仓库号 in(select 仓库号 from 职工表)

  --10

  select  职工号 from 职工表 where 工资=(select 工资 from 职工表 where 职工号='e4')

  --11

  select * from 职工表 where 工资 between 1220 and 1240

  --12

  select * from 供应商表 where 供应商名 like '%公司'

  --13

  select * from 供应商表 where 地址!='北京'

  --14

  select * from 职工表 order by 工资 asc

  --15

  select * from 职工表 order by 仓库号 asc,工资 desc

  --16

  select COUNT(distinct 地址) as 地址数 from 供应商表

  --17

  select SUM(工资) as 工资总数 from 职工表

  --18

  select  sum(工资) from 职工表 where 仓库号 in(select 仓库号 from 仓库表 where 城市='北京' or 城市='上海')

  --19

  select AVG(面积) as mavg from 仓库表 where 仓库号 not in(select 仓库号 from 职工表 where 工资<=1210) and 仓库号 in(select 仓库号 from 职工表)

  --20

  select MAX(工资) from 职工表 where 仓库号='wh2'

  --21

  select 仓库号,AVG(工资) from 职工表 group by 仓库号

  --22

  select 仓库号,AVG(工资) from 职工表 group by 仓库号 having COUNT(*)>=2

  --23

  select * from 订购单表 where 供应商号 is null

  --24

  select * from 订购单表

 left  join 供应商表 on 订购单表.供应商号 = 供应商表.供应商号 where 订购单表.供应商号 is not null

  --25

 select distinct  供应商名 from 供应商表

--26

alter table 订购单表 add 总金额 money

 

--27

--28

select * from 仓库表 where 仓库号 not in (select 仓库号 from 职工表 )

 

 --29

select * from 仓库表 where 仓库号 in (select 仓库号 from 职工表 group by 仓库号 having COUNT(职工号) >=1)

 

--30

select 仓库号 from 职工表 where 工资>any(select 工资 from 职工表 where  仓库号='wh1')

 --31

 select 仓库号 from 职工表 where 工资>=all(select 工资 from 职工表 where  仓库号='wh1')