MySQL COALESCE Function Quiz: Test Your Knowledge

Progress

True / False

The MySQL COALESCE function returns the first non-NULL value in the list of expressions.

True False

The COALESCE function evaluates the expressions in the order they are given and returns the first non-NULL value.

True / False

In MySQL, COALESCE(NULL, 'default') will return NULL.

True False

COALESCE(NULL, 'default') will return 'default' because it is the first non-NULL value.

True / False

The MySQL COALESCE function can only take two arguments.

True False

The COALESCE function can take multiple arguments.

True / False

The MySQL COALESCE function can be used to handle NULL values in a SELECT statement.

True False

COALESCE is commonly used in SELECT statements to replace NULL values with a specified value.

True / False

In MySQL, COALESCE and IFNULL are completely interchangeable.

True False

While COALESCE and IFNULL can often be used for similar purposes, COALESCE can take multiple arguments, whereas IFNULL can only take two.

True / False

Using the COALESCE function in MySQL can improve query performance compared to using multiple IF statements.

True False

COALESCE is often more efficient and readable than using multiple IF statements to handle NULL values.

True / False

In MySQL, COALESCE can be used within an UPDATE statement to set a column value based on multiple conditions.

True False

COALESCE can be used in an UPDATE statement to set a column's value based on the first non-NULL condition.

True / False

The MySQL COALESCE function evaluates all its arguments, even if the first one is non-NULL.

True False

COALESCE stops evaluating once it finds the first non-NULL value, it does not evaluate the remaining arguments.

True / False

In MySQL, COALESCE(NULL, NULL, NULL) will return an error.

True False

COALESCE(NULL, NULL, NULL) will return NULL, as all arguments are NULL.

Q10

True / False

In a MySQL certification exam, you might encounter a question asking whether the COALESCE function can handle expressions of different data types.

True False

The COALESCE function can handle expressions of different data types, but they must be compatible. The result will be of the type of the first non-NULL expression, according to MySQL's type conversion rules.

Q11

Single Choice

Which of the following best describes the COALESCE function in MySQL?

It returns the first non-NULL value from a list of expressions. It returns the last non-NULL value from a list of expressions. It returns all non-NULL values from a list of expressions.It returns NULL if all values are non-NULL.

The COALESCE function in MySQL returns the first non-NULL value from the list of provided arguments.

Q12

Single Choice

What does the following SQL query return?

SQL Code

SELECT COALESCE(NULL, 'MySQL', 'SQL');

NULL MySQL SQLError

The COALESCE function returns the first non-NULL value, which is 'MySQL' in this case.

Q13

Single Choice

In which of the following scenarios would the COALESCE function return NULL?

When all provided expressions are NULL. When the first expression is NULL. When the first expression is non-NULL.When the function is used without arguments.

The COALESCE function returns NULL only if all the provided expressions are NULL

Q14

Single Choice

What will be the result of the following query using COALESCE?

SQL Code

SELECT COALESCE(NULL, NULL, 10, 20);

10 20 30NULL

The COALESCE function returns the first non-NULL value, which is 10 in this case.

Q15

Single Choice

Which SQL statement using COALESCE will return 'UNKNOWN' if the status column contains NULL?

SQL Code

SELECT COALESCE(_____, 'UNKNOWN') FROM orders;

status IFNULL(status) ISNULL(status)NVL(status)

The COALESCE function with the status column as the first argument will return 'UNKNOWN' when status is NULL.

Q16

Single Choice

What will this query return if customer_name is NULL?

SQL Code

SELECT COALESCE(customer_name, 'No Name') AS customer FROM customers;

NULL customer_name No NameError

The query returns 'No Name' if customer_name is NULL, due to the COALESCE function

Q17

Single Choice

Given the following table employees with columns first_name, middle_name, and last_name, what will the following query return?

SQL Code

SELECT COALESCE(middle_name, first_name, last_name) FROM employees;

First non-NULL value among middle_name, first_name, and last_name NULL if middle_name is NULL All three names concatenatedLast non-NULL value among middle_name, first_name, and last_name

The COALESCE function returns the first non-NULL value among the provided arguments.

Q18

Single Choice

What is the difference between using COALESCE and IFNULL in MySQL?

COALESCE can take multiple arguments, while IFNULL only takes two. IFNULL can take multiple arguments, while COALESCE only takes two. COALESCE and IFNULL are identical in functionality.IFNULL is more efficient than COALESCE.

The COALESCE function can take multiple arguments and returns the first non-NULL value, while IFNULL only takes two arguments.

Q19

Single Choice

What will be the result?

SQL Code

SELECT COALESCE(NULL, COALESCE(NULL, COALESCE(NULL, 'Nested')));

NULL Nested ErrorEmpty string

The query will return 'Nested' as it is the first non-NULL value in the nested COALESCE functions.

Q20

Single Choice

What does this query accomplish?

SQL Code

SELECT
 COALESCE(o.order_date, 'No Date') AS order_date,
 COALESCE(c.customer_name, 'No Name') AS customer_name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

It fills in missing order_date and customer_name with 'No Date' and 'No Name' respectively. It returns only rows where order_date and customer_name are non-NULL. It replaces all NULL values with empty strings.It skips rows where order_date or customer_name is NULL.

The query uses COALESCE to replace NULL values in order_date and customer_name with 'No Date' and 'No Name', making the report more readable by avoiding NULL values.

Q21

Multiple Choice

Which SQL query uses COALESCE to replace NULL values in the 'phone' column with the value 'N/A'?

SQL Code

SELECT employee_id, COALESCE(phone, 'N/A') AS phone_number
FROM employees;

SELECT employee_id, COALESCE(phone, 'N/A') AS phone_number FROM employees; SELECT employee_id, IFNULL(phone, 'N/A') AS phone_number FROM employees; SELECT employee_id, CASE WHEN phone IS NULL THEN 'N/A' ELSE phone END AS phone_number FROM employees;SELECT employee_id, COALESCE(phone, '') AS phone_number FROM employees;

The COALESCE function checks the 'phone' column and returns 'N/A' if it contains a NULL value, otherwise it returns the actual phone number.

Q22

Multiple Choice

Identify the SQL query that uses COALESCE to handle NULL values in multiple columns for an employee's contact information.

SQL Code

SELECT employee_id, COALESCE(phone, email, 'No Contact') AS contact_info
FROM employees;

SELECT employee_id, COALESCE(phone, email, 'No Contact') AS contact_info FROM employees; SELECT employee_id, COALESCE(phone, 'No Contact') AS contact_info FROM employees; SELECT employee_id, IFNULL(phone, email) AS contact_info FROM employees;SELECT employee_id, COALESCE(phone, email) AS contact_info FROM employees;

The COALESCE function evaluates each column in order and returns the first non-NULL value it encounters. If all values are NULL, it returns 'No Contact'.

Q23

Multiple Choice

Which SQL query uses COALESCE to display the first available price for products across different price columns?

SQL Code

SELECT product_name, COALESCE(price_2022, price_2021, price_2020) AS current_price
FROM products;

SELECT product_name, COALESCE(price_2022, price_2021, price_2020) AS current_price FROM products; SELECT product_name, COALESCE(price_2021, price_2020) AS current_price FROM products; SELECT product_name, IFNULL(price_2022, price_2021) AS current_price FROM products;SELECT product_name, COALESCE(price_2020, price_2021, price_2022) AS current_price FROM products;

The COALESCE function returns the first non-NULL price from the available columns, ensuring that the most recent price is displayed if available.

Q24

Multiple Choice

Determine the SQL query that uses COALESCE to replace NULL values in an 'address' column with values from other address-related columns.

SQL Code

SELECT customer_id, COALESCE(address, alternate_address, 'Address Not Available') AS full_address
FROM customers;

SELECT customer_id, COALESCE(address, alternate_address, 'Address Not Available') AS full_address FROM customers; SELECT customer_id, COALESCE(address, 'Address Not Available') AS full_address FROM customers; SELECT customer_id, IFNULL(address, alternate_address) AS full_address FROM customers;SELECT customer_id, COALESCE(alternate_address, address, 'Address Not Available') AS full_address FROM customers;

The COALESCE function checks each address-related column in order, returning the first non-NULL value or 'Address Not Available' if all are NULL.

Q25

Multiple Choice

Which SQL query uses COALESCE to fill missing 'department' values with 'Unknown' and then with 'General' if both are NULL?

SQL Code

SELECT employee_id, COALESCE(department, 'Unknown', 'General') AS department_name
FROM employees;

SELECT employee_id, COALESCE(department, 'Unknown', 'General') AS department_name FROM employees; SELECT employee_id, COALESCE(department, 'General') AS department_name FROM employees; SELECT employee_id, IFNULL(department, 'General') AS department_name FROM employees;SELECT employee_id, COALESCE(department, 'General', 'Unknown') AS department_name FROM employees;

The COALESCE function checks for NULL in the 'department' column and replaces it first with 'Unknown' and then 'General' if the previous options are also NULL.

Q26

Multiple Choice

Which SQL query uses COALESCE to prioritize and display a customer's preferred contact method?

SQL Code

SELECT customer_id, COALESCE(phone, email, 'No Contact Available') AS preferred_contact
FROM customers;

SELECT customer_id, COALESCE(phone, email, 'No Contact Available') AS preferred_contact FROM customers; SELECT customer_id, COALESCE(phone, 'No Contact Available') AS preferred_contact FROM customers; SELECT customer_id, IFNULL(phone, email) AS preferred_contact FROM customers;SELECT customer_id, COALESCE(email, phone, 'No Contact Available') AS preferred_contact FROM customers;

The COALESCE function returns the first non-NULL value between phone and email, providing a fallback to 'No Contact Available' if both are NULL.

Q27

Multiple Choice

Identify the SQL query that uses COALESCE to provide a fallback value for missing 'date_of_birth' data.

SQL Code

SELECT employee_id, COALESCE(date_of_birth, '1900-01-01') AS birth_date
FROM employees;

SELECT employee_id, COALESCE(date_of_birth, '1900-01-01') AS birth_date FROM employees; SELECT employee_id, COALESCE(date_of_birth, CURRENT_DATE) AS birth_date FROM employees; SELECT employee_id, IFNULL(date_of_birth, '1900-01-01') AS birth_date FROM employees;SELECT employee_id, COALESCE(date_of_birth, NULL) AS birth_date FROM employees;

The COALESCE function checks if 'date_of_birth' is NULL and replaces it with '1900-01-01' as a default value.

Q28

Multiple Choice

Which SQL query uses COALESCE to combine and display the most recent non-NULL value from multiple review columns?

SQL Code

SELECT product_name, COALESCE(review_2023, review_2022, review_2021, 'No Review') AS latest_review
FROM products;

SELECT product_name, COALESCE(review_2023, review_2022, review_2021, 'No Review') AS latest_review FROM products; SELECT product_name, COALESCE(review_2021, review_2022, review_2023, 'No Review') AS latest_review FROM products; SELECT product_name, IFNULL(review_2023, review_2022) AS latest_review FROM products;SELECT product_name, COALESCE(review_2023, review_2022) AS latest_review FROM products;

The COALESCE function checks each review column in order, returning the first non-NULL value or 'No Review' if all are NULL.

Q29

Multiple Choice

Determine the SQL query that uses COALESCE to select the first available non-NULL value for a customer's loyalty tier.

SQL Code

SELECT customer_id, COALESCE(loyalty_tier_2023, loyalty_tier_2022, 'Standard') AS current_tier
FROM customers;

SELECT customer_id, COALESCE(loyalty_tier_2023, loyalty_tier_2022, 'Standard') AS current_tier FROM customers; SELECT customer_id, COALESCE(loyalty_tier_2022, loyalty_tier_2023, 'Standard') AS current_tier FROM customers; SELECT customer_id, IFNULL(loyalty_tier_2023, loyalty_tier_2022) AS current_tier FROM customers;SELECT customer_id, COALESCE(loyalty_tier_2023, 'Standard') AS current_tier FROM customers;

The COALESCE function returns the first non-NULL loyalty tier from the available columns, defaulting to 'Standard' if all are NULL.

Q30

Multiple Choice

Which SQL query uses COALESCE to handle missing 'discount' values by replacing them with a default of 0?

SQL Code

SELECT order_id, COALESCE(discount, 0) AS applied_discount
FROM orders;

SELECT order_id, COALESCE(discount, 0) AS applied_discount FROM orders; SELECT order_id, COALESCE(discount, 1) AS applied_discount FROM orders; SELECT order_id, IFNULL(discount, 0) AS applied_discount FROM orders;SELECT order_id, COALESCE(discount, NULL) AS applied_discount FROM orders;

The COALESCE function replaces any NULL 'discount' values with 0, ensuring that all orders have an applied discount value.

MySQL Database Quiz Questions