use CJGL
--select * from grade
select studentID,courseID,等级 = case
when grade > 85 then '优秀'
when grade > 70 then '还行'
when grade > 60 then '及格'
else '不及格'
end
from grade
--建立一个名称为stu_cursor的游标,通过该游标逐行浏览学生表中的记录
declare stu_cursor cursor scroll
for select * from student
open stu_cursor
fetch next from stu_cursor
fetch first from stu_cursor
fetch last from stu_cursor
while @@FETCH_STATUS = 0
begin
fetch next from stu_cursor
end
close stu_cursor
deallocate stu_cursor
--建立一个名称为gra_cursor的游标,通过该游标将学成绩表中每个学生的成绩加分
select * from grade
declare gre_cursor cursor
for select * from grade
open gre_cursor
update grade set grade = grade + 5
close gre_cursor
deallocate gre_cursor
--利用SQL语言编写计算*2*3…的积,并使用print显示计算结果。
declare @ Count bigint
declare @item bigint
declare @ sum char ( 30 )
select @ count = 10 ,@item = 1 ,@ sum = 1
while (@item < = @ count )
begin
select @ sum = @ sum * @item
select @item = @item + 1
end
print '1*2*3*4..*10=' + @ sum
--建立性别为男的学生视图view_male,要求包括学号,姓名和性别字段。
create view view_male
as select studentID,studentName,sex from student where sex = '男'
select * from view_male
--建立学生成绩表视图,要求包括学号,姓名,课程号,课程名,成绩字段并加密view_score(基于多表视图)
use CJGL
create view gre_view with encryption
as select student.studentID,studentName,course.courseID,courseName,grade from student join grade on grade.studentID = student.studentID join
course on course.courseID = grade.courseID
--修改view_male视图,要求添加总学分字段。
alter view view_male
as select studentID,studentName,sex,credithour from student where sex = '男'
--删除view_male视图。
drop view view_male
--select * from grade
select studentID,courseID,等级 = case
when grade > 85 then '优秀'
when grade > 70 then '还行'
when grade > 60 then '及格'
else '不及格'
end
from grade
--建立一个名称为stu_cursor的游标,通过该游标逐行浏览学生表中的记录
declare stu_cursor cursor scroll
for select * from student
open stu_cursor
fetch next from stu_cursor
fetch first from stu_cursor
fetch last from stu_cursor
while @@FETCH_STATUS = 0
begin
fetch next from stu_cursor
end
close stu_cursor
deallocate stu_cursor
--建立一个名称为gra_cursor的游标,通过该游标将学成绩表中每个学生的成绩加分
select * from grade
declare gre_cursor cursor
for select * from grade
open gre_cursor
update grade set grade = grade + 5
close gre_cursor
deallocate gre_cursor
--利用SQL语言编写计算*2*3…的积,并使用print显示计算结果。
declare @ Count bigint
declare @item bigint
declare @ sum char ( 30 )
select @ count = 10 ,@item = 1 ,@ sum = 1
while (@item < = @ count )
begin
select @ sum = @ sum * @item
select @item = @item + 1
end
print '1*2*3*4..*10=' + @ sum
--建立性别为男的学生视图view_male,要求包括学号,姓名和性别字段。
create view view_male
as select studentID,studentName,sex from student where sex = '男'
select * from view_male
--建立学生成绩表视图,要求包括学号,姓名,课程号,课程名,成绩字段并加密view_score(基于多表视图)
use CJGL
create view gre_view with encryption
as select student.studentID,studentName,course.courseID,courseName,grade from student join grade on grade.studentID = student.studentID join
course on course.courseID = grade.courseID
--修改view_male视图,要求添加总学分字段。
alter view view_male
as select studentID,studentName,sex,credithour from student where sex = '男'
--删除view_male视图。
drop view view_male