数据库系统原理实验作业九：第三章章后练习题

题1

--(1)
SELECT *
FROM S
WHERE A='10'
--(2)
SELECT A,B
FROM S
--(3)自然连接
SELECT A,B,S.A,S.B,E,F
FROM A,T
WHERE S.C=T.C AND S.D=T.D
--(4)
SELECT *
FROM S,T
WHERE S.A<T.E
--(5)笛卡尔积
SELECT S.C,S,D,T.*
FROM S,T

题2

drop table if exists SPJ;--先删除先前存在的表
drop table if exists S;
drop table if exists P;
drop table if exists J;

create table S(SNO char(10) primary key,
			   SNAME char(10),
			   STATUS int,
			   CITY char(10));

create table P(PNO char(10) primary key,
			   PNAME char(10),
			   COLOR char(10),
			   WEIGHT int);

create table J(JNO char(10) primary key,
			   JNAME char(10),
			   CITY char(10));

create table SPJ(SNO char(10),
				 PNO char(10),
				 JNO char(10),
				 QTY int,
				 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 P.COLOR='红' AND P.PNO=SPJ.PNO AND JNO='J1'

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

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

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

--(5)求至少用了供应商S1所供应的全部零件的工程号JNO
--语义转换为：对于满足条件的JNO，不存在S1生产的零件，而JNO没有用。
select distinct JNO
from SPJ SPJ1
where not exists(select *
				 from SPJ SPJ2
				 where SPJ2.SNO = 'S1' and
				 not exists(select *
							from SPJ SPJ3
							where SPJ3.JNO=SPJ1.JNO and
							SPJ3.PNO=SPJ2.PNO));
							

题3

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

SELECT SNAME,CITY
FROM S

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

SELECT PNAME,COLOR,WEIGHT
FROM P

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

SELECT JNO
FROM SPJ
WHERE SNO='S1'

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

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

(5)找出上海厂商供应的所有零件号码:

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

(6)找出使用上海产的零件的工程名称:

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

(7)找出没有使用天津产的零件的工程号码:

SELECT DISTINCT Jno
FROM J
WHERE Jno NOT IN(
				SELECT	JNO
				FROM S,SPJ
				WHERE S.CITY='天津' AND S.SNO=SPJ.SNO )

(8）把全部红色零件的颜色改成蓝色:

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

(9)由S5供给J4的零件P6改为由S3供应，请作必要的修改:

UPDATE SPJ
SET SPJ.SNO='S3'
WHERE SPJ.SNO='S5' AND SPJ.JNO='J4' AND SPJ.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','P4','J4',200)

题4

9.请为三建工程项目建立一个供应情况的视图，包括供应商代码（Sno），零件代码（Pno),供应数量（QTY），针对该视图完成如下查询。

创建视图

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

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

SELECT PNO,QTY
FROM T

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

SELECT *
FROM T
WHERE Sno='S1'