数据库张上机实验点评及参考答案201X.docx
《数据库张上机实验点评及参考答案201X.docx》由会员分享，可在线阅读，更多相关《数据库张上机实验点评及参考答案201X.docx（13页珍藏版）》请在冰豆网上搜索。
数据库张上机实验点评及参考答案201X
上机实验三——基本表的建立和修改
三、实习内容：
1.启动MSSQLServer服务，打开EnterpriseManager和QueryAnalyzer。
2.在QueryAnalyzer中用CREATETABLE命令在实验二创建的GradeManager数据库中定义基本表：
学生表（Student）、课程表（Course），利用EnterpriseManager的图形化功能建立班级表（Class）以及成绩表（Grade）。
createtableStudent
（Snochar（7）primarykey,
Snamevarchar（20）notnull,
Ssexchar
（2）notnull,
SageSmallint,
Clnochar（5）notnull）;
createtableClass
（Clnochar（5）primarykey,
Specialityvarchar（20）notnull,
Inyearchar（4）notnull,
Numberinteger,
Monitorchar（7））;
createtableCourse
（Cnochar
（1）primarykey,
Cnamevarchar（20）notnull,
CreditSmallint）;
createtableGrade
（Snochar（7）referencesstudent（sno）,
Cnochar
（1）referencescourse（cno）,
Gmarknumeric,
Primarykey（sno,cno））;
四、针对以上四个表，用SQL语言完成以下各项操作。
①给学生表增加一属性Nation（民族），数据类型为Varchar（20）；
②删除学生表中新增的属性Nation；
③向成绩表中插入记录（”2001110”，”3”，80）；
④修改学号为”2001110”的学生的成绩为70分；
⑤删除学号为”2001110”的学生的成绩记录；
⑥为学生表创建一个名为IX_Class的索引。
1.
ALTERTABLEStudent
ADDNationvarchar（20）;
2.
ALTERTABLEStudent
DROPCOLUMNNation;
3.
Insertintograde（sno,cno,gmark）values（‘2001110’，’3’，80）
点评：
该语句没有语法错误，但好多同学在执行时出现了问题，是对的。
但同学们要知道为什么会出现这个问题。
4.
UPDATEGrade
SETGmark=70
WHERESno='2001110';
5.
DELETE
FROMGrade
WHERESno='2001110';
6.
CREATEINDEXIX_Class
ONStudent（ClnoAsc）;
7.
DROPINDEXStudent.IX_Class;
五、思考题
在定义基本表语句时，NOTNULL参数的使用有何作用？
答：
NotNull参数，可以保证在插入数据时，该属性列的取值不为空。
上机实验四——SELECT语句的使用
（一）
三、实习内容：
完成以下各项操作的SQL语句：
①找出所有被学生选修了的课程号；
selectdistinctcnofromgrade;
该语句实现的路径应该是Grade表，Course表中可能有某门课，一个学生都没有选修的。
所以不能是查询Course表的Cno。
②找出01311班女学生的个人信息；
select*fromstudent
whereSsex='女'andclno='01311';
③找出01311班、01312班的学生姓名、性别、出生年份；
selectSname,Ssex,2012-Sageasbirth
fromstudent
whereclno='01311'orclno='01312';
OR：
selectSname,Ssex,2012-Sageasbirth
fromstudent
whereclnoin（'01311','01312'）;
OR:
（在T-SQL中,超大纲了，呵呵）
Selectsname,sex,year（getdate（））-sage
Fromstudent
Whereclnoin（‘01311’,’01312’）;
④找出所有姓李的学生的个人信息；
select*fromstudent
whereSnamelike'李%';
⑤找出学生李勇所在班级的学生人数；
Selectcount（*）fromstudent
Whereclnoin（Selectclnofromstudent
Wheresname=”李勇”）;
Or:
Selectnumberfromclass
Whereclnoin（Selectclnofromstudent
Wheresname=”李勇”）;
Or:
Selectnumberfromclass,student
Wheresname=’李勇’andclass.clno=student.clno;
⑥找出课程名为操作系统的平均成绩、最高分、最低分；
Selectavg（gmark）,Max（gmark）,Min（gmark）
Fromgrade,course
Wherecname=”操作系统”ando=o;
Or:
Selectavg（gmark）,Max（gmark）,Min（gmark）
Fromgrade
Wherecno=
（Selectcnofromcoursewherecname=”操作系统”）;
⑦找出选修了课程的学生人数；
selectcount（distinctSno）
fromgrade;
⑧找出选修了课程操作系统的学生人数。
Selectcount（sno）Fromgrade
Wherecno=
（SelectcnofromcourseWherecname=”操作系统”）;
Or:
Selectcount（sno）Fromgrade,course
Whereo=oandcname=”操作系统”;
（9）找出2000级计算机软件班的成绩为空的学生姓名。
selectSname
fromStudent
whereClnoin
（selectClno
fromClass
whereSpeciality='计算机软件'andInyear=2000）
andSnoin（selectSnofromGradewheregmarkisnull）;
四、思考题：
什么情况下需要使用关系的别名？
别名的作用范围是什么？
答：
一般几种两种情况下使用别名：
1.关系名太长，为了简化；2.为了做自身连接查询。
3.在同一个SQL语句中，为了对某个表进行2次扫描。
别名的作用范围是当前该SQL语句，离开当前SQL语句，别名就不在存在。
上机实验五——SELECT语句的使用
（二）
三、实习内容：
完成以下各项操作的SQL语句：
找出与李勇在同一个班级的学生信息；
②找出所有与李勇有相同选修课的学生信息；
③找出年龄介于学生李勇和25岁之间的学生信息；（已知李勇年龄小于25岁）
④找出选修了课程操作系统的学生学号和姓名
⑤找出没有选修1号课程的学生姓名
⑥找出选修了全部课程的学生姓名；
1找出与李勇在同一个班级的学生信息；
Select*fromstudent
Whereclno=
（Selectclnofromstudentwheresname=’李勇’）;
2找出所有与学生李勇有相同选修课程的学生信息
select*fromStudent
whereSnoin
（selectSnofromGrade
whereCnoin
（selectCnofromGrade
whereSnoin
（selectSnofromStudent
whereSname='李勇'）））
andSname<>'李勇';
3找出年龄介于学生李勇和25岁之间的学生信息；
Select*fromstudent
Wheresagebetween
（Selectsagefromstudent
Wheresname=’李勇’）
And25;
4找出选修了课程操作系统的学生学号和姓名；
Selectsno,snamefromstudent
Wheresnoin
（Selectsnofromgrade
Wherecno=
（Selectcnofromcourse
Wherecname=’操作系统’））;
5找出所有没有选修1号课程的学生姓名；
Selectsnamefromstudent
Wherenotexist
（Selectsnofromgrade
wherecno=’1’andsno=student.sno）;
OR:
Selectsnamefromstudent
Wheresnonotin
（Selectsnofromgrade
Wherecno=’1’）;
6找出选修了全部课程的学生姓名
（提示：
可找出这样的学生，没有一门课程是他不选修的。
）
下面这个编程的思路有问题，是错误的哦：
SELECTSname
FROMStudent
WHERESnoIN（SELECTSnoFROMGradeWHERECno=1）AND
SnoIN（SELECTSnoFROMGradeWHERECno=2）AND
SnoIN（SELECTSnoFROMGradeWHERECno=3）AND
SnoIN（SELECTSnoFROMGradeWHERECno=4）AND
SnoIN（SELECTSnoFROMGradeWHERECno=5）AND
SnoIN（SELECTSnoFROMGradeWHERECno=6）AND
SnoIN（SELECTSnoFROMGradeWHERECno=7）
解一:
Selectsnamefromstudent
Wherenotexists
（Select*fromcourse
wherenotexists
（Select*fromgrade
Wheresno=student.snoandcno=o）;
解二:
Selectsnamefromstudent
Wheresnoin
（Selectsnofromgrade
Groupbysno
Havingcount（*）=Selectcount（*）fromcourse）;
OR:
Selectsnamefromstudent
Where（Selectcount（cno）fromgradeGroupbysno）
=（Selectcount（*）fromcourse）;
解法三：
SelectSname
FromStudent
WHEREnotexists
（SelectCno
FromCourse
except
SelectCno
FromGrade
WHEREStudent.Sno=Grade.Sno）;
类似表达的还有：
--1.
SELECTSnameFROMStudent
WHERE（selectcount（cno）fromcourse）
=（SELECTcount（sno）FROMGrade
GROUPBYSno）
--2.
SELECTSnameFROMStudent
WHERESno=ANY
（SELECTSnoFROMGrade
GROUPBYSno
HAVING（COUNT（*）=
（SELECTCOUNT（*）FROMCourse）））
ORDERBYSname;
（1）查询选修了3号课程的学生学号及成绩，并按成绩的降序排列
selectSno,GmarkfromGrade
whereCno='3'
orderbyGmarkdesc;
（2）查询全体学生信息，要求查询结果按班级号升序排列，同一班级学生按年龄降序排列
select*fromStudent
orderbyClno,Sagedesc;
（3）求每个课程号及其相应的选课人数
selectCno,count（Sno）as选课人数fromGrade
groupbyCno;
（4）查询选修了3门以上课程的学生学号
selectSno,count（Cno）as选课门数fromGrade
groupbySno
havingcount（Cno）>3;
selectsnofromGradeg
where（selectCOUNT（cno）fromGrade
whereSno=g.Sno
）>3;
五、思考题：
1．用UNION或UNIONALL将两个SELECT命令结合为一个时，结果有何不同？
2．当既能用连接词查询又能用嵌套查询时，应该选择哪种查询较好？
为什么？
3．库函数能否直接使用在：
SELECT选取目标、HAVING子句、WHERE子句、GROUPBY列名中？
上机实验六——SQL的存储操作
三、实习内容：
完成以下各项操作的SQL语句：
1将01311班全体学生的成绩置零；
2删除2001级计算机软件的全体学生的选课记录；
3学生李勇已退学，从数据库中删除有关他的记录；
4对每个班，求学生的平均年龄，并把结果存入数据库。
①将01311班的全体学生的成绩置零；
updategrade
setgmark=0
wheresnoin（selectsnofromstudentwhereclno=’01311’）
5删除01311班全体学生的选课记录；
delete
fromgrade
wheresnoin（selectsno
fromstudent
whereclno=‘01311’;
3学生李勇已退学，从数据库中删除有关他的记录。
注意：
数据的一致性删除表的次序：
Grade/class/student
deletefromgrade
wheresnoin
（selectsnofromstudent
wheresname='李勇'）;
updateclass
setnumber=number-1
whereclno=
（selectclnofromstudent
wheresname='李勇'）;
deletefromstudent
wheresname='李勇';
作业点评：
有同学的语句如下：
deletefromstudentajoinGradebona.Sno=b.SnojoinCourseconc.Cno=b.CnowhereSname='李勇';
注意：
ANSISQL的定义是一个Delete语句只能对一个表进行删除操作！
④对每个班，求学生的平均年龄，并把结果存入数据库；
解法一：
建表、插入
createtableavgage
（clnochar（5）,
avgagesmallint）;
insertintoavgageselectclno,avg（sage）fromstudentgrpupbyclno;
解法二：
建视图
createviewv_avgage
as
selectclno,avg（sage）asavg_age
fromstudent
解法三：
修改Class表后插入
altertableclassaddavgagesmallint
go
updateclass
setavgage=（selectage（sage）fromstudent
groupbyclno
havingstudent.clno=class.clno）
解法四：
直接建表插入
selectclno,avg（sage）asavg_ageintoavgage
fromstudent
下面这种根据表中的数据进行编程的解法的思维是错误的，数据是随时都会变化的（虽然Case语句用的不错）：
altertableClassaddCagesmallintnull
updateClasssetCage=case
whenClno='00311'then（selectAVG（Sage）fromStudentwhereClno='00311'）
whenClno='00312'then（selectAVG（Sage）fromStudentwhereClno='00312'）
whenClno='01311'then（selectAVG（Sage）fromStudentwhereClno='01311'）
end
fromClass
五、思考题：
DROP命令和DELETE命令的本质区别是什么？
19.视图操作
createviewStu_00312_1--1
as
select*
fromStudent
whereStudent.Snoin（
selectSno
fromGrade
whereGrade.Cno='1'）andStudent.Clno='00312'
createviewStu_00312_2--2
asselect*
fromStudent
whereStudent.Snoin（
selectSno
fromGrade
whereGrade.Cno='1'andGrade.Gmark<=60）andStudent.Clno='00312'
createviewStu_year--3
asselectSno,Sname,2005-Sagebirthyear
fromStudent
selectSname--4
fromStu_year
wherebirthyear>1983
selectSno,Sname,2005-Sage--5
fromStu_01312_2
实验八完整性约束
Createtablestudent
（snochar（7）primarykey,
snamevarchar（20）notnull,
ssexchar
（2）notnullcheck（ssexin（'男','女'））default（'男'）,
sagesmallintcheck（sage<65andsage>14）,
clnochar（5）notnull）
---------------------------
altertablecourseaddprimarykey（cno）;
altertablecourseaddconstraintPK_courseprimarykey（cno）;
altertablecourseaddconstraintchk_cred
check（creditin（1,2,3,4,5,6））;
-----------------------------
altertableclassaddprimarykey（clno）;
altertableclassaddcheck（number>1andnumber<100）;
altertableclassaddforeignkey（monitor）referencesstudent（sno）;
------------------
altertablestudentaddforeignkey（clno）referencesclass（clno）;
---------------------------------
altertablegradeaddprimarykey（sno,cno）;
altertablegradeaddforeignkey（cno）referencescourse（cno）;
altertablegradeaddconstraintchk_markcheck（gmark>=0andGmark<=100）;
点评：
要注意外部码定义的时候，首先被参照关系（父表）必须已经定义了主码（或者唯一性约束定义），另外如果表中已经有数据的，表中已有数据不能违反参照完整性约束。
假如数据库中已有Student、Grade、Course和Class表，再添加以上约束：
Student：
altertableStudent
add Check（Ssexin（'男','女'））
altertableStudent
adddefault'男'forssex
altertableStudent
addcheck（Sage>14andSage<65）
altertableClass//先给Class表创建主键，否则Student不能建立与Class的外部约束
addprimarykey（clno）
altertableStudent
addforeignkey（clno）referencesClass（clno）
onupdateCASCADE
Course表
altertableCourse addprimarykey（cno）
altertableCourse addcheck（Creditin（1,2,3,4,5,6））
Class表
升级会员 