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 S.*,T.*
from S,T;
(4)
//等值连接
select S.*,T.*
from S,T
where S.C=T.C;
(5)
//非等值连接
select S.*,T.*
from S,T
where A<E;
(6)
//S在cd上的投影与T的笛卡尔积
//方法一:
select C,D
from S
union
select *
from T;
//方法二:
select S.C,S.D,T.*
from S,T
2、用SQL语句建立以下的4个表;针对建立的4个表用SQL完成第二章习题6中的查询。
S表:
create table S(sno char(10),
sname char(10),
status smallint,
city char(10)
);
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,'上海');
P表:
create table P(pno char(10),
pname char(10),
color char(10),
weight smallint
);
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);
J表:
create table J(jno char(10),
jname char(10),
city char(10)
);
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','半导体厂','南京');
SPJ:
CREATE TABLE SPJ(SNO CHAR(4),
PNO CHAR(4),
JNO CHAR(4),
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)求供应工程J1零件P1的供应商号码SNO;
select sno
from spj
where jno='j1' and pno='p1';
(3)求供应工程J1零件为红色的供应商号码SNO;
select sno
from spj,p
where jno='j1' and p.pno=spj.pno and color='红';
(4)求没有使用天津供应商生产的红色零件的工程号JNO;
select jno
from j
where not exists(
select *
from spj,s,p
where s.city='天津' and p.color='红'
and spj.jno=j.jno and spj.pno=p.pno);
(5)求至少用了供应商S1所供应的全部零件的工程号JNO。
select distinct jno
from spj x
where not exists(
select *
from spj y
where y.sno='s1'
and not exists(
select *
from spj z
where z.pno=y.pno
and z.jno= x.jno)
);
3、针对习题4中的4个表使用SQL完成以下各项操作:
(1)找出所有供应商的姓名和所在城市;
select jname,city
from j;
(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 jno='j2' and spj.pno=p.pno;
(5)找出上海厂商供应的所有零件号码;
select distinct pno
from spj,s
where city='上海' and spj.sno=s.sno;
(6)找出使用上海产的零件的工程项目;
select distinct jname
from spj,s,j
where s.sno=spj.sno
and s.city='上海'
and j.jno=spj.jno;
(7)找出没有使用天津产的零件的工程号码;
select jno
from j
where not exists(
select *
from spj,s
where spj.jno=j.jno
and spj.sno=s.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 spj
values('s2','p4','j6',200);
4、请为三建工程项目建立一个供应情况的视图,包括供应商代码(SNO)、零件代码(PNO)、供应商数量(QTY)。针对该视图完成下列查询:
create view view_s
as
select sno,pno,qty
from spj
where jno=(select jno
from j
where jname='三建');
(1)找出三建工程项目使用的各种零件代码及其数量;
select pno,qty
from view_s
(2)找出供应商S1的供应情况;
select *
from view_s
where sno='s1'