SQL Interview Questions Part 1

SQL Interview Questions Part 1

1. How to get unique names from Employee table without using DISTINCT keyword.

SELECT name

FROM employee

GROUP BY name

2. SQL Query to find Max Salary from each department.(Only Employee table is used)

SELECT DeptId, MAX(Salary)

FROM Employee

GROUP BY DeptId

3. SQL Query to find Max salary from each department name wise.

(We have to use Employee and Department table as well.)

SELECT DeptName, MAX(Salary)

FROM Employee e

JOIN Department d

ON e.DeptId = d.DeptID

GROUP BY DeptName;

4. Get position of ‘o’ in name ‘John’ from employee table

Oracle Syntax:

SELECT INSTR(Employee_Name,’o’)

FROM employee

WHERE Employee_Name = ‘John’

5. Select first 3 characters of Employee_NAME from EMPLOYEE

Oracle Syntax:

SELECT substr(Employee_NAME,0,3)

FROM employee

6. Get employee details from employee table whose ends with ‘n’

Oracle Syntax:

SELECT *

FROM EMPLOYEE

WHERE EMPLOYEE_NAME like ‘%n’

7. Get names of employees from employee table who has ‘%’ in Last_Name.

Tip : Escape character for special characters in a query.

Oracle Syntax:

SELECT EMPLOYEE_NAME

FROM employee

WHERE EMPLOYEE_NAME like ‘%?%%’

8. Retrieve two highest paid employees from each department.

Oracle Syntax:

SELECT Deptno, Empno, Salary

FROM Employee e

WHERE

2 > (SELECT COUNT(e1.Salary)

FROM Employee e1

WHERE e.Deptno = e1.Deptno AND e.Salary < e1.Salary)

ORDER BY 1,3 DESC;

9. Retrieve fourth highest salary from Employee table.

SELECT DeptNo, EmpName, Salary

FROM Employee A

WHERE

3 = (SELECT COUNT(B.Salary)

FROM Employee B

WHERE A.Salary < B.Salary)

ORDER BY Salary DESC;