# SQL Interview Questions | Page 2

##### Question 11:Find nth highest salary of an employee from EmployeePayHistory table.
Method 1:

SELECT MIN(SALARY) AS SALARYFROM HR.EmployeePayHistory
WHERE SALARY IN ( SELECT DISTINCT TOP 4 SALARY FROM HR.EmployeePayHistory
ORDER BY SALARYdesc)

Method 2:

SELECT SALARY AS SALARY FROM HR.EmployeePayHistory A
WHERE 1 = ( SELECT DISTINCT COUNT(SALARY)                   FROM HR.EmployeePayHistory B
WHERE B.SALARY > A.SALARY )

##### Question 12:Find highest salary of an employee from Employee from EmployeePayHistory table.
SELECT MAX(SALARY)
FROM HR.EMPLOYEEPAYHISTORY

##### Question 13:How to select unique Person name from Person table?
SELECT DISTINCT FirstName
FROM Person.Person

##### Question 14:For a given tables Employee & Department, write SQL Query to find Max Salary of each department.
SELECT DEPTNAME, MAX(SALARY)
FROM EMPLOYEE E RIGHT JOIN DEPARTMENT D
ON E.DEPTID = D.DEPTID
GROUP BY DEPTNAME

##### Question 15:Write SQL Query to display current date.

Method 1:

 SELECT GetDate();

Method 2:

SELECT CURRENT_TIMESTAMP

##### Question 16:Write an SQL Query to find employee whose Salary is equal or greater than 1000.
SELECT EmpName
FROM Employees
WHERE Salary>=1000;

##### Question 17:Write a SQL query to find all employees which are also manager.
SELECT Emp1.NAME, Emp2.NAME
FROM EMPLOYEE Emp1, EMPLOYEE Emp2
WHERE Emp1.MGR_ID = Emp2.EMP_ID;

##### Question 18:How can I create an empty table employee1 with same structure as employee?
SELECT * INTO HR.EMP1
FROM HR.EMP WHERE 1=2;

##### Question 19:How to delete duplicate rows in a table?
CREATE A TABLE Emp

SELECT * INTO Emp
FROM
(
SELECT 1 as ID,'John'as Name
union all
SELECT 1 as ID,'John'as Name
union all
SELECT 2 as ID,'Sunny'as Name
union all
SELECT 2 as ID,'Sunny'as Name
union all
SELECT 3 as ID,'Robert'as Name
union all
SELECT 3 as ID,'Robert'as Name
)X



Method 1:

Delete Duplicate Records using CTE (Common Table Expression)

WITH CTE(ROWNUM,ID,NAME) AS
(
SELECT ROW_NUMBER() over(partition by ID order by ID),* FROm Emp
)
DELETE FROM CTE WHERE ROWNUM > 1


Method 2:

SELECT DISTINCT * INTO X1 FROM EMP;

DELETE FROM EMP;

INSERT INTO EMP;

SELECT * FROM X1 ;

##### Question 20:How to fetch only common records from two tables employee and employee1?

Create Emp Table.

SELECT * INTO HR.Emp
FROM
(
SELECT 1 as ID,'John'as Name
union all
SELECT 1 as ID,'John'as Name
union all
SELECT 2 as ID,'Sunny'as Name
union all
SELECT 2 as ID,'Sunny'as Name
union all
SELECT 3 as ID,'Robert'as Name
union all
SELECT 3 as ID,'Robert'as Name
)X

Create 2nd Table Emp1 with same records.
SELECT * INTO HR.Emp1 FROM HR.Emp

Method 1:
SELECT * FROM HR.EMP
UNION
SELECT * FROM HR.EMP

Method 2:
SELECT * FROM HR.EMP
INTERSECT
SELECT * FROM HR.EMP

Share interview questions