declare c1 cursor
for select st_id,st_fname
from student
where st_address='cairo'
for read only------ update
declare @id int,@name varchar(20)
open c1
fetch c1 into @id,@name
if @@fetch_status=0
declare @id int,@name varchar(20)
open c1
fetch c1 into @id,@name
while @@fetch_status=0
begin
select @id,@name
fetch c1 into @id,@name
end
select @id,@name
close c1
deallocate c1
......................
select st_fname
from student
where st_fname is not null
one cell {ahmed,amr,mona.....}
declare c1 cursor
for select st_fname
from student
where st_fname is not null
for read only------ update
declare @name varchar(20),@allnames varchar(300)=''
open c1
fetch c1 into @name
while @@fetch_status=0
begin
set @allnames=concat(@allnames,',',@name)
fetch c1 into @name
end
select @allnames
close c1
deallocate c1
..........................
declare c1 cursor
for select salary
from instructor
for update
declare @sal int
open c1
fetch c1 into @sal
while @@fetch_status=0
begin
if @sal>=3000
update instructor
set @salary=@sal*1.20
where current of c1
else
update instructor
set @salary=@sal*1.10
where current of c1
fetch c1 into @sal
end
select @sal
close c1
deallocate c1
..............................
declare c1 cursor
for select st_fname
from student
for read only
declare @name varchar(20),@counter int=0,@flag int=0
open c1
fetch c1 into @name
while @@fetch_status=0
begin
if @name='ahmed'
begin
set @flag=1
end
if @name='amr
begin
set @flag=1
begin
set @counter+=1
set @flag=0
end
end
fetch c1 into @name
end
select @counter
close c1
deallocate c1
...........................
backup database ITI
to disk='d;\iti_db.bak'
create table dbo.t1 (column_1, column_2 varchar(30),
column_3 int identity primary key )
go
select * from t1
insert t1 values (1,'row #1');
insert t1 (column_2) ('row #2');
go
set identity_insert t1 on
set identity_insert t1 off
go
insert into t1 (column_1,column_2,column_3) values
(4,1 ' explicit identity value');
go
select column_1,column_2,column_3
................
selct * from t1
delete from t1 where column_3 between 3 and 8
insert t1 values (100,'ahmed');
insert into (column_1,column_2,column_3) values
(7,777,'eman');
truncate table t1
go
select column_1,column_2,column_3
from t1
drop table t1
dbcc checkident(t1, Reseed,3)
select @@identity
select ident_current('t1')
.....................................
type of insert statment
-----------simple insert
-----------insert constructor
-----------insert based on select
-----------insert based on execute
-----------bulk insert
bulk insert emp
from 'd:\mydata.txt'
with(fieldterminator=',')
..................
snapshot (read only db)
create snapshot itisnap
on
(
name=,
filename='d:\itisnap.ss'
)
as snapshot of iti
select * from student
restore database iti
from database_snapshot='itisnap' -------snapshot name in object explorer
-------cursor
-------backup
-------snapshot
-------SQLCLR
------function
------new data type {class struct}
sp_configure 'clr_enable',1
go
reconfigure
select dbo.sum2Int(3,5)
select dbo.sum2Int(st_id,st_age)
from student
select _desc
from shapes
where_Coords.x>=10
select _desc
from shapes
