SQL语句练习05

create table LYL_116_week7s(SNO varchar(3),SNAME varchar(10),
CITY varchar(8),primary key(SNO));

insert into LYL_116_week7s values('S1','精益','天津')
insert into LYL_116_week7s values('S2','盛锡','北京')
insert into LYL_116_week7s values('S3','东方红','北京')
insert into LYL_116_week7s values('S4','丰盛泰','天津')
insert into LYL_116_week7s values('S5','为民','上海')

create table LYL_116_week7p(PNO varchar(3),PNAME 
varchar(10),COLOR varchar(2),WEIGHT int,primary key(PNO))

insert into LYL_116_week7p values('P1','螺母','红',12)
insert into LYL_116_week7p values('P2','螺栓','绿',17)
insert into LYL_116_week7p values('P3','螺丝刀','蓝',14)
insert into LYL_116_week7p values('P4','螺丝刀','红',14)
insert into LYL_116_week7p values('P5','凸轮','蓝',40)
insert into LYL_116_week7p values('P6','齿轮','红',30)

create table LYL_116_week7j(JNO varchar(3),JNAME 
varchar(10),JCITY varchar(8),primary key(JNO))

insert into LYL_116_week7j values('J1','三建','北京')
insert into LYL_116_week7j values('J2','一气','长春')
insert into LYL_116_week7j values('J3','弹簧厂','天津')
insert into LYL_116_week7j values('J4','造船厂','天津')
insert into LYL_116_week7j values('J5','机车厂','唐山')
insert into LYL_116_week7j values('J6','无线电厂','常州')
insert into LYL_116_week7j values('J7','半导体厂','南京')

create table LYL_116_week7spj(SNO varchar(3),PNO varchar(3),
JNO varchar(3),QTY int foreign key(SNO) references LYL_116_week7s(SNO),
foreign key(PNO) references LYL_116_week7p(PNO),
foreign key(JNO) references LYL_116_week7j(JNO))

insert into LYL_116_week7spj values('S1','P1','J1',200)
insert into LYL_116_week7spj values('S1','P1','J3',100)
insert into LYL_116_week7spj values('S1','P1','J4',700)
insert into LYL_116_week7spj values('S1','P2','J2',100)
insert into LYL_116_week7spj values('S2','P3','J1',400)
insert into LYL_116_week7spj values('S2','P3','J2',200)
insert into LYL_116_week7spj values('S2','P3','J4',500)
insert into LYL_116_week7spj values('S2','P3','J5',400)
insert into LYL_116_week7spj values('S2','P5','J1',400)
insert into LYL_116_week7spj values('S2','P5','J2',100)
insert into LYL_116_week7spj values('S3','P1','J1',200)
insert into LYL_116_week7spj values('S3','P3','J1',200)
insert into LYL_116_week7spj values('S4','P5','J1',100)
insert into LYL_116_week7spj values('S4','P6','J3',300)
insert into LYL_116_week7spj values('S4','P6','J4',200)
insert into LYL_116_week7spj values('S5','P2','J4',100)
insert into LYL_116_week7spj values('S5','P3','J1',200)
insert into LYL_116_week7spj values('S5','P6','J2',200)
insert into LYL_116_week7spj values('S5','P6','J4',500)

select s.SNAME'供应商名称',s.CITY'所在城市'
from LYL_116_week7s s

select p.PNAME'零件名称',p.COLOR'零件颜色',
p.WEIGHT'零件重量' from LYL_116_week7p p

select spj.JNO'S1所供应零件的工程号码' 
from LYL_116_week7spj spj where spj.SNO='S1'

select p.PNAME,spj.QTY from 
LYL_116_week7spj spj,LYL_116_week7p p 
where spj.JNO = 'J2' and spj.PNO = p.PNO;

select spj.PNO'上海厂商供应的零件号码' from
LYL_116_week7s s,LYL_116_week7spj spj
where s.CITY = '上海' and spj.SNO = s.SNO

select j.JNAME from 
LYL_116_week7s s,LYL_116_week7spj spj,LYL_116_week7j j
where s.CITY='上海' and s.SNO=spj.SNO and spj.JNO=j.JNO

select spj2.JNO from LYL_116_week7spj spj2 
where spj2.JNO not in(
select spj1.JNO from LYL_116_week7spj spj1
where spj1.SNO in 
(select s1.SNO from LYL_116_week7s s1 where s1.CITY='天津'))

update LYL_116_week7p set COLOR = '蓝' where COLOR = '红'
select * from LYL_116_week7p

update LYL_116_week7spj set SNO = 'S3' 
where SNO = 'S5' and PNO = 'P6' and JNO = 'J4'

select * from LYL_116_week7spj spj
where spj.SNO='S5' or spj.SNO='S3'

delete from LYL_116_week7spj where PNO in
(select p.PNO from LYL_116_week7p p
where p.COLOR = '绿')