--Day6 DB-Devolopment
create procedure sp_calbonus_cur (@did int)
as
declare @ecode int, @salary money, @bonus int

open emp_cur
fetch next from emp_cur into @ecode,@salary
while @@fetch_status = 0
begin
print 'Emp Code:' + convert (char(5), @ecode)

select * from dapartment
select * from employees
alter table employees
add bonus float, dname varchar(25)
update employees set bonus = 111, dname = 'Account', salary = 1111 where ecode = 101
insert into employees values (102,'bbb',2222,201,222.2,'Account')
update employees set bonus = 111, dname = 'Account', salary = 1111 where ecode = 103
update employees set deptid = 203 where ecode = 103
select * from employees
update employees set bonus = 444.6, dname = 'Admin', salary = 4444, deptid = 203 where ecode = 104
update dapartment set dname = 'Admin' where deptid = 203
delete employees where ecode = 105
select * from employees order by ecode

______________________________________________________________________________________________
declare @deptid int
declare @ecode int,@ename varchar(20),@salary int,@bonus int,@dname varchar(20)
declare dept_cur cursor
for select deptid from dapartment where deptid in (201,203)
open dept_cur
fetch next from dept_cur into @deptid
while (@@fetch_status = 0)
Begin
Print 'DeparmentID:'+convert(char(5),@deptid)
declare emp_cur cursor
for select ecode,ename,salary,bonus,dname  from employees where deptid = @deptid
open emp_cur
fetch next from emp_cur into @ecode,@ename,@salary,@bonus,@dname
while (@@fetch_status  = 0)
Begin
Print 'ecode ename salary bonus dname '
print convert(char(5),@ecode)+' '+@ename+' '+convert(char(5),@salary)+' '+convert(char(5),@bonus)+' '+@dname
fetch next from emp_cur into @ecode,@ename,@salary,@bonus,@dname
end
close emp_cur
deallocate emp_cur
fetch next from dept_cur into @deptid
end
close dept_cur
deallocate dept_cur

______________________________________________________________________________________________
declare @dbname varchar(25)
declare @cDate datetime
declare @fname varchar(100)
set @cDate = getdate()
declare BAK_Cur cursor
for  select name from master.dbo.sysdatabases
open BAK_Cur
fetch next from BAK_Cur into @dbname
while (@@fetch_status  = 0)
Begin
 select @fname='C:SystemDB'+@dbname+'_'+ convert(char(5),getdate()) +'.BAK'
 backup database @dbname to disk = @fname
 fetch next from BAK_Cur into @dbname
end
close BAK_Cur
deallocate BAK_Cur