3.有两个关系S(A,B,C,D)和T(C,D,E,F),写出与下列查询等价的SQL表达式:
(1)
σ
A
=
10
\sigma_{A=10}
σA=10(S)
select *
from S
where A='10';
(2) Π A , B \Pi_A,_B ΠA,B(S)
select distinct A,B
from S;
(3)
S
⋈
T
S \bowtie T
S⋈T
S与T的自然连接,S与T的共同列是C与D,所以判断C,D是否相等即可
select A, B, S.C, S.D, E, F
from S, T
where S.C = T.C And S.D = T.D
(4)
S
⋈
S
.
C
=
T
.
C
T
S {\underset{S.C=T.C} \bowtie} T
SS.C=T.C⋈T
S与T的等值连接
select *
from S, T
where S.C = T.C
(5)
S
⋈
A
=
E
T
S {\underset{A=E} \bowtie} T
SA=E⋈T
S与T的非等值连接
select *
from S, T
where S.A=T.E
(6)
Π
C
,
D
(
S
)
×
T
\Pi_{C,D}(S) \times T
ΠC,D(S)×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完成第二章习题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 SNO
from SPJ
where Jno = 'J1'
(2)求供应工程J零件P1的供应商号码SNO:
select SNO
from SPJ
where 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 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 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);
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;
(2)找出供应商S1的供应情况:
select * from Three
where SNO = 'S1';