数据库系统及应用(戴小平)课后答案

程号JNO； SELECT JNO FROM J

WHERE NOT EXISTS (SELECT * FROM S,P,SPJ WHERE = AND = AND = AND = ‘红色’ AND = ‘天津’ ); 或

SELECT DISTINCT JNO FROM SPJ

WHERE JNO NOT IN (SELECT * FROM S,P,SPJ WHERE = AND = AND = ‘红色’ AND = ‘天津’

);

(4) 检索出各个工程项目使用的零件数量。 SELECT JNO, SUM(QTY) FROM SPJ GROUP BY JNO;

(5) 检索上海厂商供应的所有零件代码，并且按照重量从大到小显示出来。 SELECT FROM P,SPJ,S WHERE = AND = AND = ‘上海’ ORDER BY WEIGHT DESC; 或

SELECT DISTINCT PNO FROM SPJ,S WHERE =

AND = ‘上海’ ORDER BY WEIGHT DESC;

(6) 检索出供应商姓名头两个字为“浦东”的供应商的信息。 SELECT * FROM S

WHERE SNAME LIKE ‘浦东%’ ;

(7) 把全部红色零件的颜色改成蓝色。 UPDATE P

SET COLOR = ‘蓝色’ WHERE COLOR = ‘红色’;

(8) 从零件表中删除零件号是P2的记录。 DELETE FROM P

WHERE PNO = ‘P2’;

(9) 请将(S2，J6，P4，200)插入供应情况关系。 INSERT INTO SPJ

VALUES(‘S2’,’J6’,’P4’,200);

(10) 授予用户“王明”对零件表有INSERT的权限，并允许该权限传播。

GRANT INSERT ON P TO ‘王明’ WITH GRANT OPTION;

第 4 章 Oracle数据库 第 5 章 ORACLE存储过程与触发器