习题三参考答案.docx
- 文档编号:6634860
- 上传时间:2023-01-08
- 格式:DOCX
- 页数:15
- 大小:29.06KB
习题三参考答案.docx
《习题三参考答案.docx》由会员分享,可在线阅读,更多相关《习题三参考答案.docx(15页珍藏版)》请在冰豆网上搜索。
习题三参考答案
第三章关系数据库标准语言SQL
第三章详细介绍关系数据库语言SQL。
SQL语言是关系数据库的标准语言,内容十分丰富,是学习关系数据库概念和技术的重要部分。
一、基本知识点
关系模型和关系数据库是《概论》的重点,第三章又是重点中的重点,是全书中篇幅最大的一章,因为关系数据库系统的主要功能是通过SQL语言来实现的。
(1)需要了解的:
SQL语言发展的过程,从而进一步了解关系数据库技术和RDBMS产品的发展过程。
(2)需要牢固掌握的:
掌握SQL语言的特点、SQL语言与非关系模型(层次模型、网状模型)数据语言的不同,从而体会SQL语言之所以能够为用户和业界所接受,并成为国际标准的原因;体会面向过程的语言和SQL语言的区别和优点;体会关系数据库系统为数据库应用系统的开发提供良好环境、减轻用户负担、提高用户生产率的原因。
(3)需要举一反三的:
熟练而正确地使用SQL语言完成对数据库的查询、插入、删除、更新操作,特别是各种各样的查询,掌握SQL语言强大的查询功能。
在完成具体的SQL语句时,希望读者能有意识地和关系代数、关系演算等语言进行比较,了解它们各自的特点。
(4)难点:
本章的难点在于用SQL语言正确完成复杂查询。
因此在学习的过程中一定要多练习,要在某一个RDBMS产品上进行实际运行,检查查询的结果是否正确。
只有通过大量练习,才能真正达到举一反三的熟练程度。
二、习题解答和解析
1、试述SQL语言的特点。
答
(1)综合统一。
SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体。
(2)高度非过程化。
用SQL语言进行数据操作,只要提出"做什么",而无需指明"怎么做",因此无需了解存取路径,存取路径的选择以及SQL语句的操作过程由系统自动完成。
(3)面向集合的操作方式。
SQL语言采用集合操作方式,不仅操作对象、查找结果可以是元组的集合,而且一次插入、删除、更新操作的对象也可以是元组的集合。
(4)以同一种语法结构提供两种使用方式。
SQL语言既是自含式语言,又是嵌入式语言。
作为自含式语言,它能够独立地用于联机交互的使用方式;作为嵌人式语言,它能够嵌入到高级语言程序中,供程序员设计程序时使用。
(5)语言简捷,易学易用。
解析
详细的可参考《概论》上3.1.1。
注意不要仅仅背这些特点,关键是要通过具体的练习、使用SQL语句来理解这些特点。
2、试述SQL的定义功能。
答
SQL的数据定义功能包括定义表、定义视图和定义索引。
SQL语言使用CREATE TABLE语句建立基本表,ALTER TABLE语句修改基本表定义,DROP TABLE语句删除基本表;使用CREATE INDEX语句建立索引,DROP INDEX语句删除索引;使用CREATE VIEW语句建立视图,DROP VIEW语句删除视图。
3、用SQL语句建立第二章习题5中的4个表。
答
对于S表:
S(SNO,SNAME,STATUS,CITY);
建S表
CREATE TABLE S
(SNO CHAR(3),
SNAME CHAR(l0),
STATUS CHAR
(2),
CITY CHAR(l0));
对于P表:
P(PNO,PNAME,COLOR,WEIGHT);
建P表
CREATE TABLE P
(PNO CHAR(3),
PNAME CHAR(l0),
COLORCHAR(4),
WEIGHTINT);
对于J表:
J(JNO,JNAME,CITY);
建J表
CREATETABLE J
(JNO CHAR(3),
JNAMECHAR(l0),
CITYCHAR(l0));
对于SPJ表:
SPJ(SNO,PNO,JNO,QTY);
建SPJ表
CREATETABLESPJ
(SNOCHAR(3),
PNOCHAR(3),
JNO CHAR(3),
QTYINT);
4、针对上题中建立的4个表试用SQL语言完成第一章习题5中的查询。
答
读者可以对比SQL语言、关系代数、ALPHA语言,体会各种语言的优点。
(1)求供应工程J1零件的供应商号码SNO;
SELECT SNO
FROMSPJ
WHEREJNO=’J1’;
(2)求供应工程J1零件P1的供应商号码SNO;
SELECT SNO
FROMSPJ
WHERE JNO='Jl'
ANDPNO='P1';
(3)求供应工程J1零件为红色的供应商号码SNO;
SELECT SNO /*这是嵌套查询*/
FROM SPJ
WHERE JNO='J1'
AND PNO IN /*找出红色零件的零件号码PNO*/
(SELECT PNO
FROM P/*从P表中找*/
WHERE COLOR='红');
SELECT SNO/*这是两表连接查询*/
FROM SPJ,P
WHERE JNO='J1'/*这是复合条件连接查询*/
AND SPJ.PNO=P.PNO
AND COLOR='红';
(4)求没有使用天津供应商生产的红色零件的工程号JNO;
解析
读者可以对比第二章习题5中用ALPHA语言来完成该查询的解答。
如果大家理解了有关该题的解析说明,那么本题的解答可以看成是把关系演算用SQL来表示的过程。
RANGE SPJSPJX
PPX
SSX
GET W(J.JNO):
﹁ヨSPJX(SPJ.JNO=J.JNO∧
ヨSX(SX.SNO=SPJX.SNO∧SX.CITY='天津'∧
ヨPX(PX.PNO=SPJX.PNO∧PX.COLOR='红'))
这里的第一种解法是使用多重嵌套查询,第二种方法的子查询是一个多表连接。
注意:
从J表入手,以包含那些尚未使用任何零件的工程号。
SELECT JNO
FROMJ
WHERENOTEXISTS
(SELECT*
FROMSPJ
WHERESPJ.JNO=J.JNO
ANDSNOIN/*天津供应商的SNO*/
(SELECTSNO
FROMS
WHERECITY='天津')
ANDPNOIN/*红色零件的PNO*/
(SELECTPNO
FROMP
WHERECOLOR='红'));
或
SELECT JNO
FROMJ
WHERENOTEXISTS
(SELECT*
FROMSPJ,S,P
WHERESPJ.JNO=J.JNO
AND SPJ.SNO=S.SNO
AND SPJ.PNO=P.PNO
AND S.CITY='天津'
AND P.COLOR='红');
(5)求至少用了供应商S1所供应的全部零件的工程号JNO(类似于《概论》P113例44)。
SELECTDISTINCTJNO
FROMSPJSPJZ
WHERENOTEXISTS/*这是一个相关子查询*/
(SELECT*/*父查询和子查询均引用了SPJ表*/
FROMSPJSPJX/*用别名将父查询与子查询中的SPJ表区分开*/
WHERESPJX.SNO='S1'
ANDNOTEXISTS
(SELECT*
FROMSPJSPJY
WHERESPJY.PNO=SPJX.PNO
ANDSPJY.JNO=SPJZ.JNO));
解析
本查询的解析可以参考第二章第5题,用ALPHA语言的逻辑蕴涵来表达。
上述查询可以抽象为:
要求这样的工程x,便(
y)p→q为真。
即,对于所有的零件y,满足逻辑蕴涵p→q:
P表示谓词“供应商S1供应了零件y”;q表示谓词“工程x选用了零件y”。
即,只要“供应商S1供应了零件y”为真,则“工程x选用了零件y”为真。
逻辑蕴涵可以转换为等价形式:
它所表达的语义为:
不存在这样的零件y,供应商S1供应了y,而工程x没有选用y。
5、针对习题3中的4个表试用SQL语言完成以下各项操作:
答
(1)找出所有供应商的姓名和所在城市。
SELECT SNAME,CITY
FROM S;
(2)找出所有零件的名称、颜色、重量。
SELECT PNAME,COLOR,WEIGHT
FROM P;
(3)找出使用供应商S1所供应零件的工程号码。
SELECT JNO
FROMSPJ
WHERESNO='S1';
(4)找出工程项目J2使用的各种零件的名称及其数量。
SELECT P.PNAME,SPJ.QTY
FROMP,SPJ
WHEREP.PNO=SPJ.PNO
ANDSPJ.JNO='J2';
(5)找出上海厂商供应的所有零件号码。
SELECT DISTNCT PNO
FROMSPJ
WHERESNOIN
(SELECT SNO
FROMS
WHERECITY='上海');
(6)找出使用上海产的零件的工程名称。
SELECT JNAME
FROMJ,SPJ,S
WHERE J.JNO=SPJ.JNO
ANDSPJ.SNO=S.SNO
ANDS.CITY='上海';
或
SELECT JNAME
FROMJ
WHEREJNOIN
(SELECT JNO
FROMSPJ,S
WHERESPJ.SNO=S.SNO
ANDS.CITY='上海');
(7)找出没有使用天津产的零件的工程号码。
SELECT JNO
FROMJ
WHERENOT EXISTS
(SELECT*
FROMSPJ
WHERE SPJ.JNO=J.JNO
ANDSNOIN
(SELECTSNO
FROMS
WHERE CITY='天津'));
或
SELECT JNO
FROMJ
WHERENOTEXIST
(SELECT*
FROMSPJ,S
WHERESPJ.JNO=J.JNO
ANDSPJ.SNO=S.SNO
ANDS.CITY='天津');
(8)把全部红色零件的颜色改成蓝色。
UPDATE P
SET COLOR='蓝'
WHERECOLOR=‘红';
(9)由S5供给J4的零件P6改为由S3供应,请做必要的修改。
UPDATESPJ
SETSNO='S3'
WHERESNO='S5'
ANDJNO='J4'
ANDPNO='P6';
(10)从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录。
DELETE
FROMSPJ
WHERESNO='S2';
DELETE
FROMS
WHERESNO='S2';
解析
注意删除顺序,应该先从SPJ表中删除供应商S2所供应零件的记录,然后从S表中删除S2。
(11)请将(S2,J6,P4,200)插入供应情况关系。
INSERTINTOSPJ(SNO,JNO,PNO,QTY)/*INTO子句中指明列名*/
VALUES('S2','J6','P4',200);/*插人的属性值与指明列要对应*/
或
INSERT INTO SPJ/*INTO子句中没有指明列名*/
VALUES('S2','P4','J6',200);/*插人的记录在每个属性列上有值*/
/*并且属性列要和表定义中的次序对应*/
6、什么是基本表?
什么是视图?
两者的区别和联系是什么?
答
基本表是本身独立存在的表,在SQL中一个关系就对应一个表。
视图是从一个或几个基本表导出的表。
视图本身不独立存储在数据库中,是一个虚表。
即数据库中只存放视图的定义而不存放视图对应的数据,这些数据仍存放在导出视图的基本表中。
视图在概念上与基本表等同,用户可以如同基本表那样使用视图,可以在视图上再定义视图。
7、试述视图的优点。
答
(1)视图能够简化用户的操作;
(2)视图使用户能以多种角度看待同一数据;
(3)视图对重构数据库提供了一定程度的逻辑独立性;
(4)视图能够对机密数据提供安全保护。
详细解释参见《概论》3.5.4。
8、所有的视图是否都可以更新?
为什么?
答
不是。
视图是不实际存储数据的虚表,因此对视图的更新,最终要转换为对基本表的更新。
因为有些视图的更新不能惟一有意义地转换成对相应基本表的更新,所以,并不是所有的视图都是可更新的,如《概论》3.5.1中的视图S_G(学生的学号及他的平均成绩)
CREAT VIEW S_G(Sno,Gavg)
ASSELECTSno,AVG(Grade)/*设SC表中"成绩"列Grade为数字型*/
FROMSC
GROUPBYSno;
要修改平均成绩,必须修改各科成绩,而我们无法知道哪些课程成绩的变化导致
了平均成绩的变化。
9、哪类视图是可以更新的?
哪类视图是不可更新的?
各举一例说明。
答
基本表的行列子集视图一般是可更新的,如《概论》3.5.3中的例1。
若视图的属性来自集函数、表达式,则该视图肯定是不可以更新的,如《概论》3.5.3中的S_G视图。
11、请为三建工程项目建立一个供应情况的视图,包括供应商代码(SN0)、零件代码(PNO)、供应数量(QTY)。
针对该视图完成下列查询:
(1)找出三建工程项目使用的各种零件代码及其数量。
(2)找出供应商S1的供应情况。
答
建视图:
CREATE VlEW 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 PNO,QTY/*S1供应三建工程的零件号和对应的数量*/
FROM V_SPJ
WHERE SNO='S1';
12、针对习题3建立的表,用SQL语言完成以下各项操作:
(1)把对表S的INSERT权限授予用户张勇,并允许他再将此权限授予其他用户。
答
GRANT INSERT
ON TABLE S
TO 张勇
WITH GRANT OPTION;
(2)把查询SPJ表和修改QTY属性的权限授给用户李天明。
答
GRANT SELECT,UPDATE(QTY)
ON TABLE SPJ
TO 李天明;
补充习题
16.已知学生表S和学生选课表SC。
其关系模式如下:
S(SNO,SN,SD,PROV)
SC(SNO,CN,GR)
其中,SNO为学号,SN为姓名,SD为系名,PROV为省区,CN为课程名,GR为分数。
试用SQL语言实现下列操作:
(1)查询“信息系”的学生来自哪些省区。
(2)按分数降序排序,输出“英语系”学生选修了“计算机”课程的学生的姓名和分数。
答:
(1)SELECT DISTINCT PROV
FROM S
WHERE SD='信息系'
(2)SELECT SN,GR
FROM S,SC
WHERE SD='英语系'AND CN='计算机'AND S.SNO=SC.SNO
ORDER BY GR DESC;
17.设数据库中有3个关系:
职工表EMP(E#,ENAME,AGE,SEX,ECITY),
其属性分别表示职工工号、姓名、年龄、性别和籍贯。
工作表WORKS(E#,C#,SALARY),
其属性分别表示职工工号、工作的公司编号和工资。
公司表COMP(C#,CNAME,CITY),
其属性分别表示公司编号、公司名称和公司所在城市。
试用SQL语句写出下列操作:
(1)用CREATE TABLE语句创建上述3个表,需指出主键和外键。
(2)检索超过50岁的男职工的工号和姓名。
(3)假设每个职工只能在一个公司工作,检索工资超过1000元的男性职工工号和姓名。
(4)假设每个职工可在多个公司工作,检索在编号为C4和C8公司兼职的职工工号和姓名。
(5)检索在‘联华公司"工作、工资超过1000元的男性职工的工号和姓名。
(6)假设每个职工可在多个公司工作,检索每个职工的兼职公司数目和工资总数。
显示内容为(E#,NUM,SUM_SALARY),分别表示工号、公司数目和工资总数。
(7)工号为E6的职工在多个公司工作,试检索至少在E6职工兼职的所有公司工作的职工工号。
(8)检索联华公司中低于本公司平均工资的职工工号和姓名。
(9)在每一公司中为50岁以上职工加薪100元(若职工为多个公司工作,可重复加)。
(10)在EMP表和WORKS表中删除年龄大于60岁的职工有关元组。
解
(l)CREATE TABLE EMP
(E#CHAR(4) NOT NULL,
ENAMECHAR(8) NOT NULL,
AGESMALLINT,
SEXCHAR(l),
ECITYCHAR(20),
PRIMARY KEY(E#));
CREATE TABLE COMP
(C#CHAR(4) NOT NULL,
CNAMECHAR(2O) NOT NULL,
CITYCHAR(20),
PRIMARY KEY(C#));
CREATE TABLE WORKS
(E#CHAR(4) NOT NULL,
C#CHAR(4) NOT NULL,
SALARYSMALLINT,
PRIMARY KEY(E#,C#),
FOREIGN KEY(E#)REFERENCES EMP(E#),
FOREIGN KEY(C#)REFERENCES COMP(C#));
(2)SELECT E#,ENAME
FROMEMP
WHERE AGE>5O AND SEX='M';
(3)SELECT EMP.E#,ENAME
FROMEMP,WORKS
WHERE EMP.E#=WORKS.E#AND SALARY>l000;
(4)SELECT A.E#,A.ENAME
FROMEMP A,WORKS B,WORKS C
WHERE A.E#=B.E#AND B.E#=C.E#
AND B.C#='C4' AND C.C#='C8';
(注:
答案中的B、C代表WORKS的元组)
(5)SELECT A.E#,A.ENAME
FROMEMP A,WORKS B,COMP C
WHERE A.E#=B.E#AND B.C#=C.C#
AND CNAME='联华公司'AND SALARY>1000
AND SEX='M';
(6)SELECTE#,COUNT(C#)ASNUM,SUM(SALARY)ASSUM_SALARY
FROMWORKS
GROUP BY E#;
(7)SELECT X.E#
FROMWORKSX
WHERE NOT EXISTS
(SELECT*
FROMWORKS Y
WHERE E#='E6'
AND NOT EXISTS(SELECT*
FROMWORKS Z
WHERE Z.E#=X.E#
AND Z.C#=Y.C#));
(注:
答案中的X、Y、Z代表WORKS的元组;X是当前正在被考察的当前记录,如果当前记录符合下面的条件,则其E#是我们所要查找的职工号;让X跑遍WORKS的所有元组,便可找到全部符合条件的职工号。
关于对条件部分的理解,请参考《概论》3.3例43,P113及相关讲稿。
)
(8)SELECT A.E#,A.ENAME
FROMEMP A,WORKS B,COMP C
WHERE A.E#=B.E#AND B.C#=C.C#
AND CNAME='联华公司'
AND SALARY<(SELECT AVG(SALARY)
FROMWORKS,COMP
WHERE WORKS.C#=COMP.C#
AND CNAME='联华公司');
(9)UPDATE WORKS
SET SALARY=SALARY+l00
WHERE E#IN(SELECT E#FROMEMP WHERE AGE>50);
(10)DELETE FROM WORKS
WHERE E#IN(SELECTE#FROM EMP WHERE AGE>60);
DELETE FROM EMP
WHERE AGE>60;
18.试述SQL语言的关系代数特点和元组演算特点。
答:
SQL的关系代数特点如下:
(1)有关系代数运算的并、交、差、自然联接等运算符。
(2)FROM子句体现了笛卡儿积操作,WHERE子句体现了选择操作,SELECT子句体现了投影操作。
SQL的元组演算特点如下:
(1)FROM子句中的基本表名应视为元组变量,属性名应视为元组分量。
(2)有存在量词EXISTS符号。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 习题 参考答案
![提示](https://static.bdocx.com/images/bang_tan.gif)