数据库系统概论课后SQL语句习题  P70. 6 P130. 5

数据库系统概论课后SQL语句习题
 P70. 6
P130. 5

(1)求供应工程J1零件的供应商号码SNO

select Sno from SPJ where JNO = ‘J1’  

(2)求供应工程J1零件P1的供应商号码SNO

select SNo from SPJ where Jno = ‘J1’ and PNO = ‘P1’

(3)求供应工程J1零件为红色的供应商号码SNO

select SNO from SPJ where PNO IN (select PNO from P where COLOR='')

(4)求没有使用天津供应商生产的红色零件的工程号JNO

select * from SPJ where SNO IN(select SNO from S where CITY='天津') 
and PNO IN(select PNO from P where COLOR='')

(5)求至少使用了供应商S1所供应的全部零件的工程号JNO

select * from SPJ where exists(select * from SPJ where SNO='S1')

P130. 5

(1)找出所有供应商的姓名和所在城市

select SNAME,CITY from S

(2)找出所有零件的名称、颜色、重量

select PNAME,COLOR,WEIGHT from P

(3)找出使用供应商S1所供应零件的工程号码

select JNO from SPJ where SNO = 'S1'

(4)找出工程项目J2使用的各种零件的名称及其数量

select P.PNAME AS NAME,count(SPJ.JNO) AS NUM from SPJ,P where SPJ.JNO='J2' and SPJ.PNO = P.PNO group by P.PNAME

(5)找出上海厂商供应的所有零件号码

select distinct PNO from SPJ where SNO IN(select SNO from S where CITY='上海')

(6)找出使用上海产的零件的工程名称

select distinct JNO from SPJ where SNO =(select SNO from S where CITY='上海')

(7)找出没有使用天津产的零件的工程号码

select distinct JNO from SPJ where SNO not in(select SNO from S where CITY='天津')

(8)把全部红色零件的颜色改成蓝色

update P set COLOR = ‘蓝‘ where COLOR = ‘红’

(9)由S5供给J4的零件P6改为由S3供应,请做出必要的修改

update SPJ set SNO='S3' where SNO='S5' AND PNO='P6'

(10)从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录

delete from SPJ where SNO='S2'

delete from S where SNO='S2'

(11)请将(S2,J6,P4,200)插入供应情况关系

insert into SPJ(SNO,JNO,PNO,QTY) values('S2','J6','P4',200)