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 页