IF function in MySQL - Mini Project: Build & Apply

Using the IF Function in MySQL

📖 Scenario: You work at a bookstore that wants to classify books based on their price. Books priced above $20 are considered 'Expensive', and others are 'Affordable'.

🎯 Goal: Create a MySQL query that uses the IF function to add a new column called price_category which shows 'Expensive' if the book price is greater than 20, otherwise 'Affordable'.

📋 What You'll Learn

Create a table called books with columns id, title, and price.

Insert exactly three books with given prices.

Write a SELECT query using the IF function to classify books by price.

The output must include id, title, price, and the new price_category column.

💡 Why This Matters

🌍 Real World

Classifying products or items based on conditions like price or stock is common in retail databases.

💼 Career

Knowing how to use conditional logic in SQL queries helps in reporting and data analysis roles.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (varchar 50), and price (decimal 5,2). Then insert these three rows exactly: (1, 'Learn SQL', 15.00), (2, 'Mastering MySQL', 25.00), (3, 'Database Basics', 20.00).

MySQL

-- Create the books table and insert the three rows
-- Your code here

Need a hint?

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

2

Set the price threshold variable

Create a variable called @price_limit and set it to 20.00. This will be used as the price threshold in the IF function.

MySQL

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

INSERT INTO books (id, title, price) VALUES
(1, 'Learn SQL', 15.00),
(2, 'Mastering MySQL', 25.00),
(3, 'Database Basics', 20.00);

-- Set the price threshold variable
-- Your code here

Need a hint?

Use SET @price_limit = 20.00; to create the variable.

3

Write the SELECT query using the IF function

Write a SELECT query that retrieves id, title, price, and a new column called price_category. Use the MySQL IF function to set price_category to 'Expensive' if price is greater than @price_limit, otherwise 'Affordable'.

MySQL

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

INSERT INTO books (id, title, price) VALUES
(1, 'Learn SQL', 15.00),
(2, 'Mastering MySQL', 25.00),
(3, 'Database Basics', 20.00);

SET @price_limit = 20.00;

-- Write the SELECT query using IF function
-- Your code here

Need a hint?

Use IF(condition, true_value, false_value) in the SELECT statement.

4

Complete the query with ordering

Add an ORDER BY clause to the SELECT query to sort the results by price in ascending order.

MySQL

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

INSERT INTO books (id, title, price) VALUES
(1, 'Learn SQL', 15.00),
(2, 'Mastering MySQL', 25.00),
(3, 'Database Basics', 20.00);

SET @price_limit = 20.00;

SELECT id, title, price,
  IF(price > @price_limit, 'Expensive', 'Affordable') AS price_category
FROM books
-- Add ORDER BY clause here
-- Your code here

Need a hint?

Use ORDER BY price ASC to sort by price from low to high.