数据库实验-第二章课后题-SPJ表-SQL数据查询.doc

《数据库实验-第二章课后题-SPJ表-SQL数据查询.doc》由会员分享，可在线阅读，更多相关《数据库实验-第二章课后题-SPJ表-SQL数据查询.doc（6页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、如有侵权，请联系网站删除，仅供学习与交流数据库实验-第二章课后题-SPJ表-SQL数据查询【精品文档】第 6 页针对数据库系统概论第四版第二章SQL实现-数据库系统原理表以及数据create table S ( SNO varchar(8) not null primary key, SNAME nvarchar(20) unique, STATUS int , CITY nvarchar(20);create table P ( PNO varchar(8) not null primary key, PNAME nvarchar(20), COLOR nvarchar(10), WEIGH

2、T int);create table J( JNO varchar(8) not null primary key, JNAME nvarchar(30), CITY nvarchar(20);create table SPJ ( SNO varchar(8), PNO varchar(8), JNO varchar(8), QTY int , primary key (SNO,PNO,JNO), foreign key (SNO) references S(SNO), foreign key (PNO) references P(PNO), foreign key (JNO) refere

3、nces J(JNO);delete from s;delete from J;delete from P;delete from SPJ;-S表：供应商表；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,上海);-P表：零件表：insert into P values(P1,螺母,红,12);insert i

4、nto 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);-J表：工程项目表：insert into J values(J1,三建,北京);insert into J values(J2,一汽,长春);insert into J values(J3,弹 簧 厂,天津);insert into J values(J4,造 船 厂,天津);i

5、nsert into J values(J5,机 车 厂,唐山);insert into J values(J6,无线电厂,常州);insert into J values(J7,半导体厂,南京);-SPJ表：供应情况表：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);inse

6、rt 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 i

7、nto 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);insert into SPJ values(S1,P1,J2,5000);-、求供应工程J1零件的供应商的号码SNO：select distinct SNO 供应商号码

8、from SPJ where JNO = J1;-、求供应工程J1零件P1的供应商号码:SNO:select distinct SNO 供应商号码 from SPJ where JNO = J1 and PNO = P1;-、求供应工程J1零件为红色的供应商号码SNO：select distinct SNO 供应商号码 from SPJ where JNO = J1 and exists(select * from P where SPJ.PNO = P.PNO and p.COLOR = 红 );-、求没有使用天津供应商生产的红色零件的工程号JNO：-select distinct SPJ.

9、SNO from S,P,SPJ where S.SNO = SPJ.SNO and P.PNO = SPJ.PNO and P.COLOR = 红 and S.CITY = 天津;select distinct JNO 工程号 from SPJ where SNO not in (select distinct SPJ.SNO from S,P,SPJ where S.SNO = SPJ.SNO and P.PNO = SPJ.PNO and P.COLOR = 红 and S.CITY = 天津 );-、求至少用了供应商S1供应的所有零件的工程号JNO：-也就是说：不存在一个JNO（工程号

10、）,它S1供应的零件它没有使用。 -或者说：不存在一个JNO（工程号）,不存在某个S1供应的零件，在此工程的零件列表中不存在。select * from SPJ;-供应商S1供应的所有零件为：select distinct PNO from SPJ where SNO = S1;select distinct JNO from SPJ where PNO=P1 and JNO in (select JNO from SPJ where PNO = P2);-第一个not exists对应的是不存在，第二个not exists对应的是供应商S1供应的零件它没有使用；select distinct

11、 JNO from SPJ where not exists( select * from SPJ SPJ1 where SNO=S1 and not exists ( select * from SPJ SPJ2 where SPJ2.PNO = SPJ1.PNO and SPJ2.JNO = SPJ.JNO );-第三章：第5题：-（1）找出所有供应商的姓名和所在城市；select SNAME,CITY from S;-（2）找出所有零件的名称、颜色、重量；select PNAME,COLOR,WEIGHT from P;-（3）找出使用供应商S1供应的所有零件的工程号码；select d

12、istinct JNO from SPJ where not exists( select * from SPJ SPJ1 where SNO = S1 and not exists( select * from SPJ SPJ2 where SPJ2.PNO = SPJ1.PNO and SPJ2.JNO = SPJ.JNO );-（4）找出工程项目J2使用的各种零件的名称和重量；select P.PNAME,P.WEIGHT from SPJ,P where JNO = J2 and P.PNO = SPJ.PNO;-（5）找出上海厂商供应的所有零件号码；select distinct P

13、NO from SPJ,S where S.CITY = 上海 and SPJ.SNO = S.SNO-（6）找出使用上海产的零件的工程名称；select distinct J.JNAME from SPJ,J where J.JNO = SPJ.JNO and SPJ.SNO in ( select SNO from S where CITY = 上海);-（7）找出没有使用天津产的零件的工程号码；select distinct JNO from SPJ where exists ( select * from S where S.SNO = SPJ.SNO and S.CITY != 天津

14、);-（8)把全部红色零件的颜色改为蓝色；-select * from P;update P set COLOR = 蓝 where COLOR = 红;-（9）由S5供给J4的零件P6改为由S3供应，请做出必要的修改。-select * from SPJ where SNO = S3 and PNO = P6 and JNO = J4;update SPJ set SNO = S3 where SNO = S5 and PNO = P6 and JNO = J4;-（10）从供应商关系中删除S2的记录，并从供应情况关系中删除响应的记录；delete from SPJ where SNO = S2;delete from S where SNO = S2;-（11）请将（S2，J6，P4，200）插入供应情况关系表中。insert into SPJ values(S2,P4,J6,200);-select * from SPJ;