SQL Server Interview Question

Nth Highest salary

1. Select Max(Salary) from Employees where Salary < (Select Max(Salary) from Employees)

2. SELECT TOP 1 SALARY
FROM (
      SELECT DISTINCT TOP N SALARY
      FROM EMPLOYEES
      ORDER BY SALARY DESC
      ) RESULT
ORDER BY SALARY


3. WITH RESULT AS
(
    SELECT SALARY,
           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
    FROM EMPLOYEES
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE DENSERANK = N


NOTE-the following query can be used to get the nth highest salary. The below query will only work if there are no duplicates.
WITH RESULT AS
(
    SELECT SALARY,
           ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUMBER
    FROM EMPLOYEES
)
SELECT SALARY
FROM RESULT

WHERE ROWNUMBER = 3



Delete Duplicate rows in table


WITH EmployeesCTE AS
(
   SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) AS RowNumber
   FROM Employees
)

DELETE FROM EmployeesCTE WHERE RowNumber > 1




RANK, DENSE_RANK and ROW_NUMBER

Similarities between RANK, DENSE_RANK and ROW_NUMBER functions
Returns an increasing integer value starting at 1 based on the ordering of rows imposed by the ORDER BY clause (if there are no ties)
ORDER BY clause is required
PARTITION BY clause is optional
When the data is partitioned, the integer value is reset to 1 when the partition changes

SELECT Name, Salary, Gender,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber,
RANK() OVER (ORDER BY Salary DESC) AS [Rank],
DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees

Difference between RANK, DENSE_RANK and ROW_NUMBER functions
ROW_NUMBER : Returns an increasing unique number for each row starting at 1, even if there are duplicates.
RANK : Returns an increasing unique number for each row starting at 1. When there are duplicates, same rank is assigned to all the duplicate rows, but the next row after the duplicate rows will have the rank it would have been assigned if there had been no duplicates. So RANK function skips rankings if there are duplicates.
DENSE_RANK : Returns an increasing unique number for each row starting at 1. When there are duplicates, same rank is assigned to all the duplicate rows but the DENSE_RANK function will not skip any ranks. This means the next row after the duplicate rows will have the next rank in the sequence



Difference between except and not in sql server

Select Id, Name, Gender From TableA
Where Id NOT IN (Select Id from TableB)

Select Id, Name, Gender From TableA
Except
Select Id, Name, Gender From TableB

1. Except filters duplicates and returns only DISTINCT rows from the left query that aren’t in the right query’s results, where as NOT IN does not filter the duplicates.
2. EXCEPT operator expects the same number of columns in both the queries, where as NOT IN, compares a single column from the outer query with a single column from the subquery.


Union Vs Intersect Vs Except
UNION operator returns all the unique rows from both the left and the right query. UNION ALL included the duplicates as well.
INTERSECT operator retrieves the common unique rows from both the left and the right query.
EXCEPT operator returns unique rows from the left query that aren’t in the right query’s results. 


Cross Apply vs Outer Apply
Cross Apply is semantically equivalent to Inner Join and Outer Apply is semantically equivalent to Left Outer Join.
How does Cross Apply and Outer Apply work
The APPLY operator introduced in SQL Server 2005, is used to join a table to a table-valued function.
The Table Valued Function on the right hand side of the APPLY operator gets called for each row from the left (also called outer table) table.
Cross Apply returns only matching rows (semantically equivalent to Inner Join)
Outer Apply returns matching + non-matching rows (semantically equivalent to Left Outer Join). The unmatched columns of the table valued function will be set to NULL.

Difference between WHERE and Having
1. WHERE clause cannot be used with aggregates where as HAVING can. This means WHERE clause is used for filtering individual rows where as HAVING clause is used to filter groups.
2. WHERE comes before GROUP BY. This means WHERE clause filters rows before aggregate calculations are performed. HAVING comes after GROUP BY. This means HAVING clause filters rows after aggregate calculations are performed. So from a performance standpoint, HAVING is slower than WHERE and should be avoided when possible.
3. WHERE and HAVING can be used together in a SELECT query. In this case WHERE clause is applied first to filter individual rows. The rows are then grouped and aggregate calculations are performed, and then the HAVING clause filters the groups.


Intersect In SqlServer
The number and the order of the columns must be same in both the queries
The data types must be same or at least compatible
Select Id, Name, Gender from TableA
Intersect
Select Id, Name, Gender from TableB

same can be achieved by inner joins
1. INTERSECT filters duplicates and returns only DISTINCT rows that are common between the LEFT and Right Query, where as INNER JOIN does not filter the duplicates.
2. INNER JOIN treats two NULLS as two different values. So if you are joining two tables based on a nullable column and if both tables have NULLs in that joining column then, INNER JOIN will not include those rows in the result-set, where as INTERSECT treats two NULLs as a same value and it returns all matching rows.