数据库习题及答案(共4页).doc

精选优质文档-倾情为你奉上实验设有以下关系模式：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(2) PRIMARY KEY NOT NULL, SNAME char(2) NOT NULL, CITY char(4) NOT NULL) CREATE TABLE P (PNO char(2) PRIMARY KEY NOT NULL, PNAME char(3) NOT NULL, COLOR char(2) NOT NULL, WEIGHT int NOT NULL)CREATE TABLE J (JNO char(2) PRIMARY KEY NOT NULL, JNAME char(3) NOT NULL, CITY char(4) NOT NULL)CREATE TABLE SPJ (SNO char(2) NOT NULL, PNO char(2) NOT NULL, JNO char(2) NOT NULL, QTY int NOT NULL, CONSTRAINT spj_id PRIMARY KEY(SNO,PNO,JNO), FOREIGN KEY (SNO) REFERENCES S(SNO), FOREIGN KEY (PNO) REFERENCES P(PNO), FOREIGN KEY (JNO) REFERENCES J(JNO)2.INSERT S (SNO, SNAME, CITY) VALUES ('S1', 'N1', '上海'),('S2','N2','北京'),('S3','N3','北京'),('S4','N4','上海'),('S5','N5','南京') INSERT P (PNO, PNAME,COLOR, WEIGHT) VALUES ('P1', 'PN1', '红',12),('P2', 'PN2', '绿',18),('P3', 'PN3', '蓝',20),('P4', 'PN4', '红',13),('P5', 'PN5', '蓝',11),('P6', 'PN6', '红',15)INSERT J (JNO, JNAME, CITY) VALUES ('J1', 'JN1', '上海'),('J2','JN2','广州'),('J3','JN3','南京'),('J4','JN4','南京'),('J5','JN5','上海'),('J6','JN6','武汉'),('J7','JN7','上海')INSERT SPJ (SNO, PNO,JNO, QTY) VALUES ('S1', 'P1', 'J1',200),('S1', 'P1', 'J4',700),('S2', 'P3', 'J1',400),('S2', 'P3', 'J2',200),('S2', 'P3', 'J3',200),('S2', 'P3', 'J4',500),('S2', 'P3', 'J5',600),('S2', 'P3', 'J6',400),('S2', 'P3', 'J7',800),('S2', 'P5', 'J2',100),('S3', 'P3', 'J1',200),('S3', 'P4', 'J2',500),('S4', 'P6', 'J3',300),('S4', 'P6', 'J7',300),('S5', 'P2', 'J2',200),('S5', 'P2', 'J4',100),('S5','P5','J5',500),('S5', 'P5', 'J7',100),('S5', 'P6', 'J2',200),('S5', 'P1', 'J4',1000),('S5', 'P3', 'J4',1200),('S5', 'P4', 'J4',800),('S5', 'P5', 'J4',400),('S5', 'P6', 'J4',500)3(1) SELECT * FROM J3(2) SELECT * FROM J WHERE CITY='上海'3(3) SELECT PNO FROM P WHERE WEIGHT <= ALL(SELECT WEIGHT FROM P)3(4) SELECT SNO FROM SPJ WHERE JNO='J1'3(5) SELECT SNO FROM SPJ WHERE JNO='J1'AND PNO='P1'3(6) SELECT JNAME FROM J,SPJ WHERE SNO='S1'AND SPJ.JNO=J.JNO3(7) SELECT DISTINCT COLOR FROM P,SPJ WHERE SNO='S1'AND SPJ.PNO=P.PNO3(8) SELECT DISTINCT SNO FROM SPJ WHERE JNO='J1'OR JNO='J2'3(9) SELECT DISTINCT SNO FROM P,SPJ WHERE JNO='J1'AND SPJ.PNO=P.PNO AND P.COLOR='红'3(10) SELECT DISTINCT SNO FROM J,SPJ WHERE J.CITY='上海'AND J.JNO=SPJ.JNO3(11) SELECT DISTINCT SNO FROM J,P,SPJ WHERE (J.CITY='上海'OR J.CITY='北京')AND P.COLOR='红'AND J.JNO=SPJ.JNO AND P.PNO=SPJ.PNO3(12) SELECT DISTINCT PNO FROM S,J,SPJ WHERE S.CITY=J.CITY AND S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO3(13) SELECT DISTINCT PNO FROM S,J,SPJ WHERE S.CITY='上海'AND J.CITY='上海' AND S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO3(14) SELECT DISTINCT J.JNO FROM S,J,SPJ WHERE S.CITY <>J.CITY AND S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO3(15) SELECT DISTINCT JNO FROM J WHERE JNO NOT IN(SELECT JNO FROM S,SPJ WHERE SPJ.SNO=S.SNO AND S.CITY='上海')3(16) SELECT DISTINCT SPJ.SNO FROM SPJ WHERE PNO IN(SELECT PNO FROM SPJ WHERE SPJ.SNO IN(SELECT SPJ.SNO FROM P,S,SPJ WHERE P.COLOR='红'AND P.PNO=SPJ.PNO AND S.SNO=SPJ.SNO)3(17) SELECT DISTINCT JNO FROM SPJ WHERE SNO='S1'3(18) SELECT DISTINCT S.CITY,PNO,J.CITY FROM S,J,SPJ WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO3(19) SELECT SNO FROM SPJ GROUP BY SNO HAVING COUNT(DISTINCT PNO)=13(20) SELECT DISTINCT PNO FROM SPJ SPJX WHERE NOT EXISTS (SELECT * FROM J JY WHERE JY.CITY='上海'AND NOT EXISTS(SELECT * FROM SPJ SPJZ WHERE SPJZ.PNO=SPJX.PNO AND SPJZ.JNO=JY.JNO)3(21) SELECT DISTINCT JNO FROM SPJ SPJX WHERE NOT EXISTS (SELECT * FROM SPJ SPJY WHERE SPJY.SNO='S1'AND NOT EXISTS (SELECT * FROM SPJ SPJZ WHERE SPJZ.JNO=SPJX.JNO AND SPJZ.SNO=SPJY.SNO) )3(22) UPDATE P SET COLOR='橙'WHERE COLOR ='红'3(23) DELETE FROM SPJ WHERE SPJ.PNO IN (SELECT PNO FROM P WHERE P.COLOR='红') DELETE FROM P WHERE COLOR='红'3(24) SELECT SUM(ALL QTY)FROM SPJ WHERE SNO='S1'AND PNO='P1'3(25) SELECT COUNT(DISTINCT SNO)FROM SPJ GROUP BY JNO3(26) SELECT JNO FROM SPJ GROUP BY JNO HAVING SUM(ALL QTY)>1000专心-专注-专业