Scalar subquery in SELECT in SQL - Mini Project: Build & Apply

Using Scalar Subquery in SELECT

📖 Scenario: You are managing a small bookstore database. You have two tables: books and sales. The books table stores book details, and the sales table records each sale with the book's ID and quantity sold.You want to create a report that shows each book's title along with the total quantity sold for that book.

🎯 Goal: Build a SQL query that lists each book's title and the total quantity sold using a scalar subquery inside the SELECT clause.

📋 What You'll Learn

Create a books table with columns book_id (integer) and title (text).

Create a sales table with columns sale_id (integer), book_id (integer), and quantity (integer).

Insert the exact data provided for both tables.

Write a SELECT query on books that uses a scalar subquery in the SELECT clause to find total quantity sold per book.

The scalar subquery must use SUM(quantity) from sales filtered by the current book_id.

💡 Why This Matters

🌍 Real World

Scalar subqueries in SELECT help you calculate related data for each row without complex joins, useful in reports and dashboards.

💼 Career

Understanding scalar subqueries is important for database querying roles, data analysis, and backend development where you need to fetch aggregated data efficiently.

1

Create the books table and insert data

Create a table called books with columns book_id (integer) and title (text). Insert these exact rows: (1, 'The Great Gatsby'), (2, '1984'), (3, 'To Kill a Mockingbird').

SQL

-- Create the books table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE books (book_id INTEGER, title TEXT); to create the table.

Use INSERT INTO books (book_id, title) VALUES (...), (...), (...); to add the rows.

2

Create the sales table and insert data

Create a table called sales with columns sale_id (integer), book_id (integer), and quantity (integer). Insert these exact rows: (1, 1, 3), (2, 2, 5), (3, 1, 2), (4, 3, 4), (5, 2, 1).

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird');
-- Create the sales table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE sales (sale_id INTEGER, book_id INTEGER, quantity INTEGER); to create the table.

Use INSERT INTO sales (sale_id, book_id, quantity) VALUES (...), (...), ...; to add the rows.

3

Write the SELECT query with scalar subquery

Write a SELECT query on the books table that shows each title and a scalar subquery that calculates the total quantity sold for that book. Use the scalar subquery in the SELECT clause with SUM(quantity) from sales where sales.book_id = books.book_id. Name the total quantity column total_sold.

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird');

CREATE TABLE sales (sale_id INTEGER, book_id INTEGER, quantity INTEGER);
INSERT INTO sales (sale_id, book_id, quantity) VALUES
(1, 1, 3),
(2, 2, 5),
(3, 1, 2),
(4, 3, 4),
(5, 2, 1);

-- Write the SELECT query with scalar subquery
-- Your code here

Need a hint?

Use a scalar subquery inside the SELECT clause like this:

(SELECT SUM(quantity) FROM sales WHERE sales.book_id = books.book_id) AS total_sold

This calculates total quantity sold for each book.

4

Complete the query with ordering

Add an ORDER BY clause to the query to sort the results by total_sold in descending order.

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird');

CREATE TABLE sales (sale_id INTEGER, book_id INTEGER, quantity INTEGER);
INSERT INTO sales (sale_id, book_id, quantity) VALUES
(1, 1, 3),
(2, 2, 5),
(3, 1, 2),
(4, 3, 4),
(5, 2, 1);

SELECT title,
       (SELECT SUM(quantity) FROM sales WHERE sales.book_id = books.book_id) AS total_sold
FROM books
-- Add ORDER BY clause here

Need a hint?

Use ORDER BY total_sold DESC to sort the results from highest to lowest total sold.