二阶段测试

--创建数据库
create database DHGL
--锁定要使用的数据库
use DHGL
--创建仓库表(仓库号、城市、面积)
create table cangku
(
cno nvarchar(50) not null,
city nvarchar(50) not null,
area int not null
)
--创建职工表（仓库号、职工号、工资）
create table zhigong
(
cno nvarchar(50) not null,
zno nvarchar(50) not null,
wages int not null
)
--创建订购单表（职工号、供应商号、订购单号、订购日期）
create table dinggou
(
zno nvarchar(50) not null,
gno nvarchar(50) null,
dno nvarchar(50) not null,
timers datetime null
)
--创建供应商表（供应商号、供应商名、地址）
create table gongying
(
gno nvarchar(50) not null,
gname nvarchar(50) not null,
city nvarchar(50) not null
)
--给表添加数据
--仓库表
insert cangku values('wh1','北京',370)
insert cangku values('wh2','上海',500)
insert cangku values('wh3','广州',200)
insert cangku values('wh4','武汉',400)
--职工表
insert zhigong values('wh2','e1',1220)
insert zhigong values('wh1','e3',1210)
insert zhigong values('wh2','e4',1250)
insert zhigong values('wh3','e6',1230)
insert zhigong values('wh1','e7',1250)
--订购单表
insert dinggou values('e3','s7','or67','2015-6-23')
insert dinggou values('e1','s4','or67','2015-7-28')
insert dinggou values('e7','s4','or67','2015-5-25')
insert dinggou values('e6',null,'or67',null)
insert dinggou values('e3','s4','or67','2015-6-13')
insert dinggou values('e1',null,'or67',null)
insert dinggou values('e3',null,'or67',null)
insert dinggou values('e3','s3','or67','2015-7-13')
--供应商表
insert gongying values('s3','振华电子厂','西安')
insert gongying values('s4','华通电子公司','北京')
insert gongying values('s6','607厂','郑州')
insert gongying values('s7','爱华电子厂','北京')
--1.检索工资多于1230元的职工号
select zno from zhigong where wages > 1230
--【2】.检索仓库职工工资多于1210元的仓库地址
select city from cangku where cno
in (select cno from zhigong where wages > 1210)
--【3】.给出在仓库“wh1”或“wh2”工作，并且工资少于1250元的职工号。
select zno from zhigong where wages < 1250 and (cno = 'wh1' or cno = 'wh2')
--4.找出工资多于1230元的职工号和他们仓库所在的城市。
select zno from zhigong where wages > 1230
union
select city from cangku where cno in (select cno from zhigong where wages > 1230)
--5.找出工作在面积大于400的仓库的职工号以及这些仓库所在的城市。
select zno from zhigong where cno
in (select cno from cangku where area > 400)
union
select city from cangku where area > 400
--6.哪些城市至少有一个仓库的职工工资为1250元
select city from cangku where cno in (select cno from zhigong where wages =1250 )
--7.查询所有职工的工资都多于1210元的仓库的信息。
select *from cangku where cno in (select cno from zhigong where wages > 1210)
--8.找出和职工e4挣同样工资的所有职工,包括他自己。
select zno from zhigong where wages = (select wages from zhigong where zno = 'e4')
--9.检索出工资在1220元到1240元范围内的职工信息。
select *from zhigong where wages >= 1220 and wages <= 1240
select *from zhigong where wages between 1220 and 1240
--10.按职工的工资值升序检索出全部职工信息。
select *from zhigong order by wages asc
--11.找出不在北京的全部供应商信息。
select *from gongying where city !='北京'
--12.先按仓库号升序排序，再按工资降序并输出全部职工信息。
select *from zhigong order by cno asc,wages desc
--【13】.找出供应商所在地的数目。
select COUNT(distinct city) as '数量' from gongying
--14.求支付的工资总数
select SUM(wages) as '工资总数' from zhigong
--【15】.求所有职工的工资都多于1210元的仓库的平均面积
select AVG(area) from cangku where cno
>any (select cno from zhigong group by cno)
and
cno in (select cno from zhigong where wages >1210)
--16.求每个仓库的职工的平均工资
select AVG(wages) as '平均工资' from zhigong group by cno
--17.求至少有两个职工的每个仓库的平均工资。
select cno,AVG(wages) as '平均工资' from zhigong group by cno having COUNT(zno)>=2
--18.检索哪些仓库中还没有职工的仓库的信息
select *from cangku where cno not in (select cno from zhigong)
--19.检索有职工的工资大于或等于wh1仓库中任何一名职工工资的仓库全部信息,
--不包含wh1仓库信息
select *from cangku where cno
in (select cno from zhigong where wages
> any (select wages from zhigong where cno = 'wh1'))
and cno != 'wh1'
--20.将仓库地址在北京的职工工资统一更改为1300
update zhigong set wages = 1300 where zno
in
(
select zno from zhigong where cno
in
(select cno from cangku where city = '北京')
)

update zhigong set wages = 1300 where cno
= (select cno from cangku where city = '北京')
--select *from zhigong
--21.写出包含四个表全部信息的视图,名为AllTab
create view ALLTab
as
select cangku.cno,cangku.city,area,zhigong.zno,wages,dinggou.gno,dno,
timers,gname,gongying.city from cangku,zhigong,dinggou,gongying
where cangku.cno = zhigong.cno and zhigong.zno = dinggou.zno and
dinggou.gno = gongying.gno
--22.写出供应商表的级联删除触发器,并在删除成功后重新查询供应商表
create trigger delete_gongying
on gongying
instead of delete
as
delete from dinggou where gno = (select gno from deleted)
delete from gongying where gno = (select gno from deleted)
select *from delete_gongying
--23.写一个存储过程，要求输入两个数a和b，如果a大于b则返回两个数的差，否则返回两个数的和；
create proc cunchu
@a int,@b int
as
declare @c int
if @a > @b
begin
set @c= @a-@b
end
else
begin
set @c= @a+@b
end
return @c
--24.将23题的存储过程执行一遍，传入参数5和3，
--将返回值使用一个变量接收，然后在结果集和消息框中分别打印出来
declare @c int
exec @c = cunchu 5,3
print @c
select @c

--错题序号：2、3、13、15、22、23、24