新SQL——SERVER实验练习答案(4)

SQL-Server实验讲义

Select * from v_age

2 建立 ?JSJ? 系的学生学号、姓名、性别、年龄的视图 V_JSJ Create view v_jsj (sno,sname,ssex, sage) as

Select sno,sname,ssex,sage from student where sdept=?JSJ?

3 建立每门课程的平均分的视图 V_avggrade Create view v_avgGrade(cno, grade1 ) as

Select cno , avg(grade) from sc group by cno

4 将 视图 v_jsj 中 李文庆 的年龄改为21岁

Update v_jsj set sage=sage+1 where sname=?李文庆?

5 察看 student 中李文庆的年龄 查看 v_age 中李文庆的出生年月

Select * from student where sname= ?李文庆?

Select * from v_age where sname=?李文庆? 6 查询每门课程的及格率

Create view v1 (cno , cnt1) as

Select cno, count(*) from sc group by cno

Create view v2 (cno , cnt1) as

Select cno, count(*) from sc where grade>=60 group by cno

Select v1.cno , cnt2*1.0 / cnt1 from v1,v2 where v1.cno=v2.cno

思考: 1 利用 V_JSJ 视图,可以更新SX 的学生的年龄吗? 写出理由 如: update v_jsj set sage=25 where sno=? 0004? 0004 号学生为 SX 系.

试验九 安全性控制实验

目的:掌握Sql-server 的授权机制.

1）建立新用户 mary , 密码1234 Sp_addLogin ‘mary’, ‘1234’

2) 授予 mary 可以访问 School 数据库的权力 选择 school 数据库

Sp_grantDBaccess mary

3) 以mary 登录 sql-server ,

执行 select * from student ,记录执行结果，说明原因。 无法查到数据，因为mary 没有查询 student 的权限。

4）将 course 的查询、更改权限授予 mary Grant select , update on course to mary

5）把查询 student 表和修改学生学号的权限授予用户 mary,且他能将此权限转授他人。 Grant select , update(sno) on student to mary with grant option

16

SQL-Server实验讲义

6) 把对 course 表的更改权限从mary 收回 Revoke update on course from mary

7) 把第5）小题授予mary的权限收回。

revoke select , update(sno) on student from mary cascade

8）mary 只能查询 ‘1001’ 号课程的学生成绩,请问如何授权 Create view v_sc1 (sno,cno,grade) as

Select sno, cno,grade from sc where cno=’0001’

Grant select on v_sc1 to mary

思考: 1 sp_addlogin , sp_grantdbaccess 语句的区别.

2 如有200个人需要授权，SQL-SERVER如何简化授权机制。

试验十 存储过程

目的: 掌握存储过程的概念、编程及使用

1 编写一个存储过程 usp_avgage , 向客户端返回每个系科的学生平均年龄。 系科 平均年龄 JSJ 21 SX 20 。。。

1) 编写存储过程的代码

Create procedure usp_avgage as

Select sdept,avg(sage) from student group by sdept

2）调试、运行该存储过程。 Usp_avgage

2编写一个存储过程 usp_sdept, 传入一个系科代码，返回该系的平均年龄，人数 Create procedure usp_sdept @dept char(10) as

Select avg(sage),count(*) from student where sdept=@dept

3 编写存储过程 usp_updateGrade , 传入参数为课程号,处理逻辑:

对传入的这门课,进行如下处理:

如某学生该门课成绩>80 , 则加 2 分 如某学生该门课成绩>60 , 则加 1 分

17

SQL-Server实验讲义

如某学生该门课成绩<=60 ,则减 1分

并且返回此门课的每个学生的最新成绩: 学号 成绩.

Create procedure usp_updateGrade @cno char(4) as

Update sc set grade=grade + 2 where cno=@cno and grade>80

Update sc set grade=grade + 1 where cno=@cno and grade between 60 and 80 Update sc set grade=grade -1 where cno=@cno and grade<=80

Select sno , grade from sc where cno=@cno return

5 编写存储过程 usp_comp_age , 比较0001，0002学生的年龄的高低，输出： XXXX学生的年龄大

注意： XXXX为学生的姓名

Create procedure usp_comp_age as declare @age1 int , @age2 int

declare @name1 char(10) , @name2 char(10) --临时存储两个人的姓名

select @age1=sage ,@name1 = sname from student where sno=’0001’ select @age2=sage, @name2 = sname from student where sno=’0002’

if @age1 > @age2

print @name1 + ‘学生的年龄大’ else

print @name2 + ‘学生的年龄大’ return

7 编写存储过程 usp_comp_age1 , 比较两个学生的年龄的高低，两个学生的学号有参数输入，最后输出： XXXX学生的年龄大。 注意： XXXX为学生的姓名

Create procedure usp_comp_age1 @no1 char(6),@no2 char(6) as declare @age1 int , @age2 int

declare @name1 char(10) , @name2 char(10) --临时存储两个人的姓名

select @age1=sage ,@name1 = sname from student where sno=@no1 select @age2=sage, @name2 = sname from student where sno=@no2

if @age1 > @age2

print @name1 + ‘学生的年龄大’ else

print @name2 + ‘学生的年龄大’ return

10 编写存储过程 usp_comp_age2 , 比较两个学生的年龄的高低，两个学生的学号有参数输入，最后把年龄大的学生的姓名、性别返回客户端。

Create procedure usp_comp_age1 @no2 char(6),@no2 char(6) as declare @age1 int , @age2 int

declare @name1 char(10) , @name2 char(10) --临时存储两个人的姓名

18

SQL-Server实验讲义

select @age1=sage ,@name1 = sname from student where sno=@no1 select @age2=sage, @name2 = sname from student where sno=@no2

if @age1 > @age2

select sname ,ssex from student where sno=@no1 else

select sname ,ssex from student where sno=@no2 return

12 编写存储过程 usp_t1，传入参数为学号,把该学号的课程1001的成绩减到58分。每次只能减1分，用循环完成。

create procedure usp_t1 @no char(6) as declare @age int set @age=100 while @age>58 BEGIN

SELECT @age = sage from student where sno=@no If @age>58

Update sage=sage -1 where sno=@no END RETURN

-- 以下不需要

4 编写存储过程 usp_disp , 传入参数为课程号,处理逻辑: 返回每个学生的成绩等级。 成绩>=90 为优, 成绩>=80为良,成绩>=70 为中,成绩>=60为及格 ,成绩<=60为不及格。 返回结果如下：

学号 课程号 成绩 等第 0001 1001 91 优 0001 1002 78 中 ?????..

create procedure udp_disp @cno char(4) as --建立临时表存储结果

create table #tmp (sno char(4),cno char(4),grade int , level char(6) ) --建立某门课程的游标

declare cur1 cursor for select sno,grade from sc where cno=@cno declare @sno char(4) ,@nGrade int

declare @sLevel char(6) --临时存储某学生的成绩等级 open cur1

fetch next from cur1 into @sno , @nGrade --读出游标第一行数据 while @@fetch_status =0 begin

--处理一行数据 if @nGrade>=90

set @sLevel = ‘优’ else if @nGrade>=80 set @sLevel = ‘良’ else if @nGrade>=70 set @sLevel = ‘中’

19

SQL-Server实验讲义

else if @nGrade>=80

set @sLevel = ‘及格’ else

set @sLevel = ‘不及格’ --把结果写入临时表

insert into #tmp(sno,cno,grade,level) values (@sno,@cno,@nGrade,@sLevel)

fetch next from cur1 into @sno , @nGrade --读出游标下一行数据 end

close cur1

dealLocate cur1

select * from #tmp --返回结果给客户端 drop table #tmp --删除临时表 return

5 编写一个存储过程，传入参数为学号，执行后，把该学号的学生按如下格式输出成绩： （注意：只有一行）

学号 姓名 1001课程 1002课程 1003 课程 平均分

6 编写一个存储过程，传入参数为 系科，执行后，把该系科的学生按如下格式输出学生成绩：

学号 姓名 1001 课程 1002课程 1003 课程 平均分 create procedure usp_grade @dept char(15) as create table #tmp ( sno char(4) , sname char(10) , g1 int null, g2 int null , g3 int null , pj int null )

declare @no char(4) , @name char(10), @nG1 int ,@nG2 int ,@nG3 int

declare cur1 cursor for

select sno , sname from student where sdept = @dept --游标 某一个系的学生

open cur1

fetch next from cur1 into @no , @name

while @@fetch_status=0 begin

select @nG1=grade from sc where sno=@no and cno='1001' select @nG2=grade from sc where sno=@no and cno='1002' select @nG3=grade from sc where sno=@no and cno='1003'

insert into #tmp(sno,sname,g1,g2,g3,pj) values (@no,@name,@nG1,@nG2,@nG3,(@nG1+@nG2+@nG3)/3 )

20

百度搜索“70edu”或“70教育网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，70教育网，提供经典综合文库新SQL——SERVER实验练习答案(4)在线全文阅读。