SQL Server Query Interview Questions

1.What will be the output following script?

DECLARE @EMP TABLE
(
    ID INT,
    Name NVARCHAR(100)
)
INSERT INTO @EMP
(ID,Name)
SELECT 1,'Joe' UNION ALL
SELECT 2,'Mac' UNION ALL
SELECT 3,'Sam' UNION ALL
SELECT 4,'Peter'
SELECT 5 FROM @EMP

2. Find nth highest salary form table.

DECLARE @Employee TABLE
(
EmpID INT Identity,
EmpName Varchar(100),
EmpSalary Decimal (10,2)
)
INSERT INTO @Employee VALUES ('Joe',15000);
INSERT INTO @Employee VALUES ('Mac',13000);
INSERT INTO @Employee VALUES ('John',11000);
INSERT INTO @Employee VALUES ('Stev',14000);
INSERT INTO @Employee VALUES ('Sam',19000);

select min(EmpSalary) from @Employee where EmpSalary in
(select distinct top N EmpSalary from @Employee order by EmpSalary desc)

Note: Where N = 1,2,3 ..

3. Delete duplicate records from table.
DECLARE @Employee TABLE
(
EmpName Varchar(100),
EmpSalary Decimal (10,2)
)
INSERT INTO @Employee VALUES ('Joe',15000);
INSERT INTO @Employee VALUES ('Joe',15000);
INSERT INTO @Employee VALUES ('Mac',13000);
INSERT INTO @Employee VALUES ('Mac',13000);
INSERT INTO @Employee VALUES ('John',11000);
INSERT INTO @Employee VALUES ('Stev',14000);
INSERT INTO @Employee VALUES ('Sam',19000);
;WITH CTE AS
(
    SELECT * , ROW_NUMBER() OVER (PARTITION BY EmpName ORDER BY EmpName) AS 'Rnk'
    FROM @Employee
)
DELETE FROM CTE WHERE Rnk > 1