SQL server 上机实验

--实验十一
select*from dorm
select *from srudent 
select *from department

use student_data
go
if exists(select name from sysobjects 
          where name ='au_info'and type='P')
   drop procedure au_info
go
create procedure au_info
as
select distinct tele from dorm 
join srudent on dorm.dormno=srudent.dormno
join department on srudent.dno = department.dno
where department.dname='计算机系'
go

au_info

drop procedure au_info
go


--实验十三
select *from srudent
select *from dorm
use student_data
go
if exists(select name from sysobjects where name = 'A' and type = 'P')
drop procedure A
go
create procedure A
@_dormno nvarchar,
@newdormno nvarchar
as
begin
 --update student set dormno = @newdormno where dormno = @_dormno
 delete from dorm where dormno=@_dormno
end
go
--试
if exists (select dormno from dorm where dormno = 2101)
begin
  print'删除成功'
end
else
begin
  print'删除不成功'
end
go
select *from srudent where dormno = '2101'
select *from srudent where dormno = '2505'

 A '2101','2505'

--
use student_data
go
if exists(select name from sysobjects where name = 'B' and type = 'P')
drop procedure B
go
create procedure B
@name nvarchar
as
begin
--删除成绩
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=@name
end
go


--select sno from student join department on student.DNO=department.DNO where DNAME='物理系'
B '物理系'

select *from department
select *from srudent


--实验十二
use student_data
if exists(select name from sysobjects 
          where name='tr_up_north'and type='TR')
drop trigger tr_up_north
go
create trigger tr_up_north on srudent
for insert
as
declare @msg int
set @msg=(select sage From Inserted)
if (@msg<18)or(@msg>28)
begin
print '数据有误，请检查！'
rollback 
end

insert 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_data
go
insert into srudent values('990901','何为','男',20,NULL,2505)

if exists (select  sname from srudent where sname='何为')
begin
print'插入成功'
end
else  
begin
print'插入不成功'
end

go
save transaction points1
go

delete from srudent where sname='何为'
if exists (select *from srudent where sname='何为')
begin
print'删除不成功'
end
else
begin
print'删除成功'
end

rollback transaction points1

if exists (select *from srudent where sname='何为')
begin
print'插入数据还在'
end
else
begin
print'插入数据不在'
end