鞍山师范SQL实验专升本习题

《SQL server2000》课程实验 序号 实验学时 实验一 2 数据库及表的创建和管理 实验二 2 约束的创建及使用 实验三 2 SQL Server视图及索引的创建及使用 实验四 4 SQL Server 的存储过程 实验五 2 SQL Server 的触发器 实验六 2 数据库的分离 实验附录: 上机实验一 1( 使用企业管理器创建名为student的数据库，并设置数据库 主文件名为student_data,大小为10MB;日志文件为 student_log,大小为2MB.其他参数取默认值。 步骤:a. 1 b. 2 c. 3 d. 2.创建一个student1数据库，主文件逻辑名为student1_data，物 理文件名为student1.mdf,为10MB,增长速度为10%;数据库的日 志文件逻辑名为syudent1_log, 物理文件名为student1.ldf,初始化 大小为1MB,最大尺寸为5MB, 增长速度为1 MB;文件存放路径 为c:\data文件夹下。 3. 在student数据库中创建一个名为pupil的表，要求: (sno char(6) not null,sname char(10) not null,ssex char(2) not null,birthday datetime not null, polity char(20)) 步骤: a. 4 5 b. c. 保存并命名为pupil即可。 6 上机实验二 1.将pupil表的sno设置为主键。 2( 为sname字段设置惟一性约束。 步骤: 7 3( 为ssex和birthday设置检查性约束，要求ssex只能为‘男’ 或‘女’，birthday应该大于‘1987-1-1’。 步骤: 8 9 4( 为polity字段设置默认约束，值为‘群众’。 5( 再创建一个学生选课表sc(sno char(6) not null,cno char(10) not null,grade real). 为sc表创建外键约束，把sc表的sno和pupil表的sno关 联起来，在这两个表之间创建一种制约关系。 步骤: 10 11 12 13 最后保存该关系图即可。 14 上机实验三 1.如何通过企业管理器和Transact_SQL语句对视图进行创建、修改和删除, 通过企业管理器: 创建步骤: 15 16 最后保存该视图即可。 修改步骤: 进入该界面重新修改该视图即可。 17 删除步骤: 18 2( 通过Transact_SQL语句创建一个视图，计算各个班级的各 门课程的平均分。 Create view v_avggrade As Select substring(pupil.sno,1,len(pupil.sno)-2) as 班级， Cname as 课程名称， Avg(grade) as 平均分 From pupil,sc,grade Where pupil.sno=sc.sno and sc.cno=course.cno Group by substring(pupil.sno,1,len(pupil.sno)-2), Cname 3. 通过Transact_SQL语句创建一个视图，显示‘高等数学’未 过的学生的信息。 Create view v_gradenotpass As Select pupil.sno,sname,cname,grade From pupil,sc,course Where pupil.sno=sc.sno and sc.cno=course.cno and grade<60 And cname=’ 高等数学’ 19 4. 通过Transact_SQL语句创建一个视图，查询的数据为99521 班学生的考试成绩。 Create view v_grade99521 As Select pupil.sno,sname,cname,grade From pupil,sc,course Where pupil.sno=sc.sno and sc.cno=course.cno and left(pupil.sno,5)=’99521’ 20 5. 如何通过企业管理器创建索引, 步骤: 21 点击‘新建’按钮 点击‘确定’ 22 点击‘关闭’ 23 6. 如何删除索引, 选中该索引点击‘删除’即可 或 利用 drop index 表名. 索引名完成删除 上机实验四 1. 创建一个存储过程stugradeinfo,查询班级、学号、姓名、性别、 课程名称、分数 create procedure stugradeinfo as select 班级=substring(pupil.sno,1,len(pupil.sno)-2), 24 pupil.sno as 学号， sname as 姓名， ssex as 性别， cname as课程名称, grade as 分数 from pupil,sc,course where pupil.sno=sc.sno and course.cno=sc.cno 2. 利用企业管理器创建一个存储过程stu_info，根据传入的编号， 查询某学生的基本信息。 25 代码如下: create procedure stu_info @stunum char(10) as if not exists(select * from pupil where sno=@stunum) print ‘查无此人～～～～～’ else begin select sno as 编号， sname as 姓名， ssex as 性别， 26 birthday as 出生日期， polity as 政治面貌 from pupil where sno=@stunum end 建一个存储过程stu_age,根据输入的学生姓名，计算该学生3. 创 的年龄。 Create procedure stu_age @stuname char(10) as if not exists(select * from pupil where sname=@stuname) print ‘查无此人～～～～～’ else begin declare @age int select @age=floor(datediff(day,birthday,getdate())/365) from pupil where sname=@stuname end 27 4. 创建一个存储过程stu_grade,根据输入的学生编号，返回其选 课及其成绩。 Create procedure stu_grade @stunum char(10) if not exists(select * from pupil where sname=@stuname) print ‘查无此人～～～～～’ else begin if not exists(select * from pupil,sc where pupil.sno=sc.sno) print ‘无此学生的选课信息～～～～’ else select sname,cname,grade from pupil,course,sc where pupil.sno=sc.sno and course=cno=sc.cno end 上机实验五 1( 创建一个insert触发器,当在pupil表中插入一条新记录时， 给出‘你已经插入了一条新记录～～～’的提示信息。 步骤: 28 29 程序代码如下: create trigger stuinsert on pupil for insert as declare @msg char(50) set @msg=’ 你已经插入了一条新记录～～～’ print @msg 2.创建一个insert触发器,当在pupil表中插入一条新记录时，不允许在学号中出现重复的编号或出现空值。 程序代码如下: create trigger stuinsert1 on pupil for insert as declare @stunum char(10) set @stunum=(select sno from inserted ) if @stunum is null begin print ‘学号不能为空～～～～’ rollback transaction end else begin 30 declare @num int set @num=(select count(*) from pupil where sno in (select sno from inserted)) if @num>1 begin print ‘已经有相同的学号，请重新确认～～～～’ rollback transaction end else print ‘数据录入成功～～～～’ end 3.创建一个insert触发器,当在sc表中插入一条新记录时，sno和cno必须是已经存在的学号和课程号，且grade应该在0----100之间。 程序代码如下: create trigger scinsert on sc for insert as declare @gradevalue real set @gradevalue=(select grade from inserted) print ‘触发器开始工作………………’ if not exists(select sno from inserted 31 where sno in(select sno from pupil)) begin print ‘无此学生的编号，请重新确认～～～’ rollback transaction end else begin if not exists(select cno from inserted where cno in(select cno from course)) begin print ‘无此课程的编号，请重新确认～～～’ rollback transaction end else begin if @gradevalue<0 or @gradevalue>100 begin print ‘分数有误，请重新确认～～～～’ rollback transaction end else print ‘数据插入成功～～～～’ 32 end end 4.创建一个after触发器,在pupil表中删除某学生的记录时，删除其相应的选课记录。 程序代码如下: create trigger studelete on pupil for delete as declare @stunum char(10) select @stunum=sno from deleted if not exists(select * from pupil,sc where pupil.sno=sc.sno and pupil.sno=@stunum) print ‘无此学生的选课记录～～～～’ else begin print ‘开始查找并删除该学生相映的选课记录……..‘ delete from sc where sno=@stunum print ‘该学生的选课记录已经删除～～～～～’ end 33 5.创建一个instead of 触发器，当在course表中上出记录时，不允许删除course表中的数据。 程序代码如下: create trigger notallowdelete on course instead of delete as print ‘instead of触发器开始工作……………’ print ‘course表中的数据不允许删除～～～不能执行删除操作～～’ 上机实验六 学生学会数据库分离，上机考试结果用数据库分离方法。 34