ORDER BY multiple columns in MySQL - Mini Project: Build & Apply

Sorting Products by Category and Price

📖 Scenario: You are managing a small online store database. You want to organize your product list so that products are grouped by their category and then sorted by price within each category.

🎯 Goal: Create a SQL query that orders products first by their category in alphabetical order, and then by price in ascending order within each category.

📋 What You'll Learn

Create a table called products with columns id (integer), name (text), category (text), and price (decimal).

Insert the exact product data provided.

Write a SQL query that selects all columns from products and orders the results by category ascending and then price ascending.

💡 Why This Matters

🌍 Real World

Sorting products by category and price helps customers find items easily in online stores.

💼 Career

Knowing how to order query results by multiple columns is a common task for database administrators and developers.

1

Create the products table and insert data

Create a table called products with columns id as integer, name as text, category as text, and price as decimal. Then insert these exact rows: (1, 'T-shirt', 'Clothing', 19.99), (2, 'Jeans', 'Clothing', 39.99), (3, 'Coffee Maker', 'Appliances', 49.99), (4, 'Blender', 'Appliances', 29.99), (5, 'Novel', 'Books', 9.99).

MySQL

-- Create the products table and insert the given rows
-- Your code here

Need a hint?

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

2

Set up the SELECT statement

Write a SQL query that selects all columns from the products table. Use SELECT * FROM products as the base for your query.

MySQL

CREATE TABLE products (
  id INT,
  name TEXT,
  category TEXT,
  price DECIMAL(5,2)
);

INSERT INTO products (id, name, category, price) VALUES
(1, 'T-shirt', 'Clothing', 19.99),
(2, 'Jeans', 'Clothing', 39.99),
(3, 'Coffee Maker', 'Appliances', 49.99),
(4, 'Blender', 'Appliances', 29.99),
(5, 'Novel', 'Books', 9.99);

SELECT * FROM products
-- Your code here

Need a hint?

Use SELECT * FROM products to get all columns and rows.

3

Add ORDER BY clause to sort by category and price

Extend your SQL query to order the results first by category in ascending order, and then by price in ascending order within each category. Use ORDER BY category ASC, price ASC.

MySQL

CREATE TABLE products (
  id INT,
  name TEXT,
  category TEXT,
  price DECIMAL(5,2)
);

INSERT INTO products (id, name, category, price) VALUES
(1, 'T-shirt', 'Clothing', 19.99),
(2, 'Jeans', 'Clothing', 39.99),
(3, 'Coffee Maker', 'Appliances', 49.99),
(4, 'Blender', 'Appliances', 29.99),
(5, 'Novel', 'Books', 9.99);

SELECT * FROM products
ORDER BY category ASC, price ASC
-- Your code here

Need a hint?

Use ORDER BY with multiple columns separated by commas.

4

Complete the query with semicolon

Add a semicolon ; at the end of your SQL query to complete it.

MySQL

CREATE TABLE products (
  id INT,
  name TEXT,
  category TEXT,
  price DECIMAL(5,2)
);

INSERT INTO products (id, name, category, price) VALUES
(1, 'T-shirt', 'Clothing', 19.99),
(2, 'Jeans', 'Clothing', 39.99),
(3, 'Coffee Maker', 'Appliances', 49.99),
(4, 'Blender', 'Appliances', 29.99),
(5, 'Novel', 'Books', 9.99);

SELECT * FROM products
ORDER BY category ASC, price ASC;
-- Your code here

Need a hint?

Don't forget the semicolon ; to end your SQL statement.