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.

Practice

1. What does the WHERE clause do in a SQL query?

easy

A. Filters rows based on a specified condition

B. Sorts the rows in ascending order

C. Joins two tables together

D. Creates a new table in the database

Solution

1. Step 1: Understand the purpose of WHERE clause

   The WHERE clause is used to filter rows that meet a specific condition in a SELECT query.

2. Step 2: Differentiate from other SQL clauses

   Sorting is done by ORDER BY, joining by JOIN, and creating tables by CREATE TABLE, so these are not correct.

3. Final Answer:

   Filters rows based on a specified condition -> Option A

4. Quick Check:

   WHERE clause = filters rows [OK]

Hint: WHERE filters rows by condition, not sorting or joining [OK]

Common Mistakes:

• Confusing WHERE with ORDER BY
• Thinking WHERE creates or joins tables
• Assuming WHERE changes table structure

2. Which of the following is the correct syntax to select all columns from a table named employees where the age is greater than 30?

easy

A. SELECT * FROM employees WHERE age => 30;

B. SELECT * FROM employees WHERE age > 30;

C. SELECT * employees WHERE age > 30;

D. SELECT * FROM employees WHERE age < 30;

Solution

1. Step 1: Check the basic SELECT syntax

   The correct syntax to select all columns is SELECT * FROM table_name.

2. Step 2: Verify the WHERE condition syntax

   The comparison operator for greater than is >, not =>. Also, the condition should match the question (age > 30).

3. Final Answer:

   SELECT * FROM employees WHERE age > 30; -> Option B

4. Quick Check:

   Correct syntax with > operator [OK]

Hint: Use > for greater than, syntax: SELECT * FROM table WHERE condition [OK]

Common Mistakes:

• Using => instead of >
• Omitting FROM keyword
• Using wrong comparison operator

3. Given the table products with columns id, name, and price, what will the query SELECT name FROM products WHERE price <= 50; return?

medium

A. All product names with price less than or equal to 50

B. All product names with price greater than 50

C. All product names regardless of price

D. Syntax error due to <= operator

Solution

1. Step 1: Understand the WHERE condition

   The condition price <= 50 means selecting rows where price is less than or equal to 50.

2. Step 2: Identify the selected columns

   The query selects only the name column from those filtered rows.

3. Final Answer:

   All product names with price less than or equal to 50 -> Option A

4. Quick Check:

   price <= 50 filters products [OK]

Hint: <= means less than or equal, filters rows accordingly [OK]

Common Mistakes:

• Confusing <= with >= operator
• Assuming it returns all products
• Thinking <= causes syntax error

4. Identify the error in the following query: SELECT * FROM orders WHERE status = 'shipped' OR;

medium

A. Incorrect use of single quotes around 'shipped'

B. Using * instead of column names

C. Missing semicolon at the end

D. Missing condition after OR operator

Solution

1. Step 1: Analyze the WHERE clause

   The WHERE clause ends with OR but has no condition after it, which is invalid syntax.

2. Step 2: Check other parts of the query

   Single quotes around 'shipped' are correct, semicolon is optional, and * is valid to select all columns.

3. Final Answer:

   Missing condition after OR operator -> Option D

4. Quick Check:

   OR must be followed by a condition [OK]

Hint: OR must always have conditions on both sides [OK]

Common Mistakes:

• Leaving OR without a following condition
• Thinking quotes around strings cause error
• Assuming semicolon is mandatory

5. You have a table students with columns id, name, and score. You want to select students who scored between 70 and 90 inclusive. Which query correctly uses the WHERE clause to achieve this?

hard

A. SELECT * FROM students WHERE score > 70 AND score < 90;

B. SELECT * FROM students WHERE score > 70 OR score < 90;

C. SELECT * FROM students WHERE score BETWEEN 70 AND 90;

D. SELECT * FROM students WHERE score = 70 AND score = 90;

Solution

1. Step 1: Understand the condition for scores between 70 and 90 inclusive

   We want scores greater than or equal to 70 and less than or equal to 90.

2. Step 2: Evaluate each option

   SELECT * FROM students WHERE score > 70 AND score < 90; uses > and < which excludes the boundary values 70 and 90, incorrect. SELECT * FROM students WHERE score > 70 OR score < 90; uses OR which is incorrect because it includes scores outside the range. SELECT * FROM students WHERE score BETWEEN 70 AND 90; uses BETWEEN which is a concise way to express the inclusive range. SELECT * FROM students WHERE score = 70 AND score = 90; checks if score equals both 70 and 90 simultaneously, which is impossible.

3. Step 3: Choose the best correct option

   BETWEEN is the simplest and most readable way to filter inclusive ranges.

4. Final Answer:

   SELECT * FROM students WHERE score BETWEEN 70 AND 90; -> Option C

5. Quick Check:

   BETWEEN includes boundaries, perfect for range filtering [OK]

Hint: Use BETWEEN for inclusive ranges, AND for combined conditions [OK]

Common Mistakes:

• Using OR instead of AND for range conditions
• Confusing BETWEEN boundaries as exclusive
• Using impossible conditions with AND equals