數(shù)據(jù)庫實(shí)驗(yàn)-第二章課后題-SPJ表-SQL數(shù)據(jù)查詢

1、精品好資料學(xué)習(xí)推薦針對(duì)數(shù)據(jù)庫系統(tǒng)概論第四版第二章SQL實(shí)現(xiàn)-數(shù)據(jù)庫系統(tǒng)原理表以及數(shù)據(jù)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), WEIGHT int);create table J( JNO varchar(8) not null

2、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) references J(JNO);delete from s;delete from J;delete

3、from P;delete from SPJ;-S表：供應(yīng)商表；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 into P values(P2,螺絲,綠,17);insert into P values(P

4、3,螺絲刀,藍(lán),14);insert into P values(P4,螺絲刀,紅,14);insert into P values(P5,凸輪,藍(lán),40);insert into P values(P6,齒輪,紅,30);-J表：工程項(xiàng)目表：insert into J values(J1,三建,北京);insert into J values(J2,一汽,長(zhǎng)春);insert into J values(J3,彈 簧 廠,天津);insert into J values(J4,造 船 廠,天津);insert into J values(J5,機(jī) 車 廠,唐山);insert into J

5、values(J6,無線電廠,常州);insert into J values(J7,半導(dǎo)體廠,南京);-SPJ表：供應(yīng)情況表：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 SP

6、J 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 va

7、lues(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);-、求供應(yīng)工程J1零件的供應(yīng)商的號(hào)碼SNO：select distinct SNO 供應(yīng)商號(hào)碼 from SPJ where JNO = J1;-、求供應(yīng)工程J1零件P1的供應(yīng)商號(hào)碼:SNO

8、:select distinct SNO 供應(yīng)商號(hào)碼 from SPJ where JNO = J1 and PNO = P1;-、求供應(yīng)工程J1零件為紅色的供應(yīng)商號(hào)碼SNO：select distinct SNO 供應(yīng)商號(hào)碼 from SPJ where JNO = J1 and exists(select * from P where SPJ.PNO = P.PNO and p.COLOR = 紅 );-、求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號(hào)JNO：-select distinct SPJ.SNO from S,P,SPJ where S.SNO = SPJ.SNO and P.PN

9、O = SPJ.PNO and P.COLOR = 紅 and S.CITY = 天津;select distinct JNO 工程號(hào) 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 = 天津 );-、求至少用了供應(yīng)商S1供應(yīng)的所有零件的工程號(hào)JNO：-也就是說：不存在一個(gè)JNO（工程號(hào)）,它S1供應(yīng)的零件它沒有使用。 -或者說：不存在一個(gè)JNO（工程號(hào)）,不存在某個(gè)S1供應(yīng)的零

10、件，在此工程的零件列表中不存在。select * from SPJ;-供應(yīng)商S1供應(yīng)的所有零件為：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);-第一個(gè)not exists對(duì)應(yīng)的是不存在，第二個(gè)not exists對(duì)應(yīng)的是供應(yīng)商S1供應(yīng)的零件它沒有使用；select distinct JNO from SPJ where not exists( select * from S

11、PJ SPJ1 where SNO=S1 and not exists ( select * from SPJ SPJ2 where SPJ2.PNO = SPJ1.PNO and SPJ2.JNO = SPJ.JNO );-第三章：第5題：-（1）找出所有供應(yīng)商的姓名和所在城市；select SNAME,CITY from S;-（2）找出所有零件的名稱、顏色、重量；select PNAME,COLOR,WEIGHT from P;-（3）找出使用供應(yīng)商S1供應(yīng)的所有零件的工程號(hào)碼；select distinct JNO from SPJ where not exists( select *

12、 from SPJ SPJ1 where SNO = S1 and not exists( select * from SPJ SPJ2 where SPJ2.PNO = SPJ1.PNO and SPJ2.JNO = SPJ.JNO );-（4）找出工程項(xiàng)目J2使用的各種零件的名稱和重量；select P.PNAME,P.WEIGHT from SPJ,P where JNO = J2 and P.PNO = SPJ.PNO;-（5）找出上海廠商供應(yīng)的所有零件號(hào)碼；select distinct PNO from SPJ,S where S.CITY = 上海 and SPJ.SNO = S

13、.SNO-（6）找出使用上海產(chǎn)的零件的工程名稱；select distinct J.JNAME from SPJ,J where J.JNO = SPJ.JNO and SPJ.SNO in ( select SNO from S where CITY = 上海);-（7）找出沒有使用天津產(chǎn)的零件的工程號(hào)碼；select distinct JNO from SPJ where exists ( select * from S where S.SNO = SPJ.SNO and S.CITY != 天津);-（8)把全部紅色零件的顏色改為藍(lán)色；-select * from P;update P set COLOR = 藍(lán) where COLOR = 紅;-（9）由S5供給J4的零件P6改為由S3供應(yīng)，請(qǐng)做出必要的修改。-select * from SPJ where SNO = S3 and PNO = P6 and JNO = J4;update SPJ set SNO = S3 where SNO = S5 and