Scalar subqueries in PostgreSQL - Mini Project: Build & Apply

Using Scalar Subqueries in PostgreSQL

📖 Scenario: You are managing a small bookstore database. You want to find the price of each book along with the average price of all books in the store.

🎯 Goal: Build a SQL query that uses a scalar subquery to show each book's title, its price, and the average price of all books.

📋 What You'll Learn

Create a table called books with columns id (integer), title (text), and price (numeric).

Insert exactly three books with these values: (1, 'Book A', 10.00), (2, 'Book B', 15.00), (3, 'Book C', 20.00).

Write a scalar subquery to calculate the average price of all books.

Select the title, price, and the average price from the scalar subquery in the final query.

💡 Why This Matters

🌍 Real World

Scalar subqueries are useful when you want to include a single value calculated from the database inside another query, such as averages, counts, or maximum values.

💼 Career

Understanding scalar subqueries helps you write efficient and readable SQL queries for reports and data analysis tasks in many database-related jobs.

1

Create the books table and insert data

Create a table called books with columns id as integer, title as text, and price as numeric. Then insert these three rows exactly: (1, 'Book A', 10.00), (2, 'Book B', 15.00), and (3, 'Book C', 20.00).

PostgreSQL

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

Need a hint?

Use CREATE TABLE books (id INTEGER, title TEXT, price NUMERIC); and then INSERT INTO books (id, title, price) VALUES (...), (...), (...);

2

Write a scalar subquery to calculate average price

Create a scalar subquery that calculates the average price of all books from the books table. Assign this scalar subquery to a variable or prepare it to use in the next step.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
  (1, 'Book A', 10.00),
  (2, 'Book B', 15.00),
  (3, 'Book C', 20.00);

-- Write a scalar subquery to calculate average price
-- Your code here

Need a hint?

Use (SELECT AVG(price) FROM books) to get the average price as a scalar subquery.

3

Select book details with average price using scalar subquery

Write a SELECT query that retrieves the title and price from the books table, and also includes the average price of all books using the scalar subquery (SELECT AVG(price) FROM books) as a column named average_price.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
  (1, 'Book A', 10.00),
  (2, 'Book B', 15.00),
  (3, 'Book C', 20.00);

-- Select title, price, and average price using scalar subquery
SELECT title, price, (SELECT AVG(price) FROM books) AS average_price
FROM books;
-- Your code here

Need a hint?

Use SELECT title, price, (SELECT AVG(price) FROM books) AS average_price FROM books;

4

Complete the query with ordering by price

Add an ORDER BY price ASC clause to the query to show the books sorted by their price from lowest to highest.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
  (1, 'Book A', 10.00),
  (2, 'Book B', 15.00),
  (3, 'Book C', 20.00);

SELECT title, price, (SELECT AVG(price) FROM books) AS average_price
FROM books
ORDER BY price ASC;
-- Your code here

Need a hint?

Add ORDER BY price ASC at the end of the query to sort by price ascending.