第三章课后作业

第三章课后习题

3：有两个关系S(A,B,C,D)和 T(C,D,E,F)，写出与下列查询等价的SQL表达式:
（1）σ A = 10 ( S )；

Select * From S
Where A = 10

（2）${\Pi }_{A,B}(S)$

Select A, B From S

（3）$S\bowtie T$

Select A, B, S.C, S.D, E, F
From S, T
Where S.C = T.C And S.D = T.D

（4）$S\underset{S.C=T.C}{\bowtie }T$

Select *
From S, T
Where S.C = T.C

（5）$S\underset{A=E}{\bowtie }T$

Select *
From S, T
Where S.A=T.E

（6） ${\Pi }_{C,D}(S)\times T$
从S中选出C、D列与T做笛卡尔积。

Select S.C,S.D,T.C,T.D,T.E,T.F
From S,T

4：用SQL语句建立第2章习题6中的4个表;针对建立的4个表用SQL完成第2章习题6中的查询
建表：

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','上海');

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);

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','半导体厂','南京');

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)求供应工程J1零件的供应商号码SNO

Select distinct SNO 
From SPJ 
Where Jno = 'J1'

![在这里插入图片描述](https://img-blog.csdnimg.cn/20210419103026790.png
(2）求供应工程J零件P1的供应商号码SNO

select sno
from spj
where pno='P1';

(3)求供应工程J1零件为红色的供应商号码SNO

select distinct sno
from spj,p
where p.pno=spj.pno and color='红' and jno='j1';

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

select distinct jno
from spj
where jno not in(select jno
                 from s,p,spj
                 where city='天津' 
				 and p.pno=spj.pno 
				 and s.sno=spj.sno 
				 and color='红');

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

select distinct pno
from s,spj
where s.sno=spj.sno and s.sno='s1';

结果为：

再：

select JNO 
from SPJ      
where PNO = 'P1' and JNO in 
					(
						select JNO 
						from SPJ 
						where PNO = 'P2'
					);

5：针对上面的4个表,用SQL完成以下各项操作：
1）找出所有供应商的姓名和所在地：

select SNAME,CITY 
FROM S;

2）找出所有零件的名称、颜色、重量：

select SNAME,CITY 
FROM S;

3）找出使用供应商S1所供应零件的工程号码：

select JNO 
from SPJ 
where SNO = 'S1';

4）找出工程项目J2使用的各种零件的名称及其数量：

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 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）由S5共给J4的零件P6改为由S3供应，作必要的修改：

update  SPJ  
set SNO = 'S3' 
where SNO = 'S5' and JNO = 'J4' and PNO = 'P6';

10）从供应商关系中删除S2的记录，并从供应情况中删除相应的记录：

delete from S where SNO = 'S2';
delete from SPJ where SNO = 'S2';

11）请将(S2 , J6 , P4, 200) 插入供应关系情况：

insert into SPJ  
values('S2' , 'J6' , 'P4' , 200);

9.为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）

create view Three 
as
select SNO,SPJ.PNO,QTY 
from SPJ,J
where SPJ.JNO = J.JNO and J.JNAME = '三建';

(1）找出三建工程项目使用的各种零件代码及其数量：

select PNO,QTY
from Three;

`
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210421194004926.png)
(2）找出供应商S1的供应情况：

```sql
select * from Three 
where SNO = 'S1';