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.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