create table student(sno char(5),
sname char(5) unique,
ssex char(2) default '男',
sage int,
sdept char(2),
primary key(sno),
check(student.sno like '9500[0-4]'));
insert into student(sno,sname,sage,sdept) values('95001','李勇',20,'cs');
insert into student values('95002','刘晨','女',19,'is');
insert into student values('95003','王敏','女',18,'ma');
insert into student(sno,sname,sage,sdept) values('95004','张立',19,'is');
create table course(cno int identity(1,1),
cname char(10) not null,
cpno int,
ccredit int,
primary key(cno),
check(course.cno like '[0-9]'));
insert into course(cname,cpno,ccredit) values('数据库',5,'4');
insert into course(cname,cpno,ccredit) values('数学',null,'2');
insert into course(cname,cpno,ccredit) values('信息系统',1,'4');
insert into course(cname,cpno,ccredit) values('操作系统',6,'3');
insert into course(cname,cpno,ccredit) values('数据结构',7,'4');
insert into course(cname,cpno,ccredit) values('数据处理',null,'2');
insert into course(cname,cpno,ccredit) values('pascal语言',6,'4');
create table sc(sno char(5),
cno int,
grade int,
primary key(sno,cno),
foreign key(sno) references student(sno),
foreign key(cno) references course(cno),
check(sc.grade like '[0-9][0-9]'));
insert into sc values('95001','1',92);
insert into sc values('95001','2',85);
insert into sc values('95001','3',88);
insert into sc values('95002','2',90);
insert into sc values('95002','3',80);
create table s(sno char(2),
sname char(10) not null,
status int,
city char(5),
primary key(sno));
insert into s values('s1','精益',20,'天津');
insert into s values('s2','盛锡',10,'北京');
insert into s values('s3','东方红',30,'北京');
insert into s values('s4','丰泰盛',20,'天津');
insert into s values('s5','为民',30,'上海');
create table p(pno char(2),
pname char(10) not null,
weight int,
color char(2),
primary key(pno));
insert into p values('p1','螺母',12,'红');
insert into p values('p2','螺栓',17,'绿');
insert into p values('p3','螺丝刀',14,'蓝');
insert into p values('p4','螺丝刀',14,'红');
insert into p values('p5','凸轮',40,'蓝');
insert into p values('p6','齿轮',30,'红');
create table j(jno char(2),
jname char(10) not null,
city char(5),
primary key(jno));
insert into j values('j1','三建','北京');
insert into j values('j2','一气','长春');
insert into j values('j3','弹簧厂','天津');
insert into j values('j4','造船厂','天津');
insert into j values('j5','机车厂','唐山');
insert into j values('j6','无线电厂','常州');
insert into j values('j7','半导体厂','南京');
create table spj(sno char(2),
pno char(2),
jno char(2),
qty int,
primary key(sno,pno,jno),
foreign key(sno) references s(sno),
foreign key(pno) references p(pno),
foreign key(jno) references j(jno));
insert into spj values('s1','p1','j1',200);
insert into spj values('s1','p1','j3',100);
insert into spj values('s1','p1','j4',700);
insert into spj values('s1','p2','j2',100);
insert into spj values('s2','p3','j1',400);
insert into spj values('s2','p3','j2',200);
insert into spj values('s2','p3','j4',500);
insert into spj values('s2','p3','j5',400);
insert into spj values('s2','p5','j1',400);
insert into spj values('s2','p5','j2',100);
insert into spj values('s3','p1','j1',200);
insert into spj values('s3','p3','j1',200);
insert into spj values('s4','p5','j1',100);
insert into spj values('s4','p6','j3',300);
insert into spj values('s4','p6','j4',200);
insert into spj values('s5','p2','j4',100);
insert into spj values('s5','p3','j1',200);
insert into spj values('s5','p6','j2',200);
insert into spj values('s5','p6','j4',500);
select distinct spj.pno from spj,s where spj.sno=s.sno and s.city='上海';
select distinct j.jname from spj,j,p where spj.jno=j.jno and spj.pno=p.pno and p.color='红';
select distinct t1.cno,t1.cname,t1.cpno,t1.ccredit from course t1,course t2 where t1.cno=t2.cno and t1.ccredit=4;
select distinct j.jno,jname,city,spj.qty,spj.sno,spj.pno from j,spj where j.jno=spj.jno;
