CREATE DATABASE IF NOT EXISTS TEST_DB;
USE TEST_DB;
#S_table
CREATE TABLE S
(SNO CHAR(10) PRIMARY KEY,
 SNAME CHAR(10) UNIQUE,
 STATUS SMALLINT,
 CITY CHAR(10)
);

INSERT INTO S VALUES ('S1', '精益', 20,'天津');
INSERT INTO S VALUES ('S2', '盛锡', 10,'北京');
INSERT INTO S VALUES ('S3', '东方红', 30,'北京');
INSERT INTO S VALUES ('S4', '丰泰盛', 20,'天津');
INSERT INTO S VALUES ('S5', '为民', 30,'上海');
#P_TABLE
CREATE TABLE P
(PNO CHAR(10) PRIMARY KEY,
 PNAME CHAR(10) ,
 COLOR CHAR(10) ,
 WEIGHT SMALLINT
);

INSERT INTO P VALUES ('P1', '螺母','红',12);
INSERT INTO P VALUES ('P2', '螺栓','绿',17);
INSERT INTO P VALUES ('P3', '螺丝刀','蓝',14);
INSERT INTO P VALUES ('P4', '螺丝刀','红',14);
INSERT INTO P VALUES ('P5', '凸轮','蓝',40);
INSERT INTO P VALUES ('P6', '齿轮','红',30);

#J_TABLE
CREATE TABLE J
(JNO CHAR(10) PRIMARY KEY,
 JNAME CHAR(10) UNIQUE,
 CITY CHAR(10) 
);

INSERT INTO J VALUES('J1','三建','北京');
INSERT INTO J VALUES('J2','一汽','长春');
INSERT INTO J VALUES('J3','弹簧厂','天津');
INSERT INTO J VALUES('J4','造船厂','天津');
INSERT INTO J VALUES('J5','机车厂','唐山');
INSERT INTO J VALUES('J6','无线电厂','常州');
INSERT INTO J VALUES('J7','半导体厂','南京');

#SPJ_table
CREATE TABLE SPJ
(SNO CHAR(10),
 PNO CHAR(10),
 JNO CHAR(10),
 QTY SMALLINT,
 PRIMARY KEY(SNO,PNO,JNO),
 FOREIGN KEY (SNO)REFERENCES S(SNO),
 FOREIGN KEY (PNO)REFERENCES P(PNO),
 FOREIGN KEY (JNO)REFERENCES J(JNO)
 );
 
 INSERT INTO SPJ VALUES('S1','P1','J1',200);
 INSERT INTO SPJ VALUES('S1','P1','J3',100);
 INSERT INTO SPJ VALUES('S1','P1','J4',700);
 INSERT INTO SPJ VALUES('S1','P2','J2',100);
 INSERT INTO SPJ VALUES('S2','P3','J1',400);
 INSERT INTO SPJ VALUES('S2','P3','J2',200);
 INSERT INTO SPJ VALUES('S2','P3','J4',500);
 INSERT INTO SPJ VALUES('S2','P3','J5',400);
 INSERT INTO SPJ VALUES('S2','P5','J1',400);
 INSERT INTO SPJ VALUES('S2','P5','J2',100);
 INSERT INTO SPJ VALUES('S3','P1','J1',200);
 INSERT INTO SPJ VALUES('S3','P3','J1',200);
 INSERT INTO SPJ VALUES('S4','P5','J1',100);
 INSERT INTO SPJ VALUES('S4','P6','J3',300);
 INSERT INTO SPJ VALUES('S4','P6','J4',200);
 INSERT INTO SPJ VALUES('S5','P2','J4',100);
 INSERT INTO SPJ VALUES('S5','P3','J1',200);
 INSERT INTO SPJ VALUES('S5','P6','J2',200);
 INSERT INTO SPJ VALUES('S5','P6','J4',500);
 
SELECT SNO FROM SPJ WHERE JNO='J1';
SELECT SNO FROM SPJ WHERE JNO='J1' AND PNO='P1';
SELECT SNO FROM SPJ WHERE JNO='J1' AND (PNO IN (SELECT PNO FROM P WHERE COLOR='红'));
SELECT JNO FROM J WHERE NOT EXISTS (SELECT * FROM SPJ WHERE SPJ.JNO=J.JNO AND SNO IN (SELECT SNO FROM S WHERE CITY='天津') AND PNO IN (SELECT PNO FROM P WHERE COLOR='红'));
SELECT DISTINCT A.JNO FROM SPJ AS A WHERE NOT EXISTS ((SELECT DISTINCT PNO FROM SPJ WHERE SNO='S1')EXCEPT(SELECT DISTINCT PNO FROM SPJ WHERE JNO=A.JNO));
SELECT P.PNAME,SPJ.QTY FROM P,SPJ WHERE P.PNO=SPJ.PNO AND SPJ.JNO='J2';
SELECT DISTINCT PNO FROM SPJ WHERE SNO IN (SELECT SNO FROM S WHERE CITY='上海') ;
SELECT JNAME FROM J WHERE JNO IN(SELECT JNO FROM SPJ WHERE SNO IN (SELECT SNO FROM S WHERE CITY='上海'));
SELECT JNAME FROM J,SPJ,S WHERE J.JNO=SPJ.JNO AND S.SNO=SPJ.SNO AND S.CITY='上海';
SELECT JNO FROM J WHERE JNO NOT IN(SELECT JNO FROM SPJ WHERE SNO IN(SELECT SNO FROM S WHERE CITY='天津'));
SELECT JNO FROM J WHERE NOT EXISTS(SELECT * FROM SPJ,S WHERE SPJ.SNO=S.SNO AND SPJ.JNO=J.JNO AND S.CITY='天津');
#UPDATE SPJ SET SNO='S3' WHERE SNO='S5' AND JNO='J4';
#DELETE FROM SPJ WHERE SNO='S2';
#DELETE FROM S WHERE SNO='S2';
#INSERT INTO SPJ VALUES('S2','J6','P4',200);
#SELECT * FROM SPJ;
CREATE VIEW V_SPJ AS SELECT SNO,PNO,QTY FROM SPJ WHERE JNO IN(SELECT JNO FROM J WHERE JNAME='三建');
SELECT PNO,QTY FROM V_SPJ;
SELECT SNO,PNO,QTY FROM V_SPJ WHERE SNO='S1';