/ DATABASE EXPERT
πŸ—„οΈ
DBA-DEV TOOLKIT
πŸ‘¨β€πŸ’» Vinayak Vishweshwara Dabgar
βš™οΈ Oracle Apps DBA - Consultant
πŸ’» DBA - Developer
πŸ—οΈ Database Architect
πŸš€ Performance Tuning Expert
πŸ—ƒοΈ Database Expertise
🟒 Oracle Database
πŸ”΅ Microsoft SQL Server
🟠 MySQL
🐘 PostgreSQL
☁️ Amazon RDS
πŸ“Š Amazon Redshift
πŸ”— Professional Links
🌐 Professional Website πŸŽ“ Corporate Training LMS πŸ“§ vdabgar.in@gmail.com πŸ’Ό LinkedIn Profile
πŸ“ Office Address
🏠 No.20, SAI SADAN
🏒 Near LVS Apartments
πŸ›£οΈ TC Palya Main Road
πŸ“Œ Krishnarajapura
πŸŒ† Bangalore - 560036
πŸŒ† Karnataka, India
β¬… Back

Oracle DQL 04 - Subqueries 


/*===============================================================
SUBQUERIES

Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert

PART 04A – SUBQUERIES

Topics Covered
--------------
β€’ Subquery fundamentals
β€’ Single-row subqueries
β€’ Subqueries in WHERE clause
β€’ Subqueries with aggregates
β€’ Practical HR schema examples
===============================================================*/


/*---------------------------------------------------------------
SUBQUERY INTRODUCTION
---------------------------------------------------------------*/

/*
A subquery is a query inside another SQL statement.

Types of subqueries

Single-row subquery
Multi-row subquery
Correlated subquery
Scalar subquery
Inline view
*/



/*---------------------------------------------------------------
QUESTION 1
Employee with highest salary
---------------------------------------------------------------*/

SELECT
employee_id,
salary
FROM hr.employees
WHERE salary =
(
SELECT MAX(salary)
FROM hr.employees
);



/*---------------------------------------------------------------
QUESTION 2
Employee with lowest salary
---------------------------------------------------------------*/

SELECT
employee_id,
salary
FROM hr.employees
WHERE salary =
(
SELECT MIN(salary)
FROM hr.employees
);



/*---------------------------------------------------------------
QUESTION 3
Employees earning above company average
---------------------------------------------------------------*/

SELECT
employee_id,
salary
FROM hr.employees
WHERE salary >
(
SELECT AVG(salary)
FROM hr.employees
);



/*---------------------------------------------------------------
QUESTION 4
Employees earning below company average
---------------------------------------------------------------*/

SELECT
employee_id,
salary
FROM hr.employees
WHERE salary <
(
SELECT AVG(salary)
FROM hr.employees
);



/*---------------------------------------------------------------
QUESTION 5
Employees earning same salary as employee 100
---------------------------------------------------------------*/

SELECT
employee_id,
salary
FROM hr.employees
WHERE salary =
(
SELECT salary
FROM hr.employees
WHERE employee_id = 100
);



/*---------------------------------------------------------------
QUESTION 6
Employees in same department as employee 101
---------------------------------------------------------------*/

SELECT
employee_id,
department_id
FROM hr.employees
WHERE department_id =
(
SELECT department_id
FROM hr.employees
WHERE employee_id = 101
);



/*---------------------------------------------------------------
QUESTION 7
Employees hired after employee 110
---------------------------------------------------------------*/

SELECT
employee_id,
hire_date
FROM hr.employees
WHERE hire_date >
(
SELECT hire_date
FROM hr.employees
WHERE employee_id = 110
);



/*---------------------------------------------------------------
QUESTION 8
Employees working in IT department
---------------------------------------------------------------*/

SELECT
employee_id,
first_name
FROM hr.employees
WHERE department_id =
(
SELECT department_id
FROM hr.departments
WHERE department_name = 'IT'
);



/*---------------------------------------------------------------
QUESTION 9
Employees working in Sales department
---------------------------------------------------------------*/

SELECT
employee_id,
first_name
FROM hr.employees
WHERE department_id =
(
SELECT department_id
FROM hr.departments
WHERE department_name = 'Sales'
);



/*---------------------------------------------------------------
QUESTION 10
Employees in departments located in Seattle
---------------------------------------------------------------*/

SELECT
employee_id,
department_id
FROM hr.employees
WHERE department_id IN
(
SELECT department_id
FROM hr.departments
WHERE location_id =
(
SELECT location_id
FROM hr.locations
WHERE city = 'Seattle'
)
);



/*---------------------------------------------------------------
SUBQUERIES WITH AGGREGATES
---------------------------------------------------------------*/



/*---------------------------------------------------------------
QUESTION 11
Departments with average salary above 10000
---------------------------------------------------------------*/

SELECT
department_id
FROM hr.employees
GROUP BY department_id
HAVING AVG(salary) > 10000;



/*---------------------------------------------------------------
QUESTION 12
Employees earning above department average
---------------------------------------------------------------*/

SELECT
employee_id,
salary,
department_id
FROM hr.employees e
WHERE salary >
(
SELECT AVG(salary)
FROM hr.employees
WHERE department_id = e.department_id
);



/*---------------------------------------------------------------
QUESTION 13
Employees earning highest salary per department
---------------------------------------------------------------*/

SELECT
employee_id,
salary,
department_id
FROM hr.employees e
WHERE salary =
(
SELECT MAX(salary)
FROM hr.employees
WHERE department_id = e.department_id
);



/*---------------------------------------------------------------
QUESTION 14
Employees earning lowest salary per department
---------------------------------------------------------------*/

SELECT
employee_id,
salary,
department_id
FROM hr.employees e
WHERE salary =
(
SELECT MIN(salary)
FROM hr.employees
WHERE department_id = e.department_id
);



/*---------------------------------------------------------------
QUESTION 15
Departments with more than 5 employees
---------------------------------------------------------------*/

SELECT
department_id
FROM hr.employees
GROUP BY department_id
HAVING COUNT(*) > 5;



/*---------------------------------------------------------------
QUESTION 16
Employees working in departments with more than 5 employees
---------------------------------------------------------------*/

SELECT
employee_id,
department_id
FROM hr.employees
WHERE department_id IN
(
SELECT department_id
FROM hr.employees
GROUP BY department_id
HAVING COUNT(*) > 5
);



/*---------------------------------------------------------------
QUESTION 17
Employees earning more than department 50 average
---------------------------------------------------------------*/

SELECT
employee_id,
salary
FROM hr.employees
WHERE salary >
(
SELECT AVG(salary)
FROM hr.employees
WHERE department_id = 50
);



/*---------------------------------------------------------------
QUESTION 18
Employees hired before department average hire date
---------------------------------------------------------------*/

SELECT
employee_id,
hire_date,
department_id
FROM hr.employees e
WHERE hire_date <
(
SELECT AVG(hire_date)
FROM hr.employees
WHERE department_id = e.department_id
);



/*---------------------------------------------------------------
QUESTION 19
Employees with salary above department minimum
---------------------------------------------------------------*/

SELECT
employee_id,
salary,
department_id
FROM hr.employees e
WHERE salary >
(
SELECT MIN(salary)
FROM hr.employees
WHERE department_id = e.department_id
);



/*---------------------------------------------------------------
QUESTION 20
Employees with salary below department maximum
---------------------------------------------------------------*/

SELECT
employee_id,
salary,
department_id
FROM hr.employees e
WHERE salary <
(
SELECT MAX(salary)
FROM hr.employees
WHERE department_id = e.department_id
);



/*---------------------------------------------------------------
SUBQUERY PERFORMANCE NOTES
---------------------------------------------------------------*/

/*
Best Practices

1 Avoid unnecessary nested subqueries

2 Correlated subqueries can be expensive

3 Use joins when possible

4 Ensure subquery returns expected rows
*/


/*---------------------------------------------------------------
END OF PART 04A
---------------------------------------------------------------*/

/*===============================================================
PART 04B – ADVANCED SUBQUERIES

Continuation of PART 04A

Topics Covered
--------------
β€’ Multi-row subqueries
β€’ IN operator
β€’ ANY / ALL operators
β€’ EXISTS / NOT EXISTS
β€’ Correlated subqueries
β€’ Scalar subqueries
β€’ Inline views
===============================================================*/


/*---------------------------------------------------------------
MULTI-ROW SUBQUERIES
---------------------------------------------------------------*/

/*
Multi-row subqueries return multiple rows.
They are used with operators such as:

IN
ANY
ALL
EXISTS
*/



/*---------------------------------------------------------------
QUESTION 21
Employees working in departments located in Europe
---------------------------------------------------------------*/

SELECT
employee_id,
department_id
FROM hr.employees
WHERE department_id IN
(
SELECT department_id
FROM hr.departments
WHERE location_id IN
(
SELECT location_id
FROM hr.locations
WHERE country_id IN
(
SELECT country_id
FROM hr.countries
WHERE region_id =
(
SELECT region_id
FROM hr.regions
WHERE region_name = 'Europe'
)
)
)
);



/*---------------------------------------------------------------
QUESTION 22
Employees working in departments with high salaries
---------------------------------------------------------------*/

SELECT
employee_id,
salary
FROM hr.employees
WHERE department_id IN
(
SELECT department_id
FROM hr.employees
GROUP BY department_id
HAVING AVG(salary) > 10000
);



/*---------------------------------------------------------------
ANY OPERATOR
---------------------------------------------------------------*/

/*
ANY means the condition must be true
for at least one value returned.
*/



/*---------------------------------------------------------------
QUESTION 23
Employees earning more than ANY employee in department 60
---------------------------------------------------------------*/

SELECT
employee_id,
salary
FROM hr.employees
WHERE salary >
ANY
(
SELECT salary
FROM hr.employees
WHERE department_id = 60
);



/*---------------------------------------------------------------
QUESTION 24
Employees earning less than ANY salary in department 50
---------------------------------------------------------------*/

SELECT
employee_id,
salary
FROM hr.employees
WHERE salary <
ANY
(
SELECT salary
FROM hr.employees
WHERE department_id = 50
);



/*---------------------------------------------------------------
ALL OPERATOR
---------------------------------------------------------------*/

/*
ALL means condition must be true
for every value returned.
*/



/*---------------------------------------------------------------
QUESTION 25
Employees earning more than ALL employees in department 60
---------------------------------------------------------------*/

SELECT
employee_id,
salary
FROM hr.employees
WHERE salary >
ALL
(
SELECT salary
FROM hr.employees
WHERE department_id = 60
);



/*---------------------------------------------------------------
QUESTION 26
Employees earning less than ALL employees in department 100
---------------------------------------------------------------*/

SELECT
employee_id,
salary
FROM hr.employees
WHERE salary <
ALL
(
SELECT salary
FROM hr.employees
WHERE department_id = 100
);



/*---------------------------------------------------------------
EXISTS OPERATOR
---------------------------------------------------------------*/

/*
EXISTS returns TRUE if subquery returns rows.
*/



/*---------------------------------------------------------------
QUESTION 27
Departments that contain employees
---------------------------------------------------------------*/

SELECT
department_name
FROM hr.departments d
WHERE EXISTS
(
SELECT 1
FROM hr.employees e
WHERE e.department_id = d.department_id
);



/*---------------------------------------------------------------
QUESTION 28
Departments without employees
---------------------------------------------------------------*/

SELECT
department_name
FROM hr.departments d
WHERE NOT EXISTS
(
SELECT 1
FROM hr.employees e
WHERE e.department_id = d.department_id
);



/*---------------------------------------------------------------
CORRELATED SUBQUERIES
---------------------------------------------------------------*/

/*
A correlated subquery executes once for each row
returned by the outer query.
*/



/*---------------------------------------------------------------
QUESTION 29
Employees earning above department average
---------------------------------------------------------------*/

SELECT
employee_id,
salary,
department_id
FROM hr.employees e
WHERE salary >
(
SELECT AVG(salary)
FROM hr.employees
WHERE department_id = e.department_id
);



/*---------------------------------------------------------------
QUESTION 30
Employees hired before department average hire date
---------------------------------------------------------------*/

SELECT
employee_id,
hire_date,
department_id
FROM hr.employees e
WHERE hire_date <
(
SELECT AVG(hire_date)
FROM hr.employees
WHERE department_id = e.department_id
);



/*---------------------------------------------------------------
QUESTION 31
Employees with maximum salary per department
---------------------------------------------------------------*/

SELECT
employee_id,
salary,
department_id
FROM hr.employees e
WHERE salary =
(
SELECT MAX(salary)
FROM hr.employees
WHERE department_id = e.department_id
);



/*---------------------------------------------------------------
QUESTION 32
Employees with minimum salary per department
---------------------------------------------------------------*/

SELECT
employee_id,
salary,
department_id
FROM hr.employees e
WHERE salary =
(
SELECT MIN(salary)
FROM hr.employees
WHERE department_id = e.department_id
);



/*---------------------------------------------------------------
SCALAR SUBQUERIES
---------------------------------------------------------------*/

/*
A scalar subquery returns a single value
that can be used in SELECT clause.
*/



/*---------------------------------------------------------------
QUESTION 33
Display employee salary with company average salary
---------------------------------------------------------------*/

SELECT
employee_id,
salary,
(
SELECT AVG(salary)
FROM hr.employees
) company_average
FROM hr.employees;



/*---------------------------------------------------------------
QUESTION 34
Display employee salary with department average
---------------------------------------------------------------*/

SELECT
employee_id,
salary,
department_id,
(
SELECT AVG(salary)
FROM hr.employees
WHERE department_id = e.department_id
) dept_average
FROM hr.employees e;



/*---------------------------------------------------------------
INLINE VIEWS (SUBQUERY IN FROM)
---------------------------------------------------------------*/

/*
Inline views act like temporary tables.
*/



/*---------------------------------------------------------------
QUESTION 35
Top 5 highest paid employees
---------------------------------------------------------------*/

SELECT *
FROM
(
SELECT
employee_id,
salary
FROM hr.employees
ORDER BY salary DESC
)
WHERE ROWNUM <= 5;



/*---------------------------------------------------------------
QUESTION 36
Department salary totals
---------------------------------------------------------------*/

SELECT
department_id,
total_salary
FROM
(
SELECT
department_id,
SUM(salary) total_salary
FROM hr.employees
GROUP BY department_id
);



/*---------------------------------------------------------------
QUESTION 37
Departments with highest salary totals
---------------------------------------------------------------*/

SELECT *
FROM
(
SELECT
department_id,
SUM(salary) total_salary
FROM hr.employees
GROUP BY department_id
ORDER BY total_salary DESC
)
WHERE ROWNUM <= 3;



/*---------------------------------------------------------------
QUESTION 38
Employees with department salary rank
---------------------------------------------------------------*/

SELECT *
FROM
(
SELECT
employee_id,
department_id,
salary,
ROW_NUMBER() OVER
(
PARTITION BY department_id
ORDER BY salary DESC
) salary_rank
FROM hr.employees
)
WHERE salary_rank = 1;



/*---------------------------------------------------------------
SUBQUERY PERFORMANCE NOTES
---------------------------------------------------------------*/

/*
Optimization Tips

1 Prefer EXISTS for large datasets
2 Avoid deeply nested subqueries
3 Use joins where appropriate
4 Index columns used in subqueries
5 Use inline views for clarity
*/


/*---------------------------------------------------------------
END OF PART 04

Disclaimer and Limitation of Liability

The author provides all scripts, queries, and related materials β€œas is”, without any express 
or implied warranties, including but not limited to warranties of accuracy, completeness, 
reliability, merchantability, or fitness for a particular purpose.

By using, executing, or implementing any part of this material, the user acknowledges 
and agrees that they do so at their own risk. The author shall not be held liable for any 
direct, indirect, incidental, consequential, special, or exemplary damages, including but 
not limited to loss of data, loss of profits, system failures, security breaches, or any 
other damages or losses arising from the use or misuse of these materials.

Users are strongly advised to thoroughly review, validate, and test all scripts, queries, 
and configurations in a controlled, non-production (test) environment prior to deploying 
them in any live or production systems.

It is the sole responsibility of the user to ensure that the use of these materials 
complies with all applicable local, national, and international laws, regulations, 
and organizational policies.

By proceeding to use these materials, the user agrees to indemnify and hold harmless 
the author from any claims, damages, liabilities, or expenses arising from their use.
---------------------------------------------------------------*/