Subqueries in FROM and SELECT Clauses

---

1. Aim

To understand and implement subqueries (nested queries) in the FROM clause and SELECT clause of SQL statements.

---

2. Theory

A subquery is a query within another SQL query. Depending on its placement, it can serve different purposes.

2.1 Subquery in the FROM Clause

• When a subquery appears in the FROM clause, it is treated as a temporary table (also called a derived table).

• Useful for performing complex calculations first, then querying the result.

2.2 Subquery in the SELECT Clause

• Returns a single scalar value for each row of the outer query.

• Useful for adding computed fields like counts, averages, or maximum values.

---

3. Table Schema Setup

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)
);

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);

---

4. Subqueries in the FROM Clause

Example 1: Authors whose average book price is above 15

SELECT AuthorID, AvgPrice
FROM (
    SELECT AuthorID, AVG(Price) AS AvgPrice
    FROM Books
    GROUP BY AuthorID
) AS TempTable
WHERE AvgPrice > 15;

Output:

AuthorID	AvgPrice
1	15.00
3	25.00

---

Example 2: Maximum book price per author

SELECT A.AuthorID, A.Name, T.MaxPrice
FROM Authors A
JOIN (
    SELECT AuthorID, MAX(Price) AS MaxPrice
    FROM Books
    GROUP BY AuthorID
) AS T
ON A.AuthorID = T.AuthorID;

Output:

AuthorID	Name	MaxPrice
1	Author One	20.00
2	Author Two	15.00
3	Author Three	25.00

---

Example 3: Count of books priced above the average price

SELECT COUNT(*) AS AboveAverageBooks
FROM (
    SELECT *
    FROM Books
    WHERE Price > (SELECT AVG(Price) FROM Books)
) AS TempTable;

---

5. Subqueries in the SELECT Clause

Example 1: Show each author with the number of books they have written

SELECT 
    Name,
    (SELECT COUNT(*) 
     FROM Books B 
     WHERE B.AuthorID = A.AuthorID) AS TotalBooks
FROM Authors A;

Output:

Name	TotalBooks
Author One	2
Author Two	1
Author Three	1

---

Example 2: Display each book with the average price of all books

SELECT 
    Title,
    Price,
    (SELECT AVG(Price) FROM Books) AS AverageBookPrice
FROM Books;

---

Example 3: Show each book with the highest book price

SELECT 
    Title,
    Price,
    (SELECT MAX(Price) FROM Books) AS HighestBookPrice
FROM Books;

---

6. Combined Example (FROM + SELECT)

SELECT 
    A.Name,
    T.TotalBooks,
    (SELECT AVG(Price) FROM Books) AS OverallAveragePrice
FROM Authors A
JOIN (
    SELECT AuthorID, COUNT(*) AS TotalBooks
    FROM Books
    GROUP BY AuthorID
) AS T
ON A.AuthorID = T.AuthorID;

---

7. Results

• Successfully executed subqueries in FROM and SELECT clauses.

• Understood how derived tables and scalar subqueries enhance SQL query capability.

• Demonstrated complex data processing using nested queries.

---

8. Conclusion

Subqueries in the FROM and SELECT clauses are powerful SQL tools that allow advanced computations, filtering, and data analysis. They enable breaking down complex logic into smaller, manageable parts within a single SQL statement.