发布时间 : 星期六 文章SQL习题及答案更新完毕开始阅读
15.
SELECT DISTINCT JNO FROM J WHERE EXISTS (SELECT
*
FROM
S,SPJ
WHERE
SPJ.SNO=S.SNO AND J.JNO=SPJ.JNO AND J.CITY<>S.CITY); 16.
SELECT DISTINCT JNO FROM J WHERE NOT EXISTS (SELECT
*
FROM
S,SPJ
WHERE
J.JNO=SPJ.JNO AND SPJ.SNO=S.SNO AND S.CITY='上海'); 17.
SELECT DISTINCT SNO FROM SPJ WHERE PNO IN
(SELECT DISTINCT PNO FROM SPJ WHERE SNO IN
(SELECT DISTINCT SNO FROM SPJ,P WHERE SPJ.PNO=P.PNO AND P.COLOR='红')); 18.
SELECT DISTINCT JNO FROM SPJ WHERE SNO='S1'; 19. SELECT
DISTINCT
S.CITY,SPJ.PNO,J.CITY
FROM S,J,SPJ WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITY<>J.CITY; 20.
SELECT DISTINCT SNO FROM S WHERE NOT EXISTS
(SELECT * FROM J WHERE NOT EXISTS (SELECT
*
FROM
P,SPJ
WHERE
S.SNO=SPJ.SNO AND SPJ.PNO=P.PNO AND J.JNO= SPJ.JNO)); 21.
SELECT DISTINCT PNO FROM P WHERE NOT EXISTS
(SELECT * FROM J WHERE J.CITY='上海' AND NOT EXISTS
(SELECT * FROM SPJ WHERE SPJ.PNO=P.PNO
AND SPJ.JNO=J.JNO)); 22.
SELECT DISTINCT JNO FROM SPJ SX WHERE NOT EXISTS
(SELECT * FROM SPJ SY WHERE SY.SNO='S1' AND NOT EXISTS (SELECT 23.
UPDATE P SET COLORE='橙' WHERE COLORE='红'; 24.
DELETE FROM SPJ WHERE SPJ.PNO IN (SELECT PNO FROM P WHERE COLOR='红'); DELETE FROM P WHERE COLOR='红'; 25.
SELECT SUM(QTY) FROM SPJ WHERE SNO='S1' AND PNO='P1';
*
FROM
SPJ
SZ
WHERE
SY.PNO=SZ.PNO AND SX.JNO=SZ.JNO));
26.
SELECT JNO,COUNT(DISTINCT SNO) FROM SPJ GROUP BY JNO; 27.
SELECT JNO FROM SPJ HAVING SUM(QTY)>1000; 1. //创建s表
GROUP JNO BY