Subqueries and nested queries in DBMS Theory - Mini Project: Build & Apply

Subqueries and Nested Queries

📖 Scenario: You are working with a database of a small bookstore. The database has two tables: Books and Sales. The Books table contains information about each book, including its BookID, Title, and Price. The Sales table records each sale with SaleID, BookID, and Quantity sold.Your task is to use subqueries to find specific information about the books and their sales.

🎯 Goal: Build SQL queries using subqueries and nested queries to find books with sales above average and to identify the most expensive book sold.

📋 What You'll Learn

Create a table called Books with columns BookID, Title, and Price and insert given data

Create a table called Sales with columns SaleID, BookID, and Quantity and insert given data

Write a subquery to find the average quantity sold across all sales

Write a nested query to find the titles of books with sales quantity greater than the average

Write a subquery to find the maximum price of books sold

Write a nested query to find the title(s) of the most expensive book(s) sold

💡 Why This Matters

🌍 Real World

Subqueries and nested queries are used in real-world databases to filter and analyze data based on complex conditions, such as finding top-selling products or customers.

💼 Career

Understanding subqueries is essential for database administrators, data analysts, and developers who work with SQL to extract meaningful insights from data.

1

Create the Books and Sales tables with data

Write SQL statements to create a table called Books with columns BookID (integer), Title (text), and Price (decimal). Then insert these exact rows into Books: (1, 'The Alchemist', 15.99), (2, '1984', 12.50), (3, 'Clean Code', 33.00). Next, create a table called Sales with columns SaleID (integer), BookID (integer), and Quantity (integer). Insert these exact rows into Sales: (101, 1, 5), (102, 2, 8), (103, 3, 3).

DBMS Theory

-- Create Books table and insert data
-- Create Sales table and insert data
-- Your code here

Hint

Use CREATE TABLE to define tables and INSERT INTO to add rows with exact values.

2

Calculate the average quantity sold

Write a SQL query that creates a variable or alias called avg_quantity which holds the average Quantity sold from the Sales table using a subquery.

DBMS Theory

CREATE TABLE Books (BookID INTEGER, Title TEXT, Price DECIMAL);
INSERT INTO Books VALUES (1, 'The Alchemist', 15.99), (2, '1984', 12.50), (3, 'Clean Code', 33.00);
CREATE TABLE Sales (SaleID INTEGER, BookID INTEGER, Quantity INTEGER);
INSERT INTO Sales VALUES (101, 1, 5), (102, 2, 8), (103, 3, 3);

-- Calculate average quantity sold
SELECT AVG(Quantity) AS avg_quantity FROM Sales;
-- Your code here

Hint

Use AVG() function to calculate average and alias it as avg_quantity.

3

Find titles of books with sales quantity above average

Write a SQL query using a nested subquery to select the Title from Books where the total Quantity sold (sum from Sales) for that book is greater than the average quantity sold. Use GROUP BY on BookID in the subquery and compare with the average quantity using a subquery.

DBMS Theory

CREATE TABLE Books (BookID INTEGER, Title TEXT, Price DECIMAL);
INSERT INTO Books VALUES (1, 'The Alchemist', 15.99), (2, '1984', 12.50), (3, 'Clean Code', 33.00);
CREATE TABLE Sales (SaleID INTEGER, BookID INTEGER, Quantity INTEGER);
INSERT INTO Sales VALUES (101, 1, 5), (102, 2, 8), (103, 3, 3);

-- Select titles with total sales quantity greater than average
SELECT Title FROM Books WHERE BookID IN (
    SELECT BookID FROM Sales GROUP BY BookID HAVING SUM(Quantity) > (
        SELECT AVG(Quantity) FROM Sales
    )
);
-- Your code here

Hint

Use a nested subquery with GROUP BY and HAVING to compare sums with average.

4

Find the title(s) of the most expensive book(s) sold

Write a SQL query to select the Title from Books where the Price equals the maximum price of books that have been sold. Use a subquery to find the maximum Price from Books where BookID exists in Sales.

DBMS Theory

CREATE TABLE Books (BookID INTEGER, Title TEXT, Price DECIMAL);
INSERT INTO Books VALUES (1, 'The Alchemist', 15.99), (2, '1984', 12.50), (3, 'Clean Code', 33.00);
CREATE TABLE Sales (SaleID INTEGER, BookID INTEGER, Quantity INTEGER);
INSERT INTO Sales VALUES (101, 1, 5), (102, 2, 8), (103, 3, 3);

-- Select title(s) of the most expensive book(s) sold
SELECT Title FROM Books WHERE Price = (
    SELECT MAX(Price) FROM Books WHERE BookID IN (SELECT BookID FROM Sales)
);
-- Your code here

Hint

Use a subquery to find the maximum price of sold books and compare it in the outer query.

Practice

1. What is a subquery in a database query?

easy

A. A query nested inside another query

B. A query that updates data

C. A query that deletes tables

D. A query that creates indexes

Solution

1. Step 1: Understand the definition of subquery

   A subquery is a query written inside another query to help filter or calculate data.

2. Step 2: Identify the correct description

   Among the options, only "A query nested inside another query" matches the definition of a subquery.

3. Final Answer:

   A query nested inside another query -> Option A

4. Quick Check:

   Subquery = Nested query [OK]

Hint: Subquery means query inside another query [OK]

Common Mistakes:

• Confusing subquery with update or delete operations
• Thinking subquery creates indexes
• Mixing subquery with table creation

2. Which of the following is the correct syntax to use a subquery in the WHERE clause?

easy

A. SELECT * FROM table WHERE column = (SELECT value FROM other_table);

B. SELECT * FROM table WHERE SELECT value FROM other_table = column;

C. SELECT * FROM table WHERE column IN SELECT value FROM other_table;

D. SELECT * FROM table WHERE column = SELECT value FROM other_table;

Solution

1. Step 1: Review correct subquery syntax in WHERE clause

   The subquery must be enclosed in parentheses and used as a value to compare with a column.

2. Step 2: Check each option

   SELECT * FROM table WHERE column = (SELECT value FROM other_table); correctly uses parentheses around the subquery. Options A, B, and D have syntax errors or missing parentheses.

3. Final Answer:

   SELECT * FROM table WHERE column = (SELECT value FROM other_table); -> Option A

4. Quick Check:

   Subquery in WHERE uses parentheses [OK]

Hint: Subquery in WHERE must be inside parentheses [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Placing subquery before column
• Using IN without parentheses

3. Consider the tables:
Employees(emp_id, name, dept_id)
Departments(dept_id, dept_name)
What does this query return?

SELECT name FROM Employees WHERE dept_id = (SELECT dept_id FROM Departments WHERE dept_name = 'Sales');

medium

A. Names of employees who do not work in Sales

B. Names of all employees regardless of department

C. Names of employees who work in the Sales department

D. Department names of all employees

Solution

1. Step 1: Understand the subquery

   The subquery finds the dept_id for the department named 'Sales'.

2. Step 2: Apply the subquery result in the main query

   The main query selects employee names whose dept_id matches the Sales dept_id.

3. Final Answer:

   Names of employees who work in the Sales department -> Option C

4. Quick Check:

   Subquery filters Sales dept_id, main query filters employees [OK]

Hint: Subquery finds Sales dept_id, main query filters employees [OK]

Common Mistakes:

• Thinking it returns all employees
• Confusing employee names with department names
• Ignoring subquery filtering

4. Identify the error in this query:

SELECT name FROM Employees WHERE dept_id = SELECT dept_id FROM Departments WHERE dept_name = 'HR';

medium

A. Wrong table name used

B. Missing parentheses around the subquery

C. Using = instead of IN

D. Missing WHERE clause in subquery

Solution

1. Step 1: Check subquery syntax

   The subquery must be enclosed in parentheses when used in WHERE clause comparisons.

2. Step 2: Identify the error in the query

   The query misses parentheses around the subquery, causing a syntax error.

3. Final Answer:

   Missing parentheses around the subquery -> Option B

4. Quick Check:

   Subquery needs parentheses [OK]

Hint: Always put subqueries in parentheses [OK]

Common Mistakes:

• Omitting parentheses
• Confusing = with IN unnecessarily
• Incorrect table names

5. You want to find all employees who earn more than the average salary in their department. Which query correctly uses a subquery to achieve this?

hard

A. SELECT emp_name FROM Employees WHERE salary > (SELECT MAX(salary) FROM Employees);

B. SELECT emp_name FROM Employees WHERE salary > AVG(salary);

C. SELECT emp_name FROM Employees WHERE salary > (SELECT salary FROM Employees);

D. SELECT emp_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = Employees.dept_id);

Solution

1. Step 1: Understand the requirement

   We need to compare each employee's salary to the average salary of their own department.

2. Step 2: Analyze each option

   SELECT emp_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = Employees.dept_id); uses a correlated subquery that calculates the average salary for the employee's department. Options A, B, and C do not correctly calculate the average per department or use wrong aggregates.

3. Final Answer:

   SELECT emp_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = Employees.dept_id); -> Option D

4. Quick Check:

   Correlated subquery compares salary to dept average [OK]

Hint: Use correlated subquery with dept_id to get average salary [OK]

Common Mistakes:

• Using global average instead of per department
• Using MAX instead of AVG
• Not correlating subquery with outer query