Oracle SQL Tutorial Lesson 7

 



Oracle SQL Basic Day 7


Today's lesson we will understand sub queries in oracle SQL.

Oracle SQL is a powerful tool for querying relational databases, and subqueries are among the most essential features it offers. A subquery, also known as an inner query or nested query, is a query embedded within another SQL query. Subqueries are often used to perform intermediate processing that a single query cannot handle directly.

In this article, we'll explore how to use subqueries in Oracle SQL using the HR schema, a sample schema provided by Oracle that includes tables like EMPLOYEES, DEPARTMENTS, JOBS, and LOCATIONS. We’ll cover the fundamentals of subqueries, provide real-world examples, and explain each in detail.


What Is a Subquery?
-------------------

A subquery is a SQL query nested inside a larger query. It can be placed in a SELECT, FROM, or WHERE clause. Subqueries are useful when:

- You want to filter results based on an aggregate value.
- You need to compare a value against multiple rows.
- You need intermediate results to calculate a final output.


Subqueries can be classified as:

- Single-row subqueries: Return one row.
- Multi-row subqueries: Return multiple rows.
- Correlated subqueries: Depend on the outer query for values.
- Uncorrelated subqueries: Independent of the outer query.


HR Schema Overview


The HR schema contains several tables relevant for Human Resources data. Here are the most commonly used:

- EMPLOYEES: Contains employee information like salary, job ID, department ID, etc.
- DEPARTMENTS: Lists departments and their locations.
- JOBS: Provides job titles and their minimum and maximum salaries.
- LOCATIONS: Contains location details tied to departments.


Find Employees Earning More Than the Average Salary


Objective:
List the names and salaries of employees whose salaries are above the average salary of all employees.


SQL Query:
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);





Explanation:
- The subquery calculates the average salary of all employees.
- The outer query selects employees whose salary exceeds this average.
- Useful for identifying top earners or potential promotions.


List Departments That Have No Employees



Objective:
Identify departments in the organization that do not currently have any employees assigned to them.

SQL Query:
SELECT department_name
FROM departments
WHERE department_id NOT IN (
    SELECT DISTINCT department_id FROM employees WHERE department_id IS NOT NULL
);




Explanation:
- The subquery returns all department IDs assigned to employees.
- The outer query finds departments not in that list.
- Helps HR identify unstaffed or inactive departments.


Find Employees Who Work in the Same Department as 'Lex De Haan'



SQL Query:
SELECT first_name, last_name
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
    WHERE first_name = 'Lex' AND last_name = 'De Haan'
);





Explanation:
- The subquery retrieves Lex De Haan’s department ID.
- The outer query returns all employees in the same department.
- Useful for identifying team members or direct colleagues.


Find Employees With the Same Job as the Highest Paid Employee



SQL Query:
SELECT first_name, last_name, job_id
FROM employees
WHERE job_id = (
    SELECT job_id FROM employees
    WHERE salary = (SELECT MAX(salary) FROM employees)
);




Explanation:
- The innermost subquery gets the highest salary.
- The middle query gets the job ID with that salary.
- The outer query lists employees who have the same job.


 List Employees Earning More Than the Highest Paid Employee in Department 30

SQL Query:
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (
    SELECT MAX(salary)
    FROM employees
    WHERE department_id = 30
);




Explanation:
- The subquery finds the max salary in department 30.
- The outer query selects employees who earn more.
- Useful for salary comparisons across department

List Employees Who Are Managers



SQL Query:
SELECT first_name, last_name
FROM employees
WHERE employee_id IN (
    SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL
);




Explanation:
- The subquery returns all unique manager IDs.
- The outer query gets names of those managers.
- Helps in generating a list of current managerial staff.

Get Department Names Where the Maximum Salary Is Greater Than 10000



SQL Query:
SELECT department_name
FROM departments
WHERE department_id IN (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING MAX(salary) > 10000
);





Explanation:
- The subquery finds departments where max salary > 10000.
- The outer query returns their names.
- Helps identify high-paying departments.


 Find Employees Whose Commission Is Higher Than the Average Commission


SQL Query:
SELECT first_name, last_name, commission_pct
FROM employees
WHERE commission_pct > (
    SELECT AVG(commission_pct)
    FROM employees
    WHERE commission_pct IS NOT NULL
);


Explanation:
- Subquery calculates average commission.
- Outer query finds employees earning above this.
- Useful in sales performance reviews.


 List Employees Who Have the Same Manager as 'David Austin'
---------------------------------------------------------------------


SQL Query:
SELECT first_name, last_name
FROM employees
WHERE manager_id = (
    SELECT manager_id
    FROM employees
    WHERE first_name = 'David' AND last_name = 'Austin'
);


Explanation:
- Subquery finds David Austin’s manager.
- Outer query finds others reporting to the same manager.
- Useful for understanding reporting lines or teams.


Find Jobs Where Minimum Salary Is Below the Average Employee Salary
-------------------------------------------------------------------------------


SQL Query:
SELECT job_title
FROM jobs
WHERE min_salary < (
    SELECT AVG(salary)
    FROM employees
);


Explanation:
- Subquery calculates average employee salary.
- Outer query finds jobs with a lower min salary.
- Useful for identifying lower-paying job categories.


Best Practices for Using Subqueries
-----------------------------------

- Optimize for performance: Subqueries can be slow. Consider joins or CTEs.
- Understand return types: Use = for single-row, IN for multi-row results.
- Avoid NULL pitfalls: Use NOT EXISTS instead of NOT IN when NULLs are involved.




Below are some more oracle SQL queries for practice purpose.
SELECT first_name, last_name, salary, manager_id
FROM employees e
WHERE salary > (
    SELECT salary
    FROM employees
    WHERE employee_id = e.manager_id
);

SELECT first_name, last_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id = (
        SELECT location_id
        FROM departments
        WHERE department_id = 90
    )
);


SELECT department_name
FROM departments
WHERE department_id IN (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) < 3
);


SELECT first_name, last_name, job_id
FROM employees
WHERE job_id = (
    SELECT job_id
    FROM employees
    WHERE first_name = 'Nancy' AND last_name = 'Greenberg'
);


SELECT first_name, last_name, salary, job_id
FROM employees e
WHERE salary BETWEEN (
    SELECT min_salary FROM jobs WHERE job_id = e.job_id
) AND (
    SELECT max_salary FROM jobs WHERE job_id = e.job_id
);


SELECT job_title
FROM jobs
WHERE job_id NOT IN (
    SELECT DISTINCT job_id FROM employees
);






Conclusion
----------

Subqueries are indispensable in Oracle SQL. They allow for powerful filtering, transformation, and comparative analysis. Using the HR schema, we demonstrated how subqueries can help HR teams with insights into salaries, departments, and employee structure.

Next Steps:
- Modify these queries using correlated subqueries.
- Practice rewriting some using joins.
- Try creating subqueries in the FROM clause (inline views).
Tags

Post a Comment

0 Comments

Post a Comment