数据库作业10：第三章课后题

3、（1）

SELECT *
FROM S
WHERE A='10';

（2)

SELECT A,B
FROM S;

(3)

SELECT *
FROM S,T
WHERE S.C=T.C AND S.D=T.D;

(4)

SELECT *
FROM S,T
WHERE S.C=T.C;

(5)

SELECT *
FROM S,T
WHERE S.A<T.E;

(6)

SELECT S.C,S.D,T.*
FROM S,T;

---

4、

S表：

 CREATE TABLE S(SNO CHAR(5), SNAME CHAR(5), STATUS INT, CITY CHAR(5));
 INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES ('S1','精益',20,'天津'); 
 INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S2','盛锡',10,'北京'); 
 INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S3','东方红',30,'北京'); 
 INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S4','丰泰盛',20,'天津'); 
 INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S5','为民','30','上海');

P表：

 CREATE TABLE P( PNO CHAR(5),PNAME CHAR(5), COLOR CHAR(5), WEIGHT INT);
 INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P1','螺母','红',12);
 INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P2','螺栓','绿',17);
 INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P3','螺丝刀','蓝',14);
 INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P4','螺丝刀','红',14); 
 INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P5','凸轮','蓝',40);
 INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P6','齿轮','红',30);

J表：

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

SPJ表:

CREATE TABLE SPJ( SNO CHAR(5), PNO CHAR(5), JNO CHAR(5),QTY SMALLINT); 
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J1',200); 
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J3',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J4',700);  
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P2','J2',100);
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','J4',500);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J5',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P5','J1',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P5','J2',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S3','P1','J1',200); 
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S3','P3','J1',200); 
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P5','J1',100); 
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P6','J3',300); 
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P6','J4',200); 
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P2','J4',100); 
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P3','J1',200); 
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P6','J2',200); 
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P6','J4',500); 

(1)

SELECT SNO
FROM SPJ
WHERE JNO='J1';

(2)

SELECT SNO
FROM SPJ
WHERE JNO='J1' AND PNO='P1';

(3)

SELECT SNO
FROM SPJ,P
WHERE P.PNO=SPJ.PNO AND JNO='J1' AND COLOR='红';

(4)

SELECT JNO
FROM SPJ
WHERE JNO NOT IN(SELECT JNO
                 FROM SPJ,P,S
                 WHERE S.CITY='天津' AND COLOR='红' AND S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO)

(5)
Emmm😂
我查到一个好理解的分两步查询：

/*先查询供应商供应的零件号*/
SELECT PNO
FROM SPJ
WHERE SNO='S1';
/*结果是P1,P2*/
/*再查询哪一个工程既使用P1零件又使用P2零件*/
SELECT JNO
FROM SPJ
WHERE PNO='P1' AND JNO IN(SELECT JNO
                          FROM SPJ
                          WHERE PNO='P2');

5、（1）

SELECT SNAME,CITY
FROM S;

(2)

SELECT PNAME,COLOR,WEIGHT
FROM P;

(3)

SELECT JNO
FROM SPJ
WHERE SNO='S1';

(4)

SELECT PNAME,QTY
FROM P,SPJ
WHERE SPJ.JNO='J2' AND P.PNO=SPJ.PNO;

(5)

SELECT PNO
FROM S,SPJ
WHERE S.CITY='上海' AND S.SNO=SPJ.SNO;

(6)

SELECT JNAME
FROM S,J,SPJ
WHERE S.CITY='上海' AND S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO;

(7)

SELECT JNO
FROM SPJ
WHERE JNO NOT IN(SELECT JNO
                 FROM S,SPJ
                 WHERE S.CITY='天津' AND S.SNO=SPJ.SNO);

(8)

UPDATE P
SET COLOR='蓝'
WHERE COLOR='红';

(9)

UPDATE SPJ
SET SNO='S3'
WHERE SNO='S5' AND PNO='P6' AND JNO='J4';

(10)

DELETE 
FROM S 
WHERE SNO='S2';

DELETE 
FROM SPJ
WHERE SNO='S2';

(11)

INSERT 
INTO SPJ
VALUES('S2','J6','P4','200');

---

9、

CREATE VIEW VSP
AS SELECT SNO,PNO,QTY 
FROM SPJ,J
WHERE SPJ.JNO=J.JNO AND J.JNAME='三建';

(1)

SELECT PNO QTY
FROM VSP;

(2)

SELECT *
FROM VSP
WHERE SN0='S1';