IF function in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of IF function with numeric comparison

What is the output of this MySQL query?

SELECT IF(10 > 5, 'Yes', 'No') AS result;

MySQL

SELECT IF(10 > 5, 'Yes', 'No') AS result;

A NULL

B No

C Yes

D Syntax Error

💡 Hint

IF(condition, true_value, false_value) returns true_value if condition is true.

Since 10 is greater than 5, the condition is true, so IF returns 'Yes'.

❓ query_result

intermediate

Using IF function with NULL value

What will this query return?

SELECT IF(NULL, 'True', 'False') AS output;

MySQL

SELECT IF(NULL, 'True', 'False') AS output;

A False

B True

C NULL

D Error

💡 Hint

In MySQL, NULL is treated as false in IF conditions.

Since NULL is treated as false, IF returns the false_value 'False'.

📝 Syntax

advanced

Identify the syntax error in IF function usage

Which option contains a syntax error in using the IF function in MySQL?

A SELECT IF(score >= 60, 'Pass', 'Fail') FROM exams;

B SELECT IF(score >= 60, 'Pass', 'Fail') result FROM exams;

C SELECT IF(score >= 60, 'Pass', 'Fail') AS result FROM exams;

D SELECT IF(score >= 60, 'Pass') FROM exams;

💡 Hint

IF function requires three arguments: condition, true_value, false_value.

Option D misses the third argument, causing a syntax error.

❓ query_result

advanced

Nested IF function output

What is the output of this query?

SELECT IF(score >= 90, 'A', IF(score >= 75, 'B', 'C')) AS grade FROM (SELECT 80 AS score) AS t;

MySQL

SELECT IF(score >= 90, 'A', IF(score >= 75, 'B', 'C')) AS grade FROM (SELECT 80 AS score) AS t;

A A

B B

C C

D NULL

💡 Hint

Check the score against each condition in order.

Score 80 is not >= 90, so first IF returns false and evaluates second IF. 80 >= 75 is true, so 'B' is returned.

🧠 Conceptual

expert

Behavior of IF function with non-boolean expressions

Consider this query:

SELECT IF('abc', 'Yes', 'No') AS result;

What will be the output and why?

MySQL

SELECT IF('abc', 'Yes', 'No') AS result;

A Yes, because non-empty strings are treated as TRUE in IF condition

B No, because IF only accepts boolean TRUE or FALSE

C NULL, because 'abc' is not a boolean

D Syntax Error, because string literals cannot be used as condition

💡 Hint

In MySQL, non-zero and non-empty values are TRUE in boolean context.

MySQL treats any non-empty string as TRUE in IF condition, so it returns 'Yes'.

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