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

• 有两个关系S（A,B,C,D）和T（C,D,E,F）,写出与下列查询等价的SQL表达式：

(1）${\sigma }_{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$

select S.C,S.D,T.*
from S,T;

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

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

select DIST SNO 
from SPJ 
where  JNO='J1';

(2)求供应工程J零件P1的供应商号码SNO;

select  DIST SNO 
from SPJ 
where JNO = 'J1' and PNO = 'P1';

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

select SNO 
from SPJ,P 
where JNO='J1' 	and SPJ.PNO = P.PNO 
				and COLOR = '红';

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

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)求至少用了供应商S1所供应的全部零件的工程号JNO.

select PNO 
from SPJ          
where SNO='S1';

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

• 针对上面的四个表用SQL完成以下各项操作：

(1）找出所有供应商的姓名和所在地：

select SNAME,CITY 
FROM S;

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

select PNAME,COLOR,WEIGHT 
from P;

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

• 为三建工程项目建立一个供应情况的视图，包括供应商代码（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;

(2）找出供应商S1的供应情况：

select * from Three 
where SNO = 'S1';