第3章部分练习和解决方案.doc

第三章 习题 4 .针对上题中建立的四个表试用SQL语言完成第2章习题5中的查询。 答案： （1）求供应工程J1零件的供应商号码SNO； SELECT SNO FROM SPJ WHERE JNO=‘J1’； （2）求供应工程J1零件P1的供应商号码SNO； SELECT SNO FROM SPJ WHERE JNO=‘J1’ AND PNO=‘P1’； （3）求供应工程J1零件为红色的供应商号码SNO； SELECT SNO /*这是嵌套查询*/ FROM SPJ WHERE JNO=‘J1’AND PNO IN /*找出红色的零件的零件号码PNO */ (SELECT PNO FROM P /*从P表中找*/ WHERE COLOR=‘红’)； 或 SELECT SNO FROM SPJ，P /*这是两表连接查询*/ WHERE JNO=‘J1’ /*这是复合条件连接查询*/ AND SPJ.PNO=P.PNO AND COLOR=‘红’； （4）求没有使用天津供应商生产的红色零件的工程号JNO； *解析：第一种解法是使用多重嵌套查询，第二种方法的子查询是一个多表连接。注意：从J表入手，以包含那些尚未使用任何零件的工程号。 SELECT JNO FROM J WHERE NOT EXISTS (SELECT * FROM SPJ WHERE SPJ.JNO=J.JNO AND SNO IN /*天津供应商的SNO*/ (SELECT SNO FROM S WHERE CITY=‘天津’) AND PNO IN /*红色零件的PNO*/ (SELECT PNO FROM P WHERE COLOR=‘红’))； 或SELECT JNO FROM J WHERE NOT EXISTS (SELECT * FROM SPJ, S, P WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO AND S.CITY=‘天津’AND P. COLOR=‘红’)； //注：本例中父查询和子查询均引用了Student表，可以像自身连接那样用别名将父查询中的Student表与子查询中的Student表区分开：// （5）求至少用了供应商S1所供应的全部零件的工程号JNO (类似于P113例44)。 解析：它所表达的语义为：不存在这样的零件y，供应商S1供应了y，而工程x没有选用y。 用SQL语言表示如下： SELECT DISTINCT JNO FROM SPJ SPJZ WHERE NOT EXISTS (SELECT * FROM SPJ SPJX WHERE SNO=S1 AND NOT EXISTS (SELECT * FROM SPJ SPJY WHERE SPJY.PNO=SPJX.PNO AND SPJY.JNO=SPJZ.JNO AND SPJY.SNO=’S1’））； 5 .针对习题3中的四个表试用SQL语言完成以下各项操作： (1) 找出所有供应商的姓名和所在城市。 (2) 找出所有零件的名称、颜色、重量。 (3) 找出使用供应商S1所供应零件的工程号码。 (4) 找出工程项目J2使用的各种零件的名称及其数量。 (5) 找出上海厂商供应的所有零件号码。 (6) 找出使用上海产的零件的工程名称。 (7) 找出没有使用天津产的零件的工程号码。 (8) 把全部红色零件的颜色改成蓝色。 (9) 由S5供给J4的零件P6改为由S3供应，请作必要的修改。 (填空题) (10) 从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录。 (11) 请将 (S2，J6，P4，200) 插入供应情况关系。 答案： (1) 找出所有供应商的姓名和所在城市。 SELECT SNAME, CITY FROM S; (2) 找出所有零件的名称、颜色、重量。 SELECT PNAME, COLOR, WEIGHT FROM P; (3) 找出使用供应商S1所供应零件的工程号码。 SELECT JNO FROM SPJ WHERE SNO=‘S1’; (4) 找出工程项目J2使用的各种零件的名称及其数量。 SELECT P.PNAME, SPJ.QTY FROM P, SPJ WHERE P.PNO=SPJ.PNO AND SPJ.JNO=J2; (5) 找出上海厂商供应的所有零件号码。 SELECT DISTINCT PNO FROM SPJ WHERE SNO IN （SELECT SNO FROM S WHERE CITY=上海）; (6) 找出使用上海产的零件的工程名称。 SEL