Finding File Name from a String in Sql Server 2005

Declare
Set @PathName varchar(100),@FileName varchar(100), @RStr varchar(100),@I int @PathName = 'D:\Rajeeva\Docs\File1.aspx'SET @RStr = Reverse(@PathName)SELECT @I = CHARINDEX('\', @RStr)Select @FileName = substring(@RStr,1,@I-1)PRINT Reverse(@FileName)

ALL employee Name with Max Salary

Declare @T1 Table(Eid int,Ename varchar(10))


Declare @T2 Table(Payid int,Eid int,Sal decimal)

INSERT INTO @T1(Eid,EName)Values(1,'A')

INSERT INTO @T1(Eid,EName)Values(2,'B')

INSERT INTO @T1(Eid,EName)Values(3,'C')

INSERT INTO @T1(Eid,EName)Values(4,'D')



INSERT INTO @T2(Payid,Eid,Sal)Values(1,1,500)

INSERT INTO @T2(Payid,Eid,Sal)Values(2,2,100)

INSERT INTO @T2(Payid,Eid,Sal)Values(3,3,500)

INSERT INTO @T2(Payid,Eid,Sal)Values(4,4,250)

SELECT * from @T1

SELECT * from @T2

SELECT b.sal, a.Ename From @T1 a INNER JOIN @T2 b on a.EID = b.Eid where

b.sal =(SELECT Max(Sal) From @T2)

Nth Salary in Sql server 2005

SELECT TOP 1 salary


FROM (

SELECT DISTINCT TOP n salary

FROM TableName
ORDER BY salary DESC) a

ORDER BY salary

Some Join Examples (INNER,OUTER)

Declare @T1 Table(Eid int,Ename varchar(10))


Declare @T2 Table(Eid int,Ename varchar(10))

INSERT INTO @T1(Eid,EName)Values(1,'A')

INSERT INTO @T1(Eid,EName)Values(1,'B')

INSERT INTO @T2(Eid,EName)Values(1,'A')

INSERT INTO @T2(Eid,EName)Values(1,'B')

INSERT INTO @T2(Eid,EName)Values(1,'C')

SELECT a.Eid, a.EName from @T1 a INNER JOIN @T2 b ON a.Eid = b.Eid
 
RESULT :-
**********************************************************
Eid EName


----------- ----------

1 A

1 A

1 A

1 B

1 B

1 B
****************************************************************
NEXT :-
****************************************************************
Declare @T1 Table(Eid int,Ename varchar(10))


Declare @T2 Table(Eid int,Ename varchar(10))

INSERT INTO @T1(Eid,EName)Values(1,'A')

INSERT INTO @T1(Eid,EName)Values(2,'B')

INSERT INTO @T2(Eid,EName)Values(1,'A')

INSERT INTO @T2(Eid,EName)Values(2,'B')

INSERT INTO @T2(Eid,EName)Values(3,'C')

INSERT INTO @T2(Eid,EName)Values(4,'D')

SELECT a.Eid, a.EName from @T1 a INNER JOIN @T2 b ON a.Eid = b.Eid

SELECT a.Eid, a.EName from @T1 a LEFT OUTER JOIN @T2 b ON a.Eid = b.Eid

SELECT a.Eid, a.EName from @T1 a RIGHT OUTER JOIN @T2 b ON a.Eid = b.Eid

*********************************************************************
RESULT1 :-
*********
Eid         EName


----------- ----------

1           A

2           B
********************
RESULT 2 :-
*************
Eid     EName


----------- ----------

1       A

2       B
**************
Result 3 :-
**********
Eid                EName


----------- ----------

1                 A

2                 B

NULL        NULL

NULL        NULL

Find More Than one records in a Table

Declare @T1 Table(Eid int,Ename varchar(10),Loc varchar(20))


INSERT INTO @T1(Eid,EName,Loc)Values(1,'A','Delhi')

INSERT INTO @T1(Eid,EName,Loc)Values(2,'B','Delhi')

INSERT INTO @T1(Eid,EName,Loc)Values(3,'C','Delhi')

INSERT INTO @T1(Eid,EName,Loc)Values(4,'D','KOL')

INSERT INTO @T1(Eid,EName,Loc)Values(5,'E','MUM')

INSERT INTO @T1(Eid,EName,Loc)Values(6,'F','MUM')

INSERT INTO @T1(Eid,EName,Loc)Values(7,'G','MUM')

INSERT INTO @T1(Eid,EName,Loc)Values(8,'H','Lko')





SELECT EId, EName from @T1 where Loc IN (

SELECT Loc FROM @T1 GROUP BY Loc

HAVING COUNT(*) > 1)

Delete Duplicate records From SQL -One Field Should be unique

Declare @T1 Table(Eid int,Ename varchar(10))


INSERT INTO @T1(Eid,EName)Values(1,'A')

INSERT INTO @T1(Eid,EName)Values(2,'A')

INSERT INTO @T1(Eid,EName)Values(3,'B')

INSERT INTO @T1(Eid,EName)Values(4,'B')

INSERT INTO @T1(Eid,EName)Values(5,'C')



DELETE FROM @T1 WHERE Eid NOT IN

(SELECT MAX(Eid) FROM @T1 GROUP BY Ename)

SELECT * from @T1

Update one table From another table with INNER JOIN in SQL

Declare @T1 Table(Eid int,Ename varchar(10))


Declare @T2 Table(Eid int,Ename varchar(10))

INSERT INTO @T1(Eid,EName)Values(1,'A')

INSERT INTO @T1(Eid,EName)Values(2,'B')

INSERT INTO @T1(Eid,EName)Values(3,'C')

INSERT INTO @T1(Eid,EName)Values(4,'D')

INSERT INTO @T1(Eid,EName)Values(5,'E')

SELECT * from @T1

INSERT INTO @T2(Eid,EName)Values(1,'R')

INSERT INTO @T2(Eid,EName)Values(2,'S')

INSERT INTO @T2(Eid,EName)Values(3,'T')

INSERT INTO @T2(Eid,EName)Values(4,'U')

INSERT INTO @T2(Eid,EName)Values(5,'V')

SELECT * from @T2



UPDATE a SET a.Ename = b.Ename

FROM @T1 a INNER JOIN @T2 b ON a.Eid = b.Eid

SELECT * FROM @T1