SQL Interview Questions | Page 2


Question 11:

Find nth highest salary of an employee from EmployeePayHistory table.

Method 1:
  
SELECT MIN(SALARY) AS SALARY
FROM 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

Comments

Comments
comments powered by Disqus

Navigation

Social Media