数据库作业10：第三章课后题_数据库disp和dist

3.(1)

select * 
from S 
where A='10';
1
2
3

3.(2)

select A,B
from S;
1
2

3.(3)

select A,B,S.C,S.D,E,F 
from S,T
where S.C=T.C and S.D=T.D;
1
2
3

3.(4)

select *
from S,T
where S.C=T.C;
1
2
3

3.(5)

SELECT *
from S,T
where S.A<T.E;
1
2
3

3.(6)

SELECT S.C,S.D,T.*
from S,T;
1
2

4.
（1）求供应工程J1零件的供应商号码SNO

SELECT DIST SNO 
FROM SPJ 
WHERE  JNO=’J1’
1
2
3

（2）求供应工程J1零件P1 的供应商号码SNO

SELECT  DIST SNO 
FROM SPJ 
WHERE JNO='J1' AND PNO='P1'
1
2
3

（3）求供应工程J1零件为红色的供应商号码SNO；

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

（4）求没有使用天津供应商生产的红色零件的工程号JNO；

SELECT JNO
FROM J
WHERE NOT EXISTS
		(SPJ.JNO=J.JNO
		 AND SPJ.SNO=S.SNO
		 AND SPJ.PNO=P.PNO
		 AND S.CITY='天津'
		 AND P.COLOR='红');
1
2
3
4
5
6
7
8

（5）求至少用了供应商S1所供应的全部零件的工程号JNO

SELECT JNO
FROM SPJ
WHERE SNO='S1' AND PNO IN
(SELECT PNO
FROM SPJ
WHERE SNO='S1');  
1
2
3
4
5
6

5
（1)

SELECT SNAME,CITY 
 FROM S
1
2

(2)

SELECT PNAME,COLOR,WEIGHT 
FROM P
1
2

(3)

SELECT  DIST JNO 
FROM SPJ 
WHERE SNO='S1'
1
2
3

(4)

SELECT PNAME,QTY 
FROM SPJ,P
WHERE P.PNO=SPJ.PNO AND SPJ.JNO='J2'
1
2
3

(5)

SELECT PNO 
FROM SPJ,S 
WHERE S.SNO=SPJ.SNO AND CITY='上海'
1
2
3

(6)

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

(7)

SELECT DISP JNO 
FROM SPJ  
WHERE JNO NOT IN (SELECT DIST JNO
              FROM SPJ,S
              WHERE S.SNO=SPJ.SNO AND S.CITY='天津') 
1
2
3
4
5

(8)

UPDATE P SET COLOR='蓝'  
WHERE COLOR='红'
1
2

(9)

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

(10)

1.DELETE  FROM  S  WHERE  SNO=’S2’
2.DELETE  FROM  SPJ  WHERE  SNO=‘S2’
1
2

(11)

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

9
(1)

SELECT  DIST  PNO,QTY  
FROM  VSP
1
2

(2)

SELECT  DIST *
FROM VSP 
WHERE SNO='S1'
1
2
3