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

Need a 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

Need a 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

Need a 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

Need a hint?

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