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.