附录C大数据库上机实验.docx
《附录C大数据库上机实验.docx》由会员分享，可在线阅读，更多相关《附录C大数据库上机实验.docx（21页珍藏版）》请在冰豆网上搜索。
附录C大数据库上机实验
附录C上机实验
C.1第4章上机实验
----------------------陈宇超，仅供参考------------------------------
下列实验均使用SQLServer的SSMS工具实现。
1．用图形化方法创建符合如下条件的数据库（创建数据库的方法可参见本书附录A）：
●数据库名为：
学生数据库
●主要数据文件的逻辑文件名为：
Students_data，存放在D:
\Data文件夹下（若D:
盘中无此文件夹，请先建立此文件夹，然后再创建数据库。
），初始大小为：
5MB，增长方式为自动增长，每次增加1MB。
●日志文件的逻辑文件名字为：
Students_log，也存放在D:
\Data文件夹下，初始大小为：
2MB，增长方式为自动增长，每次增加10%。
2．选用已建立的“学生数据库”，写出创建满足表C-1到4-4条件的表的SQL语句，并执行所写代码。
（注：
“说明”部分不作为表定义内容）
表C-1Student表结构
列名
说明
数据类型
约束
Sno
学号
普通编码定长字符串，长度为7
主键
Sname
姓名
普通编码定长字符串，长度为10
非空
Ssex
性别
普通编码定长字符串，长度为2
取值范围：
{男，女}
Sage
年龄
微整型（tinyint）
取值范围：
15-45
Sdept
所在系
普通编码不定长字符串，长度为20
默认值为“计算机系”
Sid
身份证号
普通编码定长字符串，长度为10
取值不重
Sdate
入学日期
日期
默认为系统当前日期
表C-2Course表结构
列名
说明
数据类型
约束
Cno
课程号
普通编码定长字符串，长度为10
主键
Cname
课程名
普通编码不定长字符串，长度为20
非空
Credit
学时数
整型
取值大于0
Semester
学分
小整型
表C-3SC表结构
列名
说明
数据类型
约束
Sno
学号
普通编码定长字符串，长度为7
主键，引用Student的外键
Cno
课程号
普通编码定长字符串，长度为10
主键，引用Course的外键
Grade
成绩
小整型
取值范围为0-100
表C-4Teacher表结构
列名
说明
数据类型
约束
Tno
教师号
普通编码定长字符串，长度为8
非空
Tname
教师名
普通编码定长字符串，长度为10
非空
Salary
工资
定点小数，小数点前4位，小数点后2位
3.写出实现如下功能的SQL语句，并执行所写代码，查看执行结果。
（1）在Teacher表中添加一个职称列，列名为：
Title，类型为nchar（4）。
ALTERTABLETeacherADDTitleVARCHAR（4）
（2）为Teacher表中的Title列增加取值范围约束，取值范围为：
{教授，副教授，讲师}。
ALTERTABLETeacherADDCONSTRAINTTitleCHECK（TitleIN（'教授','副教授','讲师'））
（3）将Course表中Credit列的类型改为：
tinyint。
注意：
这里首先需要先删除Credit的约束,修改完数据类型后再重新添加约束项
ALTERTABLECourseDROPCONSTRAINTCK__Course__Credit__2B3F6F97
ALTERTABLECourseALTERCOLUMNCreditTINYINT
ALTERTABLECourseADDCONSTRAINTCreditCHECK（Credit>0）
（4）删除Student表中的Sid和Sdate列。
注意：
这里首先需要先删除约束
ALTERTABLEStudentDROPCONSTRAINTUQ__Student__CA1E5D79B3204FCF
ALTERTABLEStudentDROPCONSTRAINTDF__Student__Sdate__3B75D760
ALTERTABLEStudentDROPCOLUMNSdate,Sid
（5）为Teacher表添加主键约束，其主键为：
Tno。
ALTERTABLETeacherADDPRIMARYKEY（Tno）
※建立数据库主要文件和日志文件
※我们可以看到D盘增加了主要数据文件.mdf和日志文件.ldf
※给students_data建立表格
createtableStudent（
Snochar（7）,
Snamechar（10）notnull,
Ssexchar
（2）check（Ssex='男'orSsex='女'）,
Sagetinyintcheck（Sage>14ANDSage<46）,
Sdeptvarchar（20）default'计算机系',
Sidchar（10）unique,--身份证号
Sdatedatedefaultgetdate（）
primarykey（Sno）
）
createtableCourse（
Cnochar（10）primarykey,
Cnamevarchar（20）notnull,
Creditintcheck（Credit>0）,
Semestertinyint,
）
createtableSC（
Snochar（7）,
Cnochar（10）,
Gradetinyintcheck（Grade>=0ANDGrade<=100）
foreignkey（Sno）referencesStudent（Sno）,
foreignkey（Cno）referencesCourse（Cno）
）
createtableTeacher（
Tnochar（8）notnull,
Tnamechar（10）notnull,
Salarydecimal（6,2）
）
C.2第5章上机实验
本实验均在SQLServer的SSMS工具中实现。
首先在已创建的“学生数据库”中创建本章表5-1~5-3所示的Student、Course和SC表，并插入表5-4~5-6所示数据，然后编写实现如下操作的SQL语句，执行所写的语句，并查看执行结果。
有关建表及所需要的数据，见本章末尾
1.查询SC表中的全部数据。
SELECT*FROMSC
2.查询计算机系学生的姓名和年龄。
SELECTSname,SageFROMStudentWHERESdept='计算机系'
3.查询成绩在70～80分的学生的学号、课程号和成绩。
SELECTSno,Cno,GradeFROMSCWHEREGradebetween70AND80
4.查询计算机系年龄在18～20岁的男生姓名和年龄。
SELECTSname,SageFROMStudentWHERESdept='计算机系'ANDSageBETWEEN18AND20ANDSsex='男'
5.查询C001课程的最高分。
SELECTMAX（grade）AS最高成绩FROMSCWHERECno='C001'
6.查询计算机系学生的最大年龄和最小年龄。
SELECTMAX（Sage）AS年长,MIN（Sage）AS年幼FROMStudentWHERESdept='计算机系'
7.统计每个系的学生人数。
SELECTSdept,COUNT（Sno）AS人数FROMStudentGROUPBYSdept
8.统计每门课程的选课人数和最高成绩。
SELECTCno,COUNT（Sno）AS人数,MAX（Grade）AS最高成绩FROMSCGROUPBYCno
9.统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果。
SELECTSno,COUNT（Cno）AS选课数量,SUM（Grade）AS考试总成绩FROMSCGROUPBYSno
10.列出总成绩超过200的学生的学号和总成绩。
SELECTSno,SUM（Grade）AS考试总成绩FROMSCGROUPBYSnoHAVINGSUM（Grade）>200
11.查询选了C002课程的学生姓名和所在系。
SELECTSname,SdeptFROMStudentst,SCscWHEREst.Sno=sc.SnoANDCno='C002'
或者
SELECTSname,SdeptFROMStudentWHERESnoIN（SELECTSnoFROMSC
WHERECno='C002'）
或者
SELECTSname,SdeptFROMStudentWHEREEXISTS（SELECT*FROMSC
WHERESno=Student.SnoANDCno='C002'）
12.查询考试成绩80分以上的学生姓名、课程号和成绩，并按成绩降序排列结果。
SELECTSname,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.SnoANDGrade>80ORDERBYGradeDESC
13.查询与VB在同一学期开设的课程的课程名和开课学期。
SELECTc2.Cname,c2.SemesterFROMCoursec1,Coursec2
WHEREc1.Cname=c2.CnameANDc1.Cname='VB'ANDc2.Cname!
='VB'
14.查询与李勇年龄相同的学生的姓名、所在系和年龄。
SELECTs2.Sname,s2.Sdept,s2.SageFROMStudents1,Students2
WHEREs1.Sage=s2.SageANDs1.Sname='李勇'ANDs2.Sname!
='李勇'
或者
SELECTSname,Sdept,SageFROMStudentWHERESage=（SELECTSageFROMStudentWHERESname='李勇'）ANDSname!
='李勇'
15.查询哪些课程没有学生选修，列出课程号和课程名。
SELECTCourse.Cno,Course.CnameFROMCourseLEFTJOINSConSC.Cno=Course.CnoWHERESC.CnoISNULL
16.查询每个学生的选课情况，包括未选课的学生，列出学生的学号、姓名、选的课程号。
SELECTStudent.Sno,Student.Sname,CnoFROMStudentLEFTJOINSCONSC.Sno=Student.Sno
17.查询计算机系哪些学生没有选课，列出学生姓名。
SELECTSnameFROMStudentWHERESdept='计算机系'ANDSnoNOTIN（SELECTSnoFROMSC）
或者
SELECTSnameFROMStudentSLEFTJOINSCONS.Sno=SC.Sno
WHERESdept='计算机系'ANDSC.CnoISNULL
18.查询计算机系年龄最大的三个学生的姓名和年龄。
SELECTTop3Sname,SageFROMStudentWHERESdept='计算机系'ORDERBYSageDESC
19.列出“VB”课程考试成绩前三名的学生的学号、姓名、所在系和VB成绩。
SELECTTOP3WITHTIESSname,Sdept,GradeFROMStudentSJOINSConS.Sno=SC.SnoJOINCourseCONC.Cno=SC.CnoWHERECname='VB'
ORDERBYGradeDESC
20.查询选课门数最多的前2位学生，列出学号和选课门数。
√
SELECTtop2Sno,COUNT（Cno）AS选课数FROMSCGROUPBYSnoORDERBYCOUNT（Cno）DESC
21.查询计算机系学生姓名、年龄和年龄情况，其中年龄情况为：
如果年龄小于18，则显示“偏小”；如果年龄在18-22，则显示“合适”；如果年龄大于22，则显示“偏大”。
√
SELECTSno,Sage,
CASE
WHENSage<18THEN'偏小'
WHENSage>=18ANDSage<=22THEN'适合'
WHENSage>22THEN'偏大'
END
AS年龄情况FROMStudent
22.统计每门课程的选课人数，包括有人选的课程和没有人选的课程，列出课程号，选课人数及选课情况，其中选课情况为：
如果此门课程的选课人数超过100人，则显示“人多”；如果此门课程的选课人数在40～100，则显示“一般”；如果此门课程的选课人数在1～40，则显示“人少”；如果此门课程没有人选，则显示“无人选”。
√
SELECTCourse.Cno,COUNT（Sno）AS选课人数,
CASE
WHENCOUNT（Sno）>100THEN'人多'
WHENCOUNT（Sno）<40ANDCOUNT（Sno）>0THEN'人少'
WHENCOUNT（Sno）<=100ANDCOUNT（Sno）>=40THEN'一般'
WHENCOUNT（Sno）=0THEN'无人选'
end
AS人数情况FROMSCRIGHTJOINCourseONCourse.Cno=SC.CnoGROUPBYCourse.Cno
23.查询计算机系选了VB课程的学生姓名、所在系和考试成绩，并将结果保存到新表VB_Grade中。
√
v局部临时表：
#新表名。
局部于当前连接，生命期同连接期。
v全局临时表：
##新表名。
可在所有连接中使用，生命期同用户连接期
v永久表：
新表名，存储在磁盘上
v格式：
SELECT选择列表INTO新表名FROM子句…
SELECTSname,Sdept,GradeINTOVB_GradeFROMStudent,SC,Course
WHEREStudent.Sno=SC.SnoANDCourse.Cno=SC.CnoANDCname='VB'
24.统计每个系的女生人数，并将结果保存到新表Girls中。
√
SELECTSdept,Ssex,COUNT（Ssex）AS人数IntoGirlsFROMStudentWHERESsex='女'GROUPBYSdept,Ssex
25.用子查询实现如下查询：
（1）查询选了“C001”课程的学生姓名和所在系。
√
SELECTSname,SdeptFROMStudentWHERESnoIN
（SELECTSnoFROMSCWHERECno='C001'）
（2）查询通信工程系成绩80分以上的学生的学号和姓名。
√
SELECTSno,SnameFROMStudentWHERESdept='通信工程系'AND
SnoIN（SELECTSnoFROMSCWHEREGrade>80）
（3）查询计算机系考试成绩最高的学生的姓名。
√
SELECTSnameFROMStudent,SCWHERESC.Sno=Student.Sno
ANDSdept='计算机系'ANDGradein
（SELECTMAX（Grade）FROMSC,StudentWHERESC.Sno=Student.SnoANDSdept='计算机系'）
（4）查询年龄最大的男生的姓名、所在系和年龄。
√
SELECTSname,Sdept,SageFROMStudentWHERESsex='男'ANDSagein
（SELECTMAX（Sage）FROMStudentWHERESsex='男'）
26.查询C001课程的考试成绩高于该课程平均成绩的学生的学号和成绩。
SELECTSno,GradeFROMSCWHERECno='C001'
ANDGrade>（SELECTAVG（Grade）FROMSCWHERECno='C001'）
27.查询计算机系学生考试成绩高于计算机系学生平均成绩的学生的姓名、考试的课程名和考试成绩。
SELECTSname,Sdept,Cname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDCourse.Cno=SC.CnoANDSdept='计算机系'
ANDGrade>（SELECTAVG（Grade）FROMSC,Course,StudentWHERESC.Cno=Course.CnoANDStudent.Sno=SC.SnoANDSdept='计算机系'）
28.查询VB课程考试成绩高于VB平均成绩的学生姓名和VB成绩。
SELECTSname,GradeFROMStudent,SC,CourseWHEREStudent.Sno
=SC.SnoANDCourse.Cno=SC.CnoANDCname='VB'ANDGrade>
（SELECTAVG（Grade）FROMSC,CourseWHERECourse.Cno=SC.CnoANDCname='VB'）
29.查询没选VB的学生姓名和所在系。
SELECTSname,SdeptFROMStudentWHERESnoNOTIN（SELECTSnoFROMSC,CourseWHERESC.Cno=Course.CnoANDCname='VB'）
或者
SELECTSname,SdeptFROMStudentWHERENOTEXISTS（SELECT*FROMSC,CourseWHERECourse.Cno=SC.CnoANDSno=Student.SnoANDCname='VB'）
30.查询每个学期学分最高的课程信息，列出开课学期、课程名和学分。
SELECTCname,Semester,CreditFROMCoursec1WHERENOTEXISTS
（SELECT*FROMCoursec2WHEREc1.Semester=c2.SemesterANDc1.Credit31.查询每门课程考试成绩最高的学生信息，列出课程号、学生姓名和最高成绩，结果按课程号升序排序，不包括没考试的课程。
SELECTCname,sname,SC.Cno,Grade
FROMstudentjoinsconstudent.sno=sc.snojoincourseono=o
WHEREgrade=（SELECTmax（grade）
FROMsc
WHEREcno=o）orderBYSC.CnoASc
或者
SELECTsc1.sno,Sname,Cno,GradeFROMSCsc1,studentWHEREnotexists
（SELECT*FROMSCsc2WHEREsc1.Cno=sc2.CnoANDsc1.Grade32.查询选了全部课程的学生姓名
SELECTSnameFROMStudentWHERESnoin
（SELECTSnoFROMSCGROUPBYSnohavingCOUNT（*）=
（SELECTCOUNT（*）FROMCourse））
或者
SELECTSnameFROMstudent
WHERENOTexists（SELECT*FROMcourse
WHERENOTexists（SELECT*FROMSC
WHERESno=student.snoANDcno=Course.Cno））
33.创建一个新表，表名为test，其结构为（COL1,COL2,COL3），其中，
COL1：
整型，允许空值。
COL2：
普通编码定长字符型，长度为10，不允许空值。
COL3：
普通编码定长字符型，长度为10，允许空值。
试写出按行插入如下数据的语句（空白处表示是空值）。
COL1
COL2
COL3
B1
1
B2
C2
2
B3
createtabletest（
C0L1int,
C0L2char（10）notnull,
C0L3char（10）
）
insertintotest（C0L2）values（'B1'）
insertintotestvalues（1,'B2','C2'）
insertintotest（C0L1,C0L2）values（2,'B3'）
34.利用23题建立的VB_Grade表，将信息管理系选了VB课程的学生姓名、所在系和考试成绩插入到VB_Grade表中。
INSERTINTOVB_GradeSELECTSname,Sdept,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoAND
SC.Cno=Course.CnoANDSdept='信息管理系'ANDCname='VB'
35.将所有选修C001课程的学生的成绩加10分。
UPDATESCSETGrade=Grade+10WHERECno='C001'
36.将计算机系所有学生的“计算机文化学”的考试成绩加10分。
UPDATESCSETGrade=Grade+10WHERE
Snoin（SELECTSC.SnoFROMStudent,SC,Course
WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno
ANDSdept='计算机系'ANDCname='计算机文化学'）
37.修改“VB”课程的考试成绩，如果是通信工程系的学生，则增加10分；如果是信息管理系的学生则增加5分，其他系的学生不加分。
升级会员 