Test Your Knowledge of MySQL CASE Statement with This SQL Quiz

Progress

True / False

The CASE statement in MySQL can only be used in SELECT queries.

True False

The CASE statement can be used in various SQL statements such as SELECT, UPDATE, DELETE, and SET.

True / False

In MySQL, the CASE statement must always have an ELSE clause.

True False

The ELSE clause in a CASE statement is optional. If it is omitted and no condition is true, NULL is returned.

True / False

In MySQL, the CASE statement can be used to compare different columns in a table.

True False

The CASE statement can compare different columns within the same table to make decisions based on their values.

True / False

In MySQL, the CASE statement can be used to replace IF statements.

True False

The CASE statement can often be used in place of IF statements to perform conditional logic in queries.

True / False

MySQL supports a CASE statement inside another CASE statement.

True False

MySQL allows nested CASE statements, meaning you can place one CASE statement inside another.

True / False

MySQL's CASE statement can evaluate multiple conditions simultaneously.

True False

The CASE statement evaluates conditions sequentially and stops evaluating once a true condition is found.

True / False

MySQL's CASE statement can be used to execute multiple SQL statements.

True False

The CASE statement is used to return a single value based on conditional logic, not to execute multiple SQL statements.

True / False

In MySQL, you can use aggregate functions within a CASE statement.

True False

Aggregate functions like SUM, COUNT, MAX, etc., can be used within CASE statements to perform conditional aggregation.

True / False

MySQL allows the use of subqueries inside the CASE statement.

True False

Subqueries can be used within a CASE statement to perform complex conditional logic.

Q10

True / False

MySQL's CASE statement allows the use of data types that differ from those in the conditions being evaluated.

True False

The CASE statement in MySQL can handle expressions and conditions involving different data types, but all return expressions must be of a compatible type.

Q11

Single Choice

Which MySQL keyword is used to define alternative conditions in a CASE statement?

ELSE SWITCH DEFAULTCHOOSE

The ELSE keyword in MySQL is used in a CASE statement to specify a default action when none of the conditions are met.

Q12

Single Choice

What will be the output of the following SQL code?

SQL Code

SELECT CASE
 WHEN 1 = 1 THEN 'True'
 ELSE 'False'
END AS result;

True False NullNull

Since the condition 1 = 1 is true, the CASE statement will return 'True

Q13

Single Choice

: In a MySQL CASE statement, what happens if none of the WHEN conditions are met and there is no ELSE clause?

The query will return an error The query will return NULL The query will return the first WHEN conditionThe query will execute a default action

If none of the WHEN conditions are met and no ELSE clause is provided, MySQL returns NULL.

Q14

Single Choice

What will be the result of this MySQL query?

SQL Code

SELECT CASE
 WHEN salary > 50000 THEN 'High'
 WHEN salary > 30000 THEN 'Medium'
 ELSE 'Low'
END AS salary_level
FROM employees WHERE employee_id = 101;

The query will return 'High' if salary > 50000. The query will return 'Medium' if salary > 30000 and <= 50000. The query will return 'Low' if salary <= 30000.All the above

The CASE statement evaluates the conditions in order. If the salary is greater than 50000, it returns 'High', otherwise 'Medium' or 'Low' based on the salary value.

Q15

Single Choice

Consider the following SQL code. What will be the output for an employee with a salary of 45000?

SQL Code

SELECT CASE
 WHEN salary >= 50000 THEN 'A'
 WHEN salary >= 40000 THEN 'B'
 WHEN salary >= 30000 THEN 'C'
 ELSE 'D'
END AS grade
FROM employees WHERE employee_id = 202;

A B CD

Since the salary is 45000, it meets the condition salary >= 40000, so the CASE statement will return 'B'.

Q16

Single Choice

Which of the following MySQL CASE statement syntax is correct to check multiple columns?

CASE WHEN column1 = value1 AND column2 = value2 THEN result1 CASE column1 = value1 AND column2 = value2 THEN result1 CASE WHEN column1, column2 THEN result1CASE WHEN column1 AND column2 THEN result1

The correct syntax to evaluate multiple columns in a CASE statement uses the WHEN clause with a condition combining multiple columns using AND

Q17

Single Choice

What is the output of the following MySQL query?

SQL Code

SELECT CASE
 WHEN salary BETWEEN 60000 AND 80000 THEN 'Manager'
 WHEN salary BETWEEN 40000 AND 59999 THEN 'Senior Staff'
 WHEN salary BETWEEN 20000 AND 39999 THEN 'Junior Staff'
 ELSE 'Intern'
END AS position
FROM employees WHERE employee_id = 303;

Manager Junior SeniorDepend on the employee salary

The output depends on the salary of the employee with employee_id = 303. The CASE statement will return a position based on which salary range the employee falls into.

Q18

Single Choice

Which of the following MySQL CASE statements will correctly return the string 'Yes' if the value of status is either 'active' or 'pending'?

SQL Code

SELECT CASE
 WHEN status IN ('active', 'pending') THEN 'Yes'
 ELSE 'No'
END AS status_check
FROM orders WHERE order_id = 101;

The query is incorrect; CASE cannot evaluate multiple values. The query is incorrect; IN cannot be used in a CASE statement. The query is correct and will return 'Yes' for 'active' or 'pending' status.The query is incorrect; CASE must have a specific value to compare.

The IN keyword can be used in a CASE statement to check if the value matches any of the specified options.

Q19

Single Choice

Given the following MySQL query, which value of score will output 'Pass with Distinction'?

SQL Code

SELECT CASE
 WHEN score >= 85 THEN 'Pass with Distinction'
 WHEN score >= 70 THEN 'Pass'
 ELSE 'Fail'
END AS result
FROM students WHERE student_id = 404;

60 70 8085

The CASE statement will return 'Pass with Distinction' when the score is greater than or equal to 85

Q20

Single Choice

Consider a scenario where you need to calculate a dynamic discount based on the total purchase amount. Which of the following MySQL queries correctly implements the logic using a CASE statement?

SQL Code

SELECT total_amount,
CASE
 WHEN total_amount >= 1000 THEN total_amount * 0.10
 WHEN total_amount >= 500 THEN total_amount * 0.05
 ELSE 0
END AS discount
FROM purchases;

The query will apply a 10% discount for amounts 1000 or more, a 5% discount for amounts between 500 and 999, and no discount for amounts below 500. The query will apply a 10% discount for amounts 500 or more and no discount for amounts below 500 The query will only apply a 5% discount for amounts 500 or more.The query will return an error due to incorrect CASE syntax.

The CASE statement correctly implements the discount logic based on the total_amount value, applying the appropriate percentage discount or none at all

Q21

Multiple Choice

Which SQL query uses the CASE statement to categorize employees based on their salary into 'High', 'Medium', and 'Low' categories?

SQL Code

SELECT employee_id, salary,
CASE
 WHEN salary > 80000 THEN 'High'
 WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'
 ELSE 'Low'
END AS salary_category
FROM employees;

SELECT employee_id, salary, CASE WHEN salary > 80000 THEN 'High' WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium' ELSE 'Low' END AS salary_category FROM employees; SELECT employee_id, salary, CASE salary > 80000 THEN 'High' ELSE 'Low' END AS salary_category FROM employees; SELECT employee_id, salary_category = CASE WHEN salary > 80000 THEN 'High' WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium' ELSE 'Low' END FROM employees;SELECT employee_id, salary_category = CASE WHEN salary > 80000 THEN 'High' WHEN salary > 50000 THEN 'Medium' ELSE 'Low' END AS salary_category FROM employees;

The CASE statement is used to evaluate the salary and assign a category based on the defined conditions.

Q22

Multiple Choice

Identify the correct SQL query that uses the CASE statement to display 'Yes' if the order amount is greater than 1000 and 'No' otherwise.

SQL Code

SELECT order_id, order_amount,
CASE
 WHEN order_amount > 1000 THEN 'Yes'
 ELSE 'No'
END AS high_value_order
FROM orders;

SELECT order_id, order_amount, CASE WHEN order_amount > 1000 THEN 'Yes' ELSE 'No' END AS high_value_order FROM orders; SELECT order_id, order_amount, CASE WHEN order_amount > 1000 THEN 'Yes' ELSE 'No' END high_value_order FROM orders; SELECT order_id, order_amount, CASE order_amount > 1000 THEN 'Yes' ELSE 'No' END AS high_value_order FROM orders;SELECT order_id, high_value_order = CASE WHEN order_amount > 1000 THEN 'Yes' ELSE 'No' END FROM orders;

The CASE statement is used to check if the order amount is greater than 1000 and returns 'Yes' or 'No' accordingly.

Q23

Multiple Choice

Which query uses the CASE statement to assign a discount percentage based on the 'customer_type' in the 'customers' table?

SQL Code

SELECT customer_id, customer_type,
CASE customer_type
 WHEN 'Regular' THEN 10
 WHEN 'Premium' THEN 20
 ELSE 5
END AS discount_percentage
FROM customers;

SELECT customer_id, customer_type, CASE customer_type WHEN 'Regular' THEN 10 WHEN 'Premium' THEN 20 ELSE 5 END AS discount_percentage FROM customers; SELECT customer_id, customer_type, CASE WHEN customer_type = 'Regular' THEN 10 WHEN customer_type = 'Premium' THEN 20 ELSE 5 END AS discount_percentage FROM customers; SELECT customer_id, discount_percentage = CASE customer_type WHEN 'Regular' THEN 10 WHEN 'Premium' THEN 20 ELSE 5 END FROM customers;SELECT customer_id, customer_type, CASE WHEN customer_type = 'Regular' THEN 10 WHEN customer_type = 'Premium' THEN 20 ELSE 5 END AS discount FROM customers;

The CASE statement checks the 'customer_type' and assigns a discount percentage based on its value.

Q24

Multiple Choice

Determine the correct SQL query that uses the CASE statement to show 'Pass' if a student's grade is 60 or above, and 'Fail' otherwise.

SQL Code

SELECT student_id, grade,
CASE
 WHEN grade >= 60 THEN 'Pass'
 ELSE 'Fail'
END AS result
FROM students;

SELECT student_id, grade, CASE WHEN grade >= 60 THEN 'Pass' ELSE 'Fail' END AS result FROM students; SELECT student_id, grade, CASE WHEN grade >= 60 THEN 'Pass' ELSE 'Fail' END result FROM students; SELECT student_id, grade, CASE WHEN grade = 60 THEN 'Pass' ELSE 'Fail' END AS result FROM students;SELECT student_id, result = CASE WHEN grade >= 60 THEN 'Pass' ELSE 'Fail' END FROM students;

The CASE statement evaluates the 'grade' and assigns 'Pass' or 'Fail' based on whether the grade meets the threshold.

Q25

Multiple Choice

Which SQL query uses the CASE statement to categorize transactions into 'Large', 'Medium', and 'Small' based on the transaction amount?

SQL Code

SELECT transaction_id, transaction_amount,
CASE
 WHEN transaction_amount > 10000 THEN 'Large'
 WHEN transaction_amount BETWEEN 5000 AND 10000 THEN 'Medium'
 ELSE 'Small'
END AS transaction_size
FROM transactions;

SELECT transaction_id, transaction_amount, CASE WHEN transaction_amount > 10000 THEN 'Large' WHEN transaction_amount BETWEEN 5000 AND 10000 THEN 'Medium' ELSE 'Small' END AS transaction_size FROM transactions; SELECT transaction_id, transaction_amount, CASE WHEN transaction_amount > 10000 THEN 'Large' ELSE 'Small' END AS transaction_size FROM transactions; SELECT transaction_id, transaction_size = CASE WHEN transaction_amount > 10000 THEN 'Large' WHEN transaction_amount BETWEEN 5000 AND 10000 THEN 'Medium' ELSE 'Small' END FROM transactions;SELECT transaction_id, transaction_amount, CASE WHEN transaction_amount > 10000 THEN 'Large' WHEN transaction_amount BETWEEN 5000 AND 10000 THEN 'Medium' ELSE 'Small' END AS size FROM transactions;

The CASE statement categorizes the transaction amount into 'Large', 'Medium', and 'Small' based on the specified conditions.

Q26

Multiple Choice

Which SQL query uses the CASE statement to classify orders as 'High Priority' if the order amount is greater than 5000 and 'Normal Priority' otherwise?

SQL Code

SELECT order_id, order_amount,
CASE
 WHEN order_amount > 5000 THEN 'High Priority'
 ELSE 'Normal Priority'
END AS priority
FROM orders;

SELECT order_id, order_amount, CASE WHEN order_amount > 5000 THEN 'High Priority' ELSE 'Normal Priority' END AS priority FROM orders; SELECT order_id, priority = CASE WHEN order_amount > 5000 THEN 'High Priority' ELSE 'Normal Priority' END FROM orders; SELECT order_id, order_amount, CASE WHEN order_amount > 5000 THEN 'High Priority' ELSE 'Normal Priority' END priority FROM orders;SELECT order_id, order_amount, CASE order_amount > 5000 THEN 'High Priority' ELSE 'Normal Priority' END AS priority FROM orders;

This query uses the CASE statement to classify orders into 'High Priority' or 'Normal Priority' based on the order amount.

Q27

Multiple Choice

Determine the correct SQL query that uses the CASE statement to apply a 10% discount if the customer is 'Premium' and 5% if 'Regular'.

SQL Code

SELECT customer_id, customer_type,
CASE
 WHEN customer_type = 'Premium' THEN '10%'
 WHEN customer_type = 'Regular' THEN '5%'
 ELSE 'No Discount'
END AS discount
FROM customers;

SELECT customer_id, customer_type, CASE WHEN customer_type = 'Premium' THEN '10%' WHEN customer_type = 'Regular' THEN '5%' ELSE 'No Discount' END AS discount FROM customers; SELECT customer_id, customer_type, CASE WHEN customer_type = 'Premium' THEN '10%' ELSE '5%' END AS discount FROM customers; SELECT customer_id, discount = CASE WHEN customer_type = 'Premium' THEN '10%' WHEN customer_type = 'Regular' THEN '5%' ELSE 'No Discount' END FROM customers;SELECT customer_id, customer_type, CASE customer_type WHEN 'Premium' THEN '10%' WHEN 'Regular' THEN '5%' ELSE 'No Discount' END AS discount FROM customers;

The CASE statement is used to apply different discount percentages based on the customer type.

Q28

Multiple Choice

Which query uses the CASE statement to determine the grade of a student as 'A', 'B', 'C', or 'F' based on the score in the 'students' table?

SQL Code

SELECT student_id, score,
CASE
 WHEN score >= 90 THEN 'A'
 WHEN score >= 80 THEN 'B'
 WHEN score >= 70 THEN 'C'
 ELSE 'F'
END AS grade
FROM students;

SELECT student_id, score, CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' WHEN score >= 70 THEN 'C' ELSE 'F' END AS grade FROM students; SELECT student_id, score, CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'F' END AS grade FROM students; SELECT student_id, grade = CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' WHEN score >= 70 THEN 'C' ELSE 'F' END FROM students;SELECT student_id, score, CASE WHEN score >= 90 THEN 'A' WHEN score >= 70 THEN 'B' ELSE 'F' END AS grade FROM students;

The CASE statement categorizes the score into different grades based on the defined thresholds.

Q29

Multiple Choice

Identify the correct SQL query that uses the CASE statement to label an employee's experience level as 'Senior', 'Mid', or 'Junior' based on years of experience.

SQL Code

SELECT employee_id, years_of_experience,
CASE
 WHEN years_of_experience > 10 THEN 'Senior'
 WHEN years_of_experience BETWEEN 5 AND 10 THEN 'Mid'
 ELSE 'Junior'
END AS experience_level
FROM employees;

SELECT employee_id, years_of_experience, CASE WHEN years_of_experience > 10 THEN 'Senior' WHEN years_of_experience BETWEEN 5 AND 10 THEN 'Mid' ELSE 'Junior' END AS experience_level FROM employees; SELECT employee_id, experience_level = CASE WHEN years_of_experience > 10 THEN 'Senior' WHEN years_of_experience BETWEEN 5 AND 10 THEN 'Mid' ELSE 'Junior' END FROM employees; SELECT employee_id, years_of_experience, CASE years_of_experience WHEN > 10 THEN 'Senior' WHEN BETWEEN 5 AND 10 THEN 'Mid' ELSE 'Junior' END AS experience_level FROM employees;SELECT employee_id, years_of_experience, CASE WHEN years_of_experience > 10 THEN 'Senior' WHEN years_of_experience >= 5 THEN 'Mid' ELSE 'Junior' END AS experience_level FROM employees;

The CASE statement categorizes the experience level based on the number of years of experience.

Q30

Multiple Choice

Which SQL query correctly uses the CASE statement to display 'Above Average' if a product's price is higher than the average price, and 'Below Average' otherwise?

SQL Code

SELECT product_id, price,
CASE
 WHEN price > (SELECT AVG(price) FROM products) THEN 'Above Average'
 ELSE 'Below Average'
END AS price_category
FROM products;

SELECT product_id, price, CASE WHEN price > (SELECT AVG(price) FROM products) THEN 'Above Average' ELSE 'Below Average' END AS price_category FROM products; SELECT product_id, price_category = CASE WHEN price > (SELECT AVG(price) FROM products) THEN 'Above Average' ELSE 'Below Average' END FROM products; SELECT product_id, price, CASE WHEN price > AVG(price) THEN 'Above Average' ELSE 'Below Average' END AS price_category FROM products;SELECT product_id, price, CASE WHEN price > (SELECT AVG(price) FROM products) THEN 'Above Average' ELSE 'Below Average' END AS category FROM products;

This query uses a subquery within the CASE statement to compare each product's price to the average price of all products.

MySQL Database Quiz Questions