Nested Queries in SQL

 

Nested Queries (Subqueries) in SQL

---

1. Aim

To understand and implement nested queries (subqueries) in SQL for retrieving data based on the results of other queries.

---

2. Theory

A nested query, also known as a subquery, is a query embedded inside another SQL query. Subqueries help build complex logic by using the output of one query as the input to another.

2.1 Types of Subqueries

1. Subquery in WHERE clause – commonly used for filtering.

2. Subquery in FROM clause – used as a derived table.

3. Subquery in SELECT clause – used for scalar values.

4. Subquery in HAVING clause – used with grouped data.

2.2 Characteristics of Subqueries

• A subquery is executed first; its result is passed to the outer query.

• Subqueries can return single values, multiple values, or entire tables.

• They are used with operators such as IN, NOT IN, ANY, ALL, and comparison operators.

---

3. Problem Statement

Given two tables: Authors and Books, perform different nested queries to retrieve information based on prices, averages, author contributions, and other criteria.

---

4. Table Schema

4.1 Create Tables

CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    AuthorID INT,
    Price DECIMAL(10, 2),
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

4.2 Insert Sample Data

INSERT INTO Authors (AuthorID, Name) VALUES
(1, 'Author One'),
(2, 'Author Two'),
(3, 'Author Three');

INSERT INTO Books (BookID, Title, AuthorID, Price) VALUES
(1, 'Book A', 1, 10.00),
(2, 'Book B', 2, 15.00),
(3, 'Book C', 1, 20.00),
(4, 'Book D', 3, 25.00);

---

5. Nested Query Examples

---

5.1 Find the Titles of Books Written by the Author Who Wrote the Book with the Highest Price

SELECT Title
FROM Books
WHERE AuthorID = (
    SELECT AuthorID
    FROM Books
    ORDER BY Price DESC
    LIMIT 1
);

Output:

Title
Book D

---

5.2 Find the Names of Authors Who Have Written at Least One Book Priced Above the Average Price of All Books

SELECT Name
FROM Authors
WHERE AuthorID IN (
    SELECT DISTINCT AuthorID
    FROM Books
    WHERE Price > (
        SELECT AVG(Price)
        FROM Books
    )
);

Output:

Name
Author One
Author Three

---

5.3 Find the Titles of Books Written by Authors Who Have Written More Than One Book

SELECT Title
FROM Books
WHERE AuthorID IN (
    SELECT AuthorID
    FROM Books
    GROUP BY AuthorID
    HAVING COUNT(BookID) > 1
);

Output:

Title
Book A
Book C

---

5.4 Find the Names of Authors Who Have Written Books With an Average Price Above $15

SELECT Name
FROM Authors
WHERE AuthorID IN (
    SELECT AuthorID
    FROM Books
    GROUP BY AuthorID
    HAVING AVG(Price) > 15
);

Output:

Name
Author Three

---

5.5 Find the Titles of Books Written by Authors Who Have Written at Least One Book Priced Below the Average Price

SELECT Title
FROM Books
WHERE AuthorID IN (
    SELECT DISTINCT AuthorID
    FROM Books
    WHERE Price < (
        SELECT AVG(Price)
        FROM Books
    )
);

---

5.6 Find the Titles of Books Written by the Most Prolific Author (Author with Maximum Books)

SELECT Title
FROM Books
WHERE AuthorID = (
    SELECT AuthorID
    FROM Books
    GROUP BY AuthorID
    ORDER BY COUNT(BookID) DESC
    LIMIT 1
);

Output:

Title
Book A
Book C

---

6. Results

• Successfully executed various nested queries involving subqueries within WHERE, HAVING, and ORDER BY clauses.

• Understood how subqueries help in complex filtering and grouping operations.

• Applied concepts to retrieve author/book information based on averages, maximum values, and counts.

---

7. Conclusion

Nested queries (subqueries) are powerful SQL tools for building complex logic and extracting meaningful insights. They allow one query to depend on another, enabling advanced filtering, comparison, and aggregation operations.