CONSTRAINT CONSTRAINT CONSTRAINT ); 2.
SELECT * FROM J; 3.
FK_SNO FK_PNO FK_JNO
FOREIGN FOREIGN FOREIGN
KEY(SNO)REFERENCES S(SNO), KEY(PNO)REFERENCES P(PNO), KEY(JNO)REFERENCES J(JNO)
SELECT * FROM J WHERE CITY='上海'; 4. SELECT 5.
SELECT DISTINCT SNO FROM SPJ WHERE JNO='J1';
PNO
FROM
P
WHERE
WEIGHT=(SELECT MIN(WEIGHT) FROM P);
6.
SELECT SNO FROM SPJ WHERE JNO='J1' AND PNO='P1'; 7.
SELECT DISTINCT JNAME FROM J,SPJ WHERE J.JNO=SPJ.JNO AND SPJ.SNO='S1'; 8.
SELECT DISTINCT COLOR FROM P,SPJ WHERE P.PNO=SPJ.PNO AND SPJ.SNO='S1; 9. SELECT
X.SNO
FROM
SPJ
X
WHERE
X.JNO='J1'AND EXISTS
(SELECT * FROM SPJ Y WHERE Y.SNO=X.SNO AND Y.JNO='J2'); 10.
SELECT DISTINCT SNO FROM SPJ,P WHERE SPJ.JNO='J1' P.COLOR='红';
AND
SPJ.PNO=P.PNO
AND
11.
SELECT DISTINCT SNO FROM SPJ,J WHERE SPJ.JNO=J.JNO AND J.CITY='上海'; 12.
SELECT DISTINCT SPJ.SNO FROM SPJ,J,P WHERE SPJ.JNO=J.JNO AND SPJ.PNO=P.PNO AND P.COLOR ='红' AND (J.CITY='上海' OR J.CITY=' 北京') ; 13.
SELECT DISTINCT P.PNO FROM S,J,P,SPJ WHERE P.PNO=SPJ.PNO AND SPJ.SNO=S.SNO AND SPJ.JNO=J.JNO AND S.CITY=J.CITY; 14.
SELECT DISTINCT P.PNO FROM S,J,P,SPJ WHERE P.PNO=SPJ.PNO AND SPJ.SNO=S.SNO AND SPJ.JNO=J.JNO AND S.CITY='上海' AND J.CITY='上海';
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