SQL - Sub Queries

Step-by-Step Guide: Table Creation, Data Insertion, and Subquery Examples with Explanations

1. Table Creation

First, we create two tables: departments and employees.

-- Create the departments table CREATE TABLE departments ( department_id NUMBER PRIMARY KEY, department_name VARCHAR2(50), location_id NUMBER ); 

-- Create the employees table CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, employee_name VARCHAR2(50), department_id NUMBER, salary NUMBER, CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id) ); 

Explanation:

• departments table contains details about each department like department_id, department_name, and location_id.
• employees table contains details about each employee like employee_id, employee_name, department_id (foreign key linked to the departments table), and salary.

2. Data Insertion

Now, we insert some sample data into both the departments
and employees tables.

-- Insert data into the departments table INSERT INTO departments (department_id, department_name, location_id) VALUES (10, 'HR', 100); INSERT INTO departments (department_id, department_name, location_id) VALUES (20, 'IT', 101); INSERT INTO departments (department_id, department_name, location_id) VALUES (30, 'Sales', 102); 

-- Insert data into the employees table INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES (1, 'Alice', 10, 5000); INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES (2, 'Bob', 20, 6000); INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES (3, 'Charlie', 30, 7000); 

Explanation:

• We insert three departments: HR, IT, and Sales, each with a unique location_id.
• We insert three employees: Alice, Bob, and Charlie, who are assigned to different departments and have varying salaries.

Subquery Operations with Detailed Explanations

1. Single-Row Subquery

 -- Get employees from the IT department (only one row returned by subquery) SELECT employee_name FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT'); 

Explanation:

• This subquery returns a single row (the department_id of the IT department).
• The outer query uses this result to retrieve the employee(s) from the IT department.

2. Multiple-Row Subquery

-- Get employees from departments located in location_id = 101 SELECT employee_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 101); 

Explanation:

• The subquery returns multiple rows (department IDs located in location_id = 101).
• The outer query retrieves the employees who belong to any of the departments returned by the subquery.

3. Correlated Subquery

-- Get employees whose salary is greater than  -- the average salary in their department SELECT employee_name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id); 

Explanation:

• A correlated subquery runs once for each row processed by the outer query.
• Here, it calculates the average salary for the department of each employee, and then the outer query checks if the employee’s salary is greater than the average.

4. EXISTS Subquery

-- Get employees working in departments located in location_id = 102 SELECT employee_name FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE e.department_id = d.department_id AND d.location_id = 102); 

Explanation:

• EXISTS checks if the subquery returns at least one row.
• If an employee’s department exists in location_id = 102, the outer query includes that employee.

5. NOT EXISTS Subquery

-- Get employees NOT working in departments located in location_id = 102 SELECT employee_name FROM employees e WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE e.department_id = d.department_id AND d.location_id = 102); 

Explanation:

• Similar to EXISTS, but NOT EXISTS filters out employees whose departments are in location_id = 102.
• The outer query returns employees in departments not located at location_id = 102.

6. Scalar Subquery

-- Get employee names and their department names SELECT employee_name, (SELECT department_name FROM departments WHERE department_id = e.department_id) AS dept_name FROM employees e; 

Explanation:

• A scalar subquery returns a single value.
• For each employee, the subquery retrieves the department_name, and the outer query displays the employee’s name along with their department name.

7. Inline View

-- Get the average salary for each department SELECT dept_name, avg_salary FROM (SELECT department_name AS dept_name, AVG(salary) AS avg_salary FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY department_name); 

Explanation:

• An inline view is a subquery in the FROM clause that behaves like a temporary table.
• This subquery calculates the average salary for each department and then the outer query retrieves the results for each department.

8. Subquery with HAVING Clause

-- Get departments where the average salary is greater than  -- the average salary of department 10 SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > (SELECT AVG(salary) FROM employees WHERE department_id = 10); 

Explanation:

• The HAVING clause filters groups after they have been aggregated.
• This query returns departments whose average salary is higher than the average salary of department 10.

Conclusion:

1. Table Creation: Created departments and employees tables.
2. Data Insertion: Populated both tables with sample data.
3. Subqueries: Demonstrated different types of subqueries including single-row, multiple-row, correlated, EXISTS, scalar subqueries, inline views, and HAVING with subqueries.

Each subquery serves a unique purpose, allowing you to retrieve data dynamically and efficiently, making SQL a powerful tool for complex data manipulation and analysis.