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.