在SQL-server-2000-中创建数据库和表的实例及相关的-SQL查询语句.doc

CREATE DATABASE 库存管理goUSE 库存管理GOCREATE TABLE 仓库(仓库号 CHAR(5) PRIMARY KEY,城市 CHAR(10),面积 INT CHECK(面积0)GOCREATE TABLE 职工(仓库号 CHAR(5) FOREIGN KEY REFERENCES 仓库,职工号 CHAR(5) PRIMARY KEY,工资 INT CHECK(工资=1000 AND 工资=5000) DEFAULT 1200)GOCREATE TABLE 供应商(供应商号 CHAR(5) PRIMARY KEY,供应商名 CHAR(20),地址 CHAR(20)GOCREATE TABLE 订购单(职工号 CHAR(5) NOT NULL FOREIGN KEY REFERENCES 职工,供应商号 CHAR(5) NULL FOREIGN KEY REFERENCES 供应商,订购单号 CHAR(5) PRIMARY KEY,订购日期 DATETIME DEFAULT GetDate()GOINSERT INTO 仓库 VALUES(WH1,北京,370)INSERT INTO 仓库 VALUES(WH2,上海,500)INSERT INTO 仓库 VALUES(WH3,广州,200)INSERT INTO 仓库 VALUES(WH4,武汉,400)GOINSERT INTO 职工 VALUES(WH2,E1,1220)INSERT INTO 职工 VALUES(WH1,E3,1210)INSERT INTO 职工 VALUES(WH2,E4,1250)INSERT INTO 职工 VALUES(WH3,E6,1230)INSERT INTO 职工 VALUES(WH1,E7,1250)GOINSERT INTO 供应商 VALUES(S3,振华电子厂,西安)INSERT INTO 供应商 VALUES(S4,华通电子公司,北京)INSERT INTO 供应商 VALUES(S6,607厂,郑州)INSERT INTO 供应商 VALUES(S7,爱华电子厂,北京)GOINSERT INTO 订购单 VALUES(E3,S7,OR67,2002-06-23)INSERT INTO 订购单 VALUES(E1,S4,OR73,2002-07-28)INSERT INTO 订购单 VALUES(E7,S4,OR76,2002-05-25)INSERT INTO 订购单 VALUES(E6,NULL,OR77,NULL)INSERT INTO 订购单 VALUES(E3,S4,OR79,2002-06-13)INSERT INTO 订购单 VALUES(E1,NULL,OR80,NULL)INSERT INTO 订购单 VALUES(E3,NULL,OR90,NULL)INSERT INTO 订购单 VALUES(E3,S3,OR91,2002-07-13)GOselect *from 仓库select *from 职工select *from 订购单select *from 供应商 select 供应商名 from 供应商 where 地址=北京 select 订购单号 from 订购单 where 供应商号=S6 select *from 订购单 where 职工号=E6and 供应商号=S6 select 职工号,仓库号 from 职工 where 职工号 in( select 职工号 from 订购单 where 供应商号=S3) select *from 职工 where 职工号 not in ( select 职工号 from 订购单 where 供应商号 =S3) select *from 供应商 where 供应商号 not in ( select 供应商号 from 订购单 where 供应商号 is not null) select *from 供应商 where 地址=北京 and 供应商号 in ( select 供应商号 from 订购单 where 职工号 = E1)and 供应商号 in ( select 供应商号 from 订购单 where 职工号 =E3) select 工资 from 职工 where 职工号 in( select 职工号 from 订购单 A where 供应商号 in (select 供应商号 from 供应商 where 供应商名=华通电子公司) select 供应商名 from 供应商 where 供应商号 not in( select 供应商号 from 订购单 where 供应商号 is not null and 职工号 in( select 职工号 from 职工 where 工资1230) select count(distinct 仓库号)from 仓库 select*from 仓库 where 面积 =(select (max) 面积 from 仓库) select avg(面积) 平均面积 from 仓库 select avg(面积)from 仓库 where 仓库号 in ( select 仓库号 from 职工 where 职工号 in ( select 职工号 from 订购单 where 供应商号 =s4) select 地址, count (distinct 供应商号) from 供应商 group by 地址 select 仓库号,count(distinct 职工号 )from 职工 where 工资 1220 group by 仓库号 select count( distinct 供应商号)from 订购单 where 职工号 in ( select 职工号 from 职工 where 仓库号 in ( select 仓库号 from 仓库 where 面积 =( select min ( 面积)from 仓库) select count( distinct 供应商号)from 订购单 where 职工号 in ( select 职工号 from 职工 where 仓库号 in ( select 仓库号 from 仓库 where 面积 = all ( select 面积 from 仓库) select *from 职工 X where 工资 21 select sname from s where not exists ( select *from c where teacher=程军and not exists( select*from sc where s.s#=sc.s# and sc.c#=c.c#) select c# from c where c# not in ( select c# from sc where s# in ( select s# from s where sname =李强) select c# from c where not exists ( select*from sc where c.c#=sc.c# and s# in ( select s# from s where sname = 李强) select distinct x.s# from sc x,sc y where x.s#=y.s# and x.c# !=y.c# select s# from sc group by s# having count(*)=2 select c#,cname from c where not exists ( select * from s where not exists( select *from sc where s.s#=sc.s# and c.c# =sc.c#) select distinct s# from sc where c# in ( select c# from c where teacher =程军) select x.s# from sc x,sc y where x.s# =y.s# and x.c#=c1and y.c#=c5 select s# from s where s# in ( select s# from sc where c#=c1)and s# in( select s# from sc where c#=c5) select sname from s where not exists( select*from c where not exists( select*from sc where sc.c#=c.c# and sc.s#=s.s#) select s# from s where not exists( select*from sc x where s#=s2and not exists( select*from sc y where s.s#=y.s# and x.c#=y.c#) select s#,sname from s where s# in( select s# from sc where c# in( select c# from c where cname=c语言) select s#,sname from s where s# not in( select s# from sc where c# in( select c# from c where cname=c语言) select s#, count(distinct c#)as 选课门数 ,avg(grade)as 平均分,max(grade)as 最高分,min(grade)as 最低分 from sc group by s# select cname from c where not exists( select *from s where sex =男and not exists( select *from sc where s.s#=sc.s# and c.c#=sc.c#) select s#,sname from s where exists( select s# from sc x where s.s#=x.s# and exists( select s#,min(grade) from sc y where x.s#=y.s# group by s# having min(grade)80) select s#,sname from s where s# not in( select s# from sc where s.s#=sc.s# and grade =all( select count(distinct c#) from sc group by s#) select s#,sname from s where s# not in ( select s# from sc x where grade 60 group by s# having count(distinct c#)=2) select cname,teacher from sc,c where sc.c#=c.c# and s#=s1 select sname from s,sc,c where sc.c#=c.c# and sc.s#=s.s# and sex =女and teacher like 王% select sname from s where sex=女and s# in( select s# from sc where c# in( select c# from c where teacher like王%) select sname from s where sex =女and exists( select*from sc where s.s#=sc.s# and exists( select*from c where sc.c#=c.c# and teacher like王%) select cname from c where c# not in( select c# from s,sc where s.s#=sc.s# and sc.c#=c.c# and sname like王%) select cname from c where not exists( select * from s,sc where s.s#=sc.s# and sc.c#=c.c# and sname like王%) select d