SQL Server Interview Questions Series - Part 3

43. What is the function name for getting the current date in SQL Server?

We can use GETDATE() with SELECT statement for getting the current date.

SELECT GetDate()

44. Print the name of distinct employee whose date of birth is between 01/01/1950 to 12/31/1970.

SELECT DISTINCT Name FROM Employee WHERE DOB BETWEEN '01/01/1950' AND '12/31/1970'

45. Count the total number of employees based on Gender whose date of birth is between 01/01/1950 to 12/31/1970.

SELECT Gender, Count(Gender)
FROM Employee 
WHERE DOB BETWEEN '01/01/1950' AND '12/31/1970'
GROUP BY Gender

46. Print current year in SQL.

SELECT YEAR(GETDATE()) as 'Current Year'

47. Print duplicate rows from employee table and then write query to delete them.

Query for finding duplicate rows from employee table.

SELECT ID, NAME, Salary FROM Employee a
WHERE ID IN (SELECT Min(a.ID) FROM Employee a GROUP BY a.Name)

Delete statement for deleting duplicate rows

DELETE EMPLOYEE WHERE ID NOT IN (SELECT Min(a.ID) FROM Employee a GROUP BY a.Name)

48. Print whose employee names whose salary is greater than average salary of all employees.

SELECT Name
FROM Employee
WHERE Salary > (SELECT AVG(SALARY) FROM EMPLOYEE)

49. Print all the employees and their manager names from employee table.

SELECT a.Name, b.Name
FROM Employee a INNER JOIN Employee b
ON a.mgrid = b.id

50. Print all employees who are also managers from employee table.

SELECT Name
FROM Employee
WHERE MgrID = ID

51. Print second highest salary from an Employee table.

SELECT TOP 1 Salary
FROM (SELECT DISTINCT TOP 2 Salary FROM Employee ORDER BY SALARY DESC) AS emp
ORDER BY Salary

52. Print 10th highest salary from an Employee table.

SELECT TOP 1 Salary
FROM (SELECT DISTINCT TOP 10 Salary FROM Employee ORDER BY SALARY DESC) AS emp
ORDER BY Salary

53. Find alternate or even/odd rows from Employee table.

Odd rows

WITH MyTable AS
(
	SELECT ROW_NUMBER() OVER(ORDER BY ID) as 'RowNumber',* FROM Employee
)
SELECT *
FROM MyTable
WHERE (RowNumber % 2) = 1

Even or Alternate rows

WITH MyTable AS
(
	SELECT ROW_NUMBER() OVER(ORDER BY ID) as 'RowNumber',* FROM Employee
)
SELECT *
FROM MyTable
WHERE (RowNumber % 2) = 0

54. Print the 3rd maximum salary from the employee table.

SELECT DISTINCT SALARY
FROM Employee a WHERE 3 = (SELECT COUNT(DISTINCT Salary) FROM Employee b WHERE a.SALARY <= b.SALARY)

OR

SELECT MIN(Salary) 
FROM Employee WHERE Salary IN (SELECT DISTINCT TOP 3 Salary FROM EMPLOYEE ORDER BY Salary DESC)

55. Print the 3rd minimum salary from the employee table.

SELECT DISTINCT SALARY
FROM Employee a WHERE 3 = (SELECT COUNT(DISTINCT Salary) FROM Employee b WHERE a.SALARY >= b.SALARY)

OR

SELECT MAX(Salary) 
FROM Employee WHERE Salary IN (SELECT DISTINCT TOP 3 Salary FROM EMPLOYEE ORDER BY Salary ASC)

56. Print department name from the department table in which there are no employees.

SELECT Name 
FROM DEPT 
WHERE ID NOT IN (SELECT DISTINCT DEPTID FROM EMPLOYEE)

57. Print 3 maximum salaries from employee table.

SELECT DISTINCT Salary
FROM Employee a 
WHERE 3 >= (SELECT COUNT(DISTINCT Salary) FROM Employee b WHERE a.Salary <= b.Salary) 
ORDER BY a.Salary DESC

58. Print 3 minimum salaries from employee table.

SELECT DISTINCT Salary
FROM Employee a 
WHERE 3 >= (SELECT COUNT(DISTINCT Salary) FROM Employee b WHERE a.Salary >= b.Salary) 
ORDER BY a.Salary DESC

59. Print the nth maximum salaries from employee table.

Replace nth Number with the number you want. 

SELECT DISTINCT Salary
FROM Employee a 
WHERE (nth Number) = (SELECT COUNT(DISTINCT Salary) FROM Employee b WHERE a.Salary <= b.Salary)

OR

SELECT MIN(Salary) 
FROM Employee 
WHERE Salary IN (SELECT DISTINCT TOP (nth Number) Salary FROM Employee ORDER BY Salary DESC)

60. Print the nth minimum salaries from employee table.

Replace nth Number with the number you want.

SELECT DISTINCT Salary
FROM Employee a 
WHERE (nth Number) = (SELECT COUNT(DISTINCT Salary) FROM Employee b WHERE a.Salary >= b.Salary)

OR

SELECT MAX(Salary) 
FROM Employee 
WHERE Salary IN (SELECT DISTINCT TOP (nth Number) Salary FROM Employee ORDER BY Salary ASC)

61. Print those department and their sum of salaries in which more than 2 employee exist.

SELECT DeptID, SUM(Salary) as 'Sum'
FROM Employee
GROUP BY DeptID
HAVING COUNT(ID) > 2

62. Can you delete duplicate rows from employee table using temporary table.

SELECT TOP 0 * into emp_temp
FROM Employee

INSERT INTO emp_temp
SELECT Name, Salary, DeptID, DOB, Gender, MGRID 
FROM Employee
GROUP BY Name, Salary, DeptID, DOB, Gender, MGRID

TRUNCATE TABlE employee

INSERT INTO Employee
SELECT Name, Salary, DeptID, DOB, Gender, MGRID FROM emp_temp

SELECT * FROM Employee

63. Can you delete duplicate rows from employee table without using temporary table.

WITH T AS
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) AS RANK
	FROM Employee
)

DELETE
FROM T
WHERE RANK > 1

 

Kindly provide more SQL interview questions in comments. I'll include those in the sql interview question series.

Thanks for your help.