INSERT INTO J(JNO,JNAME,CITY) VALUES(‘J3’,’JN3’,’南京’); INSERT INTO J(JNO,JNAME,CITY) VALUES(‘J4’,’JN4’,’南京’); INSERT INTO J(JNO,JNAME,CITY) VALUES(‘J5’,’JN5’,’上海’); INSERT INTO J(JNO,JNAME,CITY) VALUES(‘J6’,’JN6’,’武汉’); INSERT INTO J(JNO,JNAME,CITY) VALUES(‘J7’,’JN7’,’上海’); //向表spj中插入数据
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S1’,’P1’,’J1’,’200’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S1’,’P1’,’J4’,’700’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S2’,’P3’,’J1’,’400’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S2’,’P3’,’J2’,’200’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S2’,’P3’,’J3’,’200’);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S2’,’P3’,’J4’,’500’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S2’,’P3’,’J5’,’600’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S2’,’P3’,’J6’,’400’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S2’,’P3’,’J7’,’800’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S2’,’P5’,’J2’,’100’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S3’,’P3’,’J1’,’200’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S3’,’P4’,’J2’,’500’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S4’,’P6’,’J7’,’300’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P2’,’J2’,’200’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P2’,’J4’,’100’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P5’,’J5’,’500’);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P5’,’J7’,’100’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P6’,’J2’,’200’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P1’,’J4’,’1000’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P3’,’J4’,’1200’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P4’,’J4’,’800’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P5’,’J4’,’400’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P6’,’J4’,’500’); 3.1
SELECT * FROM J; 3.2
SELECT * FROM J WHERE CITY LIKE ‘上海’; 3.3
SELECT PNO FROM P WHERE WEIGHT=(SELECT MAX(WEIGHT)FROM P);
3.4
SELECT SNO FROM SPJ WHERE JNO IN(SELECT JNO FROM J WHERE JNO=‘J1’); 3.5
SELECT SNO FROM SPJ WHERE JNO=‘J1’ AND PNO=‘P1’; 3.6
SELECT JNAME FROM J,SPJ WHERE J.JNO=SPJ.JNO AND SPJ.SNO=‘S1’; 3.7
SELECT COLOR FROM P,SPJ WHERE P.PNO=SPJ.PNO AND SPJ.SNO=‘S1’; 3.8
SELECT DISTINCT SNO FROM SPJ WHERE JNO=‘J1’ OR JNO=‘J2’; 3.9
SELECT SNO FROM SPJ,P WHERE
P.PNO=SPJ.PNO AND SPJ.JNO=‘J1’ AND P.COLOR=‘红’’ 3.10
SELECT SNO FROM SPJ,J WHERE J.JNO=SPJ.JNO AND J.CITY=‘上海’;
3.11
SELECT SNO FROM SPJ,J,P
WHERE SPJ.PNO=P.PNO AND J.JNO=SPJ.JNO AND P.COLOR=‘红’; 3.12
SELECT SPJ.PNO FROM SPJ,S,J
WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITY=J.CITY; 3.13
SELECT SPJ.PNO FROM SPJ,S,J
WHERE S.SNO=SPJ.SNO AND J,JNO=SPJ.JNO AND S.CITY=‘上海’ AND J.CITY=‘上海’; 3.14
SELECT SPJ.JNO FROM SPJ,S,J
WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITY<>J.CITY; 3.15
SELECT JNO FROM SPJ WHERE JNO NOT IN (SELECT SPJ.JNO FROM S,SPJ WHERE S.SNO=SPJ.SNO AND S.CITY =‘上海’); 3.16