样例试卷数据库1

样卷



现有如下表结构，其中包括供应商表，零件表，工程表，供应情况表

--供应商表
create table s 
(sno char(10) primary key, --供应商号
 sname char(20), --供应商名
 city char(20))  --供应商所在城市

--零件表
create table p
(pno char(10) primary key,  --零件号
 pname char(20),  --零件名
 color char(10),  --零件颜色
 weight smallint)  --零件重量

--工程表
create table j
(jno char(10) primary key,  --工程号
 jname char(20),  --工程名
 city char(20))  --工程所在城市
 
 --工程供应情况表
create table spj
(sno char(10),  --供应商号
 pno char(10),  --零件号
 jno char(10),  --工程号
 qty smallint)  --使用数量

--插入数据
insert into s values('s1','精益','天津')
insert into s values('s2','盛锡','北京')
insert into s values('s3','东方红','北京')
insert into s values('s4','丰泰盛','天津')
insert into s values('s5','为民','上海')

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)

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','半导体厂','南京')

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)