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 CREATE TABLE S
(SNO CHAR(8) NOT NULL UNIQUE,
SNAME VARCHAR(20) NOT NULL UNIQUE, CITY VARCHAR(8),
CONSTRAINT CS PRIMARY KEY(SNO)); //创建p表 CREATE TABLE P
(PNO CHAR(8) NOT NULL UNIQUE,
PNAME VARCHAR(20) NOT NULL UNIQUE, COLOR CHAR(1), WEIGHT INT,
CONSTRAINT CP PRIMARY KEY(PNO)); //创建j表 CREATE TABLE J
(JNO CHAR(8) NOT NULL UNIQUE,
JNAME VARCHAR(20) NOT NULL UNIQUE, CITY VARCHAR(8),
CONSTRAINT CJ PRIMARY KEY(JNO)); //创建spj表 CREATE TABLE SPJ
(SNO CHAR(8) NOT NULL NUIQUE, PNO CHAR(8) NOT NULL UNIQUE,
JNO CHAR(8) NOT NULL UNIQUE, QTY INT,
CONSTRAINT CSPJ PRIMARY KEY(SON,PNO,JNO),
CONSTRAINT CSPJ FOREING KEY(SNO) REFERENCES S(SNO),
CONSTRAINT CSPJ FOREING KEY(PNO) REFERENCES P(PNO),
CONSTRAINT CSPJ FOREING KEY(JNO) REFERENCES J(JNO)); 2.
//向s表插入数据
INSERT INTO S(SNO,SNAME,CITY) VALUES(‘S1’,’N1’,’上海’); INSERT INTO S(SNO,SNAME,CITY) VALUES(‘S2’,’N2’,’北京’); INSERT INTO S(SNO,SNAME,CITY) VALUES(‘S3’,’N3’,’北京’);
INSERT INTO S(SNO,SNAME,CITY) VALUES(‘S4’,’N4’,’上海’); INSERT INTO S(SNO,SNAME,CITY) VALUES(‘S5’,’N5’,’南京’); //向p表插入数据
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES(‘P1’,’PN1’,’红’,’12’); INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES(‘P2’,’PN2’,’绿’,’18’); INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES(‘P3’,’PN3’,’蓝’,’20’); INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES(‘P4’,’PN4’,’红’,’13’); INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES(‘P5’,’PN5’,’蓝’,’11’); INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES(‘P6’,’PN6’,’红’,’15’); //向j表插入数据
INSERT INTO J(JNO,JNAME,CITY) VALUES(‘J1’,’JN1’,’上海’); INSERT INTO J(JNO,JNAME,CITY) VALUES(‘J2’,’JN2’,’广州’);