附录C 数据库上机实验.docx

附录C 数据库上机实验.docx

《附录C 数据库上机实验.docx》由会员分享，可在线阅读，更多相关《附录C 数据库上机实验.docx（24页珍藏版）》请在冰豆网上搜索。

附录C数据库上机实验

附录C上机实验

第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）

）

第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,SCscWHERE=ANDCno='C002'

或者

SELECTSname,SdeptFROMStudentWHERESnoIN（SELECTSnoFROMSC

WHERECno='C002'）

或者

SELECTSname,SdeptFROMStudentWHEREEXISTS（SELECT*FROMSC

WHERESno=ANDCno='C002'）

12.查询考试成绩80分以上的学生姓名、课程号和成绩，并按成绩降序排列结果。

SELECTSname,Cno,GradeFROMStudent,SCWHERE=ANDGrade>80ORDERBYGradeDESC

13.查询与VB在同一学期开设的课程的课程名和开课学期。

SELECT,FROMCoursec1,Coursec2

WHERE=AND='VB'AND!

='VB'

14.查询与李勇年龄相同的学生的姓名、所在系和年龄。

SELECT,,FROMStudents1,Students2

WHERE=AND='李勇'AND!

='李勇'

或者

SELECTSname,Sdept,SageFROMStudentWHERESage=（SELECTSageFROMStudentWHERESname='李勇'）ANDSname!

='李勇'

15.查询哪些课程没有学生选修，列出课程号和课程名。

SELECT,FROMCourseLEFTJOINSCon=WHEREISNULL

16.查询每个学生的选课情况，包括未选课的学生，列出学生的学号、姓名、选的课程号。

SELECT,,CnoFROMStudentLEFTJOINSCON=

17.查询计算机系哪些学生没有选课，列出学生姓名。

SELECTSnameFROMStudentWHERESdept='计算机系'ANDSnoNOTIN（SELECTSnoFROMSC）

或者

SELECTSnameFROMStudentSLEFTJOINSCON=

WHERESdept='计算机系'ANDISNULL

18.查询计算机系年龄最大的三个学生的姓名和年龄。

SELECTTop3Sname,SageFROMStudentWHERESdept='计算机系'ORDERBYSageDESC

19.列出“VB”课程考试成绩前三名的学生的学号、姓名、所在系和VB成绩。

SELECTTOP3WITHTIESSname,Sdept,GradeFROMStudentSJOINSCon=JOINCourseCON=WHERECname='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，则显示“人少”；如果此门课程没有人选，则显示“无人选”。

√

SELECT,COUNT（Sno）AS选课人数,

CASE

WHENCOUNT（Sno）>100THEN'人多'

WHENCOUNT（Sno）<40ANDCOUNT（Sno）>0THEN'人少'

WHENCOUNT（Sno）<=100ANDCOUNT（Sno）>=40THEN'一般'

WHENCOUNT（Sno）=0THEN'无人选'

end

AS人数情况FROMSCRIGHTJOINCourseON=GROUPBY

23.查询计算机系选了VB课程的学生姓名、所在系和考试成绩，并将结果保存到新表VB_Grade中。

√

v局部临时表：

#新表名。

局部于当前连接，生命期同连接期。

v全局临时表：

##新表名。

可在所有连接中使用，生命期同用户连接期

v永久表：

新表名，存储在磁盘上

v格式：

SELECT选择列表INTO新表名FROM子句…

SELECTSname,Sdept,GradeINTOVB_GradeFROMStudent,SC,Course

WHERE=AND=ANDCname='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,SCWHERE=

ANDSdept='计算机系'ANDGradein

（SELECTMAX（Grade）FROMSC,StudentWHERE=ANDSdept='计算机系'）

（4）查询年龄最大的男生的姓名、所在系和年龄。

√

SELECTSname,Sdept,SageFROMStudentWHERESsex='男'ANDSagein

（SELECTMAX（Sage）FROMStudentWHERESsex='男'）

26.查询C001课程的考试成绩高于该课程平均成绩的学生的学号和成绩。

SELECTSno,GradeFROMSCWHERECno='C001'

ANDGrade>（SELECTAVG（Grade）FROMSCWHERECno='C001'）

27.查询计算机系学生考试成绩高于计算机系学生平均成绩的学生的姓名、考试的课程名和考试成绩。

SELECTSname,Sdept,Cname,GradeFROMStudent,SC,CourseWHERE=AND=ANDSdept='计算机系'

ANDGrade>（SELECTAVG（Grade）FROMSC,Course,StudentWHERE=AND=ANDSdept='计算机系'）

28.查询VB课程考试成绩高于VB平均成绩的学生姓名和VB成绩。

SELECTSname,GradeFROMStudent,SC,CourseWHERE

=AND=ANDCname='VB'ANDGrade>

（SELECTAVG（Grade）FROMSC,CourseWHERE=ANDCname='VB'）

29.查询没选VB的学生姓名和所在系。

SELECTSname,SdeptFROMStudentWHERESnoNOTIN（SELECTSnoFROMSC,CourseWHERE=ANDCname='VB'）

或者

SELECTSname,SdeptFROMStudentWHERENOTEXISTS（SELECT*FROMSC,CourseWHERE=ANDSno=ANDCname='VB'）

30.查询每个学期学分最高的课程信息，列出开课学期、课程名和学分。

SELECTCname,Semester,CreditFROMCoursec1WHERENOTEXISTS

（SELECT*FROMCoursec2WHERE=AND<）

31.查询每门课程考试成绩最高的学生信息，列出课程号、学生姓名和最高成绩，结果按课程号升序排序，不包括没考试的课程。

SELECTCname,sname,,Grade

FROMstudentjoinscon=joincourseon=

WHEREgrade=（SELECTmax（grade）

FROMsc

WHEREcno=）orderBYASc

或者

SELECT,Sname,Cno,GradeFROMSCsc1,studentWHEREnotexists

（SELECT*FROMSCsc2WHERE=AND

32.查询选了全部课程的学生姓名

SELECTSnameFROMStudentWHERESnoin

（SELECTSnoFROMSCGROUPBYSnohavingCOUNT（*）=

（SELECTCOUNT（*）FROMCourse））

或者

SELECTSnameFROMstudent

WHERENOTexists（SELECT*FROMcourse

WHERENOTexists（SELECT*FROMSC

WHERESno=ANDcno=）

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,CourseWHERE=AND

=ANDSdept='信息管理系'ANDCname='VB'

35.将所有选修C001课程的学生的成绩加10分。

UPDATESCSETGrade=Grade+10WHERECno='C001'

36.将计算机系所有学生的“计算机文化学”的考试成绩加10分。

UPDATESCSETGrade=Grade+10WHERE

Snoin（SELECTFROMStudent,SC,Course

WHERE=AND=

ANDSdept='计算机系'ANDCname='计算机文化学'）

37.修改“VB”课程的考试成绩，如果是通信工程系的学生，则增加10分；如果是信息管理系的学生则增加5分，其他系的学生不加分。

UPDATESCSETGrade=Grade+

CASESdept

WHEN'通信工程系'THEN10

WHEN'信息管理系'THEN5

else0

end

FROMStudent,SC,Course

WHERE=AND=ANDCname='VB'

38.删除成绩小于50分的学生的选课记录。

DELETEFROMSCWHEREGrade<50

39.删除计算机系VB考试成绩不及格学生的VB选课记录。

DELETEFROMSC

FROMSC,Student,CourseWHERE=

AND=ANDSdept='计算机系'AND

Cname='VB'ANDGrade<60

40.删除“VB”考试成绩最低的学生的VB修课记录。

DELETEFROMSCFROMSC,Course

WHERE=ANDCname='VB'

ANDGrade=

（SELECTMIN（Grade）FROMSC,CourseWHERE=ANDCname='VB'）

41.删除没人选的课程的基本信息。

DELETEFROMCourseFROMCourseleftjoinSCon=

WHEREISNULL

本章有关建表和插入表格中的数据

CREATETABLEStudent（

SnoCHAR（7）PRIMARYKEY,

SnameNCHAR（5）NOTNULL,

SsexNCHAR

（1）,

SageTINYINT,

SdeptNVARCHAR（20）

）

CREATETABLECourse（

CnoCHAR（6）,

CnameNVARCHAR（