【数据库系统原理作业】九、第三章章后练习题

1.有两个关系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 * 
from S
inner join T on S.C = T.C and S.D = T.D;
--(4)连接
select * 
from S
inner join T on S.C = T.C;
--(5)连接
select * 
from S
inner join T on S.A<S.E;
--(6)投影+笛卡尔积
select * 
from S,T
where S.C=T.C and T.D=S.D;

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

建表:

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 SNO
from SPJ
where JNO='J1';--直接查找即可
--(2)求供应工程J1零件P1的供应商号码SNO
select SNO
from SPJ
where JNO='J1' and PNO='P1';
--(3)求供应工程J1零件为红色的供应商号码SNO
select SNO
from SPJ,P
where SPJ.JNO='J1' and SPJ.PNO=P.PNO and P.COLOR='红';
--(4)求没有使用天津供应商生产的红色零件的工程号JNO
select distinct JNO--加入distinct消除重复的JNO
from SPJ
where JNO not in(select JNO
				 from SPJ,P,S
				 where S.CITY='天津'and P.COLOR='红' and S.SNO=SPJ.SNO and P.PNO=SPJ.PNO);
--(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.针对建立的4各表用SQL完成第二章习题6中的查询

(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 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 SPJ
where SNO='S2';
delete 
from S
where SNO='S2';

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

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

3.请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）针对该视图完成下列查询。

建表

drop view if exists sanjian;
go
create view sanjian
as (select SPJ.SNO,SPJ.PNO,SPJ.QTY
	from J,SPJ
	where J.JNO=SPJ.JNO 
	and J.JNAME='三建');

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

select PNO,QTY
from sanjian;

(2)找出供应商S1的供应情况。

select * from sanjian 
where SNO = 'S1';