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
 
 

Trigger Creation on Tables with Magic Table

I am going to create a Trigger on Table tblRaj.
This Trigger wil help to Insert the data in AUDIT Trail Table on each insertion and deletion.
AUDIT Trail Table tblRaj_AuditTrail has same structure (Except these columns
AuditTrailId,OperationType, OperationDate,ApplicationUser,DatabaseUser).
Now The Syantax is as Follows :-


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE TRIGGER [dbo].[trg_tblRaj_AuditTrail] on [dbo].[tblRaj] for insert, update , delete

AS

DECLARE @CONTEXT_INFO int

SELECT @CONTEXT_INFO=cast(context_info as int) FROM sys.dm_exec_requests WHERE session_id = @@SPID

if exists (select * from inserted) and exists (select * from deleted)

BEGIN

INSERT INTO tblRaj_AuditTrail select 'U', getdate(), @CONTEXT_INFO,system_user,* from inserted

END

else if exists (select * from inserted)

BEGIN

INSERT INTO tblRaj_AuditTrail select 'I', getdate(), @CONTEXT_INFO,system_user,* from inserted

END

else

BEGIN

INSERT INTO tblRaj_AuditTrail select 'D', getdate(), @CONTEXT_INFO,system_user,* from deleted

END