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.