Comparison operators in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of comparison with NULL using = operator

Consider the following SQL query:

SELECT * FROM employees WHERE manager_id = NULL;

What will be the result of this query?

MySQL

SELECT * FROM employees WHERE manager_id = NULL;

A Returns no rows

B Returns all rows where manager_id is NULL

C Returns all rows regardless of manager_id

D Raises a syntax error

💡 Hint

Remember how NULL behaves in comparisons in SQL.

In SQL, comparing anything to NULL using = returns UNKNOWN, which is treated as false in WHERE clause, so no rows are returned.

❓ query_result

intermediate

Result of BETWEEN operator with boundary values

Given a table 'products' with a column 'price', what rows will this query return?

SELECT * FROM products WHERE price BETWEEN 10 AND 20;

Specifically, will it include products priced exactly 10 or 20?

MySQL

SELECT * FROM products WHERE price BETWEEN 10 AND 20;

A Includes products priced between 10 and 20 but excludes 10

B Excludes products priced exactly 10 and 20

C Includes products priced exactly 10 and 20

D Includes products priced between 10 and 20 but excludes 20

💡 Hint

BETWEEN includes the boundary values in SQL.

The BETWEEN operator in SQL is inclusive, so it includes the boundary values 10 and 20.

📝 Syntax

advanced

Identify the invalid comparison operator usage

Which of the following SQL WHERE clauses will cause a syntax error?

A WHERE name =! 'John'

B WHERE salary != 50000

C WHERE age <> 30

D WHERE score >= 75

💡 Hint

Check the correct syntax for 'not equal' in SQL.

The operator '=!' is invalid in SQL. The correct 'not equal' operators are '<>' or '!='.

❓ optimization

advanced

Optimizing a query with multiple OR conditions

You have a table 'orders' with a column 'status'. Which query is more efficient to find orders with status 'pending', 'processing', or 'shipped'?

A SELECT * FROM orders WHERE status = 'pending' OR status = 'processing' OR status = 'shipped';

B SELECT * FROM orders WHERE status BETWEEN 'pending' AND 'shipped';

C SELECT * FROM orders WHERE status LIKE 'pending' OR status LIKE 'processing' OR status LIKE 'shipped';

D SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped');

💡 Hint

Consider readability and how the database engine optimizes IN vs multiple ORs.

Using IN is more concise and often optimized better by the database engine than multiple OR conditions.

🧠 Conceptual

expert

Understanding three-valued logic in SQL comparisons

In SQL, what is the result of the expression 5 = NULL and why?

A TRUE, because 5 equals NULL

B UNKNOWN, because NULL represents an unknown value

C FALSE, because 5 does not equal NULL

D Syntax error, because NULL cannot be compared

💡 Hint

Think about how SQL treats NULL in logical expressions.

In SQL, any comparison with NULL results in UNKNOWN, which is treated as false in WHERE clauses but is distinct from TRUE or FALSE.