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

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

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

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

Hint

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

Practice

1. What does the MySQL IF function do?

easy

A. Returns one value if a condition is true, another if false

B. Loops through rows in a table

C. Deletes rows based on a condition

D. Creates a new table

Solution

1. Step 1: Understand the purpose of IF function

   The IF function checks a condition and returns one value if true, another if false.

2. Step 2: Compare options with this behavior

   Only Returns one value if a condition is true, another if false describes this behavior correctly; others describe unrelated actions.

3. Final Answer:

   Returns one value if a condition is true, another if false -> Option A

4. Quick Check:

   IF function = conditional value choice [OK]

Hint: IF returns two possible values based on one condition [OK]

Common Mistakes:

• Thinking IF loops through rows
• Confusing IF with DELETE or CREATE
• Assuming IF creates tables

2. Which of the following is the correct syntax for the MySQL IF function?

easy

A. IF[condition] {true_value} ELSE {false_value}

B. IF condition THEN true_value ELSE false_value END

C. IF(condition, true_value, false_value)

D. IF condition ? true_value : false_value

Solution

1. Step 1: Recall MySQL IF function syntax

   MySQL IF function uses parentheses and commas: IF(condition, true_value, false_value).

2. Step 2: Check each option

   IF(condition, true_value, false_value) matches correct syntax; the THEN/ELSE/END version is PL/SQL style, the bracketed version and ternary operator are invalid in MySQL.

3. Final Answer:

   IF(condition, true_value, false_value) -> Option C

4. Quick Check:

   Correct syntax uses parentheses and commas [OK]

Hint: IF uses parentheses and commas, not keywords or symbols [OK]

Common Mistakes:

• Using IF without parentheses
• Confusing IF with procedural IF syntax
• Using brackets or ternary operators

3. Given a table employees with a column salary, what will this query return?

SELECT name, IF(salary >= 5000, 'High', 'Low') AS salary_level FROM employees;

medium

A. An error because IF cannot be used in SELECT

B. Only employees with salary ≥ 5000

C. Salary values replaced by 'High' or 'Low'

D. Names with 'High' if salary ≥ 5000, else 'Low'

Solution

1. Step 1: Understand the IF condition in SELECT

   The IF checks if salary is 5000 or more; returns 'High' if true, else 'Low'.

2. Step 2: Analyze the query output

   The query returns each employee's name and a label 'High' or 'Low' in salary_level column.

3. Final Answer:

   Names with 'High' if salary ≥ 5000, else 'Low' -> Option D

4. Quick Check:

   IF labels salary levels, does not filter rows [OK]

Hint: IF in SELECT returns values, does not filter rows [OK]

Common Mistakes:

• Thinking IF filters rows like WHERE
• Assuming salary values are replaced
• Believing IF causes syntax error in SELECT

4. Identify the error in this MySQL query using IF:

SELECT name, IF salary > 1000, 'Rich', 'Poor' AS status FROM users;

medium

A. Missing FROM keyword

B. Missing parentheses around IF arguments

C. IF cannot be used in SELECT clause

D. Wrong column name 'status'

Solution

1. Step 1: Check IF function syntax

   IF requires parentheses around condition and values: IF(condition, true_value, false_value).

2. Step 2: Identify error in query

   The query lacks parentheses around IF arguments, causing syntax error.

3. Final Answer:

   Missing parentheses around IF arguments -> Option B

4. Quick Check:

   IF needs parentheses for arguments [OK]

Hint: Always use parentheses with IF arguments [OK]

Common Mistakes:

• Omitting parentheses in IF
• Confusing alias placement
• Thinking IF is not allowed in SELECT

5. You want to classify products as 'Expensive', 'Moderate', or 'Cheap' based on price:
- Price > 1000: 'Expensive'
- Price between 500 and 1000: 'Moderate'
- Price < 500: 'Cheap'
Which query correctly uses nested IF functions to achieve this?

hard

A. SELECT product_name, IF(price > 1000, 'Expensive', IF(price >= 500, 'Moderate', 'Cheap')) AS price_category FROM products;

B. SELECT product_name, IF(price > 1000, 'Expensive', 'Moderate', 'Cheap') AS price_category FROM products;

C. SELECT product_name, IF(price > 1000, 'Expensive') ELSE IF(price >= 500, 'Moderate') ELSE 'Cheap' AS price_category FROM products;

D. SELECT product_name, IF(price > 1000, 'Expensive', IF(price > 500, 'Moderate', 'Cheap')) AS price_category FROM products;

Solution

1. Step 1: Understand nested IF logic

   First IF checks if price > 1000, else second IF checks if price ≥ 500, else 'Cheap'.

2. Step 2: Evaluate each option

   The version with ELSE IF uses invalid syntax. The version with four arguments has too many arguments for IF. The version with inner IF(price > 500, ...) misses 'Moderate' for price = 500. SELECT product_name, IF(price > 1000, 'Expensive', IF(price >= 500, 'Moderate', 'Cheap')) AS price_category FROM products; correctly nests IF with proper conditions and values.

3. Final Answer:

   SELECT product_name, IF(price > 1000, 'Expensive', IF(price >= 500, 'Moderate', 'Cheap')) AS price_category FROM products; -> Option A

4. Quick Check:

   Nested IF with correct conditions and syntax [OK]

Hint: Nest IF inside ELSE part with correct conditions [OK]

Common Mistakes:

• Using too many arguments in one IF
• Incorrect nested IF syntax
• Wrong comparison operators in conditions