SQL习题及答案

实验设有以下关系模式：S(SNO，SNAME，CITY)其中，S表示别SUPPLIER(供应者)，SNO为供应者代号，SNAME为供应者的名字，CITY为供应商所在的城市主键为SNO。P(PNO，PNAME，COLOR，WEIGHT)其中，P表示PART(零件)，PNO为零件代号，PNAME为零件名，COLOR为零件颜色，WEIGHT为零件重量，主键为PNO。J(JNO，JNAME，CITY)其中，J表示JOB(工程)，JNO为工程编号，JNAME为工程名，CITY为工程所在城市，主键为JNO。SPJ(SNO，PNO，JNO，QTY)其中，SPJ表示供应关系，SNO是为指定工程提供零件的供应者代号，PNO是所提供的零件代号，JNO为工程编号，QTY表示提供的零件数量，主键为(SNO，PNO，JNO)，外部键分别为SNO，PNO，JNO。试做以下各题：1、用SQL的DDL语言创建S，P，J，SPJ四个基本表。2、按照下面表格提供的数据，用SQL的插入语句插入所有记录。3、给出下列各题的查询、存储等操作的语句序列，并且上机验证结果。1)取出所有工程的全部细节；2)取出所在城市为上海的所有工程的全部细节；3)取出重量最轻的那些零件的号码；4)取出为工程J1提供零件的供应者的代号；5)取出为工程J1提供零件P1的供应者的代号；6)取出由供应者S1提供零件的工程的名称；7)取出由供应者S1提供的零件的颜色；8)取出为工程J1和J2提供零件的供应者的代号；9)取出为工程J1提供红色零件的供应者的代号；10)取出为所在城市为上海的工程提供零件的供应者的代号；11)取出为所在城市为上海或北京的工程提供红色零件的供应者的代号；12)取出供应者与工程所在城市相同的供应者提供的零件的代号；13)取出上海的供应者提供给上海的任一工程的零件的代号；14)取出至少有一个和工程不在同一城市的供应者提供零件的工程的代号；15)取出上海供应者不提供任何零件的工程的代号；16)取出这样一些供应者的代号，他们能够提供至少一种由红色零件的供应者提供的零件；17)取出由供应者S1提供零件的工程的代号；18)取出所有这样的三元组CITY，PNO，CITY，使得第一个城市的供应者为第二个城市的工程提供零件；19)取出为所有工程提供同样零件的供应者的代号；20)取出提供给上海的所有工程的零件代号；21)取出至少需要供应者S1提供的所有零件的工程的代号；22)把所有红色零件改成橙色；23)删除所有红色零件和对应的SPJ记录；24)取出供应者Sl提供的代号为P1的零件总数；25)取出每个工程的供应商数；26)取出所需的零件总数大于1000的工程代号；1. CREATE TABLE S( SNO CHAR(5) PRIMARY KEY, SNAME CHAR(5), CITY CHAR(10);CREATE TABLE P( PNO CHAR(5) PRIMARY KEY, PNAME CHAR(5), COLOR CHAR(5), WEIGHT INT);CREATE TABLE J( JNO CHAR(5) PRIMARY KEY, JNAME CHAR(5), CITY CHAR(10);CREATE TABLE SPJ( SNO CHAR(5), PNO CHAR(5), JNO CHAR(5), QTY INT, CONSTRAINT PK_SPJ PRIMARY KEY(SNO,PNO,JNO), CONSTRAINT FK_SNO FOREIGN KEY(SNO)REFERENCES S(SNO), CONSTRAINT FK_PNO FOREIGN KEY(PNO)REFERENCES P(PNO), CONSTRAINT FK_JNO FOREIGN KEY(JNO)REFERENCES J(JNO);2.SELECT * FROM J;3.SELECT * FROM J WHERE CITY=上海;4.SELECT PNO FROM P WHERE WEIGHT=(SELECT MIN(WEIGHT) FROM P);5.SELECT DISTINCT SNO FROM SPJ WHERE JNO=J1;6.SELECT SNO FROM SPJ WHERE JNO=J1 AND PNO=P1;7.SELECT DISTINCT JNAME FROM J,SPJ WHERE J.JNO=SPJ.JNO AND SPJ.SNO=S1;8.SELECT DISTINCT COLOR FROM P,SPJ WHERE P.PNO=SPJ.PNO AND SPJ.SNO=S1;9.SELECT X.SNO FROM SPJ X WHERE X.JNO=J1AND EXISTS (SELECT * FROM SPJ Y WHERE Y.SNO=X.SNO AND Y.JNO=J2);10.SELECT DISTINCT SNO FROM SPJ,P WHERE SPJ.JNO=J1 AND SPJ.PNO=P.PNO AND P.COLOR=红;11.SELECT DISTINCT SNO FROM SPJ,J WHERE SPJ.JNO=J.JNO AND J.CITY=上海;12.SELECT DISTINCT SPJ.SNO FROM SPJ,J,P WHERE SPJ.JNO=J.JNO AND SPJ.PNO=P.PNO AND P.COLOR =红 AND (J.CITY=上海 OR J.CITY= 北京) ;13.SELECT DISTINCT P.PNO FROM S,J,P,SPJ WHERE P.PNO=SPJ.PNO AND SPJ.SNO=S.SNO AND SPJ.JNO=J.JNO AND S.CITY=J.CITY;14.SELECT DISTINCT P.PNO FROM S,J,P,SPJ WHERE P.PNO=SPJ.PNO AND SPJ.SNO=S.SNO AND SPJ.JNO=J.JNO AND S.CITY=上海 AND J.CITY=上海;15.SELECT DISTINCT JNO FROM J WHERE EXISTS (SELECT * FROM S,SPJ WHERE SPJ.SNO=S.SNO AND J.JNO=SPJ.JNO AND J.CITYS.CITY);16.SELECT DISTINCT JNO FROM J WHERE NOT EXISTS (SELECT * FROM S,SPJ WHERE J.JNO=SPJ.JNO AND SPJ.SNO=S.SNO AND S.CITY=上海);17.SELECT DISTINCT SNO FROM SPJ WHERE PNO IN(SELECT DISTINCT PNO FROM SPJ WHERE SNO IN(SELECT DISTINCT SNO FROM SPJ,PWHERE SPJ.PNO=P.PNO AND P.COLOR=红);18.SELECT DISTINCT JNO FROM SPJ WHERE SNO=S1;19.SELECT DISTINCT S.CITY,SPJ.PNO,J.CITY FROM S,J,SPJ WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITYJ.CITY;20.SELECT DISTINCT SNO FROM S WHERE NOT EXISTS(SELECT * FROM J WHERE NOT EXISTS(SELECT * FROM P,SPJ WHERE S.SNO=SPJ.SNO AND SPJ.PNO=P.PNO AND J.JNO= SPJ.JNO);21.SELECT DISTINCT PNO FROM P WHERE NOT EXISTS(SELECT * FROM J WHERE J.CITY=上海 AND NOT EXISTS(SELECT * FROM SPJ WHERE SPJ.PNO=P.PNO AND SPJ.JNO=J.JNO);22.SELECT DISTINCT JNO FROM SPJ SX WHERE NOT EXISTS(SELECT * FROM SPJ SY WHERE SY.SNO=S1 AND NOT EXISTS(SELECT * FROM SPJ SZ WHERE SY.PNO=SZ.PNO AND SX.JNO=SZ.JNO);23.UPDATE P SET COLORE=橙 WHERE COLORE=红;24.DELETE FROM SPJ WHERE SPJ.PNO IN (SELECT PNO FROM P WHERE COLOR=红);DELETE FROM P WHERE COLOR=红;25.SELECT SUM(QTY) FROM SPJ WHERE SNO=S1 AND PNO=P1;26.SELECT JNO,COUNT(DISTINCT SNO) FROM SPJ GROUP BY JNO;27.SELECT JNO FROM SPJ GROUP BY JNO HAVING SUM(QTY)1000;1./创建s表CREATE TABLE S(SNO CHAR(8) NOT NULL UNIQUE,SNAME VARCHAR(20) NOT NULL UNIQUE,CITY VARCHAR(8),CONSTRAINT CS PRIMARY KEY(SNO);/创建p表CREATE TABLE P(PNO CHAR(8) NOT NULL UNIQUE,PNAME VARCHAR(20) NOT NULL UNIQUE,COLOR CHAR(1),WEIGHT INT,CONSTRAINT CP PRIMARY KEY(PNO);/创建j表CREATE TABLE J(JNO CHAR(8) NOT NULL UNIQUE,JNAME VARCHAR(20) NOT NULL UNIQUE,CITY VARCHAR(8),CONSTRAINT CJ PRIMARY KEY(JNO);/创建spj表CREATE TABLE SPJ(SNO CHAR(8) NOT NULL NUIQUE,PNO CHAR(8) NOT NULL UNIQUE,JNO CHAR(8) NOT NULL UNIQUE,QTY INT,CONSTRAINT CSPJ PRIMARY KEY(SON,PNO,JNO),CONSTRAINT CSPJ FOREING KEY(SNO) REFERENCES S(SNO),CONSTRAINT CSPJ FOREING KEY(PNO) REFERENCES P(PNO),CONSTRAINT CSPJ FOREING KEY(JNO) REFERENCES J(JNO);2./向s表插入数据INSERT INTO S(SNO,SNAME,CITY) VALUES(S1,N1,上海);INSERT INTO S(SNO,SNAME,CITY) VALUES(S2,N2,北京);INSERT INTO S(SNO,SNAME,CITY) VALUES(S3,N3,北京);INSERT INTO S(SNO,SNAME,CITY) VALUES(S4,N4,上海);INSERT INTO S(SNO,SNAME,CITY) VALUES(S5,N5,南京);/向p表插入数据INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES(P1,PN1,红,12);INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES(P2,PN2,绿,18);INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES(P3,PN3,蓝,20);INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES(P4,PN4,红,13);INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES(P5,PN5,蓝,11);INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES(P6,PN6,红,15);/向j表插入数据INSERT INTO J(JNO,JNAME,CITY) VALUES(J1,JN1,上海);INSERT INTO J(JNO,JNAME,CITY) VALUES(J2,JN2,广州);INSERT INTO J(JNO,JNAME,CITY) VALUES(J3,JN3,南京);INSERT INTO J(JNO,JNAME,CITY) VALUES(J4,JN4,南京);INSERT INTO J(JNO,JNAME,CITY) VALUES(J5,JN5,上海);INSERT INTO J(JNO,JNAME,CITY) VALUES(J6,JN6,武汉);INSERT INTO J(JNO,JNAME,CITY) VALUES(J7,JN7,上海);/向表spj中插入数据INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S1,P1,J1,200);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S1,P1,J4,700);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S2,P3,J1,400);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S2,P3,J2,200);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S2,P3,J3,200);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S2,P3,J4,500);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S2,P3,J5,600);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S2,P3,J6,400);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S2,P3,J7,800);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S2,P5,J2,100);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S3,P3,J1,200);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S3,P4,J2,500);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S4,P6,J7,300);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S5,P2,J2,200);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S5,P2,J4,100);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S5,P5,J5,500);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S5,P5,J7,100);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S5,P6,J2,200);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S5,P1,J4,1000);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S5,P3,J4,1200);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S5,P4,J4,800);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S5,P5,J4,400);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(S5,P6,J4,500);3.1SELECT * FROM J;3.2SELECT * FROM J WHERE CITY LIKE 上海;3.3SELECT PNO FROM P WHERE WEIGHT=(SELECT MAX(WEIGHT)FROM P);3.4SELECT SNO FROM SPJ WHERE JNO IN(SELECT JNO FROM J WHERE JNO=J1);3.5SELECT SNO FROM SPJ WHERE JNO=J1 AND PNO=P1;3.6SELECT JNAME FROM J,SPJ WHERE J.JNO=SPJ.JNO AND SPJ.SNO=S1;3.7SELECT COLOR FROM P,SPJ WHERE P.PNO=SPJ.PNO AND SPJ.SNO=S1;3.8SELECT DISTINCT SNO FROM SPJ WHERE JNO=J1 OR JNO=J2;3.9SELECT SNO FROM SPJ,P WHERE P.PNO=SPJ.PNO AND SPJ.JNO=J1 AND P.COLOR=红3.10SELECT SNO FROM SPJ,J WHERE J.JNO=SPJ.JNO AND J.CITY=上海;3.11SELECT SNO FROM SPJ,J,P WHERE SPJ.PNO=P.PNO AND J.JNO=SPJ.JNO AND P.COLOR=红;3.12SELECT SPJ.PNO FROM SPJ,S,JWHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITY=J.CITY;3.13SELECT SPJ.PNO FROM SPJ,S,JWHERE S.SNO=SPJ.SNO AND J,JNO=SPJ.JNO AND S.CITY=上海 AND J.CITY=上海;3.14 SELECT SPJ.JNO FROM SPJ,S,JWHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITYJ.CITY;3.15SELECT JNO FROM SPJ WHERE JNO NOT IN(SELECT SPJ.JNO FROM S,SPJ WHERE S.SNO=SPJ.SNO AND S.CITY =上海);3.16SELECT SPJ.SNO FROM SPJ,P WHERE SPJ.PNO IN(SELECT SPJ.PNO FROM SPJ,S,P WHERE S.SNO=SP