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

(1)

select * 
from S 
where A='10';

(2)

select A,B
from S;

(3)

select A,B,S.C,S.D,E,F 
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 TABLES S(Sno CHAR(2) UNIQUE,
Sname CHAR(6),
Status CHAR(2),
City CHAR(4));

建P表：

CREATE TABLES P(Pno CHAR(2) UNIQUE,
Pname CHAR(6),
COLOR CHAR(2),
WEIGHT INT);

建J表：

CREATE TABLES J(Jno CHAR(2) UNIQUE,
JNAME CHAR(8),
CITY CHAR(4));

建SPJ表：

CREATE TABLES SPJ(Sno CHAR(2),
Pno CHAR(2),
Jno CHAR(2),
QTY INT);

针对建立的4个表用SQL语言完成第二章习题6中的查询
（1）

SELECT DIST SNO 
FROM SPJ 
WHERE  JNO=’J1’;

(2)

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

(3)

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

(4)

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

(5)

SELECT DIST PNO 
FROM SPJ          //先查询S1供应商供应的零件号
WHERE SNO='S1';

SELECT JNO 
FROM SPJ      //再查询哪一个工程既使用P1零件又使用P2零件。
WHERE PNO='P1'AND JNO IN (SELECT JNO FROM SPJ WHERE PNO='P2');

（1）

SELECT SNAME，CITY FROM S

（2）

SELECT PNAME,COLOR,WEIGHT FROM P

（3）

SELECT  DIST JNO 
FROM SPJ 
WHERE SNO='S1';

（4）

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

（5）

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

（6）

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

(7)

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

(8)

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

(9)

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

(10)

DELETE  FROM  S  WHERE  SNO=’S2’;

DELETE  FROM  SPJ  WHERE  SNO=‘S2’;

(11)

INSERT  INTO  SPJ  
VALUES（‘S2’，‘J6’，‘P4’，200）;

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

(1)

SELECT  DIST  PNO,QTY  FROM  VSP

(2)

SELECT  DIST * FROM VSP WHERE SNO='S1';