数据库第三章课后习题解答.docx
《数据库第三章课后习题解答.docx》由会员分享，可在线阅读，更多相关《数据库第三章课后习题解答.docx（20页珍藏版）》请在冰豆网上搜索。
数据库第三章课后习题解答
3-3习题3
在SQLServer中，创建一个名为students且包含有下列几个属性的表。
SNOchar（10）；
NAMEvarchar（10）；
SEXchar
（1）；
BDATEdatetime；
DEPTvarchar（10）；
DORMITORY varchar（10）.
要求：
1.采用两种形式创建表，即用SQL语句和用图形界面的形式来创建。
2.定义必要的约束，包括主键SNO,NAME值不允许为空，且SEX取值为0或1。
【解答】
·进入SQL查询分析器建立查询，创建students表的SQL语句如下，操作如图所示。
usemydb/*假设在mydb库中建表*/
createtablestudents
（SNOchar（10）notNULLprimarykey,
NAMEvarchar（10）notNULL,
SEXchar
（1）notNULLcheck（sex='0'orsex='1'）,
BDATEdatetime,
DEPTvarchar（10）,
DORMITORYvarchar（10）
）
图用SQL语句创建students表
·进入企业管理器用基本操作创建students表。
用右键单击“mydb”数据库，从弹出的菜单中选择“新建”,再从其下一级菜单中选择“表”。
或者，用右键单击“mydb”数据库下一级的“表”，从弹出的菜单中选择“新建表”。
然后，在弹出的窗体中，把students表所包含的字段逐一输入，每个字段都要指明列名、数据类型、长度和是否允许空值、是否主键等内容，如图所示。
图用基本操作创建students表
其中，SEX字段取值为0或1，需要建立约束。
操作是用右键单击SEX字段，从弹出的菜单中选择“CHECK约束”，再从弹出的“属性”窗体中，选择“CHECK约束”卡，在约束表达式框中输入约束表达式，如图所示。
图输入约束表达式
最后，单击“保存”图标，SQLServer将弹出一个“选择名称”对话框，输入表名“students”，单击“确定”按钮，新建的students表结构将被保存起来。
在Access中，完成习题的要求。
【解答】
createtablestudents
（SNOtext（10）notnullprimarykey,
NAMEtext（10）notnull,
SEXtext（10）notnull,
BDATEdatetime,
DEPTtext（10）,
DORMITORYtext（10））
注意，J-SQL的CREATETABLE语句没有提供对字段的检查约束。
可以在创建表后，使用基本操作方式，对SEX字段建立有效性规则。
在SQLServer中，创建表Depts（DNO,DNAME,MGR）。
用SQL语句在习题中创建的Students表中将DEPT设置为外键，引用Depts表中的DNO列值。
若某系还有学生时，不得在Depts表中删除该系的记录。
usemydb
createtableDepts
（DNOvarchar（10）notNULLprimarykey,
DNAMEchar（10）notNULL,
MGRchar（10））
altertablestudentsaddconstraintc1
foreignkey（Dept）referencesDepts（Dno）ondeletecascade
什么是视图视图的作用是什么在习题所创建的Students表的基础上，建立一个显示所有计算机系学生的视图，假设计算机系的代号为‘CS’。
usemydb
go
createviewstudent_cs_view/*SQLSERVER约定：
本语句必须为批处理的第一个语句*/
as
select*
fromstudents
where='cs'
在SQLServer中完成下列操作：
①用修改表结构语句在Students表中添加整型的Height和Weight字段。
②创建一个规则并绑定到Height列，用以限制插入到该列的整数范围。
③创建一个默认值并绑定到Weight列，插入记录时，默认值自动填充到该列中。
④基于Students表与Depts表，分别建立惟一性约束、检查约束、主键约束、外键约束和参照（引用）完整性约束，并辅以其他操作予以验证。
⑤创建并执行一个带SELECT查询语句的存储过程,统计出每个系的学生平均身高。
⑥创建一个触发器，其功能是：
当试图在Depts表中修改数据时将发出警告消息。
【解答】
①在Students表中添加整型的Height和Weight字段：
usemydb
go
altertablestudentsaddHeightint,Weightint
②创建一个规则并绑定到Height列，用以限制插入到该列的整数范围：
usemydb
go
createruleR1as@range>=145and@range<=200
go
execsp_bindrule@rulename='R1',@objname=''
③创建一个默认值并绑定到Weight列，插入记录时默认值自动填充到该列中：
usemydb
go
createdefaultD1as50
go
execsp_bindefault@defname='D1',@objname=''
④基于Students表与Depts表，分别建立惟一性约束、检查约束、主键约束、外键约束和参照（引用）完整性约束：
/*假设在mydb库中建表，若表students已存在，创建前先把该表删除*/
usemydb
createtablestudents
（SNOchar（10）notNULLprimarykey,/*主键约束*/
NAMEvarchar（10）notNULLunique,/*惟一性约束*/
SEXchar
（1）notNULLcheck（sex='0'orsex='1'）,/*检查约束*/
BDATEdatetime,
DEPTvarchar（10）constraintc1foreignkey（Dept）/*外键约束*/
referencesDepts（Dno）ondeletecascade,/*参照完整性约束*/
DORMITORYvarchar（10）
）
注：
创建表后，有关验证性的操作，请读者自行完成。
⑤创建一个带SELECT查询语句的存储过程p1，统计出每个系的学生平均身高：
usemydb
go
createprocp1
as
selectdept,avg（Height）asavg_h
fromstudents
groupbydept
return
执行存储过程p1的语句如下：
usemydb
go
execp1
⑥创建一个触发器，其功能是当试图在Depts表中修改数据时将发出警告消息：
usemydb
go
createtriggert1ondepts
forupdate
as
raiserror（'警告！
',10,1）
go
当使用update语句修改Depts表中数据时，将在消息栏显示“警告！
”消息。
假设教学数据库中有三个表，其数据结构如下：
学生表S（学号SNO,姓名SNAME,年龄AGE,性别SEX）；
选修表SC（学号SNO,课程号CNO,成绩GRADE）；
课程表C（课程号CNO,课程名CNAME,任课教师TEACHER）；
试用基本的SELECT语句表达下列操作：
①检索选修课程号为C06的学生学号与成绩。
②检索选修课程号为C06的学生学号与姓名。
③检索选修课程名为ENGLISH的学生学号与姓名。
④检索选修课程号为C08或C12的学生学号与成绩。
⑤检索至少选修课程号为C08和C12的学生学号与成绩。
⑥检索没有选修C02号课程的学生姓名与年龄。
⑦检索选修了全部课程的学生姓名。
⑧检索选修课程中包含了学生S05所学课程的学生学号。
⑨求女学生的总人数和平均年龄。
⑩统计选修了课程的学生人数。
【解答】
①检索选修课程号为C06的学生学号与成绩。
usemydb
selectsno,grade
fromsc
wherecno='c06'
②检索选修课程号为C06的学生学号与姓名。
·第1种查询方法——连接查询：
usemydb
SELECT,sname
FROMs,sc
WHERE=andcno='c06';
·第2种查询方法——嵌套查询：
usemydb
SELECTsno,sname
FROMs
WHEREsnoin
（SELECTsno
FROMsc
WHEREcno='c06'）;
·嵌套查询（使用相关查询）：
usemydb
SELECTsno,sname
FROMs
WHERE'c06'in
（SELECTcno
FROMsc
WHEREsno=;
·第3种查询方法——使用存在量词的嵌套查询：
usemydb
SELECTsno,sname
FROMs
WHEREexists（SELECT*
FROMsc
WHERE=andcno='c06'）;
③检索选修课程名为ENGLISH的学生学号与姓名。
·嵌套查询：
usemydb
SELECTsno,sname
FROMs
WHEREsnoin
（SELECTsno
FROMsc
WHEREcnoin
（SELECTcno
FROMc
WHEREcname='ENGLISH'））;
·连接查询：
usemydb
SELECT,sname
FROMs,c,sc
WHERE=and=andcname='english';
④检索选修课程号为C08或C12的学生学号与成绩。
usemydb
SELECT*
FROMsc
WHEREcno='C08'orcno='C12';
注：
这里输出选修表sc的所有列，除学生学号与成绩外，还有选课的课程号。
若某个学生同时选修了C08和C12两门课程，可通过选课的课程号予以区分。
⑤检索至少选修课程号为C08和C12的学生学号与成绩。
usemydb
SELECT,,
FROMscASA,scASB
WHERE=and='C08'and='C12';
⑥检索没有选修C02号课程的学生姓名与年龄。
usemydb
SELECTsname,age
FROMs
WHEREsnonotin
（SELECTsno
FROMsc
WHEREcnoin
（SELECTcno
FROMc
WHEREcno='C02'））;
若把最外层的WHERE子句由“notin”改为“notexists”，则代码如下：
usemydb
SELECTsname,age
FROMs
WHEREnotexists
（SELECTsno
FROMsc
WHERE=andcnoin
（SELECTcno
FROMc
WHEREcno='C02'））;
⑦检索选修了全部课程的学生姓名。
usemydb
SELECTsno,sname
FROMs
WHEREnotexists
（SELECT*
FROMc
WHEREnotexists
（SELECT*
FROMsc
WHERE=
and=））;
⑧检索选修课程中包含了学生S05所学课程的学生学号。
usemydb
SELECTDISTINCTsno
FROMsc
WHEREsno<>'S05'andcnoin
（SELECTcno
FROMsc
WHEREsno='S05'）;
⑨求女学生的总人数和平均年龄。
usemydb
SELECTcount（*）as总人数,avg（age）as平均年龄
FROMs
WHEREsex='女'
⑩统计选修了课程的学生人数。
usemydb
selectcount（distinctsno）as选课人数
fromsc
对习题给出的表，用完整的SELECT语句或使用限定等方式表达下列操作：
①统计每一年龄选修课程的学生人数。
②求S表中男学生的每一年龄组（超过3人）的人数；查询结果按人数升序排列，若人数相同按年龄降序排列。
③检索女学生选修的所有课程号。
④检索每个学生的出生年份,输出学生姓名和出生年份分别用新列名:
XM,CSNF。
⑤检索18～20岁且姓名以字符L打头的学生姓名。
⑥检索至少没有选修C02和C03两门课程的学生学号。
⑦检索选修表SC中平均成绩最高的学生学号。
⑧检索出每门课程的最高分和最低分。
⑨使用COMPUTE子句生成Students表中Weight列的和及平均值。
⑩建立另一个Students1表，求Students表与Students1表的并集、差集、交集。
【解答】
①统计每一年龄选修课程的学生人数。
usemydb
selectageas年龄,count（distinctas人数
froms,sc
where=
groupbyage
②求S表中男学生的每一年龄组（超过3人）的人数；查询结果按人数升序排列，若人数相同按年龄降序排列。
usemydb
selectageas年龄,count（distinctas人数
froms,sc
where=andsex='男'
groupbyage
havingcount（distinct>3
orderby2,agedesc
③检索女学生选修的所有课程号。
usemydb
selectdistinctas课程号
froms,sc
where=andsex='女'
④检索每个学生的出生年份,输出学生姓名和出生年份分别用新列名:
XM,CSNF。
usemydb
selectsnameasXM,year（getdate（））-ageasCSNF
froms
⑤检索18～20岁且姓名以字符L打头的学生姓名。
usemydb
selectsname
froms
whereagebetween18and20andsnamelike'L%'
⑥检索至少没有选修C02和C03两门课程的学生学号。
usemydb
SELECTsno
FROMs
WHEREsnonotin
（SELECT
FROMscASA,scASB
WHERE=and='C02'and='C03'）;
⑦检索选修表SC中平均成绩最高的学生学号。
usemydb
selectsnoas学号,mas最高平均分
from（SELECTsno,m=avg（grade）FROMscGROUPBYsno）asA
wherem>=all
（SELECTavg（grade）FROMscGROUPBYsno）
⑧检索出每门课程的最高分和最低分。
usemydb
selectcnoas课程号,min（grade）as最高分,max（grade）as最低分
fromsc
groupbycno
⑨使用COMPUTE子句生成Students表中Weight列的和及平均值。
usemydb
select*
fromstudents
computesum（weight）,avg（weight）
⑩建立另一个Students1表，求Students表与Students1表的并集、差集、交集。
usemydb
/*并集*/
select*
fromstudents
union
select*
fromstudent1
/*差集*/
select*
fromstudents
wheresnonotin
（selectsno
fromstudent1）
/*交集*/
selectstudents.*
fromstudentsINNERJOINstudent1ON=;
对于如下关系模式：
雇员表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表中，假设新进雇员薪酬未定，暂以空值表示。
【解答】
①检索出所有为“IBM公司”工作的雇员名字。
usemydb
selectENAME
fromEMP
whereEIDin
（selectEID
fromWORKS
whereCIDin
（selectCID
fromCOMP
whereCNAME='IBM公司'））
本题的检索可以使用多种不同的形式，例如：
usemydb
selectENAME
fromEMP,WORKS,COMP
where='IBM公司'AND
=AND
=
②检索出所有年龄超过50岁的女性雇员的姓名和所在公司的名称。
usemydb
selectENAME,
fromEMP,WORKS,COMP
whereyear（getdate（））-year>50AND
='女'AND
=AND
=
③检索出所有居住城市与公司所在城市相同的雇员。
usemydb
selectENAME,,
fromEMP,WORKS,COMP
where=AND
=AND
=
④检索出“IBM公司”雇员的人数，平均工资，最高工资和最低工资，并且分别用E#，AVG_SAL，MAX_SAL，MIN_SAL作为列标题。
usemydb
selectCOUNT（EID）ASE#,AVG（SALARY）ASAVG_SAL,
MAX（SALARY）ASMAX_SAL,MIN（SALARY）ASMIN_SAL
fromWORKS,COMP
where='IBM公司'AND=
⑤检索同时在“IBM公司”和“SAP公司”兼职的雇员名字。
usemydb
selectEID,ENAME
fromEMP
whereEIDIN
（selectEID
fromWORKS
whereCIDIN
（selectCID
fromCOMP
whereCNAME='IBM公司'orCNAME='SAP公司'））
⑥检索出工资高于其所在公司雇员平均工资的所有雇员。
usemydb
selectEIDas雇员编号,ENAMEas姓名
fromEMP
whereEIDin
（select
from（selectCID,M=avg（SALARY）fromWORKSgroupbyCID）asA,WORKSasB
where=and>
⑦检索雇员最多的公司。
usemydb
selectCIDas公司编号,CNAMEas公司名称
fromCOMP
whereCIDin
（selectCID
from（selectCID,M=COUNT（EID）fromWORKSgroupbyCID）asA
whereM>=all
（selectCOUNT（EID）fromWORKSgroupbyCID））
⑧为工龄超出10年的雇员加薪10%。
usemydb
updateWORKS
setSALARY=SALARY*
whereyear（getdate（））-year（STARTDATE）>10
⑨年龄大于60岁的雇员应办理退休手续，删除退休雇员的所有相关记录。
usemydb
deleteEMP
whereyear（getdate（））-year（BDATE）>60
⑩“IBM公司”增加某新雇员，将该雇员有关的记录插入到EMP表和WORKS表中，假设新进雇员薪酬未定，暂以空值表示。
usemydb
insertintoEMP（EID,ENAME,BDATE,SEX,CITY）
values（'E07','andy','1970-3-8','男','广州'）
go
insertintoWORKS（EID,CID,STARTDATE,SALARY）
values（'E07','C01',getdate（）,null）
根据习题给出的关系模式，创建一个视图，按照公司顺序来显示其所有雇员的有关信息。
【解答】
usemydb
go
createviewC_E_VIEW
as
select,CNAME,CITY,EID,STARTDATE,SALARY
fromCOMPleftjoinWORKSon=
go
select*
fromC_E_VIEW
对习题给出的三个表，试用T-SQL更新语句表达下列更新操作：
①在S表中插入一行：
（‘S06’,‘WANG’,20）。
②在S表中检索出每一门成绩
升级会员 