目录
第三章课后题
3.有两个关系S(A,B,C,D)和T(C,D,E,F),写出与下列查询等价的SQL表达式:
select * from S where A='10';
select A,B from S;
select *
from S,T
where S.C=T.C and S.D=T.D;
select * from S,T where S.C=T.C;
select * from S,T where S.A<T.E;
select * from S,T
where S.C=T.C and T.D=S.D;
4.用SQL语句建立第2章习题6中的4个表;
create table S
(sno char(5) unique,
sname char(20),
status smallint,
city char(20)
);
create table P
(pno char(5) unique,
pname char(20),
color char(5),
weight smallint
);
create table J
(jno char(5) unique,
jname char(20),
city char(20)
);
create table SPJ
(sno char(5),
pno char(5),
jno char(5),
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','东方红',20,'北京');
INSERT INTO S
VALUES ('S4','丰泰盛',20,'天津');
INSERT INTO S
VALUES ('S5','为民',20,'上海');
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);
select * from S;
select * from P;
select * from J;
select * from SPJ;
4.针对建立的4各表用SQL完成第二章习题6中的查询:
(1)求供应工程J1零件的供应商号码SNO;
select sno
from SPJ
where jno='J1';
(2)求供应工程J零件P1的供应商号码SNO;
select sno
from SPJ
where jno='J1' and pno='P1';
(3)求供应工程JI零件为红色的供应商号码SNO;
select sno
from SPJ,P
where jno='J1' and color='红' and SPJ.pno=P.pno;
(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 pno
from SPJ
where sno='S1';
select jno
from SPJ
where pno='P1' and jno in(
select jno
from SPJ
where pno='P2'
);
5.针对上面的四个表用SQL完成以下各项操作:
(1)找出所有供应商的姓名和所在地:
select sname,city
from S;
(2)找出所有零件的名称、颜色、重量:
select pno,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 P.pno=SPJ.pno;
(5)找出上海厂商供应的所有零件号码:
select pno
from SPJ,S
where city='上海'and S.sno=SPJ.sno;
(6)找出使用上海产的零件的工程名称:
select jname
from SPJ,S,J
where S.city='上海'and S.sno=SPJ.sno and J.jno=SPJ.jno;
(7)找出没有使用天津产的零件的工程号码:
select jno
from SPJ
where jno not in(
select 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','P4','J6',200);
9.为三建工程项目建立一个供应情况的视图,包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。基于上面的视图完成以下查询:
create view v_SPJ as
select sno,pno,qty
from SPJ
where jno=(select jno
from J
where jname='三建');
(1)找出三建工程项目使用的各种零件代码及其数量:
select pno,qty from v_SPJ;
(2)找出供应商S1的供应情况:
select * from v_SPJ where sno='S1';
心得:
终于写完了啊!
完结,撒花!