INTERVIEW QUESTIONS: SUBQUERIES

24 programming challenges to help you master the fundamentals of DATABASE MANAGEMENT SYSTEM USING ORACLE.

1

What is a subquery in SQL?

Start Solving
2

What are the different types of subqueries?

Start Solving
3

What is the difference between a subquery and a join?

Start Solving
4

What is a single-row subquery?

Start Solving
5

What is a multi-row subquery?

Start Solving
6

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose salary is greater than the average salary.

Start Solving
7

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees who earn more than employee ‘SMITH’.

Start Solving
8

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees working in the same department as ‘SCOTT’.

Start Solving
9

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose salary is equal to the minimum salary.

Start Solving
10

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose salary is greater than department 10 average salary.

Start Solving
11

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose salary is in salaries of department 10.

Start Solving
12

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose job is same as any employee in department 20.

Start Solving
13

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose salary is greater than ALL employees in department 30.

Start Solving
14

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose salary is greater than company average but less than department average.

Start Solving
15

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees earning top 3 salaries in each department.

Start Solving
16

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display nth highest salary using subquery.

Start Solving
17

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display second highest salary using subquery.

Start Solving
18

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees who are not assigned to any department.

Start Solving
19

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display departments that do not have employees.

Start Solving
20

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display departments that have employees.

Start Solving
21

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose salary is above average of their job role.

Start Solving
22

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees who earn more than their manager.

Start Solving
23

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display highest paid employee in each department.

Start Solving
24

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)Display employees whose salary is less than ANY employee in department 10.

Display employees who earn more than their department average salary.

Start Solving