SQL
create table Cgn_mstLocation(i_LocationID int primary key,s_Location varchar(25),c_Status char)
alter table Cgn_mstLocation add s_LocationSpecial varchar(50)
sp_help Cgn_mstLocation
select * from Cgn_mstLocation
alter table Cgn_mstLocation drop column s_LocationSpecial
select * from Cgn_mstLocation where c_status='Y' and s_location='Hyderabad'
select * from Cgn_mstLocation where c_status='Y' or c_status='N'
select * from Cgn_mstLocation where s_location like '%hyd%' --any place
select * from Cgn_mstLocation where s_location like 'hyd%' --First three characters
select * from Cgn_mstLocation where s_location like '%hyd' --last three characters
create table Cgn_User(i_UserID int primary key,s_UserName varchar(25),c_Status char,i_LocationID int references Cgn_mstLocation(i_LocationID) )
insert into Cgn_User values(1,'Ashok','Y',4)
insert into Cgn_User values(4,'Ashok','Y',1)
insert into Cgn_User values(2,'Jhon','Y',1)
insert into Cgn_User values(3,'Sunil','N',2)
select i_UserID as 'User Id',s_UserName as 'User Name',c_Status as 'User Status',
(select s_Location from Cgn_mstLocation where Cgn_mstLocation.i_LocationID=Cgn_User.i_LocationID)as 'Location'
from Cgn_User
select i_UserID as 'User Id',s_UserName+' / '+c_Status as 'User Name / Location' from Cgn_User
delete from Cgn_User --All the records will get deleted
delete from Cgn_User where i_UserID=3 --All the records will get deleted
--drop table Cgn_User
select * from Cgn_User
select count(*) from Cgn_User where i_Locationid=1
select sum(i_userId)from Cgn_User
print 'Checking'
select replace('welcome','o','0')
select cos(5)
select pi()
select tan(4)
select sin(5)
select substring('welcome',2,2)
select upper('welCOme')
select lower('WelCOME')
select sqrt(2)
declare @str varchar(150)
select @str=coalesce(@str+s_UserName+',',' ') from Cgn_User
print @str
select usr.i_UserID as 'User Id',usr.s_UserName as 'User Name',usr.c_Status as 'User Status', loc.s_Location as 'Location'
from Cgn_User usr,Cgn_mstLocation loc
where usr.i_LocationID=loc.i_LocationID
-----------------------------------------------------------------
select name from sys.tables
select * from sys.procedures
----------------------------
create procedure sp_UserDetails
as
select * from Cgn_User
------------------------------
exec sp_UserDetails
execute sp_UserDetails
sp_depends sp_UserDetails_Add
sp_help sp_UserDetails
sp_helptext sp_UserDetails_Add
------------------------------------
alter procedure sp_UserDetails(@i_UserId int,@i_LocationId int)
as
declare @sUserName varchar(max) --Variable Declaration
select i_UserID,s_UserName from Cgn_User
where i_UserID=@i_UserId and
i_LocationId=@i_LocationId
exec sp_UserDetails 1,4
select * from Cgn_User
sp_help Cgn_User
----------------------------------
/*
Procedure Name :
Created BY : Raja
Created on : 21-May-2010
Purpose : To insert data in to user details table
Modification:-
S.No Date Modified By Puprpose
1 21-May-2010 Raja If condition checking implementation
### sp_UserDetails_Add 6,'Ram','Y',4
*/
alter procedure sp_UserDetails_Add(@i_UserID int,@s_UserName varchar(max),@c_Status char,@i_LocationID int)
as
if Exists(select * from Cgn_User where s_UserName=@s_UserName)
begin
select 0
return
end
else
begin
insert into Cgn_User values(@i_UserID,@s_UserName,@c_Status,@i_LocationID)
select 1
return
end
/*
Procedure Name :sp_UserDetails_Delete
Created BY : Raja
Created on : 21-May-2010
Purpose : To delete data from user details table
Modification:-
S.No Date Modified By Puprpose
### sp_UserDetails_Delete 2
*/
alter procedure sp_UserDetails_Delete(@i_UserID int)
as
if Exists(select * from Cgn_User where i_UserID=@i_UserID)
begin
delete from Cgn_User where i_UserID=@i_UserID
select 1
return
end
else
begin
select 0
return
end
--------------------------
create procedure sp_mstrdetails_add(@i_LocationID int,@s_Location varchar(max),@c_status char,@s_LocationSpecial varchar(max))
as
if exists(select * from Cgn_mstLocation where i_LocationID=@i_LocationID)
begin
select 0
return
end
else
begin
insert into Cgn_mstLocation values(@i_LocationID ,@s_Location,@c_status,@s_LocationSpecial)
select 1
return
end
sp_help Cgn_mstLocation
exec sp_mstrdetails_add 7,hyd,y,aaa
create table tbl_cgnuser_log(dt_time datetime)
--------------------
create trigger tr_cgnUser on cgn_user
for insert,update,delete as
insert into tbl_cgnuser_log values(getdate())
select user_name()
select * from tbl_cgnuser_log
select * from tr_cgnUser
-------------------------------
select * from Cgn_User
select *,case i_LocationId
when 1 then 'Bangalore'
when 2 then 'Mumbai'
else 'Other location' end as 'Location Name'
from cgn_user
----------------------------------
alter view vw_user
as
select * from cgn_user where i_locationid=1
select * from vw_user
update vw_user set s_USerName='A.Ashok' where i_USerId=4