武汉大学数据库例题及解答.docx

武汉大学数据库例题及解答.docx

《武汉大学数据库例题及解答.docx》由会员分享，可在线阅读，更多相关《武汉大学数据库例题及解答.docx（34页珍藏版）》请在冰豆网上搜索。

武汉大学数据库例题及解答

【例4.3】创建大学教学管理数据库，数据库名为JXGL，其主数据文件逻辑名称为JXGL_data，数据文件的操作系统文件名称为JXGL.mdf，数据文件初始大小为5MB，最大值为200MB，以5%的增量增加。

日志逻辑文件名称为JXGL_log，日志的操作系统文件名称为JXGL.ldf，日志文件初始大小为5MB，可按2MB增量增加，最大值为50MB。

CREATEDATABASEJXGL

ON

（NAME=JXGL_data,//默认为主数据文件

FILENAME='''+@data_path+'JXGL.mdf'',

SIZE=5,

MAXSIZE=200,

FILEGROWTH=5%）

LOGON

（NAME=JXGL_log,

FILENAME='''+@data_path+'JXGL.ldf'',

SIZE=5MB,

MAXSIZE=50MB,

FILEGROWTH=2MB）

【例】创建test数据库，包含一个主文件组和两个次文件组。

CREATEDATABASEtest

ON

PRIMARY/*定义在主文件组上的文件*/

（NAME=pri_file1,

FILENAME='C:

\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\pri_file1.mdf',

SIZE=10,MAXSIZE=50,FILEGROWTH=15%）,

（NAME=pri_file2,

FILENAME='C:

\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\pri_file2.ndf',

SIZE=10,MAXSIZE=50,FILEGROWTH=15%）,

FILEGROUPGrp1/*定义在次文件组Grp1上的文件*/

（NAME=Grp1_file1,

FILENAME='C:

\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\Grp1_file1.ndf',

SIZE=10,MAXSIZE=50,FILEGROWTH=5）,

FILEGROUPGrp2/*定义在次文件组Grp2上的文件*/

（NAME=Grp2_file1,

FILENAME='C:

\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\Grp2_file1.ndf',

SIZE=10,MAXSIZE=50,FILEGROWTH=5）,

LOGON/*定义事务日志文件*/

（NAME='test_log',

FILENAME='C:

\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\test_log.ldf',

SIZE=5,MAXSIZE=25,FILEGROWTH=5）

GO

例：

在原有数据库的基础上增加一个文件组date1,添加一个新文件并加入到文件组date1中

ALTERDATABASEstuDB

ADDFILEGROUPdate1

GO

ALTERDATABASEstuDB

ADDFILE

（NAME='stuDB_data1',--主数据文件的逻辑名

FILENAME='D:

\project\stuDB_data1.ndf',--主数据文件的物理名

SIZE=5mb,--主数据文件初始大小

MAXSIZE=100mb,--主数据文件增长的最大值

FILEGROWTH=15%--主数据文件的增长率

）

TOFILEGROUPdate1

GO

【例】删除已经创建的“学生管理数据库”。

DROPDATABASE学生管理数据库

GO

例:

在xsgl数据库中创建kc表。

USExsgl

GO

CREATETABLEkc

（

课程号char（4）PRIMARYKEY,

课程名char（16）notnull,

学分smallint,

学时数smallint

）

GO

【例】在xs表的“专业”列和“姓名”列上创建名为“IX_zyxm”的非聚集、复合索引。

运行如下命令。

CREATENONCLUSTEREDINDEXIX_zyxmON

xs（专业,姓名）

GO

--查看和维护索引信息

EXECsp_helpindex<表名>

--删除索引

DROPINDEX索引名[,...]ON表名

【例】向xs表中添加如表4-1所示字段

USExsgl

GO

ALTERTABLExs

ADD电话CHAR（8）NULL

ADD电子邮件CHAR（40）NULL

GO

【例】将xs表中的电子邮件字段的列长改为20。

ALTERTABLExs

ALTERCOLUMN电子邮件CHAR（20）NULL

【例】在SC表中增加完整性约束定义，使grade在0-100之间。

ALTERTABLESC

ADDCONSTRAINTgrade_CHKCHECK（grade

BETWEEN0AND100）

【例】将xs表中的电话列删除。

ALTERTABLExsDROPCOLUMN电话

【例】查询所有女生的信息并将结果保存在名为“女生表”的数据表中。

USExsgl

GO

SELECT*INTO女生表

FROMxs

WHERE性别='女'

【例】查询所有男生的信息并将结果存入临时表中。

USExsgl

SELECT*

INTO#TEMPDB

FROMxs

WHERE性别='男'

要查看临时表的内容可用下面的语句：

SELECT*FROM#TEMPDB

【例】查询所有学生的学号，姓名及年龄。

USExsgl

GO

SELECT学号,姓名,年龄=DATEDIFF（YY,出生时间,GETDATE（））

FROMxs

GO

【例】查询xs表中前5条记录

USExsgl

GO

SELECTTOP5*

FROMxs

GO

【例】在cj表中求每门课程成绩大于90分的人数，

并显示统计的明细。

USExsgl

GO

SELECT*

FROMcj

WHERE成绩>=90

ORDERBY课程号

COMPUTECOUNT（学号）BY课程号

GO

【例】用命令方式通过视图修改王丹同学的“计算机基础”

课成绩，将成绩改为91分，并查看修改结果。

具体操作命令如下。

UPDATE电子商务专业学生成绩视图

SET成绩=91

WHERE姓名='王丹'AND课程名='计算机基础'

GO

SELECT*FROM电子商务专业学生成绩视图

WHERE姓名='王丹'AND课程名='计算机基础'

GO

例5.1图1.11所示的大学教学管理数据库中的专业关系SS，可用如下的表定义语句定义：

CREATETABLESS

（SCODE#CHAR（5）PRIMARYKEY,

SSNAMEVARCHAR（30）NOTNULL）;

例5.2图1.11所示的大学教学管理数据库中的学生关系表S，可用如下的表定义语句定义：

CREATETABLES

（S#CHAR（9）PRIMARYKEY，

SNAMECHAR（10）NOTNULL，

SSEXCHAR

（2）

CHECK（SSEXIN（′男′，′女′）），

SBIRTHINDATENOTNULL，

PLACEOFBCHAR（16），

SCODE#CHAR（5）NOTNULL，

CLASSCHAR（5）NOTNULL）；

例：

教学管理数据库系统中的学习关系SC。

CREATETABLESC

（S#CHAR（9）,

C#CHAR（7），

GRADEINT，

PRIMARYKEY（S#，C#））；

例5.7给专业表SS增加一个新属性NOUSE_COLUMN，设其数据类型为NUMERIC（8，1）。

语句应为：

ALTERTABLESSADDNOUSE_COLUMN

NUMERIC（8，1）；

例5.8删除专业表SS中增加的属性NOUSE_COLUMN的两种删除语句形式分别为：

ALTERTABLESSDROPNOUSE_COLUMNCASCADE；

ALTERTABLESSDROPNOUSE_COLUMNRESTRICT；

例5.9将专业表SS中的专业名称SSNAME（30）修改为SSNAME（40）（即长度增加10）的列修改语句：

ALTERTABLESSMODIFYSSNAMEVARCHAR（40）；

例：

给学习关系SC中插入王丽丽同学（学号为

200401003）学习计算机网络课（课程号为

C403001）的成绩（89分）。

INSERTINTOSC（S#，C#，GRADE）

VALUES（’200401003’，’C403001’，89）；

例5.12写出将学生关系S中的学生名字“王丽丽”（学号为200401003）改为“王黎丽”的数据修改语句。

UPDATES

SETSNAME＝＇王黎丽＇

WHERES#＝＇200401003＇；

例5.13写出将所有女同学的专业改为S0404的数据修改语句。

UPDATES

SETSCODE#＝＇S0404＇

WHERESSEX＝＇女＇；

例5.14从学生关系S中删除学号为200403001的学生的信息。

DELETEFROMS

WHERES#=＇200403001＇；

例5.15删除专业关系中的全部信息。

DELETEFROMSS；

例5.19查询所有学生所学课程的最高分数、最低分

数和平均分数。

SELECTMAX（GRADE）AS最高分数,

MIN（GRADE）AS最低分数,

AVG（GRADE）AS平均分数

FROMSC；

例5.20写出查询所有学习了计算机网络课（课程号为

C403001）的学生的学号和成绩的查询语句。

学习关系模式：

SC（S#，C#，GRADE）

SELECTS#，GRADE

FROMSC

WHEREC#=´C403001´；

例5.21查询选修了计算机网络课（课程号为C403001）或信

息安全技术课（课程号为C403002）的学生的学号。

SELECTS#

FROMSC

WHEREC#=´C403001´ORC#=´C403002´；

例5.22查询年龄在21岁至28岁之间学生的基本信息。

SELECT*

FROMS

WHEREYEAR（GETDATE（））-YEAR（SBIRTHIN）

BETWEEN21AND28;

例5.23查询各个同学所学课程的平均分数。

SELECTS#，AVG（GRADE）AS平均分数

FROMSC

GROUPBYS#；

例5.24查询每个专业男、女生的人数。

SELECTSCODE#AS专业代码，SSEXAS性别，

COUNT（*）AS人数

FROMS

GROUPBYSCODE#，SSEX；

例5.25查询学生总数超过300人的专业及其具体的总

人数。

SELECTSCODE#，COUNT（*）

FROMS

GROUPBYSCODE#

HAVINGCOUNT（*）>300；

例5.26按学号递增的顺序（查询）显示学生的基本信

息。

SELECT*

FROMS

ORDERBYS#ASC；

例5.27按学号递增、课程成绩递减的顺序（查询）显

示学生的课程成绩。

SELECTS#，C#，GRADE

FROMSC

ORDERBYS#ASC，GRADEDESC；

例查询选修了3号课程的学生的学号及其成绩，查询结果

按分数降序排列。

SELECTSno，Grade

FROMSC

WHERECno='3'

ORDERBYGradeDESC；

例5.28查询学生关系S中姓李的学生的学号和姓名。

SELECTS#，SNAME

FROMS

WHERESNAMELIKE＇李%＇；

例5.29查询出生日期在1982年1月1日到1982年12月31日之间的所有学生的学号、姓名和出生日期，并按日期的递增顺序排列。

SELECTS#，SNAME，SBIRTHIN

FROMS

WHERESBIRTHIN

BETWEEN＇1982-1-1＇AND‘1982-12-31＇

ORDERBYSBIRTHIN；

例5.30查询所有学生当前的年龄，并按年龄递增顺序排列。

SELECTS#,SNAME,

year（getdate（））-year（SBIRTHIN）ASAGE

FROMS

ORDERBYAGE；

例5.31：

统计各教研室开设课程的门数。

分析课程关系模式及其当前值可知，各门课程号的第1位由字符C开头，第2至第4位为教研室编号，第5至第7位是该教研室所开课程的序号。

所以仅由课程关系就可以统计出各教研室所开设课程的门数，其查询语句如下：

SELECTsubstring（c#,2,3）AS教研室,

count（c#）AS开课门数

FROMC

GROUPBYsubstring（c#,2,3）;

例5.32查询所有学习了数据结构课（课程号为C401001）的学生的学号和姓名。

S（S#，SNAME，SSEX，SBIRTHIN，PLACEOFB，SCODE#，CLASS）

SC（S#，C#E，DRADE）

SELECTS.S#，SNAME

FROMS，SC

WHERES.S#=SC.S#ANDC#=＇C401001＇；

例5.33查询选修了“信息安全技术”课程的学生的学

号与姓名。

S（S#，SNAME，SSEX，SBIRTHIN，PLACEOFB，SCODE#，CLASS）

SC（S#，C#E，DRADE）

C（C#，CNAME，CLASSH）

SELECTS.S#，SNAME

FROMS，SC，C

WHERES.S#=SC.S#ANDSC.C#=C.C#

ANDCNAME=＇信息安全技术＇；

例查询每一门课的间接先修课。

SELECTFIRST.Cno,SECOND.Cpno

FROMCourseFIRST，CourseSECOND

WHEREFIRST.Cpno=SECOND.Cno;

例查询每个学生基本情况及其选修课程的情况。

SELECTStudent.*,SC.Cno,Grade

FROMStudent,SC

WHEREStudent.Sno=SC.Sno（*）;

SELECTStudent.*,SC.Cno,Grade

FROMStudent,SC

WHEREStudent.Sno=SC.Sno;

例5.34查询张华同学（学号为200401001）的那个班的女同学的基本信息。

SELECT*

FROMS

WHERECLASS=（SELECTCLASS

FROMS

WHERES#=＇200401001＇）

ANDSSEX=＇女＇；

例5.35检索考试成绩比该课程平均成绩低的学生的成绩。

SELECTS#，C#，GRADE

FROMSC

WHEREGRADE<（SELECTAVG（GRADE）

FROMSCASX

WHEREX.C#=SC.C#）；

例5.36查询所有学习了数据结构课（课程号为C401001）的学生的学号和姓名。

SELECTS.S#，SNAME

FROMS

WHERES#IN（SELECTS#

FROMSC

WHEREC#=＇C401001＇）；

例5.37利用嵌套查询实现例5.33，也即查询选修了“信息安全技术”课程的学生的学号与姓名。

SELECTS.S#，SNAME

FROMS

WHERES#IN（SELECTS#

FROMSC

WHEREC#IN（SELECTC#

FROMC

WHERECNAME=＇信息安全技术＇））;

例5.38查询所有学习了数据结构课（课程号为C401001）的学生的学号和姓名。

SELECTS.S#，SNAME

FROMS

WHERES#=ANY（SELECTS#

FROMSC

WHEREC#=＇C401001＇）；

其中，“=ANY”的作用相当于IN。

例5.39查询考试成绩大于网络工程专业（专业代码为S0403）所有学生的课程成绩的学生的基本信息。

SELECTS.S#，SNAME，SSEX，SBIRTHIN，

PLACEOFB，SCODE#，CLASS

FROMS，SC

WHERES.S#=SC.S#ANDGRADE>ALL

（SELECTGRADE

FROMS，SC

WHERES.S#=SC.S#

ANDSCODE#=＇S0403＇）；

例查询其他系中比IS系任一个学生年龄小的学生（其中

某一个）名单。

SELECTSname

FROMStudent

WHERESage

（SELECTSage

FROMStudent

WHERESdept=‘IS’）

ANDSdept〈〉’IS’;

例5.40查询所有学习了数据结构课（课程号为C401001）的学生的学号和姓名。

SELECTS.S#，SNAME

FROMS

WHEREEXISTS（SELECT*

FROMSC

WHERESC.S#=S.S#

ANDC#=＇C401001＇）；

直观的意义为：

查询的是那些，在学习关系中存在所学课程为数据结构的学生的学号和姓名。

例5.41查询没有学习数据结构课（课程号为C401001）的学生的学号和姓名。

SELECTS.S#，SNAME

FROMS

WHERENOTEXISTS

（SELECT*

FROMSC

WHERESC.S#=S.S#

ANDC#=´C401001´）;

例查询与“刘晨”在同一个系学习的学生的学号、姓名和所属系。

SELECTSno，Sname，Sdept

FROMStudentS1

WHEREEXISTS

（SELECT*

FROMStudentS2

WHERES2.Sdept=S1.SdeptAND

S2.Sname=‘刘晨’）;

例5.42合并学生关系和专业关系中的专业代码。

SELECTSCODE#

FROMS

UNIONSELECTSCODE#

FROMSS；

例5.43查询有成绩的学生的学号。

SELECTS#

FROMS

INTERSECTSELECTS#

FROMSC

[WHEREGRADEISNOTNULL];

例5.44查询没有成绩的学生的学号。

SELECTS#

FROMS

MINUSSELECTS#

FROMSC

[WHEREGRADEISNOTNULL];

例查询选修了课程1的学生集合与选修了课程2的

学生集合的交集。

SELECTSno

FROMSC

WHERECno=‘1’

INTERSECT

SELECTSno

FROMSC

WHERECno=‘2’;

例5.45写出教学安排视图：

TA（C#，CNAME，CLASSH，TNAME，TRSECTION）

的创建语句。

课程关系模式：

C（C#，CNAME，CLASSH）

教师关系模式：

T（T#，TNAME，TSEX，TBIRTHIN，TITLEOF，TRSECTION，TEL）

讲授关系模式：

TEACH（T#，C#）

例5.45写出教学安排视图：

TA（C#，CNAME，CLASSH，TNAME，TRSECTION）

的创建语句。

CREATEVIEWTA

ASSELECTC#，CNAME，CLASSH，TNAME，TRSECTION

FROMC，TEACH，T

WHEREC.C#=TEACH.C#ANDTEACH.T#=T.T#；

其中，只有视图名TA，没有<视图列名表>选项说明视图TA的列名表与SELECT语句中的列名表相同。

例5.45写出查询每个学生平均成绩的视图

A_GRADE（S#，AVG_GRADE）

的创建语句。

学生关系模式：

S（S#，SNAME，SSEX，SBIRTHIN，…，CLASS）

学习关系模式：

SC（S#，C#，GRADE）

例5.45写出查询每个学生平均成绩的视图

A_GRADE（S#，AVG_GRADE）

的创建语句。

CREATEVIEWA_GRADE（S#，AVG_GRADE）

ASSELECTS.S#，AVG（GRADE）

FROMS，SC

WHERES.S#=SC.S#ANDGRADEISNOTNULL；

GROUPBYS.S#；

例5.46利用视图A_GRADE查询学生所学课程的平均成绩。

SELECT*

FROMA_GRADE；

如果用查询语句实现查询学生所学课程的平均成绩，其查询语句应为：

SELECTS.S#，AVG（GRADE）

FROMS，SC

WHERES.S#=SC.S#ANDGRADEISNOTNULL；

GROUPBYS.S#;

例5.47设在教学管理数据库系统中，要建立某些临时表来辅助有关管理过程。

若其中有一个临时表用于暂存选修了“信息安全技术”课程的学生的学号、姓名、专业名称和班级，表的其格式为：

S_C（S#，SNAME，SSNAME，CLASS）

例5.47（续）将从有关表中查询出的有关数据组成的记录插入该表的插入语句：

INSERTINTOS_C（S#，SNAME，SSNAME，CLASS）

SELECTS.S#，SNAME，SSNAME，CLASS

FROMS，SS

WHERES.SCODE#=SS.SCODEANDS#IN

（SELECTS#{↑学习了信息安全技术课程的学生的学号}

FROMSC

WHEREC#IN

（SELECTC#{↑信息安全技术课程的课程号}

FR