WHERE with IN list in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of WHERE with IN list filtering

Given the table Employees with columns id, name, and department, what is the output of this query?

SELECT name FROM Employees WHERE department IN ('Sales', 'Marketing');

SQL

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

A Alice, Charlie

B Bob, Diana

C Alice, Bob, Charlie

D Charlie, Diana

💡 Hint

Look for employees whose department is either 'Sales' or 'Marketing'.

The WHERE clause filters rows where the department is in the list ('Sales', 'Marketing'). Only Alice (Sales) and Charlie (Marketing) match.

📝 Syntax

intermediate

Identify the syntax error in WHERE with IN list

Which option contains a syntax error in the use of the IN list in the WHERE clause?

SQL

SELECT * FROM Products WHERE category IN ('Books', 'Electronics');

A SELECT * FROM Products WHERE category IN ('Books' 'Electronics');

B SELECT * FROM Products WHERE category IN 'Books', 'Electronics';

C SELECT * FROM Products WHERE category IN ('Books', 'Electronics');

D SELECT * FROM Products WHERE category IN ('Books', 'Electronics',);

💡 Hint

Check the parentheses and commas around the list values.

Option B is missing parentheses around the list, causing a syntax error. The IN operator requires parentheses enclosing the list.

❓ optimization

advanced

Optimizing WHERE with IN list for large datasets

You have a large table Orders with millions of rows. You want to filter orders where status is in a list of 1000 possible values. Which approach is most efficient?

A Use WHERE status IN (list of 1000 values) directly in the query.

B Filter the Orders table in application code after selecting all rows.

C Use multiple OR conditions like WHERE status = val1 OR status = val2 ... for all 1000 values.

D Create a temporary table with the 1000 values and join it with Orders on status.

💡 Hint

Consider how databases optimize joins versus large IN lists.

Joining with a temporary table containing the 1000 values is more efficient than a large IN list or many OR conditions. Filtering in application code is inefficient.

🧠 Conceptual

advanced

Understanding NULL behavior with WHERE IN list

Consider a table Students with a column grade that can be NULL. What is the result of this query?

SELECT * FROM Students WHERE grade IN (NULL, 'A', 'B');

A Returns all rows regardless of grade.

B Returns rows where grade is 'A', 'B', or NULL.

C Returns rows where grade is 'A' or 'B' only, NULLs excluded.

D Returns no rows because NULL in IN list causes error.

💡 Hint

Remember how NULL comparisons behave in SQL.

NULL in an IN list does not match any value, including NULLs. So only rows with grade 'A' or 'B' are returned.

🔧 Debug

expert

Debugging unexpected results with WHERE IN list

A developer runs this query:

SELECT * FROM Inventory WHERE product_code IN ('P001', 'P002', 'P003');

But the result is empty even though these product codes exist. Which option explains the most likely cause?

A The product_code column has trailing spaces, so values don't match the list exactly.

B The IN list syntax is incorrect and causes a syntax error.

C The product_code column is numeric but the list uses strings.

D The table Inventory is empty.

💡 Hint

Think about string matching and whitespace in SQL.

If product_code values have trailing spaces, they won't match the exact strings in the IN list, causing no rows to be returned.