SQL query Question and Answers

**Q1:**Find nth highest salary using CTE
How to find nth highest salary in SQL Server using a Sub-Query
How to find nth highest salary in SQL Server using a CTE
How to find the 2nd, 3rd or 15th highest salary
To find nth highest salary using Sub-Query

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

To find nth highest salary using CTE

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

To find 2nd highest salary we can use any of the above queries. Simple replace N with 2.

Similarly, to find 3rd highest salary, simple replace N with 3.

Please Note: On many of the websites, you may have seen that, 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

Q2:
Here is the problem definition:
1. Employees table contains the following columns a) EmployeeId, b) EmployeeName c) ManagerId
2. If an EmployeeId is passed, the query should list down the entire organization hierarchy i.e who is the manager of the EmployeeId passed and who is managers manager and so on till full hierarchy is listed.

Here is the SQL that does the job

Declare @ID int ;
Set @ID = 7;

WITH EmployeeCTE AS
(
Select EmployeeId, EmployeeName, ManagerID
From Employees
Where EmployeeId = @ID

UNION ALL

Select Employees.EmployeeId , Employees.EmployeeName, Employees.ManagerID
From Employees
JOIN EmployeeCTE
ON Employees.EmployeeId = EmployeeCTE.ManagerID
)

Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName
From EmployeeCTE E1
LEFT Join EmployeeCTE E2
ON E1.ManagerID = E2.EmployeeId

Q3:

Q4:
We will discuss deleting all duplicate rows except one from a sql server table.

SQL Script to create Employees table

Create table Employees
(
ID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Gender nvarchar(50),
Salary int
)
GO

Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000)
Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000)
Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000)
Insert into Employees values (2, 'Mary', 'Lambeth', 'Female', 30000)
Insert into Employees values (2, 'Mary', 'Lambeth', 'Female', 30000)
Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000)
Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000)
Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000)

The delete query should delete all duplicate rows except one.

Here is the SQL query that does the job. PARTITION BY divides the query result set into partitions.

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

**Q5:**Replace N with number of months
This question is asked is many sql server interviews. If you have used DATEDIFF() sql server function then you already know the answer.

– Replace N with number of months

Select *
FROM Employees
Where DATEDIFF(MONTH, HireDate, GETDATE()) Between 1 and N

Q6: sql query to transpose rows to columns.

SQL to create the table

Create Table Countries
(
Country nvarchar(50),
City nvarchar(50)
)
GO

Insert into Countries values ('USA','New York')
Insert into Countries values ('USA','Houston')
Insert into Countries values ('USA','Dallas')

Insert into Countries values ('India','Hyderabad')
Insert into Countries values ('India','Bangalore')
Insert into Countries values ('India','New Delhi')

Insert into Countries values ('UK','London')
Insert into Countries values ('UK','Birmingham')
Insert into Countries values ('UK','Manchester')

Write a sql query to transpose rows to columns.
Using PIVOT operator we can very easily transform rows to columns.

Select Country, City1, City2, City3
From
(
Select Country, City, 'City'+ cast(row_number() over(partition by Country order by Country) as varchar(10)) ColumnSequence
from Countries
) Temp
pivot
(
max(City)
for ColumnSequence in (City1, City2, City3)
) Piv

**Q7:**Write a SQL query to retrieve rows that contain only numerical data.

SELECT Value FROM TestTable WHERE ISNUMERIC(Value) 

**Q8:**Based on the above two tables write a SQL Query to get the name of the Department that has got the maximum number of Employees.

SELECT TOP 1 DepartmentName
FROM Employees
JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID
GROUP BY DepartmentName
ORDER BY COUNT(*) DESC

**Q9:**Differences between Joins
INNER JOIN returns only the matching rows between the tables involved in the JOIN.

LEFT JOIN returns all rows from left table including non-matching rows.

What is the difference between INNER JOIN and RIGHT JOIN
INNER JOIN returns only the matching rows between the tables involved in the JOIN, where as RIGHT JOIN returns all the rows from the right table including the NON-MATCHING rows.

What is the difference between INNER JOIN and FULL JOIN
FULL JOIN returns all the rows from both the left and right tables including the NON-MATCHING rows.

What is the Difference between INNER JOIN and JOIN
There is no difference they are exactly the same. Similarly there is also no difference between
LEFT JOIN and LEFT OUTER JOIN
RIGHT JOIN and RIGHT OUTER JOIN
FULL JOIN and FULL OUTER JOIN

**Q10:**Write a query to join 3 the tables and retrieve EmployeeName, DepartmentName and Gender.

Query:

SELECT EmployeeName, DepartmentName, Gender
FROM Employees
JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
JOIN Genders ON Employees.GenderID = Genders.GenderID

Write a query to show the total number of employees by DEPARTMENT and by GENDER.
Query:

SELECT DepartmentName, Gender, COUNT(*) as TotalEmployees
FROM Employees
JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
JOIN Genders ON Employees.GenderID = Genders.GenderID
GROUP BY DepartmentName, Gender
ORDER BY DepartmentName, Gender