数据库第三章课后习题解答Word格式.docx
《数据库第三章课后习题解答Word格式.docx》由会员分享，可在线阅读，更多相关《数据库第三章课后习题解答Word格式.docx（20页珍藏版）》请在冰豆网上搜索。
图3.18用基本操作创建students表
其中，SEX字段取值为0或1，需要建立约束。
操作是用右键单击SEX字段，从弹出的菜单中选择“CHECK约束”，再从弹出的“属性”窗体中，选择“CHECK约束”卡，在约束表达式框中输入约束表达式，如图3.19所示。
图3.19输入约束表达式
最后，单击“保存”图标，SQLServer将弹出一个“选择名称”对话框，输入表名“students”，单击“确定”按钮，新建的students表结构将被保存起来。
3.5在Access中，完成习题3.4的要求。
（SNOtext（10）notnullprimarykey,
NAMEtext（10）notnull,
SEXtext（10）notnull,
BDATEdatetime,
DEPTtext（10）,
DORMITORYtext（10））
注意，J-SQL的CREATETABLE语句没有提供对字段的检查约束。
可以在创建表后，使用基本操作方式，对SEX字段建立有效性规则。
3.7在SQLServer中，创建表Depts（DNO,DNAME,MGR）。
用SQL语句在习题3.4中创建的Students表中将DEPT设置为外键，引用Depts表中的DNO列值。
若某系还有学生时，不得在Depts表中删除该系的记录。
usemydb
createtableDepts
（DNOvarchar（10）notNULLprimarykey,
DNAMEchar（10）notNULL,
MGRchar（10））
altertablestudentsaddconstraintc1
foreignkey（Dept）referencesDepts（Dno）ondeletecascade
3.8什么是视图？
视图的作用是什么？
在习题3.4所创建的Students表的基础上，建立一个显示所有计算机系学生的视图，假设计算机系的代号为‘CS’。
go
createviewstudent_cs_view/*SQLSERVER约定：
本语句必须为批处理的第一个语句*/
as
select*
fromstudents
wherestudents.DEPT='
cs'
3.11在SQLServer中完成下列操作：
①用修改表结构语句在Students表中添加整型的Height和Weight字段。
②创建一个规则并绑定到Height列，用以限制插入到该列的整数围。
③创建一个默认值并绑定到Weight列，插入记录时，默认值自动填充到该列中。
④基于Students表与Depts表，分别建立惟一性约束、检查约束、主键约束、外键约束和参照（引用）完整性约束，并辅以其他操作予以验证。
⑤创建并执行一个带SELECT查询语句的存储过程,统计出每个系的学生平均身高。
⑥创建一个触发器，其功能是：
当试图在Depts表中修改数据时将发出警告消息。
①在Students表中添加整型的Height和Weight字段：
altertablestudentsaddHeightint,Weightint
②创建一个规则并绑定到Height列，用以限制插入到该列的整数围：
createruleR1asrange>
=145andrange<
=200
execsp_bindrulerulename='
R1'
objname='
students.Height'
③创建一个默认值并绑定到Weight列，插入记录时默认值自动填充到该列中：
createdefaultD1as50
execsp_bindefaultdefname='
D1'
objname='
students.Weight'
④基于Students表与Depts表，分别建立惟一性约束、检查约束、主键约束、外键约束和参照（引用）完整性约束：
/*假设在mydb库中建表，若表students已存在，创建前先把该表删除*/
（SNOchar（10）notNULLprimarykey,/*主键约束*/
NAMEvarchar（10）notNULLunique,/*惟一性约束*/
SEXchar
（1）notNULLcheck（sex='
orsex='
）,/*检查约束*/
DEPTvarchar（10）constraintc1foreignkey（Dept）/*外键约束*/
referencesDepts（Dno）ondeletecascade,/*参照完整性约束*/
DORMITORYvarchar（10）
注：
创建表后，有关验证性的操作，请读者自行完成。
⑤创建一个带SELECT查询语句的存储过程p1，统计出每个系的学生平均身高：
createprocp1
as
selectdept,avg（Height）asavg_h
fromstudents
groupbydept
return
执行存储过程p1的语句如下：
execp1
⑥创建一个触发器，其功能是当试图在Depts表中修改数据时将发出警告消息：
createtriggert1ondepts
forupdate
raiserror（'
警告！
'
10,1）
当使用update语句修改Depts表中数据时，将在消息栏显示“警告！
”消息。
3.12假设教学数据库中有三个表，其数据结构如下：
学生表S（学号SNO,SNAME,年龄AGE,性别SEX）；
选修表SC（学号SNO,课程号CNO,成绩GRADE）；
课程表C（课程号CNO,课程名CNAME,任课教师TEACHER）；
试用基本的SELECT语句表达下列操作：
①检索选修课程号为C06的学生学号与成绩。
②检索选修课程号为C06的学生学号与。
③检索选修课程名为ENGLISH的学生学号与。
④检索选修课程号为C08或C12的学生学号与成绩。
⑤检索至少选修课程号为C08和C12的学生学号与成绩。
⑥检索没有选修C02号课程的学生与年龄。
⑦检索选修了全部课程的学生。
⑧检索选修课程中包含了学生S05所学课程的学生学号。
⑨求女学生的总人数和平均年龄。
⑩统计选修了课程的学生人数。
selectsno,grade
fromsc
whereo='
c06'
第1种查询方法——连接查询：
SELECTs.sno,sname
FROMs,sc
WHEREs.sno=sc.snoando='
;
第2种查询方法——嵌套查询：
SELECTsno,sname
FROMs
WHEREsnoin
（SELECTsno
FROMsc
WHEREo='
）;
嵌套查询（使用相关查询）：
WHERE'
in
（SELECTo
WHEREsno=s.sno）;
第3种查询方法——使用存在量词的嵌套查询：
SELECTsno,sname
WHEREexists（SELECT*
WHEREs.sno=sc.snoando='
嵌套查询：
（SELECTsno
FROMsc
WHEREoin
FROMc
WHEREame='
ENGLISH'
））;
连接查询：
FROMs,c,sc
WHEREs.sno=sc.snoando=oandame='
english'
;
SELECT*
FROMsc
WHEREo='
C08'
oro='
C12'
注：
这里输出选修表sc的所有列，除学生学号与成绩外，还有选课的课程号。
若某个学生同时选修了C08和C12两门课程，可通过选课的课程号予以区分。
SELECTA.sno,A.grade,B.grade
FROMscASA,scASB
WHEREA.sno=B.snoandA.cno='
andB.cno='
;
SELECTsname,age
WHEREsnonotin
（SELECTo
FROMc
C02'
））;
若把最外层的WHERE子句由“notin”改为“notexists”，则代码如下：
WHEREnotexists
（SELECTsno
WHEREs.sno=sc.snoandoin
（SELECT*
WHEREnotexists
WHEREs.sno=sc.sno
ando=o））;
SELECTDISTINCTsno
WHEREsno<
>
S05'
andoin
WHEREsno='
）;
SELECTcount（*）as总人数,avg（age）as平均年龄
WHEREsex='
女'
selectcount（distinctsno）as选课人数
3.13对习题3.12给出的表，用完整的SELECT语句或使用限定等方式表达下列操作：
①统计每一年龄选修课程的学生人数。
②求S表中男学生的每一年龄组（超过3人）的人数；
查询结果按人数升序排列，若人数相同按年龄降序排列。
③检索女学生选修的所有课程号。
④检索每个学生的出生年份,输出学生和出生年份分别用新列名:
XM,CSNF。
⑤检索18～20岁且以字符L打头的学生。
⑥检索至少没有选修C02和C03两门课程的学生学号。
⑦检索选修表SC中平均成绩最高的学生学号。
⑧检索出每门课程的最高分和最低分。
⑨使用COMPUTE子句生成Students表中Weight列的和及平均值。
⑩建立另一个Students1表，求Students表与Students1表的并集、差集、交集。
selectageas年龄,count（distincts.sno）as人数
froms,sc
wheres.sno=sc.sno
groupbyage
wheres.sno=sc.snoandsex='
男'
havingcount（distincts.sno）>
3
orderby2,agedesc
selectdistinctoas课程号
XM,CSNF。
selectsnameasXM,year（getdate（））-ageasCSNF
froms
selectsname
whereagebetween18and20andsnamelike'
L%'
SELECTsno
WHEREsnonotin
（SELECTA.sno
FROMscASA,scASB
WHEREA.sno=B.snoandA.cno='
C03'
selectsnoas学号,mas最高平均分
from（SELECTsno,m=avg（grade）FROMscGROUPBYsno）asA
wherem>
=all
（SELECTavg（grade）FROMscGROUPBYsno）
selectoas课程号,min（grade）as最高分,max（grade）as最低分
groupbyo
computesum（weight）,avg（weight）
/*并集*/
union
fromstudent1
/*差集*/
wheresnonotin
（selectsno
fromstudent1）
/*交集*/
selectstudents.*
fromstudentsINNERJOINstudent1ONstudents.sno=student1.sno;
3.14对于如下关系模式：
雇员表EMP（雇员编号EID,ENAME,出生年月BDATE,性别SEX,居住城市CITY）；
公司表COMP（公司编号CID,公司名称CNAME,公司所在城市CITY）；
工作表WORKS（雇员编号EID,公司编号CID,加入公司日期STARTDATE，薪酬SALARY）；
试用SQL完成下列操作：
①检索出所有为“IBM公司”工作的雇员名字。
②检索出所有年龄超过50岁的女性雇员的和所在公司的名称。
③检索出所有居住城市与公司所在城市相同的雇员。
④检索出“IBM公司”雇员的人数，平均工资，最高工资和最低工资，并且分别用E#，AVG_SAL，MAX_SAL，MIN_SAL作为列标题。
⑤检索同时在“IBM公司”和“SAP公司”兼职的雇员名字。
⑥检索出工资高于其所在公司雇员平均工资的所有雇员。
⑦检索雇员最多的公司。
⑧为工龄超出10年的雇员加薪10%。
⑨年龄大于60岁的雇员应办理退休手续，删除退休雇员的所有相关记录。
⑩“IBM公司”增加某新雇员，将该雇员有关的记录插入到EMP表和WORKS表中，假设新进雇员薪酬未定，暂以空值表示。
selectENAME
fromEMP
whereEIDin
（selectEID
fromWORKS
whereCIDin
（selectCID
fromCOMP
whereCNAME='
IBM公司'
））
本题的检索可以使用多种不同的形式，例如：
fromEMP,WORKS,COMP
whereCOMP.CNAME='
AND
COMP.CID=WORKS.CIDAND
WORKS.EID=EMP.EID
selectENAME,COMP.CNAME
whereyear（getdate（））-year（EMP.BDATE）>
50AND
EMP.SEX='
AND
EMP.EID=WORKS.EIDAND
WORKS.CID=COMP.CID
selectENAME,EMP.CITY,COMP.CITY
whereEMP.CITY=COMP.CITYAND
selectCOUNT（EID）ASE#,AVG（SALARY）ASAVG_SAL,
MAX（SALARY）ASMAX_SAL,MIN（SALARY）ASMIN_SAL
fromWORKS,COMP
whereCOMP.CNAME='
ANDCOMP.CID=WORKS.CID
selectEID,ENAME
whereEIDIN
whereCIDIN
whereCNAME='
orCNAME='
SAP公司'
selectEIDas雇员编号,ENAMEas
（selectB.EID
from（selectCID,M=avg（SALARY）fromWORKSgroupbyCID）asA,WORKSasB
whereA.CID=B.CIDandB.SALARY>
A.M）
selectCIDas公司编号,CNAMEas公司名称
fromCOMP
whereCIDin
from（selectCID,M=COUNT（EID）fromWORKSgroupbyCID）asA
whereM>
（selectCOUNT（EID）fromWORKSgroupbyCID））
updateWORKS
setSALARY=SALARY*1.1
whereyear（getdate（））-year（STARTDATE）>
10
deleteEMP
whereyear（getdate（））-year（BDATE）>
60
⑩“IBM公司”增加某新雇员，将该雇员有关的记录插入到EMP表和W
升级会员 