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

1、武汉大学数据库例题及解答【例4.3 】创建大学教学管理数据库，数据库名为JXGL，其主数据文件逻辑名称为JXGL_ data，数据文件的操作系统文件名称为JXGL.mdf，数据文件初始大小为5 MB，最大值为200 MB，以5%的增量增加。日志逻辑文件名称为JXGL_log，日志的操作系统文件名称为JXGL.ldf，日志文件初始大小为5 MB，可按2 MB增量增加，最大值为50 MB。CREATE DATABASE JXGLON ( NAME = JXGL_data, /默认为主数据文件 FILENAME = + data_path + JXGL.mdf, SIZE = 5, MAXSIZE

2、= 200, FILEGROWTH = 5% )LOG ON ( NAME = JXGL_log, FILENAME = + data_path + JXGL.ldf, SIZE = 5MB, MAXSIZE = 50MB, FILEGROWTH = 2MB )【例】创建test数据库，包含一个主文件组和两个次文件组。CREATE DATABASE testON PRIMARY /*定义在主文件组上的文件*/( NAME=pri_file1, FILENAME= C:Program FilesMicrosoft SQL Server MSSQLData pri_file1.mdf , SIZE

3、=10,MAXSIZE=50,FILEGROWTH=15%),( NAME=pri_file2, FILENAME= C:Program FilesMicrosoft SQL ServerMSSQLData pri_file2.ndf , SIZE=10,MAXSIZE=50,FILEGROWTH=15%),FILEGROUP Grp1 /*定义在次文件组Grp1上的文件*/( NAME=Grp1_file1, FILENAME= C:Program FilesMicrosoft SQL Server MSSQLData Grp1_file1.ndf , SIZE=10,MAXSIZE = 5

4、0,FILEGROWTH=5),FILEGROUP Grp2 /*定义在次文件组Grp2上的文件*/( NAME = Grp2_file1, FILENAME= C:Program FilesMicrosoft SQL ServerMSSQLData Grp2_file1.ndf , SIZE=10,MAXSIZE=50,FILEGROWTH=5),LOG ON /*定义事务日志文件*/( NAME=test_log, FILENAME= C:Program FilesMicrosoft SQL ServerMSSQLData test_log.ldf , SIZE=5,MAXSIZE=25,

5、FILEGROWTH=5 )GO 例：在原有数据库的基础上增加一个文件组date1,添加一个新文件并加入到文件组date1中ALTER DATABASE stuDB ADD FILEGROUP date1 GO ALTER DATABASE stuDB ADD FILE（NAME=stuDB_data1, -主数据文件的逻辑名 FILENAME=D:projectstuDB_data1.ndf, -主数据文件的物理名 SIZE=5mb, -主数据文件初始大小 MAXSIZE=100mb, -主数据文件增长的最大值 FILEGROWTH=15% -主数据文件的增长率） TO FILEGROUP

6、date1 GO【例】删除已经创建的“学生管理数据库”。DROP DATABASE 学生管理数据库GO 例:在xsgl数据库中创建kc表。USE xsglGOCREATE TABLE kc(课程号 char(4) PRIMARY KEY,课程名 char(16) not null,学分 smallint,学时数 smallint)GO【例】在xs表的“专业”列和“姓名”列上创建名为“IX_zyxm”的非聚集、复合索引。运行如下命令。CREATE NONCLUSTERED INDEX IX_zyxm ON xs (专业,姓名)GO-查看和维护索引信息EXEC sp_helpindex -删除索引

7、DROP INDEX 索引名,. ON 表名【例】向xs表中添加如表4-1所示字段USE xsglGOALTER TABLE xsADD 电话CHAR(8)NULLADD 电子邮件CHAR(40) NULLGO【例】将xs表中的电子邮件字段的列长改为20。 ALTER TABLE xs ALTER COLUMN 电子邮件CHAR(20) NULL【例】在SC表中增加完整性约束定义，使grade在0-100之间。 ALTER TABLE SC ADD CONSTRAINT grade_CHK CHECK(grade BETWEEN 0 AND 100) 【例】将xs表中的电话列删除。 ALTER

8、 TABLE xs DROP COLUMN 电话【例】查询所有女生的信息并将结果保存在名为“女生表”的数据表中。USE xsglGO SELECT * INTO 女生表FROM xsWHERE 性别=女【例】查询所有男生的信息并将结果存入临时表中。USE xsglSELECT *INTO #TEMPDBFROM xsWHERE 性别=男要查看临时表的内容可用下面的语句：SELECT * FROM #TEMPDB【例】查询所有学生的学号，姓名及年龄。USE xsglGOSELECT 学号,姓名,年龄=DATEDIFF(YY,出生时间,GETDATE()FROM xsGO 【例】查询xs表中前5条

9、记录USE xsglGO SELECT TOP 5 *FROM xsGO【例】在cj表中求每门课程成绩大于90分的人数，并显示统计的明细。USE xsglGOSELECT *FROM cjWHERE 成绩=90ORDER BY 课程号COMPUTE COUNT(学号) BY 课程号GO【例】用命令方式通过视图修改王丹同学的“计算机基础”课成绩，将成绩改为91分，并查看修改结果。具体操作命令如下。UPDATE 电子商务专业学生成绩视图SET 成绩=91WHERE 姓名=王丹AND 课程名=计算机基础GOSELECT * FROM 电子商务专业学生成绩视图 WHERE 姓名=王丹 AND 课程名=

10、计算机基础GO例5.1图1.11所示的大学教学管理数据库中的专业关系SS，可用如下的表定义语句定义： CREATE TABLE SS（SCODE# CHAR(5) PRIMARY KEY, SSNAME VARCHAR(30) NOT NULL）;例5.2图1.11所示的大学教学管理数据库中的学生关系表S，可用如下的表定义语句定义： CREATE TABLE S（S# CHAR(9) PRIMARY KEY， SNAME CHAR(10) NOT NULL， SSEX CHAR(2) CHECK(SSEX IN (男，女)， SBIRTHIN DATE NOT NULL， PLACEOFB C

11、HAR(16)， SCODE# CHAR(5) NOT NULL， CLASS CHAR(5) NOT NULL）；例：教学管理数据库系统中的学习关系SC。 CREATE TABLE SC (S# CHAR(9), C# CHAR(7)， GRADE INT， PRIMARY KEY(S#，C#)；例5.7给专业表SS增加一个新属性NOUSE_COLUMN，设其数据类型为NUMERIC(8，1)。语句应为： ALTER TABLE SS ADD NOUSE_COLUMN NUMERIC(8，1)；例5.8删除专业表SS中增加的属性NOUSE_COLUMN的两种删除语句形式分别为： ALTER

12、TABLE SS DROP NOUSE_COLUMN CASCADE； ALTER TABLE SS DROP NOUSE_COLUMN RESTRICT；例5.9将专业表SS中的专业名称SSNAME(30)修改为SSNAME(40)（即长度增加10）的列修改语句： ALTER TABLE SS MODIFY SSNAME VARCHAR(40)；例：给学习关系SC中插入王丽丽同学（学号为 200401003）学习计算机网络课（课程号为 C403001）的成绩（89分）。 INSERT INTO SC（S#，C#，GRADE） VALUES（200401003，C403001，89）；例5.1

13、2 写出将学生关系S中的学生名字“王丽丽”（学号为200401003）改为“王黎丽”的数据修改语句。 UPDATE S SET SNAME王黎丽 WHERE S#200401003；例5.13写出将所有女同学的专业改为S0404的数据修改语句。 UPDATE S SET SCODE# S0404 WHERE SSEX女；例5.14从学生关系S中删除学号为200403001的学生的信息。 DELETE FROM S WHERE S# =200403001；例5.15删除专业关系中的全部信息。 DELETE FROM SS；例5.19查询所有学生所学课程的最高分数、最低分数和平均分数。 SELEC

14、T MAX(GRADE) AS 最高分数, MIN(GRADE) AS 最低分数, AVG(GRADE) AS 平均分数 FROM SC；例5.20 写出查询所有学习了计算机网络课（课程号为 C403001)的学生的学号和成绩的查询语句。学习关系模式：SC（S#，C#，GRADE） SELECT S#，GRADE FROM SC WHERE C# =C403001；例5.21查询选修了计算机网络课(课程号为C403001)或信息安全技术课(课程号为C403002)的学生的学号。 SELECT S# FROM SC WHERE C# =C403001 OR C# =C403002；例5.22查询

15、年龄在21岁至28岁之间学生的基本信息。 SELECT * FROM S WHERE YEAR(GETDATE()-YEAR(SBIRTHIN) BETWEEN 21 AND 28; 例5.23查询各个同学所学课程的平均分数。 SELECT S# ，AVG(GRADE) AS 平均分数 FROM SC GROUP BY S# ；例5.24 查询每个专业男、女生的人数。 SELECT SCODE# AS 专业代码，SSEX AS 性别， COUNT(*) AS 人数 FROM S GROUP BY SCODE#，SSEX；例5.25查询学生总数超过 300 人的专业及其具体的总人数。SELECT

16、 SCODE#，COUNT(*) FROM S GROUP BY SCODE# HAVING COUNT(*)300；例5.26按学号递增的顺序（查询）显示学生的基本信息。 SELECT * FROM S ORDER BY S# ASC；例5.27按学号递增、课程成绩递减的顺序（查询）显示学生的课程成绩。 SELECT S#，C#，GRADE FROM SC ORDER BY S# ASC，GRADE DESC；例查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。 SELECT Sno，Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC；

17、例5.28查询学生关系S 中姓李的学生的学号和姓名。 SELECT S#，SNAME FROM S WHERE SNAME LIKE 李%；例5.29查询出生日期在1982年1月1日到1982年12月31日之间的所有学生的学号、姓名和出生日期，并按日期的递增顺序排列。 SELECT S#，SNAME，SBIRTHIN FROM S WHERE SBIRTHIN BETWEEN 1982-1-1AND 1982-12-31 ORDER BY SBIRTHIN；例5.30查询所有学生当前的年龄，并按年龄递增顺序排列。 SELECT S#,SNAME, year(getdate()-year(SBI

18、RTHIN) AS AGE FROM S ORDER BY AGE；例5.31：统计各教研室开设课程的门数。分析课程关系模式及其当前值可知，各门课程号的第1位由字符C开头，第2至第4位为教研室编号，第5至第7位是该教研室所开课程的序号。所以仅由课程关系就可以统计出各教研室所开设课程的门数，其查询语句如下： SELECT substring(c#, 2, 3) AS 教研室, count(c#) AS 开课门数 FROM C GROUP BY substring(c#, 2, 3);例5.32查询所有学习了数据结构课（课程号为C401001）的学生的学号和姓名。S（S#，SNAME，SSEX，S

19、BIRTHIN，PLACEOFB，SCODE#，CLASS）SC（S#，C#E，DRADE）SELECT S.S#，SNAME FROM S，SC WHERE S.S# = SC.S# AND C# =C401001；例5.33查询选修了“信息安全技术”课程的学生的学号与姓名。S（S#，SNAME，SSEX，SBIRTHIN，PLACEOFB，SCODE#，CLASS）SC（S#，C#E，DRADE）C（C#，CNAME，CLASSH） SELECT S.S#，SNAME FROM S，SC，C WHERE S.S# = SC.S# AND SC.C# = C.C# AND CNAME =信息

20、安全技术；例查询每一门课的间接先修课。SELECT FIRST.Cno,SECOND.CpnoFROM Course FIRST， Course SECOND WHERE FIRST.Cpno = SECOND.Cno;例查询每个学生基本情况及其选修课程的情况。SELECT Student.*,SC.Cno,GradeFROM Student ,SCWHERE Student.Sno=SC.Sno（*）;SELECT Student.*,SC.Cno,GradeFROM Student ,SCWHERE Student.Sno=SC.Sno;例5.34查询张华同学（学号为200401001）的

21、那个班的女同学的基本信息。 SELECT * FROM S WHERE CLASS=(SELECT CLASS FROM S WHERE S# =200401001) AND SSEX =女；例5.35检索考试成绩比该课程平均成绩低的学生的成绩。 SELECT S#，C#，GRADE FROM SC WHERE GRADE ALL (SELECT GRADE FROM S，SC WHERE S.S# = SC.S# AND SCODE# = S0403)；例查询其他系中比IS系任一个学生年龄小的学生(其中某一个)名单。SELECT SnameFROM Student WHERE Sage AN

22、Y（ SELECT Sage FROM Student WHERE Sdept=IS） AND SdeptIS;例5.40查询所有学习了数据结构课（课程号为C401001）的学生的学号和姓名。 SELECT S.S#，SNAME FROM S WHERE EXISTS (SELECT * FROM SC WHERE SC.S# = S.S# AND C# =C401001)；直观的意义为：查询的是那些，在学习关系中存在所学课程为数据结构的学生的学号和姓名。例5.41查询没有学习数据结构课（课程号为C401001）的学生的学号和姓名。 SELECT S.S#，SNAME FROM S WHERE

23、 NOT EXISTS (SELECT * FROM SC WHERE SC.S# = S.S# AND C# =C401001); 例查询与“刘晨”在同一个系学习的学生的学号、姓名和所属系。SELECT Sno，Sname，SdeptFROM Student S1 WHERE EXISTS（ SELECT * FROM Student S2 WHERE S2.Sdept=S1.Sdept AND S2.Sname=刘晨）;例5.42合并学生关系和专业关系中的专业代码。 SELECT SCODE# FROM S UNION SELECT SCODE# FROM SS；例5.43查询有成绩的学生

24、的学号。 SELECT S# FROM S INTERSECT SELECT S# FROM SC WHERE GRADE IS NOT NULL;例5.44查询没有成绩的学生的学号。 SELECT S# FROM S MINUS SELECT S# FROM SC WHERE GRADE IS NOT NULL;例查询选修了课程1的学生集合与选修了课程2的学生集合的交集。SELECT SnoFROM SCWHERE Cno=1INTERSECTSELECT SnoFROM SCWHERE Cno=2;例5.45 写出教学安排视图： TA(C#，CNAME，CLASSH，TNAME，TRSEC

25、TION）的创建语句。课程关系模式：C（C#，CNAME，CLASSH）教师关系模式： T（T#，TNAME，TSEX，TBIRTHIN，TITLEOF，TRSECTION，TEL）讲授关系模式： TEACH（T#，C#）例5.45 写出教学安排视图： TA(C#，CNAME，CLASSH，TNAME，TRSECTION）的创建语句。 CREATE VIEW TA AS SELECT C#，CNAME，CLASSH，TNAME，TRSECTION FROM C，TEACH，T WHERE C.C#=TEACH.C# AND TEACH.T#=T.T#；其中，只有视图名TA，没有选项说明视图TA

26、的列名表与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) 的创建语句。 CREATE VIEW A_GRADE（S#，AVG_GRADE） AS SELECT S.S#，AVG(GRADE) FROM S，SC WHERE S.S# = SC.S# AND GRADE IS NOT NULL； GROU

27、P BY S.S#；例5.46利用视图A_GRADE查询学生所学课程的平均成绩。 SELECT * FROM A_GRADE；如果用查询语句实现查询学生所学课程的平均成绩，其查询语句应为： SELECT S.S#，AVG(GRADE) FROM S，SC WHERE S.S# = SC.S# AND GRADE IS NOT NULL； GROUP BY S.S#; 例5.47 设在教学管理数据库系统中，要建立某些临时表来辅助有关管理过程。若其中有一个临时表用于暂存选修了“信息安全技术”课程的学生的学号、姓名、专业名称和班级，表的其格式为： S_C（S#，SNAME，SSNAME，CLASS）例5.47（续）将从有关表中查询出的有关数据组成的记录插入该表的插入语句： INSERT INTO S_C(S#，SNAME，SSNAME，CLASS) SELECT S.S#，SNAME，SSNAME，CLASS FROM S，SS WHERE S.SCODE# = SS.SCODE AND S# IN (SELECT S# 学习了信息安全技术课程的学生的学号 FROM SC WHERE C# IN (SELECT C# 信息安全技术课程的课程号 FR