SQL1

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

 */

create 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 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 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