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

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

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

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

Hint

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

Practice

1. What does a scalar subquery in the SELECT clause return?

easy

A. Only column names

B. Multiple rows and columns

C. A single value (one row, one column)

D. Only table names

Solution

1. Step 1: Understand scalar subquery definition

   A scalar subquery returns exactly one value, meaning one row and one column.

2. Step 2: Compare with other subquery types

   Unlike table subqueries, scalar subqueries cannot return multiple rows or columns.

3. Final Answer:

   A single value (one row, one column) -> Option C

4. Quick Check:

   Scalar subquery = single value [OK]

Hint: Scalar subquery returns one value only, not a table [OK]

Common Mistakes:

• Thinking scalar subquery returns multiple rows
• Confusing scalar subquery with table subquery
• Assuming scalar subquery returns column names only

2. Which of the following is the correct syntax for using a scalar subquery in the SELECT clause?

easy

A. SELECT name, SELECT MAX(score) FROM scores AS max_score FROM students;

B. SELECT name, (SELECT MAX(score) FROM scores) AS max_score FROM students;

C. SELECT name, MAX(score) FROM scores AS max_score FROM students;

D. SELECT name, (MAX(score) FROM scores) AS max_score FROM students;

Solution

1. Step 1: Identify correct scalar subquery syntax

   The scalar subquery must be enclosed in parentheses and used inside the SELECT clause.

2. Step 2: Check each option

   SELECT name, (SELECT MAX(score) FROM scores) AS max_score FROM students; correctly uses parentheses around the subquery. Others miss parentheses or have wrong placement.

3. Final Answer:

   SELECT name, (SELECT MAX(score) FROM scores) AS max_score FROM students; -> Option B

4. Quick Check:

   Scalar subquery syntax = parentheses [OK]

Hint: Always put scalar subquery inside parentheses in SELECT [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Placing SELECT keyword incorrectly
• Using aggregate functions without subquery

3. Given tables employees(id, name, dept_id) and departments(id, dept_name), what is the output of this query?

SELECT name, (SELECT dept_name FROM departments WHERE id = employees.dept_id) AS department FROM employees ORDER BY name;

medium

A. Syntax error due to subquery

B. [{"name": "Alice", "department": null}, {"name": "Bob", "department": null}]

C. [{"name": "Alice", "department": "IT"}, {"name": "Bob", "department": "HR"}]

D. [{"name": "Alice", "department": "HR"}, {"name": "Bob", "department": "IT"}]

Solution

1. Step 1: Understand query logic

   For each employee, the scalar subquery fetches the department name matching their dept_id.

2. Step 2: Match employees to departments

   Alice's dept_id matches HR, Bob's matches IT, so the output shows correct department names.

3. Final Answer:

   [{"name": "Alice", "department": "HR"}, {"name": "Bob", "department": "IT"}] -> Option D

4. Quick Check:

   Scalar subquery returns matching department name per employee [OK]

Hint: Scalar subquery returns one value per row, matching join logic [OK]

Common Mistakes:

• Assuming subquery returns multiple rows causing error
• Mixing up department names for employees
• Expecting nulls when matching keys exist

4. Identify the error in this query:

SELECT name, (SELECT dept_name FROM departments WHERE id = employees.dept_id) AS department FROM employees WHERE (SELECT COUNT(*) FROM departments) > 0;

medium

A. Scalar subquery in WHERE is valid but inefficient

B. Scalar subquery in WHERE returns multiple rows

C. Missing alias for subquery in SELECT

D. Subquery in WHERE must return a single value

Solution

1. Step 1: Analyze subquery in WHERE clause

   The subquery in WHERE returns COUNT(*), which is a single value, so it's valid.

2. Step 2: Consider efficiency and logic

   Using a scalar subquery in WHERE like this works but is inefficient; better to check existence differently.

3. Final Answer:

   Scalar subquery in WHERE is valid but inefficient -> Option A

4. Quick Check:

   Scalar subquery in WHERE can be valid but watch efficiency [OK]

Hint: Scalar subquery in WHERE must return one value; check efficiency [OK]

Common Mistakes:

• Thinking scalar subquery in WHERE always causes error
• Confusing alias requirement in SELECT with WHERE
• Assuming subquery returns multiple rows here

5. You want to list all products with their category name, but some products have no category assigned (category_id is NULL). Which query correctly uses a scalar subquery in SELECT to show category names or 'Uncategorized' if none?

Options:
A) SELECT product_name, IFNULL((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products WHERE category_id IS NOT NULL;
B) SELECT product_name, (SELECT category_name FROM categories WHERE id = products.category_id) OR 'Uncategorized' AS category FROM products;
C) SELECT product_name, (SELECT category_name FROM categories WHERE id = products.category_id) AS category FROM products WHERE category_id IS NOT NULL;
D) SELECT product_name, COALESCE((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products;

hard

A. SELECT product_name, COALESCE((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products;

B. SELECT product_name, (SELECT category_name FROM categories WHERE id = products.category_id) OR 'Uncategorized' AS category FROM products;

C. SELECT product_name, (SELECT category_name FROM categories WHERE id = products.category_id) AS category FROM products WHERE category_id IS NOT NULL;

D. SELECT product_name, IFNULL((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products WHERE category_id IS NOT NULL;

Solution

1. Step 1: Handle NULL category_id with scalar subquery

   Use COALESCE to replace NULL result from subquery with 'Uncategorized'.

2. Step 2: Check each option's correctness

   The query with COALESCE((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products; correctly uses COALESCE and includes all products. The query with (SELECT category_name FROM categories WHERE id = products.category_id) OR 'Uncategorized' uses invalid OR syntax. The queries with WHERE category_id IS NOT NULL exclude products with NULL category_id.

3. Final Answer:

   SELECT product_name, COALESCE((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products; -> Option A

4. Quick Check:

   Use COALESCE with scalar subquery for NULL handling [OK]

Hint: Use COALESCE to handle NULL from scalar subquery [OK]

Common Mistakes:

• Using OR instead of COALESCE or IFNULL
• Filtering out NULL category_id rows
• Not handling NULL results from subquery