FROM J

WHERE NOT EXISTS (SELECT * FROM S,P,SPJ

WHERE S.SNO = SPJ.SNO AND J.JNO = SPJ.JNO AND P.PNO = SPJ.PNO AND P.COLOR = ‘红色’ AND S.CITY = ‘天津’ ); 或

SELECT DISTINCT JNO FROM SPJ

WHERE JNO NOT IN (SELECT * FROM S,P,SPJ

WHERE S.SNO = SPJ.SNO AND P.PNO = SPJ.PNO AND P.COLOR = ‘红色’ AND S.CITY = ‘天津’ );

第 21 页

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

(5)检索上海厂商供应的所有零件代码，并且按照重量从大到小显示出来。 SELECT P.PNO FROM P,SPJ,S

WHERE S.SNO = SPJ.SNO AND P.PNO = SPJ.PNO AND S.CITY = ‘上海’ ORDER BY WEIGHT DESC; 或

SELECT DISTINCT PNO FROM SPJ,S

WHERE S.SNO = SPJ.SNO AND S.CITY = ‘上海’ ORDER BY WEIGHT DESC;

(6) 检索出供应商姓名头两个字为“浦东”的供应商

第 22 页

的信息。 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

第 23 页

TO ‘王明’

WITH GRANT OPTION;

第 4 章 Oracle数据库

第 5 章 ORACLE存储过程与触发器

第 24 页