数据库第三章课后习题解答.docx
- 文档编号:4189802
- 上传时间:2022-11-28
- 格式:DOCX
- 页数:20
- 大小:183.10KB
数据库第三章课后习题解答.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表中检索出每一门成绩
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 第三 课后 习题 解答