Oracle Subquery/Correlated Query Examples

  • Updated
  • Posted in Programming
  • 3 mins read


A subquery is a SELECT assertion which is utilized in one other SELECT assertion. Subqueries are very helpful when you must choose rows from a desk with a situation that is determined by the information of the desk itself. You can use the subquery within the SQL clauses together with WHERE clause, HAVING clause, FROM clause and many others.

The subquery may also be referred as nested SELECT, sub SELECT or internal SELECT. In normal, the subquery executes first and its output is utilized in the primary question or outer question.

Types of Sub queries:

There are two sorts of subqueries in oracle:

  • Single Row Subqueries: The subquery returns just one row. Use single row comparability operators like =, > and many others whereas doing comparisions.
  • Multiple Row Subqueries: The subquery returns multiple row. Use a number of row comparability operators like IN, ANY, ALL within the comparisons.

Single Row Subquery Examples

1. Write a question to seek out the wage of workers whose wage is larger than the wage of worker whose id is 100?

SELECT EMPLOYEE_ID,
SALARY
FROM EMPLOYEES
WHERE SALARY >
(
SELECT SALARY
FROM EMPLOYEES
WHERE EMPLOYEED_ID = 100
)

2. Write a question to seek out the workers who all are incomes the best wage?

SELECT EMPLOYEE_ID,
SALARY
FROM EMPLOYEES
WHERE SALARY =
(
SELECT MAX(SALARY)
FROM EMPLOYEES
)

3. Write a question to seek out the departments wherein the least wage is larger than the best wage within the division of id 200?

SELECT DEPARTMENT_ID,
MIN(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MIN(SALARY) >
(
SELECT MAX(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 200
)

Multiple Row Subquery Examples

1. Write a question to seek out the workers whose wage is the same as the wage of at the very least one worker in division of id 300?

SELECT EMPLOYEE_ID,
SALARY
FROM EMPLOYEES
WHERE SALARY IN
(
SELECT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 300
)

2. Write a question to seek out the workers whose wage is larger than at the very least on worker in division of id 500?

SELECT EMPLOYEE_ID,
SALARY
FROM EMPLOYEES
WHERE SALARY > ANY
(
SELECT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 500
)

3. Write a question to seek out the workers whose wage is lower than the wage of all workers in division of id 100?

SELECT EMPLOYEE_ID,
SALARY
FROM EMPLOYEES
WHERE SALARY < ALL
(
SELECT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
)

4. Write a question to seek out the workers whose supervisor and division ought to match with the worker of id 20 or 30?

SELECT EMPLOYEE_ID,
MANAGER_ID,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE (MANAGER_ID,DEPARTMENT_ID) IN
(
SELECT MANAGER_ID,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID IN (20,30)
)

5. Write a question to get the division identify of an worker?

SELECT EMPLOYEE_ID,
DEPARTMENT_ID,
(SELECT DEPARTMENT_NAME
FROM DEPARTMENTS D
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
)
FROM EMPLOYEES E

Correlated SubQueries Examples

Correlated sub question is used for row by row processing. The sub question is executed for every row of the primary question.

1. Write a question to seek out the best incomes worker in every division?

SELECT DEPARTMENT_ID,
EMPLOYEE_ID,
SALARY
FROM EMPLOYEES E_0
WHERE 1 =
(
SELECT COUNT(DISTINCT SALARY)
FROM EMPLOYEES E_I
WHERE E_O.DEPARTMENT_ID = E_I.DEPARTMENT_ID
AND E_O.SALARY <= E_I.SALARY
)

2. Write a question to checklist the division names which have at lease one worker?

SELECT DEPARTMENT_ID,
DEPARTMENT_NAME
FROM DEPARTMENTS D
WHERE EXISTS
(
SELECT 1
FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID)

3. Write a question to seek out the departments which shouldn’t have workers in any respect?

SELECT DEPARTMENT_ID,
DEPARTMENT_NAME
FROM DEPARTMENTS D
WHERE NOT EXISTS
(
SELECT 1
FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID)

Leave a Reply