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.

Practice

1.

What does the WHERE clause do in a SQL query?

easy

A. Groups rows by a column

B. Filters rows based on a condition

C. Joins two tables together

D. Sorts the rows in ascending order

Solution

1. Step 1: Understand the purpose of WHERE clause

   The WHERE clause is used to select only rows that meet a specific condition.

2. Step 2: Compare with other SQL clauses

   Sorting is done by ORDER BY, joining by JOIN, grouping by GROUP BY, so WHERE is for filtering rows.

3. Final Answer:

   Filters rows based on a condition -> Option B

4. Quick Check:

   WHERE clause = filter rows [OK]

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

Common Mistakes:

• Confusing WHERE with ORDER BY
• Thinking WHERE joins tables
• Mixing WHERE with GROUP BY

2.

Which of the following is the correct syntax to filter rows where Age is greater than 30?

SELECT * FROM Users WHERE ___;

easy

A. Age > 30

B. Age = > 30

C. Age >> 30

D. Age >= 30

Solution

1. Step 1: Identify correct comparison operator

   The operator for 'greater than' is >, so 'Age > 30' is correct.

2. Step 2: Check other options for syntax errors

   'Age = > 30' and 'Age >> 30' are invalid syntax. 'Age >= 30' means 'greater or equal', not strictly greater.

3. Final Answer:

   Age > 30 -> Option A

4. Quick Check:

   Use > for greater than [OK]

Hint: Use > for greater than, >= for greater or equal [OK]

Common Mistakes:

• Using = > instead of >
• Using >> which is invalid
• Confusing > with >= operator

3.

Given the table Employees with columns Name and Salary, what rows will this query return?

SELECT Name FROM Employees WHERE Salary < 50000;

medium

A. Employees with salary less than 50000

B. Employees with salary greater than 50000

C. All employees regardless of salary

D. Employees with salary equal to 50000

Solution

1. Step 1: Understand the WHERE condition

   The condition Salary < 50000 means select rows where salary is less than 50000.

2. Step 2: Interpret the query result

   The query returns only the Name column for employees meeting that condition.

3. Final Answer:

   Employees with salary less than 50000 -> Option A

4. Quick Check:

   WHERE Salary < 50000 filters salaries below 50000 [OK]

Hint: Less than means <, so Salary < 50000 filters lower salaries [OK]

Common Mistakes:

• Confusing < with >
• Thinking it returns all employees
• Assuming it returns salary column too

4.

Identify the error in this query that tries to select users with age 18 or older:

SELECT * FROM Users WHERE Age => 18;

medium

A. WHERE clause cannot use numeric comparisons

B. Missing quotes around 18

C. The operator => is invalid; should be >= instead

D. SELECT * is not allowed with WHERE

Solution

1. Step 1: Check the comparison operator

   The operator => is not valid SQL syntax; the correct operator for 'greater or equal' is >=.

2. Step 2: Verify other parts of the query

   Numeric values like 18 do not need quotes, WHERE supports numeric comparisons, and SELECT * works with WHERE.

3. Final Answer:

   The operator => is invalid; should be >= instead -> Option C

4. Quick Check:

   Use >= for greater or equal, not => [OK]

Hint: Use >= for greater or equal, not => [OK]

Common Mistakes:

• Using => instead of >=
• Adding quotes around numbers
• Thinking WHERE can't compare numbers

5.

You want to select all products from a Products table where the Price is between 10 and 20 inclusive. Which WHERE clause is correct?

hard

A. Price BETWEEN 10 AND 20 EXCLUSIVE

B. Price > 10 AND Price < 20

C. Price >= 10 OR Price <= 20

D. Price >= 10 AND Price <= 20

Solution

1. Step 1: Understand inclusive range filtering

   Inclusive means including 10 and 20, so use >= and <= operators.

2. Step 2: Analyze each option

   Price > 10 AND Price < 20 excludes 10 and 20 (strictly greater and less). Price >= 10 OR Price <= 20 uses OR, which selects too many rows. Price BETWEEN 10 AND 20 EXCLUSIVE is invalid syntax.

3. Final Answer:

   Price >= 10 AND Price <= 20 -> Option D

4. Quick Check:

   Inclusive range uses >= and <= with AND [OK]

Hint: Use >= and <= with AND for inclusive ranges [OK]

Common Mistakes:

• Using > and < excludes boundary values
• Using OR instead of AND
• Trying invalid BETWEEN syntax