第4章关系数据库的结构化查询语言习题解答

1第4章关系数据库的结构化查询语言习题解答一.简答题1．SQL的特点是什么?（1）一体化特点。SQL提供了一系列完整的数据定义和操纵功能，用SQL可以实现数据库生命周期中的全部活动，包括定义关系模式，录入数据以建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求。（2）高度非过程化。SQL和其它数据操作语言不同的关键是SQL为非过程语言，它允许用户依据做什么来说明操作，而不用说明怎样做，存取路径的选择和SQL语句操作的过程由系统自动完成。（3）语言简洁，易学易用。SQL的语法很简单，语言十分简洁，完成核心功能只用了9个动词。初学者经过短期的学习就可以使用SQL进行数据库的存取等操作，易学易用是它的最大特点。（4）统一的语法结构对待不同的工作方式。无论是联机交互使用方式，还是嵌入到高级语言中使用，其语法结构是基本一致的，这就大大改善了最终用户和程序设计人员之间的通信。（5）面向集合的操作方式。SQL采用集合操作方式，无论是操作对象还是查询更新的结果均是元组的集合。（6）SQL语言可以对两种基本数据结构进行操作，一种是“表”，另一种是“视图(View)”。2．什么是基本表?什么是视图?两者的区别和联系是什么?基本表是本身独立存在的表，一个（或多个）基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。存储文件的逻辑结构组成了关系数据库的内模式。视图是从一个或几个基本表导出的表，它本身不独立存储在数据库中，即数据库中只存储视图的定义而不存储对应的数据，因此视图是一个虚表。用户可在视图和/或基本表上定义新的视图。基本表是实表，而视图则是虚表。用户可以用SQL语言对视图(View)和基本表(Basetable)进行查询等操作,在用户观点里，视图和基本表一样都是关系。3．视图是否都可以更新？试举例说明。在一般情况下，只有行列子集视图才能更新，而由以下例子建立的视图不仅用到分组，而且还有avg函数，所以不能更新。已知关系模式：学生（学号，姓名，年级，专业）、选课（学号，课号，成绩），将学生的学号、姓名及他的平均成绩定义为如下一个视图：CreateviewPJCJ(学号,姓名,平均成绩)AsSelect学号,姓名,avg(成绩)2From学生,选课Where学生.学号=选课.学号Groupby学生.学号,姓名;4．建索引的目的是什么?是否索引建得越多越好?建立索引的目的是为了加快检索速度，但并不意味着表的索引建得越多越好，因为维护索引结构也需要花费系统的一定开销，尤其是对那些经常有更新操作的表，其索引结构维护的代价是很大的。通常是根据需要建立索引，如果需要经常使用某列数据进行查询，则使用该列建立索引效果会很好。另外，一般数据库管理系统中，所有的索引都是自动维护的，无须用户介入；如果建太多的索引，则会增加维护索引的代价，影响系统性能。二．问答题1．设两个关系模型为：S(sno，name,sex，age)、SC(sno，cno，grade)，请用SQL语言实现下列操作(假设每门课都有人选)：(l)求“01”号课成绩大于80分的所有男生的姓名；SelectnameFromS,SCWhereS.sno=SC.snoandcno=’01’andgrade80andsex=’男’;(2)求至少选修“01”和“03”两门课的学生信息；SelectS.*fromSWheresnoin(selectsnofromSCWherecno=’01’andsnoin(selectsnofromSCWherecno=’03’));(3)建立一个新关系模型TSCC(sno，name，avggrade)，并将学生的平均成绩存入该关系中；Selectsno,name,avg(grade)asavggradeIntoTSCCFromS,SCWhereS.sno=SC.snoGroupbyS.sno,name(4)求学习全部课程的所有学生姓名。Selectsno,nameFromS,SCWhereS.sno=SC.sno3GroupbyS.sno,namehavingcount(*)=(selectcount(distinctcno)FromSC);(5)求各课不及格学生的课号、姓名及成绩。Selectcno,name,gradeFromS,SCWhereS.sno=SC.snoandgrade60;2．已知四个关系模式：学生（学号，姓名，年级，专业）、选课（学号，课号，成绩）、课程（课号，课名，学时数）、必修课（课号，必修专业），请用SQL语言完成下列操作：(1)列出选修“数据库原理”的学生名单；Select姓名From学生,选课,课程Where学生.学号=选课.学号and选课.课号=课程.课号and课名=’数据库原理’;(2)将学生的学号、姓名及他的平均成绩定义为一个视图；CreateviewPJCJ(学号,姓名,平均成绩)AsSelect学号,姓名,avg(成绩)From学生,选课Where学生.学号=选课.学号Groupby学生.学号,姓名;(3)由(2)建立的视图是否可更新？请说明理由。由(2)建立的视图不可更新。因为在一般情况下，只有行列子集视图才能更新，而由(2)建立的视图不仅用到分组，而且还有avg函数，所以不能更新。3．今有两个关系模式：学生(学号，姓名，性别，出生日期)、选修课程(学号，课号，成绩)，请用SQL中的GRANT和REVOKE语句，完成以下授权定义或存取控制功能：(1)用户王明对两个表有SELECT权力；GrantSELECTon学生,选修课程to王明;(2)用户刘刚对两个表有INSERT和DELETE权力；GrantINSERT,DELETEon学生,选修课程to刘刚;(3)用户金星对选修课表有SELECT权力，对学生表有更新姓名字段的权力；GrantSELECTon选修课程,UPDATEon学生(姓名)to金星;(4)用户周平具有对两个表的所有权力，并具有给其他用户授权的权力；GrantALLon学生,选修课程to周平withgrantoption;4(5)对于上述每种授权情况，撤销为用户所授予的权力。RevokeSELECTon学生,选修课程from王明;RevokeINSERT,DELETEon学生,选修课程from刘刚;RevokeSELECTon选修课程,UPDATEon学生(姓名)from金星;RevokeALLon学生,选修课程from周平;4.以ss(sno,sname,sage,sdept),sc(sno,cno,grade)和cc(cno,cname,chour)为例，建立以下触发器：(1)维护引用（参照）完整性；例如，学生转专业时需要修改学号(sno)和课号(cno)，我们可以创建以下而二个触发器。CreatetriggerSS_updateOnssForupdateAsIfupdate(sno)If(selectcount(*)fromdeleted,sswheredeleted.sno=ss.sno)0UpdatescSetsc.sno=inserted.snoFromsc,deletedWheresc.sno=deleted.sno同理可得CreatetriggerCC_updateOnccForupdateAsIfupdate(cno)If(selectcount(*)fromdeleted,ccwheredeleted.cno=cc.cno)0UpdatescSetsc.sno=inserted.snoFromcc,deletedWherecc.sno=deleted.sno(2)级联删除；例如，学生转学时需要在删除学生记录的同时也删除相应的选课记录，下面是创建的触发器。CreatetriggerSS_deleteOnssFordelete5AsDeletefromscFromsc,deletedWheresc.sno=deleted.sno(3)更新grade时保证0≤grade≤100。CreatetriggerGrade_updateOnscForupdateAsIfupdate(grade)If(selectcount(*)fromdeleted,insertedwheredeleted.sno=inserted.snoanddeleted.cno=inserted.cnoand(inserted.grade0orinserted.grade100))0beginprint‘成绩更新超范围！’rollbacktransactionend[补充题]设有三个关系：S(S#,SNAME,AGE,SEX)，SC(S#,C#,GRADE)，C(C#,CNAME,TEACHER)，试用SQL语言完成下列查询语句：(1)检索LIU老师所授课程的课程号、课程名。SelectC#,CNAMEFromCWhereTEACHER=’LIU%’；(2)检索年龄大于23岁的男学生的学号与姓名。SelectS#,SNAMEFromSWhereAGE23andSEX=’男’；(3)检索学号为’S1’学生所学课程的课程名与任课教师名。SelectCNAME,TEACHERFromSC,CWhereSC.C#=C.C#andS#=’S1’;(4)检索至少选修LIU老师所授课程中一门课的女学生姓名。SelectSNAMEFromS,SC,C6WhereS.S#=SC.S#andSC.C#=C.C#andTEACHER=’LIU%’andSEX=’女’;(5)检索WANG同学不学的课程的课程号。(SelectC#fromCwhereC#notin(selectC#FromS,SCWhereS.S#=SC.S#andSNAME=’WANG%’);(6)检索至少选修两门课程的学生学号。SelectS#FromSCGroupbyS#havingcount(*)=2;*(7)检索全部学生都选修的课程的课程号与课程名。[解法1]selectSC.C#,CNAMEFromS,SC,CWhereS.S#=SC.S#andSC.C#=C.C#GroupbySC.C#,CNAMEhavingcount(*)=(selectcount(*)fromS);[解法2]用p表示谓词：学生y选修了课程x，上述题意可形式化表示如下。))(()(pypy意即：没有一个学生y是不选修课程x。SelectC#,CNAMEFromCWherenotexists(select*FromSWherenotexists(select*FromSCWhereS#=S.S#andC#=C.C#));*(8)检索选修课程包含LIU老师所授课程的学生学号。查询学号为x的学生，对任一课程y，只要老师’LIU’讲授了课程y，则学生x也选修了课程y。7假设：若用p表示谓词“老师’LIU’讲授了课程y”，用q表示谓词“学生x选修了课程y”，则上述题意可形式化表示如下：))(()))((()))((()(qpyqpyqpyqpy意即：不存在这样的课程y，老师’LIU’讲授了课程y，而学生x没有选修课程y。SelectS#FromSCSCXWherenotexists(select*FromCCYWhereTEACHER=’LIU’andnotexists(select*FromSCSCZWhereCY.C#=SCZ.C#andSCZ.S#=SCX.S#));