IFNULL and COALESCE in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to return the value of 'score' or 0 if it is NULL.

MySQL

SELECT IFNULL(score, [1]) AS result FROM games;

Drag options to blanks, or click blank then click option'

A0

BNULL

Cscore

D''

Attempts:

3 left

💡 Hint

Common Mistakes

Using NULL as the replacement value, which does not change NULL results.

Using the column name again instead of a default value.

The IFNULL function returns the first argument if it is not NULL; otherwise, it returns the second argument. Here, if 'score' is NULL, it returns 0.

2fill in blank

medium

Complete the code to return the first non-NULL value among 'nickname', 'username', or 'guest'.

MySQL

SELECT COALESCE(nickname, username, [1]) AS display_name FROM users;

Drag options to blanks, or click blank then click option'

A'username'

BNULL

Cnickname

D'guest'

Attempts:

3 left

💡 Hint

Common Mistakes

Using NULL as the last value, which does not provide a fallback.

Using column names instead of a string literal for the fallback.

COALESCE returns the first non-NULL value from the list. If both 'nickname' and 'username' are NULL, it returns 'guest'.

3fill in blank

hard

Complete the code to return the value of 'price' or 100 if it is NULL.

MySQL

SELECT IFNULL(price, [1]) AS final_price FROM products;

Drag options to blanks, or click blank then click option'

A100

Bprice

CNULL

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using NULL as the replacement value, which does not change NULL results.

Using the column name again instead of a default value.

The IFNULL function returns the first argument if it is not NULL; otherwise, it returns the second argument. Here, if 'price' is NULL, it returns 100.

4fill in blank

hard

Fill both blanks to create a dictionary of words and their lengths only if length is greater than 3.

MySQL

SELECT word, LENGTH(word) AS length FROM words WHERE LENGTH(word) [1] [2];

Drag options to blanks, or click blank then click option'

A>

B<

C3

D5

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' reverses the condition.

Using 5 instead of 3 changes the filter.

The query selects words where the length is greater than 3. So the operator is '>' and the number is 3.

5fill in blank

hard

Fill all three blanks to select the first non-NULL email from 'email1', 'email2', or 'email3'.

MySQL

SELECT COALESCE([1], [2], [3]) AS contact_email FROM contacts;

Drag options to blanks, or click blank then click option'

Aemail1

Bemail2

Cemail3

Demail4

Attempts:

3 left

💡 Hint

Common Mistakes

Including a non-existent column like 'email4'.

Repeating the same column multiple times.

COALESCE returns the first non-NULL value among the given columns. Here, it checks 'email1', then 'email2', then 'email3'.

Practice

1. What does the IFNULL(expr1, expr2) function do in MySQL?

easy

A. Checks if expr1 equals expr2 and returns TRUE or FALSE.

B. Returns expr2 if expr1 is NULL, otherwise returns expr1.

C. Returns the first non-NULL value from a list of expressions.

D. Returns NULL if expr1 is NULL, otherwise returns expr2.

Solution

1. Step 1: Understand IFNULL function behavior

   IFNULL takes two expressions and checks if the first is NULL.

2. Step 2: Determine return value based on NULL check

   If the first expression is NULL, it returns the second; otherwise, it returns the first.

3. Final Answer:

   Returns expr2 if expr1 is NULL, otherwise returns expr1. -> Option B

4. Quick Check:

   IFNULL(expr1, expr2) = expr2 if expr1 is NULL [OK]

Hint: IFNULL replaces NULL with one default value quickly [OK]

Common Mistakes:

• Confusing IFNULL with COALESCE
• Thinking IFNULL returns NULL always
• Mixing order of arguments

2. Which of the following is the correct syntax to use COALESCE in MySQL?

easy

A. COALESCE(expr1, expr2, ..., exprN)

B. COALESCE(expr1; expr2; ...; exprN)

C. COALESCE[expr1, expr2, ..., exprN]

D. COALESCE{expr1, expr2, ..., exprN}

Solution

1. Step 1: Recall COALESCE syntax

   COALESCE takes a comma-separated list of expressions inside parentheses.

2. Step 2: Identify correct punctuation

   Only COALESCE(expr1, expr2, ..., exprN) uses commas and parentheses correctly.

3. Final Answer:

   COALESCE(expr1, expr2, ..., exprN) -> Option A

4. Quick Check:

   COALESCE uses commas and parentheses [OK]

Hint: Use commas inside parentheses for COALESCE [OK]

Common Mistakes:

• Using semicolons instead of commas
• Using brackets or braces instead of parentheses
• Missing parentheses

3. Given the table users with columns id, nickname, and fullname, what will this query return?

SELECT id, COALESCE(nickname, fullname, 'Guest') AS display_name FROM users;

Assuming a row has nickname = NULL and fullname = 'Alice'.

medium

A. 'Alice'

B. 'Guest'

C. NULL

D. Empty string

Solution

1. Step 1: Understand COALESCE behavior with given values

   COALESCE returns the first non-NULL value from the list: nickname, fullname, 'Guest'.

2. Step 2: Apply values to COALESCE

   Since nickname is NULL, it checks fullname which is 'Alice', so it returns 'Alice'.

3. Final Answer:

   'Alice' -> Option A

4. Quick Check:

   COALESCE(NULL, 'Alice', 'Guest') = 'Alice' [OK]

Hint: COALESCE picks first non-NULL value from left to right [OK]

Common Mistakes:

• Assuming COALESCE returns NULL if first is NULL
• Confusing COALESCE with IFNULL
• Thinking it returns default always

4. Identify the error in this query:

SELECT IFNULL(name, 'Unknown' 'N/A') FROM employees;

medium

A. No error, query is correct

B. IFNULL cannot take string literals

C. Incorrect function name, should be COALESCE

D. Missing comma between 'Unknown' and 'N/A'

Solution

1. Step 1: Check IFNULL syntax

   IFNULL takes exactly two arguments separated by a comma.

2. Step 2: Identify argument separation error

   The query has two string literals without a comma between them, causing syntax error.

3. Final Answer:

   Missing comma between 'Unknown' and 'N/A' -> Option D

4. Quick Check:

   Arguments must be comma-separated [OK]

Hint: Separate arguments with commas in functions [OK]

Common Mistakes:

• Forgetting commas between arguments
• Using IFNULL with more than two arguments
• Confusing IFNULL and COALESCE syntax

5. You want to display a user's contact info by checking email, then phone, and if both are NULL, show 'No contact'. Which query correctly uses COALESCE for this?

hard

A. SELECT COALESCE(email, 'No contact', phone) AS contact_info FROM users;

B. SELECT IFNULL(email, IFNULL(phone, 'No contact')) AS contact_info FROM users;

C. SELECT COALESCE(email, phone, 'No contact') AS contact_info FROM users;

D. SELECT IFNULL(email, phone, 'No contact') AS contact_info FROM users;

Solution

1. Step 1: Understand COALESCE usage for multiple values

   COALESCE returns the first non-NULL value from all arguments in order.

2. Step 2: Check argument order for contact info

   We want email first, then phone, then 'No contact' if both NULL.

3. Step 3: Validate each option

   SELECT COALESCE(email, phone, 'No contact') AS contact_info FROM users; correctly orders arguments. SELECT IFNULL(email, IFNULL(phone, 'No contact')) AS contact_info FROM users; uses nested IFNULL which works but is more complex. SELECT COALESCE(email, 'No contact', phone) AS contact_info FROM users; puts 'No contact' before phone, which is wrong. SELECT IFNULL(email, phone, 'No contact') AS contact_info FROM users; has wrong IFNULL syntax with three arguments.

4. Final Answer:

   SELECT COALESCE(email, phone, 'No contact') AS contact_info FROM users; -> Option C

5. Quick Check:

   COALESCE picks first non-NULL in order [OK]

Hint: List values in COALESCE from most to least preferred [OK]

Common Mistakes:

• Putting default value before other columns
• Using IFNULL with more than two arguments
• Confusing argument order