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.

Practice

1. Which of the following comparison operators in MySQL means "not equal to"?

easy

A. =

B. <>

C. >=

D. <

Solution

1. Step 1: Understand the meaning of comparison operators

   The operator "<>" is used in SQL to mean "not equal to".

2. Step 2: Compare with other operators

   "=" means equal, ">=" means greater than or equal, and "<" means less than.

3. Final Answer:

   <> -> Option B

4. Quick Check:

   Not equal operator = <> [OK]

Hint: Remember: <> means not equal in SQL [OK]

Common Mistakes:

• Confusing = with not equal
• Using != which is valid but less common in MySQL
• Mixing up > and < operators

2. Which of the following is the correct syntax to check if column age is greater than 18 in a WHERE clause?

easy

A. WHERE age > 18

B. WHERE age => 18

C. WHERE age =< 18

D. WHERE age <> 18

Solution

1. Step 1: Identify the correct greater than operator

   The correct operator for "greater than" is ">". The operator ">=" means "greater than or equal to".

2. Step 2: Check syntax correctness

   "=>" and "=<" are invalid operators in SQL syntax. "<>" means not equal, which is not what we want.

3. Final Answer:

   WHERE age > 18 -> Option A

4. Quick Check:

   Greater than operator is > [OK]

Hint: Use > for greater than, not => or =< [OK]

Common Mistakes:

• Using => or =< which are invalid
• Confusing <> with greater than
• Using = instead of >

3. Given the table Employees with a column salary, what will the query SELECT * FROM Employees WHERE salary <= 50000; return?

medium

A. All employees with salary less than or equal to 50000

B. All employees with salary exactly 50000

C. All employees with salary less than 50000

D. All employees with salary greater than 50000

Solution

1. Step 1: Understand the meaning of <= operator

   The operator <= means "less than or equal to".

2. Step 2: Interpret the query condition

   The query filters employees whose salary is either less than 50000 or exactly 50000.

3. Final Answer:

   All employees with salary less than or equal to 50000 -> Option A

4. Quick Check:

   <= means less than or equal [OK]

Hint: <= means less than or equal, includes the number [OK]

Common Mistakes:

• Thinking <= means only less than
• Confusing <= with <
• Ignoring the equal part of <= operator

4. Identify the error in this MySQL query: SELECT * FROM Products WHERE price ! 100;

medium

A. Missing comparison operator after !

B. No error, query is correct

C. Missing quotes around 100

D. Incorrect use of ! for not equal, should be != or <>

Solution

1. Step 1: Analyze the use of ! operator

   In MySQL, "!" alone is not a valid comparison operator. For "not equal", use "!=" or "<>".

2. Step 2: Check the query syntax

   The query uses "price ! 100" which is invalid syntax and will cause an error.

3. Final Answer:

   Incorrect use of ! for not equal, should be != or <> -> Option D

4. Quick Check:

   Use != or <> for not equal [OK]

Hint: Use != or <> for not equal, not just ! [OK]

Common Mistakes:

• Using ! alone as not equal
• Forgetting to use != or <>
• Assuming ! means not equal in SQL

5. You want to select all customers whose age is between 20 and 30 inclusive. Which WHERE clause correctly uses comparison operators?

hard

A. WHERE age > 20 AND age < 30

B. WHERE age = 20 OR age = 30

C. WHERE age >= 20 AND age <= 30

D. WHERE age <> 20 AND age <> 30

Solution

1. Step 1: Understand the requirement for inclusive range

   Inclusive means including 20 and 30, so use >= and <= operators.

2. Step 2: Analyze each option

   WHERE age > 20 AND age < 30 excludes 20 and 30 (strictly greater and less). WHERE age = 20 OR age = 30 only selects exactly 20 or 30, not the range. WHERE age <> 20 AND age <> 30 excludes 20 and 30, opposite of requirement.

3. Final Answer:

   WHERE age >= 20 AND age <= 30 -> Option C

4. Quick Check:

   Use >= and <= for inclusive ranges [OK]

Hint: Use >= and <= for inclusive range checks [OK]

Common Mistakes:

• Using > and < excludes boundary values
• Using OR instead of AND for range
• Using <> which excludes values