数据库作业——第三章习题

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

（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 A,B,S.C,S.D,T.C,T.D,E,F
from S,T
where S.C=T.C;

（5）

select A,B,S.C,S.D,T.C,T.D,E,F
from S,T
where S.A<T.E;

（6）

select S1.C,S1.D,T.C,T.D,E,F
from T,(select C,D from S)as
S1(C,D);

4. 建立四个表，并进行查询

首先创建四个表：

--创建S表
create table S
(SNO char(5) primary key,
SNAME char(20),
STATUS_ smallint,
CITY char(20));
--创建P表
create table P
(PNO char(5) primary key,
PNAME　char(20),
COLOR char(5),
WEIGTH smallint);
--创建J表
create table J
(JNO char(5) primary key,
JNAME char(20),
CITY char(20));
--创建SPJ表
create table SPJ
(SNO char(5),
PNO char(5),
JNO char(5),
QTY smallint ,
primary key(SNO,PNO,JNO),
foreign key(SNO) references S(SNO),
foreign key(PNO) references P(PNO),
foreign key(JNO) references J(JNO));

插入数据：

insert into S values('S1','精益','20','天津')
insert into S values('S2','盛锡','10','北京')
insert into S values('S3','东方红','30','北京')
insert into S values('S4','丰泰盛','20','天津')
insert into S values('S5','为民','30','上海')

insert into P values ('P1','螺母','红',12)
insert into P values ('P2','螺栓','绿',17)
insert into P values ('P3','螺丝刀','蓝',14)
insert into P values ('P4','螺丝刀','红',14)
insert into P values ('P5','凸轮','蓝',40)
insert into P values ('P6','齿轮','红',30)

insert into J values('J1','三建','北京')
insert into J values('J2','一汽','长春')
insert into J values('J3','弹簧厂','天津')
insert into J values('J4','造船厂','天津')
insert into J values('J5','机船厂','唐山')
insert into J values('J6','无线电厂','常州')
insert into J values('J7','半导体厂','南京')

insert into SPJ values('S1','P1','J1',200)
insert into SPJ values('S1','P1','J3',100)
insert into SPJ values('S1','P1','J4',700)
insert into SPJ values('S1','P2','J2',100)
insert into SPJ values('S2','P3','J1',400)
insert into SPJ values('S2','P3','J2',200)
insert into SPJ values('S2','P3','J4',500)
insert into SPJ values('S2','P3','J5',400)
insert into SPJ values('S2','P5','J1',400)
insert into SPJ values('S2','P5','J2',100)
insert into SPJ values('S3','P1','J1',200)
insert into SPJ values('S3','P3','J1',200)
insert into SPJ values('S4','P5','J1',100)
insert into SPJ values('S4','P6','J3',300)
insert into SPJ values('S4','P6','J4',200)
insert into SPJ values('S5','P2','J4',100)
insert into SPJ values('S5','P3','J1',200)
insert into SPJ values('S5','P6','J2',200)
insert into SPJ values('S5','P6','J4',500)

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

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

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

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

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

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

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

select distinct JNO
from SPJ
where JNO not in
(select JNO
from SPJ,S,P
where SPJ.SNO=S.SNO
and SPJ.PNO=P.PNO
and S.CITY='天津'
and P.COLOR='红');

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

select distinct JNO
from SPJ A
where not exists
(select *
from SPJ B
where SNO='S1' and not exists 
(select *
from SPJ C
where C.JNO=A.JNO and 
C.PNO=B.PNO));

5.

（1）找出所有供应商的姓名和所在城市。

select SNO,CITY
from S;

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

select PNAME,COLOR,WEIGTH
from P;

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

select JNO
from SPJ
where SNO='S1';

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

select PNAME,QTY
from SPJ,P
where SPJ.JNO='J2' and SPJ.PNO=P.PNO;

（5）找出上海厂商供应的所有零件号码。

select distinct PNO
from S,SPJ
where S.CITY='上海' and S.SNO=SPJ.SNO ;

（6）找出使用上海产的零件的工程名称。

select distinct JNO
from S,SPJ
where S.CITY='上海' and S.SNO=SPJ.SNO ;

（7）找出没有使用天津产的零件的工程号码。

select distinct JNO
from SPJ
where JNO not in
(select JNO 
from SPJ,S
where S.CITY='天津' and SPJ.SNO=S.SNO);

（8）把全部红色零件的颜色改为蓝色。

update P
set COLOR='蓝'
where COLOR='红';
select * from P;

（9）由S5供给J4的零件P6改为由S3供应，请做必要的修改。

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

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

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

（11）将（S2，J6，P4，200）插入供应情况表。

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

（10）问中已经把S2删了，又因为有外键关系，所以得先把S2插入回来才能继续插入。

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

--三建工程项目视图
create view SPQ
as
select SNO,PNO,QTY
from SPJ
where JNO='J1';

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

select distinct PNO,QTY
from SPQ;

（2）找出供应商S1的供应情况。

select PNO,QTY
from SPQ
where SNO='S1';

使用not exists真的好难啊，又是根据书上例题做题的一天。