CASE in ORDER BY in SQL - Mini Project: Build & Apply

Sorting Products by Category Priority Using CASE in ORDER BY

📖 Scenario: You manage a product database for an online store. You want to display products sorted by category priority: Electronics first, then Clothing, then Home, and finally all other categories.

🎯 Goal: Create a SQL query that sorts products by category priority using CASE inside the ORDER BY clause.

📋 What You'll Learn

Create a table called products with columns id, name, and category.

Insert exactly these products: (1, 'Laptop', 'Electronics'), (2, 'T-Shirt', 'Clothing'), (3, 'Vacuum', 'Home'), (4, 'Smartphone', 'Electronics'), (5, 'Jeans', 'Clothing').

Write a SELECT query to get name and category from products.

Use CASE inside ORDER BY to sort categories in this order: Electronics, Clothing, Home, then others alphabetically.

💡 Why This Matters

🌍 Real World

Sorting products by priority helps online stores show important categories first, improving user experience.

💼 Career

Knowing how to use CASE in ORDER BY is useful for database querying and reporting tasks in many jobs.

1

Create the products table and insert data

Create a table called products with columns id (integer), name (text), and category (text). Then insert these exact rows: (1, 'Laptop', 'Electronics'), (2, 'T-Shirt', 'Clothing'), (3, 'Vacuum', 'Home'), (4, 'Smartphone', 'Electronics'), (5, 'Jeans', 'Clothing').

SQL

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

Need a hint?

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

2

Write a basic SELECT query for product names and categories

Write a SELECT query to get the name and category columns from the products table.

SQL

CREATE TABLE products (
  id INTEGER,
  name TEXT,
  category TEXT
);

INSERT INTO products (id, name, category) VALUES
  (1, 'Laptop', 'Electronics'),
  (2, 'T-Shirt', 'Clothing'),
  (3, 'Vacuum', 'Home'),
  (4, 'Smartphone', 'Electronics'),
  (5, 'Jeans', 'Clothing');

-- Write SELECT query to get name and category
-- Your code here

Need a hint?

Use SELECT name, category FROM products; to get the product names and categories.

3

Add CASE in ORDER BY to sort by category priority

Modify the SELECT query to sort the results by category priority using CASE inside ORDER BY. Sort categories in this order: 'Electronics' first, then 'Clothing', then 'Home', and then all other categories alphabetically.

SQL

CREATE TABLE products (
  id INTEGER,
  name TEXT,
  category TEXT
);

INSERT INTO products (id, name, category) VALUES
  (1, 'Laptop', 'Electronics'),
  (2, 'T-Shirt', 'Clothing'),
  (3, 'Vacuum', 'Home'),
  (4, 'Smartphone', 'Electronics'),
  (5, 'Jeans', 'Clothing');

-- Add ORDER BY with CASE to sort by category priority
-- Your code here

Need a hint?

Use CASE category WHEN 'Electronics' THEN 1 WHEN 'Clothing' THEN 2 WHEN 'Home' THEN 3 ELSE 4 END inside ORDER BY to set priority.

4

Complete the query with final sorting and formatting

Ensure the final query selects name and category from products and sorts by category priority using CASE in ORDER BY, then by category alphabetically for others.

SQL

CREATE TABLE products (
  id INTEGER,
  name TEXT,
  category TEXT
);

INSERT INTO products (id, name, category) VALUES
  (1, 'Laptop', 'Electronics'),
  (2, 'T-Shirt', 'Clothing'),
  (3, 'Vacuum', 'Home'),
  (4, 'Smartphone', 'Electronics'),
  (5, 'Jeans', 'Clothing');

-- Finalize the query with ORDER BY using CASE and category
-- Your code here

Need a hint?

Make sure the query includes the full SELECT statement with the ORDER BY CASE sorting logic.