SQL上机习题

更新时间：2023-10-03 18:33:01 阅读量：综合文库文档下载

说明：文章内容仅供预览，部分内容可能不全。下载后的文档，内容与下面显示的完全一致。下载之前请确认下面内容是否您想要的，是否完整无缺。

第一套测试题（数据库及sql语言基础操作）

首先创建一个数据库（名称为：db1），要求将其数据文件的逻辑名改为： db1_姓名全拼，物理名及路径为：c:\\姓名全拼_data.mdf；日志文件的逻辑名改为：log_姓名全拼，物理名及路径为：c:\\姓名全拼_log.ldf。

create database db2 on

( name=db2_h,

filename='c:\\db2.mdf') log on (

name=log_h,

filename='c:\\log.ldf' )

完成如下两部分操作： 1. 基础部分(50分)：

使用Transact-sql语言在上述数据库中创建表sc，包含三个字段： sno：固定字符长10； cno：可变字符长15；

grade；定点小数，不为空，且取值范围限定为0-100之间。设定实体完整性、参照完整性，并自定义约束名；参照完整性约束违约处理方式均设置为级联方式。

将上述Transact-sql语句保存为：姓名全拼_db1.sql，不需要执行。

create table sc ( sno char(10), cno varchar(15),

grade numeric(5,2) not null check(grade between 0 and 100), constraint pk_2 primary key (sno,cno), foreign key (sno) references student(sno) on delete cascade on update cascade,

foreign key(cno) references course(cno) on delete cascade on update cascade )

2.用Transact-sql语句完成如下操作（以参考书中student，sc，course表为例）（50分）： (1)向student表中添加sname列的唯一性约束，自定义约束名； alter table student add constraint unique_sname unique(sname) (2)向student表中增加新列birth，类型为时间日期类型； alter table student add birth datetime

(3)将sc表中所有计算机系的学生成绩置为0；

update sc set grade=0 where sno=(select sno from student where sdept=”计算机”)

(4)查询如下满足条件的数据，并将其保存到新表newtable1中。查询男学生中，哪个年龄的学生人数超过10个，列名命名为“年龄”。 select sage as 年龄 into newtable1 from student

where ssex='男' group by sage having count(sage)>10

(5) 查询每个学生的学号、选课总门数及平均成绩，按学号降序显示，并要求将其记录明细显示出来。

select sno,count(cno) 选课总门数,avg(grade) 平均成绩from sc group by sno order by sno desc

不用执行上述语句，将语句保存为：姓名全拼_SQL1.sql。

交卷说明：先分离数据库，然后将数据库的数据文件(.mdf)、日志文件(.ldf)、姓名全拼_db1.sql, 姓名全拼_SQL1.sql文本上交即可。

第二套测试题（数据库操作及索引视图管理）

首先创建一个数据库（名称为：db2），要求将其数据文件的逻辑名改为： db2_姓名全拼，物理名及路径为：c:\\姓名全拼_data.mdf；日志文件的逻辑名改为：log_姓名全拼，物理名及路径为：c:\\姓名全拼_log.ldf。完成如下两部分操作： 1. 基础部分(50分)：

使用Transact-sql语言在上述数据库中创建表sc，包含三个字段： sno：固定字符长10； cno：可变字符长15；

grade；定点小数，不为空，且取值范围限定为0-100之间。设定实体完整性、参照完整性，并自定义约束名；参照完整性约束违约处理方式均设置为级联方式。 Create table sc ( Sno char(10), Cno varchar(15),

Grade numeric(5,2) not null check (grade between 0 and 100) Constraint pk_2 primary key (sno,cno), Foreign key (sno) references student(sno) On delete cascade On update cascade

Foreign key (cno) reference course (cno) On delete cascade On delete cascade

将上述Transact-sql语句保存为：姓名全拼_db2.sql，不需要执行。 2.索引、视图知识操作【使用T-sql语句完成】（50分）： (1)创建student表，设置实体完整性，并添加完整性约束名；

create table student ( sno char(10), Sname char(10), Ssex char(2), Sage numeric(3)

constraint pk_2 primary key (sno),

(2)将student表中sname列创建唯一非聚簇索引，索引名为un_index2；

create unique nonclustered index un_index2 on student(sname)

(3)请简要描述一下，表中对哪些列自动创建索引？

(4)创建所在系为’cs’ 学生中选修1号课程且成绩在90分以上的学生信息的视图，视图名为:姓名全拼_v1，视图列名分别为：学号，姓名，所在系，选修课程号，成绩；

create view lili_v1(sno,sname,sdept,cno,grade) as

select student.sno,sname,sdept,cno,grade from student,sc

where student.sno=sc.sno and sdept='cs' and cno='1' and grade>=90

(5)创建一个选课人数超过15人的视图，要求显示每个课程号、课程名、选课人数及平均成绩，视图名为：

姓名全拼_v2，各列名为：课程号，课程名，选课人数，平均成绩。

Create view shiyuanxia_v2 (cno,cname,count(sno) 选课人数，avg（grade）平均成绩）as

Select sc.cno,cname,count(sno) 选课人数，avg（grade）平均成绩 from sc,student where sc.sno=student.sno group by cno having count(sno)>=15

create view lili_v3(cno,cname,count(sno) 选课人数,avg(grade) 平均成绩) as select sc.cno,cname,count(sno) 选课人数,avg(grade) 平均成绩from sc,course where sc.cno=course.cno

group by cno having count(sno)>=15

将所有语句保存为： inview_2.sql

交卷说明：先分离数据库，然后将数据库的数据文件(.mdf)、日志文件(.ldf)、姓名全拼_db2.sql、inview_2.sql文本上交即可。

第三套测试题（数据库操作及存储过程管理）

首先创建一个数据库（名称为：db3），要求将其数据文件的逻辑名改为： db3_姓名全拼，物理名及路径为：c:\\姓名全拼_data.mdf；日志文件的逻辑名改为：log_姓名全拼，物理名及路径为：c:\\姓名全拼_log.ldf。完成如下两部分操作： 1. 基础部分(50分)：

使用Transact-sql语言在上述数据库中创建表sc，包含三个字段： sno：固定字符长10； cno：可变字符长15；

Grade numeric(5,2) not null check (grade between o and 100), Constraint pk_2 primary key (sno,cno), Foreign key (sno) references student(sno) On delect cascade On update cascade

Foreign key (cno) references course(cno) On delect cascade On update cascade

将上述Transact-sql语句保存为：姓名全拼_db3.sql，不需要执行。

2.存储过程知识操作（要求使用T-sql语言完成以下两小题）（第1小题35分，第2小题15分）：

（1）创建存储过程（mypro）：从student, course, sc三个表中显示所有学生的sno, sname, cno, cname, grade。该存储过程成绩grade的取值参数为：起始值和终止值, 并返回该成绩区间的最高分、平均分。

create procedure mypro

@Startgrade int=10, @Endgrade int, @outmax int output,@outavg float output AS Begin

SELECT student.sno, sname, sc.cno,cname,grade

FROM sc, student, course

where student. sno=sc. sno and course. cno=sc. Cno and sc.grade BETWEEN @Startgrade AND @Endgrade select @outmax=max(grade), @outavg=avg(grade) from sc end

（2）调用存储过程，并将最高分、平均分两个返回值打印输出。

declare @max int ,@average float

execute mypro default, @Endgrade=100,@outmax=@max output,@outavg=@average output print @max print @average

要求：不需要另外创建course和sc表，只需创建存储过程，并写出调用语句即可，不需要真正执行。最后保存为：姓名全拼_pro3.sql文本。

交卷说明：先分离数据库，然后将数据库的数据文件(.mdf)、日志文件(.ldf)、姓名全拼_db3.sql、姓名全拼_pro3.sql文本上交即可。

第五套测试题（数据库操作及备份还原操作）

首先创建一个数据库（名称为：db5），要求将其数据文件的逻辑名改为： db5_姓名全拼，物理名及路径为：c:\\姓名全拼_data.mdf；日志文件的逻辑名改为：log_姓名全拼，物理名及路径为：c:\\姓名全拼_log.ldf。完成如下两部分操作： 1. 基础部分(50分)：

使用Transact-sql语言在上述数据库中创建表sc，包含三个字段： sno：固定字符长10； cno：可变字符长15；

Grade numeric(5,2) nol null check (grade between 0 and 100), Constraint pk_2 primary key (sno ,cno), Foreign key (sno) references student(sno), On delete cascade On update cascade

Foreign key (cno) references course (cno), On delect cascade On update cascade

将上述Transact-sql语句保存为：姓名全拼_db5.sql，不需要执行。 2.备份和还原知识操作【可以企业管理器或T-sql语言完成】：

(1)首先创建一个备份设备，设备名为：shebei，其物理名所在路径为： c:\\mybeifen.bak。服务器对象---

(2)创建db5数据库的完全备份，备份集名为：wqbf_db5，并将其保存在备份设备（shebei）中。 backup database db5 to shebei with name='wqbf-db5'

(3)创建db5数据库的差异备份，备份集名为：cybf_test5，并将其保存在备份设备（shebei）中。 backup database db5 to shebei with differential, name='cybf-test5'

(4)将sc表中及格学生选课保存到新表newtable中【使用T-sql语言完成】，执行此语句，并将脚本保存在selin_db5.sql。

select * into newtable from sc where grade>=60

(5)创建db5数据库的日志备份，备份集名为：rzbf_test5，并将其追加保存在备份设备（shebei）中。 backup log db5 to shebei with name='rzbf-test5'

(6)要求依次还原到日志备份时刻，并将其还原为另外一数据库名：db5_cyhf，将将此数据库分离并上交其数据文件和日志文件。（10分）

(7)请描述依次还原到日志备份需要注意哪些操作选项的设置，与一次性还原的

区别。保存为miaoshu5.sql.

交卷说明：先分离数据库，然后将数据库的数据文件*.mdf、日志文件*.ldf、mybeifen.bak、姓名全拼_db5.sql、selin_db5.sql、 db5_cyhf、miaoshu5.sql数据库的数据文件和日志文件上交。

第四套测试题(数据库操作及触发器管理)

首先创建一个数据库（名称为：db4），要求将其数据文件的逻辑名改为： db4_姓名全拼，物理名及路径为：c:\\姓名全拼_data.mdf；日志文件的逻辑名改为：log_姓名全拼，物理名及路径为：c:\\姓名全拼_log.ldf。完成如下两部分操作： 1. 基础部分(50分)：

使用Transact-sql语言在上述数据库中创建表sc，包含三个字段： sno：固定字符长10； cno：可变字符长15；

将上述Transact-sql语句保存为：姓名全拼_db4.sql，不需要执行。 2.触发器知识操作（要求使用T-sql语言中完成。）（50分）：

1. 使用Transact-sql语言创建如下触发器（mytri）：为student表创建一后触发器，当插入或修改记录时，此触发器确保此记录的sage在18-25之间（包括18及25），同时保证sname不为空。[不考虑多条元组的插入情况]

CREATE TRIGGER mytri1 ON student after INSERT, update AS begin

if update(sage) or update(sname) begin

declare @newage int, @newname char(10),@flag int select @newage=sage from inserted select @newname=sname from inserted set @flag=0

if @newage not between 18 and 25 begin

print '年龄不符合条件，无法完成上述操作！' set @flag=1 end

if @newname is null begin

print '姓名输入不符合条件，无法完成上述操作！' set @flag=1 end

if @flag=1

rollback transaction --退出触发器对象的执行！ else

return --可省略return end

End --此完整性条件可用check约束

2. 请描述存储过程与触发器的相同及不同之处。

执行上述Transact-sql语句，并将上述语句及描述保存为姓名全拼_tri4.sql。

交卷说明：先分离数据库，然后将数据库的数据文件(.mdf)、日志文件(.ldf)、姓名全拼_db4.sql、姓名全拼_tri4.sql文本上交即可。

第七套测试题（数据库操作及安全性管理）

首先创建一个数据库（名称为：db7），要求将其数据文件的逻辑名改为： db7_姓名全拼，物理名及路径为：c:\\姓名全拼_data.mdf；日志文件的逻辑名改为：log_姓名全拼，物理名及路径为：c:\\姓名全拼_log.ldf。完成如下两部分操作： 1. 基础部分(50分)：

使用Transact-sql语言在上述数据库中创建表sc，包含三个字段： sno：固定字符长10； cno：可变字符长15；

将上述Transact-sql语句保存为：姓名全拼_db7.sql，不需要执行。 2.安全性知识操作（50分）：

(1)创建登录账户login1(sql server身份认证模式)，密码：姓名全拼（10分）；

安全性---登陆名----右击新建登陆名

(2)创建login1访问数据库db7的用户账户user1（10分）；

db7---安全性-----用户----右击新建用户----登陆名

(3)使用SSMS工具完成下面授权（10分：

创建student表，给user1授予更新student表及查询sname列的权限，同时允许权限传递。

student----右击属性---权限----用户或角色添加

(4)使用Transact-sql语言给user1授予如下权限（20分）：

a. 给user1授予对student表的插入、修改以及sno列的查询权限；

grant insert, update ,select(sno) on student to user1 b. 收回user1对student表插入数据的权限； revoke insert on student from user1 c. 禁止user1对student表的更新权限； deny update on student to user1

要求执行上述三条语句并将脚本保存为：姓名全拼_grt7.sql

(5)创建角色role1，并为角色分配权限：对student表的查询、删除权限；

db7---安全性----角色-----数据库角色---新建 student----右击属性---权限----用户或角色添加

(6)将用户user1添加到角色中。

role1---属性---角色成员添加

交卷说明：先分离数据库，然后将数据库的数据文件(.mdf)、日志文件(.ldf)、姓名全拼_db7.sql、

姓名全拼_grt7.sql文本上交即可。

第八套测试题(数据库操作及游标管理)

首先创建一个数据库（名称为：db8），要求将其数据文件的逻辑名改为： db8_姓名全拼，物理名及路径为：

c:\\姓名全拼_data.mdf；日志文件的逻辑名改为：log_姓名全拼，物理名及路径为：c:\\姓名全拼_log.ldf。完成如下两部分操作： 1. 基础部分(50分)：

使用Transact-sql语言在上述数据库中创建表sc，包含三个字段： sno：固定字符长10； cno：可变字符长15；

将上述Transact-sql语句保存为：姓名全拼_db8.sql，不需要执行。 2.游标知识操作（要求使用T-sql语言中完成。）（50分）： 1. 将每个学生的sno，选课门数，平均成绩显示出来，要求：平均成绩小于60分，提示为不及格；平均成绩在60-70分，提示为及格；平均成绩在70-80之间的，提示为中等；平均成绩在80-90之间，提示为良好；平均成绩在90-100之间，提示为优秀；

declare mycur1 CURSOR for select sno,count(cno),avg(grade) from sc group by sno open mycur1

declare @xuehao char(9),@menshu int,@chji numeric(5,3) fetch next from mycur1 into @xuehao,@menshu,@chji --取当前指针记录并下移指针

while @@fetch_status=0 --判断指针取记录操作是否成功 begin

print case --也可使用select

when @chji>90 then @xuehao+' '+cast(@menshu as char(11))+' '+'平均成绩优秀!'

when @chji>80 then @xuehao+' '+cast(@menshu as char(11))+' '+'平均成绩良好!'

when @chji>70 then @xuehao+' '+cast(@menshu as char(11))+' '+'平均成绩中等!'

when @chji>60 then @xuehao+' '+cast(@menshu as char(11))+' '+'平均成绩及格!'

else @xuehao+' '+cast(@menshu as char(11))+' '+'平均成绩不合格!' end

fetch next from mycur1 into @xuehao,@menshu,@chji end

close mycur1 -- 如果彻底删除此游标，执行deallocate mycur1

2. 请描游标的用途。