CREATE DATABASE 库存管理
go
USE 库存管理
GO
CREATE TABLE 仓库
(仓库号 CHAR(5) PRIMARY KEY,
城市 CHAR(10),
面积 INT CHECK(面积>0))
GO
CREATE TABLE 职工(
仓库号 CHAR(5) FOREIGN KEY REFERENCES 仓库,
职工号 CHAR(5) PRIMARY KEY,
工资 INT CHECK(工资>=1000 AND 工资<=5000) DEFAULT 1200)
GO
CREATE TABLE 供应商(
供应商号 CHAR(5) PRIMARY KEY,
供应商名 CHAR(20),
地址 CHAR(20))
GO
CREATE TABLE 订购单(
职工号 CHAR(5) NOT NULL FOREIGN KEY REFERENCES 职工,
供应商号 CHAR(5) NULL FOREIGN KEY REFERENCES 供应商,
订购单号 CHAR(5) PRIMARY KEY,
订购日期 DATETIME DEFAULT GetDate())
GO
INSERT INTO 仓库 VALUES('WH1','北京',370)
INSERT INTO 仓库 VALUES('WH2','上海',500)
INSERT INTO 仓库 VALUES('WH3','广州',200)
INSERT INTO 仓库 VALUES('WH4','武汉',400)
GO
INSERT 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)
GO
INSERT INTO 供应商 VALUES('S3','振华电子厂','西安')
INSERT INTO 供应商 VALUES('S4','华通电子公司','北京')
INSERT INTO 供应商 VALUES('S6','607厂','郑州')
INSERT INTO 供应商 VALUES('S7','爱华电子厂','北京')
GO
INSERT 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')
GO
select *from 仓库
select *from 职工
select *from 订购单
select *from 供应商
select 供应商名 from 供应商 where 地址='北京'
select 订购单号 from 订购单 where 供应商号='S6'
select *from 订购单 where 职工号='E6'and 供应商号='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 工资<1220))
select 城市 from 仓库 where 仓库号 in (
select 仓库号 from 职工 where 职工号 in (
select 职工号 from 订购单 where 供应商号='S4'))
select 职工号 from 职工 where 仓库号 in (
select 仓库号 from 仓库 where 城市 ='上海')and 职工号 in (
select 职工号 from 订购单 where 供应商号='S6')
select 职工号 from 职工 where 仓库号 in(
select 仓库号 from 仓库 where 城市='广州') and 职工号 in(
select 职工号 from 订购单 where 供应商号='S6')and 职工号 not in (
select 职工号 from 订购单 where 供应商号 !='S6')
select 职工号 from 职工 where 仓库号 in(
select 仓库号 from 仓库 where 城市='北京') and exists(
select* from 订购单 where 供应商号='S4'and 职工号=职工.职工号)and not exists (
select * from 订购单 where 供应商号 !='S4'and 职工号=职工.职工号)
select 职工号 from 职工 where 仓库号 in(
select 仓库号 from 仓库 where 城市='北京') and 职工号 in(
select 职工号 from 订购单 where 供应商号='S4')and 职工号 not in (
select 职工号 from 订购单 where 供应商号 !='S4')
select 订购单号 from 订购单 where  供应商号 in(
select 供应商号 from 供应商 where 地址 ='北京')and 职工号 in (
select 职工号 from 职工 where 工资 >1230)sql server2000简介
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 工资 <(
select AVG(工资) from 职工 Y where X.仓库号=Y.仓库号)
create database 教学管理
GO
USE 教学管理
GO
CREATE TABLE S(
[S#] varchar(20) primary key,
SName varchar(20) not null,
Sex varchar(2) default '男',
Age int)
GO
CREATE TABLE C(
[C#] varchar(20) primary key,
CName varchar(20) not null unique,
Teacher varchar(20))
GO
CREATE TABLE SC(
[S#] varchar(20) not null,
[C#] varchar(20) not null,
Grade int defaul
t 0 check(grade is null
or Grade between 0 and 100),
primary key (S#,C#),
constraint FK_SC_S foreign key([S#])
references S([S#]) on delete cascade
on update cascade,
constraint FK_SC_C foreign key([C#])
references C([C#]) on delete cascade
on update cascade)
GO
INSERT INTO S([S#],SName,Age,Sex)
VALUES('S1','李强',23,'男')
INSERT INTO S([S#],SName,Age,Sex)
VALUES('S2','刘丽',22,'女')
INSERT INTO S([S#],SName,Age,Sex)
VALUES('S3','张友',22,'男')
INSERT INTO S([S#],SName,Age,Sex)
VALUES('S4','王芳',21,'女')
INSERT INTO S([S#],SName,Age,Sex)
VALUES('S5','王海',21,'男')
INSERT INTO C([C#],CName,Teacher)
VALUES('C1','C语言','王华')
INSERT INTO C([C#],CName,Teacher)
VALUES('C2','数据库原理','程军')
INSERT INTO C([C#],CName,Teacher)
VALUES('C3','编译原理','程军')
INSERT INTO SC([S#],[C#],Grade)
VALUES('S1','C1',83)
INSERT INTO SC([S#],[C#],Grade)
VALUES('S1','C2',70)
INSERT INTO SC([S#],[C#],Grade)
VALUES('S1','C3',90)
INSERT INTO SC([S#],[C#],Grade)
VALUES('S2','C1',85)
INSERT INTO SC([S#],[C#],Grade)
VALUES('S2','C2',84)
INSERT INTO SC([S#],[C#],Grade)
VALUES('S3','C1',82)
INSERT INTO SC([S#],[C#],Grade)
VALUES('S3','C2',90)
INSERT INTO SC([S#],[C#],Grade)
VALUES('S3','C3',95)
INSERT INTO SC([S#],[C#],Grade)
VALUES('S5','C1',85)
INSERT INTO SC([S#],[C#],Grade)
VALUES('S5','C3',84)
select *from S
select *from c
select *from sc
select cname from c where c# not in (
select c# from sc where s# in (
select s# from s where sname = '王海'))
select s#,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 s#,sname from s where sex ='女'and s# in(
select s# from sc where c# in (
select c# from c where cname ='c语言') and c# not in (
select c# from c where cname !='c语言'))
select c#,cname from c where teacher='程军'
select s#,sname from s where sex='男'and age > 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#='c1'and 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#='s2'and 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 <=80)and s# in(select s# from sc)
select s#,sname from s where exists(
select s# from sc x where s.s#=x.s# and exists(
select s#,count(distinct c#) from sc y where x.s#=y.s# group by s# having count(distinct c#)>=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 <(
select avg(grade)from sc y where x.c#=y.c# group by c#))
and s# in (select s# from sc)
select s# from s where exists(
select s#,count(distinct c#)from sc  where s.s#=sc.s# and 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 distinct s# from sc x where not exists (
select * from c where teacher like'程%'and not exists(
select * from sc y where y.s#=x.s# and y.c#=c.c#))
select s#,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#))