WHERE clause filtering in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Filter employees with salary greater than 50000

Given a table employees with columns id, name, and salary, which query returns all employees earning more than 50000?

MySQL

SELECT * FROM employees WHERE salary > 50000;

A SELECT * FROM employees WHERE salary < 50000;

B SELECT * FROM employees WHERE salary >= 50000;

C SELECT * FROM employees WHERE salary > 50000;

D SELECT * FROM employees WHERE salary = 50000;

💡 Hint

Think about the meaning of the > operator in filtering salaries.

Option C correctly filters employees with salary strictly greater than 50000. Option C filters salaries less than 50000, Option C includes 50000 itself, and D filters only those exactly equal to 50000.

❓ query_result

intermediate

Find customers from specific cities

Given a table customers with columns id, name, and city, which query returns customers from either 'New York' or 'Los Angeles'?

MySQL

SELECT * FROM customers WHERE city = 'New York' OR city = 'Los Angeles';

A SELECT * FROM customers WHERE city = 'New York' OR city = 'Los Angeles';

B SELECT * FROM customers WHERE city IN ('New York', 'Los Angeles');

C SELECT * FROM customers WHERE city = 'New York' AND city = 'Los Angeles';

D SELECT * FROM customers WHERE city LIKE 'New York' OR city LIKE 'Los Angeles';

💡 Hint

Use OR to include multiple possible values.

Option A correctly uses OR to select customers from either city. Option A is valid SQL but not the exact query given. Option A uses AND which is impossible for one city to be both. Option A uses LIKE but without wildcards, so it behaves like =.

📝 Syntax

advanced

Identify the syntax error in this WHERE clause

Which option contains a syntax error in the WHERE clause?

MySQL

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

A SELECT * FROM orders WHERE order_date > '2023-01-01' AND order_date < '2023-12-31';

B SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

C SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date <= '2023-12-31';

D SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' TO '2023-12-31';

💡 Hint

Check the correct syntax for BETWEEN operator.

Option D uses 'TO' instead of 'AND' in BETWEEN clause, which is invalid syntax. Options B, C, and D are syntactically correct.

❓ optimization

advanced

Optimize filtering for multiple values

Which query is the most efficient way to filter rows where status is 'active', 'pending', or 'suspended'?

A SELECT * FROM users WHERE status = 'active' OR status = 'pending' OR status = 'suspended';

B SELECT * FROM users WHERE status IN ('active', 'pending', 'suspended');

C SELECT * FROM users WHERE status LIKE 'active' OR status LIKE 'pending' OR status LIKE 'suspended';

D SELECT * FROM users WHERE status = 'active' AND status = 'pending' AND status = 'suspended';

💡 Hint

Use a concise operator for multiple values.

Option B uses IN which is more concise and optimized for multiple values. Option B works but is longer. Option B is logically impossible. Option B uses LIKE unnecessarily.

🧠 Conceptual

expert

Understanding NULL filtering in WHERE clause

Given a table products with a column discount that can be NULL, which query correctly selects products with no discount?

A SELECT * FROM products WHERE discount IS NULL;

B SELECT * FROM products WHERE discount = NULL;

C SELECT * FROM products WHERE discount = 0;

D SELECT * FROM products WHERE discount != NULL;

💡 Hint

Remember how SQL treats NULL comparisons.

Option A correctly uses IS NULL to check for NULL values. Options B and D use = or != with NULL which always return false. Option A checks for zero discount, not NULL.