Scalar subqueries in MySQL - Mini Project: Build & Apply

Using Scalar Subqueries in MySQL

📖 Scenario: You are managing a small bookstore database. You want to find out the price of the most expensive book in each category and display it alongside the category name.

🎯 Goal: Build a MySQL query using scalar subqueries to show each book category with the highest book price in that category.

📋 What You'll Learn

Create a table called books with columns id (integer), title (varchar), category (varchar), and price (decimal).

Insert at least 5 rows with different categories and prices.

Write a scalar subquery to find the maximum price for each category.

Select the category and the maximum price using the scalar subquery.

💡 Why This Matters

🌍 Real World

Scalar subqueries help you get single values from related data, like finding max prices per category in a bookstore.

💼 Career

Database developers and analysts often use scalar subqueries to write efficient queries that summarize or compare data within groups.

1

Create the books table and insert data

Create a table called books with columns id as integer, title as varchar(100), category as varchar(50), and price as decimal(5,2). Then insert these exact rows: (1, 'Learn SQL', 'Programming', 29.99), (2, 'Cooking 101', 'Cooking', 15.50), (3, 'Advanced SQL', 'Programming', 39.99), (4, 'Baking Basics', 'Cooking', 12.00), (5, 'Gardening Tips', 'Gardening', 22.00).

MySQL

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

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Write a scalar subquery to find the maximum price per category

Create a scalar subquery that selects the maximum price from books where the category matches the outer query's category. Assign this subquery to a column alias called max_price.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  category VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, category, price) VALUES
(1, 'Learn SQL', 'Programming', 29.99),
(2, 'Cooking 101', 'Cooking', 15.50),
(3, 'Advanced SQL', 'Programming', 39.99),
(4, 'Baking Basics', 'Cooking', 12.00),
(5, 'Gardening Tips', 'Gardening', 22.00);

-- Write a scalar subquery to find max price per category
-- Your code here

Need a hint?

Use a subquery inside the SELECT clause that compares the outer query's category with the inner query's category.

3

Select distinct categories with their maximum price

Modify the query to select distinct category values from books and use the scalar subquery to show the maximum price for each category. Use DISTINCT in the outer query.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  category VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, category, price) VALUES
(1, 'Learn SQL', 'Programming', 29.99),
(2, 'Cooking 101', 'Cooking', 15.50),
(3, 'Advanced SQL', 'Programming', 39.99),
(4, 'Baking Basics', 'Cooking', 12.00),
(5, 'Gardening Tips', 'Gardening', 22.00);

-- Select distinct categories with max price using scalar subquery
-- Your code here

Need a hint?

Use SELECT DISTINCT to get unique categories and the scalar subquery for max price.

4

Complete the query with ordering by category

Add an ORDER BY category clause at the end of the query to sort the results alphabetically by category.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  category VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, category, price) VALUES
(1, 'Learn SQL', 'Programming', 29.99),
(2, 'Cooking 101', 'Cooking', 15.50),
(3, 'Advanced SQL', 'Programming', 39.99),
(4, 'Baking Basics', 'Cooking', 12.00),
(5, 'Gardening Tips', 'Gardening', 22.00);

-- Add ORDER BY category to sort results
-- Your code here

Need a hint?

Use ORDER BY category at the end of the query to sort alphabetically.