SQL server 上机实验

--实验十一select*from dormselect *from srudent select *from departmentuse student_datagoif exists(select name from sysobjects           where name ='au_info'and type='P')   drop procedure au_infogocreate procedure au_infoasselect distinct tele from dorm join srudent on dorm.dormno=srudent.dormnojoin department on srudent.dno = department.dnowhere department.dname='计算机系'goau_infodrop procedure au_infogo--实验十三select *from srudentselect *from dormuse student_datagoif exists(select name from sysobjects where name = 'A' and type = 'P')drop procedure Agocreate procedure A@_dormno nvarchar,@newdormno nvarcharasbegin --update student set dormno = @newdormno where dormno = @_dormno delete from dorm where dormno=@_dormnoendgo--试if exists (select dormno from dorm where dormno = 2101)begin  print'删除成功'endelsebegin  print'删除不成功'endgoselect *from srudent where dormno = '2101'select *from srudent where dormno = '2505' A '2101','2505'--use student_datagoif exists(select name from sysobjects where name = 'B' and type = 'P')drop procedure Bgocreate procedure B@name nvarcharasbegin--删除成绩delete from grade where sno=(select sno from srudent join department on srudent.dno=department.dno where dname=@name)--删除学生delete from srudent where dno= (select dno from department where department.DNAME = @name)--删除系号delete from department where DNAME=@nameendgo--select sno from student join department on student.DNO=department.DNO where DNAME='物理系'B '物理系'select *from departmentselect *from srudent--实验十二use student_dataif exists(select name from sysobjects           where name='tr_up_north'and type='TR')drop trigger tr_up_northgocreate trigger tr_up_north on srudentfor insertasdeclare @msg intset @msg=(select sage From Inserted)if (@msg<18)or(@msg>28)beginprint '数据有误，请检查！'rollback endinsert into srudent values('990505','小贺','女',17,2,2404)insert into srudent values('990606','小潇','女',20,2,2404)select *from srudent where SNAME='小贺' delete from srudent where SNAME='小贺'select *from srudent where SNAME='小潇'delete from srudent where SNAME='小潇'--实验十四begin transaction  use student_datagoinsert into srudent values('990901','何为','男',20,NULL,2505)if exists (select  sname from srudent where sname='何为')beginprint'插入成功'endelse  beginprint'插入不成功'endgosave transaction points1godelete from srudent where sname='何为'if exists (select *from srudent where sname='何为')beginprint'删除不成功'endelsebeginprint'删除成功'endrollback transaction points1if exists (select *from srudent where sname='何为')beginprint'插入数据还在'endelsebeginprint'插入数据不在'end