WHERE with OR operator in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of WHERE with OR filtering

Given the table Employees with columns id, name, and department, what rows will this query return?

SELECT * FROM Employees WHERE department = 'Sales' OR department = 'HR';

SQL

CREATE TABLE Employees (id INT, name VARCHAR(50), department VARCHAR(50));
INSERT INTO Employees VALUES (1, 'Alice', 'Sales'), (2, 'Bob', 'HR'), (3, 'Charlie', 'IT'), (4, 'Diana', 'Sales');

A [{id:1, name:'Alice', department:'Sales'}, {id:2, name:'Bob', department:'HR'}, {id:4, name:'Diana', department:'Sales'}]

B [{id:1, name:'Alice', department:'Sales'}, {id:3, name:'Charlie', department:'IT'}, {id:4, name:'Diana', department:'Sales'}]

C [{id:2, name:'Bob', department:'HR'}, {id:3, name:'Charlie', department:'IT'}]

D [{id:3, name:'Charlie', department:'IT'}]

💡 Hint

Remember OR means either condition can be true to include the row.

The query selects rows where the department is either 'Sales' or 'HR'. So employees Alice, Bob, and Diana match.

❓ query_result

intermediate

Rows returned with mixed OR conditions

Consider the table Products with columns product_id, category, and price. What rows will this query return?

SELECT * FROM Products WHERE category = 'Books' OR price < 20;

SQL

CREATE TABLE Products (product_id INT, category VARCHAR(50), price DECIMAL);
INSERT INTO Products VALUES (1, 'Books', 25), (2, 'Electronics', 15), (3, 'Books', 10), (4, 'Clothing', 30);

A [{product_id:3, category:'Books', price:10}]

B [{product_id:2, category:'Electronics', price:15}, {product_id:4, category:'Clothing', price:30}]

C [{product_id:1, category:'Books', price:25}, {product_id:2, category:'Electronics', price:15}, {product_id:3, category:'Books', price:10}]

D [{product_id:1, category:'Books', price:25}, {product_id:3, category:'Books', price:10}, {product_id:4, category:'Clothing', price:30}]

💡 Hint

Rows with category 'Books' or price less than 20 are included.

Rows 1 and 3 have category 'Books'. Row 2 has price less than 20. So rows 1, 2, and 3 are returned.

📝 Syntax

advanced

Identify the syntax error in OR condition

Which option contains a syntax error in the WHERE clause using OR operator?

A SELECT * FROM Orders WHERE status = 'Pending' OR status = 'Shipped' AND customer_id = 5;

B SELECT * FROM Orders WHERE status = 'Pending' OR;

C SELECT * FROM Orders WHERE status = 'Pending' OR status = 'Shipped';

D SELECT * FROM Orders WHERE status = 'Pending' OR status = 'Shipped'

💡 Hint

Look for incomplete conditions after OR.

Option B ends with OR but no condition after it, causing syntax error.

❓ optimization

advanced

Optimizing OR conditions in WHERE clause

Which query is more efficient when filtering rows where category is 'A' or 'B' or 'C'?

A SELECT * FROM Items WHERE category = 'A' AND category = 'B' AND category = 'C';

B SELECT * FROM Items WHERE category = 'A' OR category = 'B' OR category = 'C';

C SELECT * FROM Items WHERE category = 'A' OR category = 'B' AND category = 'C';

D SELECT * FROM Items WHERE category IN ('A', 'B', 'C');

💡 Hint

IN clause is often optimized for multiple OR conditions.

Option D uses IN which is clearer and usually optimized by SQL engines compared to multiple ORs.

🧠 Conceptual

expert

Understanding operator precedence with OR and AND

What is the result of this query on table Tasks with columns id, status, and priority?

SELECT * FROM Tasks WHERE status = 'Open' OR status = 'In Progress' AND priority = 'High';

Assuming rows:
1: (1, 'Open', 'Low')
2: (2, 'In Progress', 'High')
3: (3, 'In Progress', 'Low')
4: (4, 'Closed', 'High')

A [{id:1, status:'Open', priority:'Low'}, {id:2, status:'In Progress', priority:'High'}]

B [{id:1, status:'Open', priority:'Low'}, {id:2, status:'In Progress', priority:'High'}, {id:3, status:'In Progress', priority:'Low'}]

C [{id:1, status:'Open', priority:'Low'}]

D [{id:2, status:'In Progress', priority:'High'}, {id:4, status:'Closed', priority:'High'}]

💡 Hint

AND has higher precedence than OR in SQL.

The condition is evaluated as status = 'Open' OR (status = 'In Progress' AND priority = 'High'). So rows 1 and 2 match.

Practice

1. What does the SQL WHERE clause with the OR operator do?

easy

A. It deletes rows that meet any condition.

B. It selects rows that meet at least one of the conditions.

C. It selects rows that meet all conditions simultaneously.

D. It updates rows that meet none of the conditions.

Solution

1. Step 1: Understand the WHERE clause

   The WHERE clause filters rows based on conditions.

2. Step 2: Understand the OR operator

   OR means if any condition is true, the row is selected.

3. Final Answer:

   It selects rows that meet at least one of the conditions. -> Option B

4. Quick Check:

   WHERE with OR = any condition true [OK]

Hint: OR means any condition true selects the row [OK]

Common Mistakes:

• Thinking OR requires all conditions true
• Confusing OR with AND
• Assuming OR deletes or updates rows

2. Which of the following SQL queries correctly uses the WHERE clause with the OR operator to find employees in department 10 or 20?

easy

A. SELECT * FROM employees WHERE department_id = 10 AND 20;

B. SELECT * FROM employees WHERE department_id = (10 OR 20);

C. SELECT * FROM employees WHERE department_id = 10 OR department_id = 20;

D. SELECT * FROM employees WHERE department_id IN 10 OR 20;

Solution

1. Step 1: Check correct OR syntax

   OR must connect full conditions: column = value OR column = value.

2. Step 2: Analyze each option

   A uses AND incorrectly; C uses invalid syntax; D misuses IN without parentheses.

3. Final Answer:

   SELECT * FROM employees WHERE department_id = 10 OR department_id = 20; -> Option C

4. Quick Check:

   Correct OR syntax = SELECT * FROM employees WHERE department_id = 10 OR department_id = 20; [OK]

Hint: Use OR between full conditions, not partial expressions [OK]

Common Mistakes:

• Using AND instead of OR
• Writing OR inside parentheses incorrectly
• Misusing IN without parentheses

3. Given the table products with columns id, name, and category, what rows will this query return?

SELECT * FROM products WHERE category = 'Books' OR category = 'Toys';

medium

A. Products in either 'Books' or 'Toys' categories.

B. Only products in both 'Books' and 'Toys' categories.

C. All products except those in 'Books' or 'Toys'.

D. No products because OR is invalid here.

Solution

1. Step 1: Understand the OR condition

   The query selects rows where category is 'Books' or 'Toys'.

2. Step 2: Determine matching rows

   Rows with category 'Books' or 'Toys' will be included, others excluded.

3. Final Answer:

   Products in either 'Books' or 'Toys' categories. -> Option A

4. Quick Check:

   OR selects rows matching any condition [OK]

Hint: OR includes rows matching any listed condition [OK]

Common Mistakes:

• Thinking OR means both conditions must be true
• Assuming OR excludes matching rows
• Believing OR causes syntax error

4. Identify the error in this SQL query:

SELECT * FROM orders WHERE status = 'Pending' OR;

medium

A. Missing condition after OR operator.

B. Incorrect use of WHERE clause.

C. OR operator should be AND.

D. No error, query is valid.

Solution

1. Step 1: Analyze the WHERE clause

   The WHERE clause ends with OR but no condition follows it.

2. Step 2: Understand OR syntax

   OR must connect two conditions; missing second condition causes syntax error.

3. Final Answer:

   Missing condition after OR operator. -> Option A

4. Quick Check:

   OR needs two conditions [OK]

Hint: OR must connect two conditions; don't leave it hanging [OK]

Common Mistakes:

• Leaving OR at end without condition
• Confusing OR with AND syntax
• Ignoring syntax errors in WHERE clause

5. You have a table students with columns id, grade, and city. Write a query to select students who have grade 'A' or live in 'New York' or 'Los Angeles'. Which query is correct?

hard

A. SELECT * FROM students WHERE grade = 'A' OR city = 'New York' OR 'Los Angeles';

B. SELECT * FROM students WHERE grade = 'A' AND city = 'New York' OR 'Los Angeles';

C. SELECT * FROM students WHERE grade = 'A' OR city = 'New York' AND city = 'Los Angeles';

D. SELECT * FROM students WHERE grade = 'A' OR city IN ('New York', 'Los Angeles');

Solution

1. Step 1: Understand the conditions

   Select students with grade 'A' OR city 'New York' OR city 'Los Angeles'.

2. Step 2: Analyze each query

   A has syntax error (missing column in last condition); B has syntax error and wrong logic; C has incorrect logic due to AND; D is correct and concise using IN.

3. Final Answer:

   SELECT * FROM students WHERE grade = 'A' OR city IN ('New York', 'Los Angeles'); -> Option D

4. Quick Check:

   Use OR with IN for multiple values [OK]

Hint: Use OR with IN for multiple city values [OK]

Common Mistakes:

• Using AND between city conditions
• Omitting column name after OR
• Syntax errors with OR and IN