发布时间 : 星期六 文章SQL习题及答案更新完毕开始阅读
);
CREATE TABLE P(
PNO CHAR(5) PRIMARY KEY, PNAME CHAR(5), COLOR CHAR(5), WEIGHT INT );
CREATE TABLE J(
JNO CHAR(5) PRIMARY KEY, JNAME CHAR(5), CITY CHAR(10) );
CREATE TABLE SPJ( SNO CHAR(5), PNO CHAR(5), JNO CHAR(5), QTY INT, CONSTRAINT PK_SPJ
KEY(SNO,PNO,JNO),
PRIMARY
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='上海';