Where clause filtering in C Sharp (C#) - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Filtering rows with WHERE clause

Given a table Employees with columns Id, Name, and Age, what will be the output of this query?

SELECT Name FROM Employees WHERE Age > 30;

C Sharp (C#)

Employees table data:
Id | Name    | Age
1  | Alice   | 28
2  | Bob     | 35
3  | Charlie | 40
4  | Diana   | 25

A ["Alice", "Bob", "Charlie", "Diana"]

B ["Alice", "Diana"]

C ["Bob", "Charlie"]

D []

💡 Hint

Think about which employees have Age greater than 30.

The WHERE clause filters rows where Age is greater than 30. Only Bob (35) and Charlie (40) satisfy this.

📝 Syntax

intermediate

Identify the syntax error in WHERE clause

Which option contains a syntax error in the WHERE clause of this SQL query?

SELECT * FROM Products WHERE Price <= 100 AND;

A SELECT * FROM Products WHERE Price <= 100;

B SELECT * FROM Products WHERE Price <= 100 OR Quantity > 10;

C SELECT * FROM Products WHERE Price <= 100 AND Quantity > 10;

D SELECT * FROM Products WHERE Price <= 100 AND;

💡 Hint

Look for incomplete logical expressions after AND.

Option D ends the WHERE clause with AND but no condition after it, causing a syntax error.

❓ optimization

advanced

Optimizing WHERE clause for performance

You want to retrieve all orders placed in 2023 from a large Orders table with an index on the OrderDate column. Which WHERE clause is most efficient?

Assume OrderDate is a datetime type.

A WHERE OrderDate &gt;= '2023-01-01' AND OrderDate &lt; '2024-01-01'

B WHERE OrderDate &gt;= '2023-01-01' OR OrderDate &lt; '2024-01-01'

C WHERE YEAR(OrderDate) = 2023

D WHERE OrderDate &gt; '2022-12-31' AND YEAR(OrderDate) = 2023

💡 Hint

Using functions on indexed columns can prevent index usage.

Option A uses a range condition that allows the database to use the index efficiently. Option A uses a function YEAR() on the column, which disables index use.

🧠 Conceptual

advanced

Understanding WHERE clause with NULL values

Given a table Students with a column GraduationDate that can be NULL, what does this query return?

SELECT * FROM Students WHERE GraduationDate != '2023-06-01';

C Sharp (C#)

Students table data:
Id | Name  | GraduationDate
1  | John  | 2023-06-01
2  | Mary  | NULL
3  | Steve | 2023-05-30

A Rows with GraduationDate not equal to '2023-06-01', excluding NULLs

B Rows with GraduationDate not equal to '2023-06-01', including NULLs

C Only rows where GraduationDate is NULL

D No rows, because NULL comparison returns false

💡 Hint

Remember how SQL treats NULL in comparisons.

In SQL, NULL compared with any value using != returns UNKNOWN, which is treated as false in WHERE. So rows with NULL GraduationDate are excluded.

🔧 Debug

expert

Debugging unexpected WHERE clause behavior

You run this query:

SELECT * FROM Employees WHERE Department = 'Sales' OR 'Marketing';

It returns all rows, not just Sales or Marketing. Why?

A The Department column does not contain 'Marketing' values.

B The condition 'Marketing' is treated as a constant TRUE, so the WHERE clause always passes.

C The query is missing parentheses around the OR conditions.

D The OR operator is invalid here and causes a syntax error.

💡 Hint

Check how SQL evaluates non-boolean expressions in WHERE.

The string 'Marketing' alone is a truthy constant, so the WHERE clause is effectively always true, returning all rows.