数据库综合练习
练习题
⼀、创建数据库(本题满分10分)：
创建名称为“学⽣课程管理”的数据库，其中包括两个数据⽂件，⼀个⽇志⽂件。
主数据⽂件属性为：逻辑名student_data1，存储路径为E:\\student,初始⼤⼩为2MB，最⼤为50MB，⽂件增长为2MB；次要数据⽂件属性为：逻辑名student_data2，存储路径为E:\\ student,初始⼤⼩1MB，最⼤20MB，⽂件增长为30%；⽇志⽂件的属性为：逻辑名student_log，存储路径为E:\\ student,初始⼤⼩2MB,最⼤10MB，⽂件增长为10%。Use master
If exists (select * from sysdatabases where name='学⽣课程管理 ')Drop database 学⽣课程管理create database 学⽣课程管理On(
name ='student_data1',
filename ='E:\\student\\student_data1.mdf',size = 2Mb,maxsize = 50Mb,filegrowth = 2Mb),(
name='student_data2',
filename='E:\\student\\student_data2.ndf ',size = 1Mb,maxsize =20Mb,filegrowth = 30%)Log on(
name='student_log',
filename ='E:\\student\\student_log.ldf' ,size = 2Mb,maxsize = 10Mb,filegrowth = 10%)
⼆、创建数据表（本题满分20分）
根据如下数据表创建三个数据表，表名分别为：学⽣表，成绩表，课程表。根据表格中的数据设计个属性列的属性。
If exists(select * from sysobjects where name='学⽣表')Drop table 学⽣表Create table 学⽣表(
学号 int NOT NULL ,姓名 char(6) NOT NULL,性别 char(2) NOT NULL,年龄 int,
出⽣⽇期 datetime,⾝份证号 numeric(18,0),专业 char(8))
Use 学⽣课程管理
If exists(select * from sysobjects where name='成绩表')Drop table 成绩表Create table 成绩表(
学号 int NOT NULL ,课程号 int NOT NULL,课程成绩 float,学期 char(2))
Use 学⽣课程管理
If exists(select * from sysobjects where name='课程表') Drop table 课程表Create table 课程表(
课程号 int NOT NULL ,
课程名称 varchar(20) NOT NULL,课程类别 varchar(20) NOT NULL,课程内容 varchar(20))
三、添加约束（本题满分20分，共有5道⼩题，每道⼩题4分）1.在学⽣表中对“学号”列添加主键约束Alter table 学⽣表
Add constraint PK_学号 primary key (学号)
2.在学⽣表中对“专业”列添加默认值约束默认值为“艺术”Alter table 学⽣表
Add constraint DF_专业 default(‘艺术’) for 专业
3.在学⽣表中对“年龄”添加检查约束：年龄在15到30之间 Alter table 学⽣表
Add constraint CK_年龄 check(年龄 between 15 and 30) 4.在学⽣表中的“⾝份证号”添加唯⼀约束Alter table 学⽣表
Add constraint UQ_⾝份证号 unique(⾝份证号)5.在成绩表中“学号”列添加外键约束，主表为学⽣表Alter table 学⽣表Add constraint FK_学号
Foreign key(学号) references 学⽣表（学号）
四、增删改（本题满分18分，共有6道⼩题，每道⼩题3分）
1.课程表中插⼊⼀个完整的新纪录内容为：‘8’‘喜爱的演唱会’‘娱乐类’‘李聪聪个⼈演唱会’
Insert into 课程表（课程号，课程名称，课程类别，课程内容）Values（‘8’，‘喜爱的演唱会’，‘娱乐类’，‘李聪聪个⼈演唱会’）2.修改成绩表学号为3的学⽣“课程成绩”和“学期”都加1
Update 成绩表 set 课程成绩=课程成绩+1，学期=学期+1 where 学号=’3’3.修改成绩表使表中“学期”为1的“课程成绩”加上学⽣表中“学号”为1的“年龄”Update 成绩表
Set 课程成绩=课程成绩（select 年龄 from 学⽣表 where 学号=‘1’）4.修改成绩表使表中“学号”为3的学⽣“学期”改为1“课程成绩”改为98Update 成绩表
Set 学期=1，课程成绩=98 where 学号=‘3’
5.删除成绩表中“学期”为1且“课程成绩”⼤于95的记录Delete from 成绩表 where 学期=’1’ and 课程成绩 >956.删除课程表中‘李⼩⽩’的记录Delete from 成绩表
where 学号=（select 学号 from 学⽣表 where 姓名=’李⼩⽩’）五、查询（本题满分24分，共有8道⼩题，每道⼩题3分）1.在学⽣表中，查询“年龄”不在20-22之间的所有学⽣。Select * from 学⽣表 where 年龄 not between 20 and 222.在课程表中，查询“课程号”为1，3和4的课程信息。Select * from 课程表 where 课程号 in（1,3,4）3.在学⽣表中，查询姓“李”的学⽣信息Select * from 学⽣表 where 姓名 like‘李%’
4.在成绩表中，按照学⽣的“课程成绩”升序显⽰，然后按照“学期”降序排序。Select * from 成绩表
Order by 课程成绩 asc ,学期 desc
5.显⽰学⽣表中的年龄最⼤的⼀名学⽣的信息。Select top1 * from 学⽣表Order by 年龄 desc
6.在学⽣表中，分别求男⼥⽣的平均年龄。Select * from 学⽣表Compute avg(年龄)
7.在学⽣表中按“性别”分组求平均年龄，并且查询平均年龄⼤于21的学⽣信息。Select * from 学⽣表Order by 性别
Compute avg(年龄) by 性别
8.在课程表中，查询“课程类别”是“艺术类”的结果保存到newtable表中，并查看newtable表的信息。
Create view newtable as
Select * from 课程表 where 课程类别=‘艺术类’Select * from newtable六、程序设计（本题满分8分）
则根据如下规则对成绩表中的“成绩”进⾏反复加分，直到平均分超过85分为⽌。请编写T-SQL语句实现并显⽰修改之前和修改之后的成绩表情况。90分以上：不加分80－分：加1分70－79分：加2分60－69分：加3分60分以下：加5分select * from 成绩表declare @平均成绩 intwhile(1=1)beginupdate 成绩表set 平均成绩=case
when 平均成绩<60 then 平均成绩+5
when 平均成绩 between 60 and 69 then 平均成绩+3when 平均成绩 between 70 and 79 then 平均成绩+2when 平均成绩 between 80 and then 平均成绩+1else 平均成绩end
select @平均成绩=avg(平均成绩) from 成绩表if @平均成绩>=85breakend
select * from 成绩表
创建名称为“图书馆”的数据库，其中包括两个数据⽂件，⼀个⽇志⽂件。
主数据⽂件属性为：逻辑名library_data1，存储路径为D:\\library,初始⼤⼩为3MB，最⼤为10MB，⽂件增长为1MB；次要数据⽂件属性为：逻辑名library_data2，存储路径为D:\\library,初始⼤⼩2MB，最⼤5MB，⽂件增长为10%；⽇志⽂件的属性为：逻辑名library_log，存储路径为D:\\library,初始⼤⼩1MB,最⼤3MB，⽂件增长为5%。Use master
If exists (select * from sysdatabases where name='图书馆')Drop database 图书馆create database 图书馆
On(
name ='library_data1',
filename ='D:\\library\\library_data1.mdf',size = 3Mb,maxsize = 10Mb,filegrowth = 1Mb),(
name='library_data2',
filename='D:\\library\\library_data2.ndf ',size = 2Mb,maxsize = 5Mb,filegrowth = 10%)Log on(
name='library_log',
filename ='D:\\library\\library_log.ldf' ,size = 1Mb,maxsize = 3Mb,filegrowth = 5%)
根据如下数据表创建三个数据表，表名分别为：图书表，馆藏状态表，阅览室表。根据表格中的数据设计个属性列的属性。
Use 图书馆
If exists(select * from sysobjects where name='图书表')Drop table 图书表Create table 图书表
(
图书编号 char(4) NOT NULL ,图书名 varchar(30) NOT NULL,出版社 varchar(30) NOT NULL,作者 char(10),出版时间 datetime,图书数量 int)
Use 图书馆
If exists(select * from sysobjects where name='馆藏状态表')Drop table 馆藏状态表Create table 馆藏状态表(
图书编号 varchar(20) NOT NULL ,阅览室编号 varchar(20) NOT NULL,存书量 int)
Use 图书馆
If exists(select * from sysobjects where name='阅览室表')Drop table 阅览室表Create table 阅览室表(
阅览室编号 varchar(20) NOT NULL,阅览室位置 varchar(30),
阅览室容量 int,现存图书数量 int)
三、添加约束（本题满分20分，共有5道⼩题，每道⼩题4分）1.在图书表中添加主键约束，“图书编号”列设置为主键Alter table 图书表
Add constraint PK_图书编号 primary key (图书编号)
2.在图书表中添加默认约束，“出版社”列默认值为‘清华⼤学出版社’ Alter table 图书表
Add constraint DF_出版社 default(‘清华⼤学出版社’) for 出版社3.阅览室表中，“阅览室编号”列添加唯⼀约束Alter table 阅览室表
Add constraint UQ_阅览室编号 unique(阅览室编号)4.在阅览室表中添加检查约束，“阅览室容量”必须⼤于5000Alter table 阅览室表
Add constraint CK_阅览室容量 check(阅览室容量>5000)5.在馆藏状态表添加外键约束，“图书编号”列做外键进⾏连接Alter table 图书表
Add constraint FK_图书编号
Foreign key(图书编号) references 馆藏状态表（图书编号）四、增删改（本题满分18分，共有6道⼩题，每道⼩题3分）
7.在图书表中添加数据：“图书编号”，“图书名”，“出版社”列内容分别为：‘P005’，‘C程序设计’，‘清华⼤学出版社’。
Insert into 图书表（图书编号，图书名，出版社）Values（‘P005’，‘C程序设计’，‘清华⼤学出版社’）8.在馆藏状态表中添加完整数据：‘P001’，‘Y002’，300。9.修改阅览室表信息，将所有“阅览室容量”扩⼤为原来的2倍10.修改馆藏状态表，将P003号图书的存书量增加100本11.删除阅览室表中Y003的阅览室信息
12.删除馆藏状态表中，图书名为“C程序设计”的信息五、查询（本题满分24分，共有8道⼩题，每道⼩题3分）9.查询图书表中“图书编号”为P002的图书信息Select * from 图书表 where 图书编号=P002
10.查询图书表中出版社列的信息，并去掉重复记录。
11.查询图书表中出版社为‘清华⼤学出版社’的图书编号，图书名和出版社信息12.查询馆藏状态表中存书量最⼤的馆藏信息。Select top1 * from 馆藏状态表
Order by 存书量 desc
13.查询阅览室表中的阅览室情况信息并按照现存图书数量由⼤到⼩排序。14.按出版社进⾏分组，查询图书表中各出版社的图书总数。15.图书表中分别求各出版社的图书总数（⽤compute⼦句）。
16.查询阅览室表的全部内容，并将查询结果保存的⼀个新的视图⾥，视图名字为newview。六、程序设计（本题满分8分）
根据实际情况来增加图书数量，使得每种图书的数量不低于300本，对图书表做如下修改：若0<图书数量<=100，每次增加100本
若100<图书数量<=200，每次增加50本若200<图书数量<=300，每次增加30本
通过T-SQL编程实现以上效果，并显⽰修改之前和修改之后的图书表情况。select * from 图书表declare @图书数量 intwhile(1=1)beginupdate 图书表set 图书数量=case
when 图书数量<=100 then 图书数量+100
when 图书数量 between 100 and 200 then 图书数量+50when 图书数量 between 200 and 300 then 图书数量+30else 图书数量end
select @图书数量=avg(图书数量) from 图书表if @图书数量>=300breakend
select * from 图书表
