Examples of Nested Select Statement in PL/SQL

Examples 1

Let’s Say we have two tables, “employees” and “departments.” with the Following Data.

We will create the Employees and Departments table which defines the table structure with columns such as emp_id, emp_name, dept_Id and Salary of Employees TABLE and dept_Id, dept_name, and location of departments TABLE which specify the appropriate data types for each column to store information about employees and departments.

Query:

-- Create departments table

CREATE TABLE departments 
(
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50),
    location VARCHAR(50)
);

INSERT INTO departments (dept_id, dept_name, location) VALUES
(1, 'IT', 'New York'),
(2, 'HR', 'London'),
(3, 'Finance', 'New York');

-- Create employees table

CREATE TABLE employees 
(
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

INSERT INTO employees (emp_id, emp_name, dept_id, salary) VALUES
(101, 'John Doe', 1, 60000.00),
(102, 'Jane Smith', 1, 65000.00),
(103, 'Michael Johnson', 2, 55000.00),
(104, 'Emily Brown', 3, 70000.00),
(105, 'David Lee', 1, 62000.00);

After inserting data into the departments and employees table, The table looks:

In this example we want to retrieve the names of employees along with their corresponding department names. We can achieve this using a nested select statement as shown below:

Query:

SELECT emp_name, 
    (SELECT dept_name 
     FROM departments 
     WHERE departments.dept_id = employees.dept_id) AS dept_name
FROM 
    employees
WHERE 
    dept_id IN (
        SELECT dept_id
        FROM departments
        WHERE location = 'New York'
    );

Output:

Explanation: We can observe that we get the names of employees along with their corresponding department names.

Examples 2

Let’s Say we have two tables Students and Grades with the Following Data.

We can create the Students and Grades table using the following query which defines the table structure with columns such as student_id, and name of Students TABLE and student_id, course, and grade of Grades TABLE which specify the appropriate data types for each column to store information about Students and Grades.

Query:

-- Create students table

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);


INSERT INTO students (student_id, name) VALUES
(1, 'John Doe'),
(2, 'Jane Smith'),
(3, 'Michael Johnson'),
(4, 'Emily Brown');

-- Create grades table

CREATE TABLE grades (
    student_id INT,
    course VARCHAR(100),
    grade CHAR(1)
);


INSERT INTO grades (student_id, course, grade) VALUES
(1, 'Math', 'A'),
(1, 'Science', 'B'),
(2, 'Math', 'B'),
(2, 'Science', 'A'),
(3, 'Math', 'A'),
(3, 'Science', 'A'),
(4, 'Math', 'C'),
(4, 'Science', 'B');

After inserting data into the students table, The table looks:

After inserting data into the grades table, The table looks:

In this example We want to retrieve the names of students who have achieved an ‘A‘ grade in any course. We can achieve this using a nested select statement.

Query:

SELECT name
FROM students
WHERE student_id IN (
    SELECT student_id
    FROM grades
    WHERE grade = 'A'
);

Output:

Explanation: We can observe that the names of students who have achieved an ‘A‘ grade in any course.

PL/SQL is a Procedural Language/Structured Query Language and it enables users to write procedural logic directly within the database, including procedures, functions, triggers, and packages. In this article, we will understand Nested select statements in PL/SQL along with the examples and so on.