WHERE clause for filtering in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Filtering rows with WHERE clause in Google Sheets QUERY

Given a sheet named 'Sales' with columns Date, Product, and Amount, which QUERY formula filters rows where Amount is greater than 100?

Google Sheets

QUERY(Sales!A:C, "SELECT A, B, C WHERE C > 100", 1)

A QUERY(Sales!A:C, "SELECT A, B, C WHERE Amount > 100", 1)

B QUERY(Sales!A:C, "SELECT A, B, C WHERE C > 100", 1)

C QUERY(Sales!A:C, "SELECT A, B, C WHERE C >= 100", 1)

D QUERY(Sales!A:C, "SELECT A, B, C WHERE B > 100", 1)

💡 Hint

Remember that column letters in QUERY use letters like A, B, C, not column names.

In Google Sheets QUERY, you filter by column letters, not column headers. So 'WHERE C > 100' filters rows where the third column (Amount) is greater than 100.

🔧 Formula Fix

intermediate

Identify the syntax error in this QUERY with WHERE clause

Which QUERY formula has a syntax error when filtering rows where Product equals 'Apple'?

A QUERY(Sales!A:C, "SELECT A, B, C WHERE B == 'Apple'", 1)

B )1 ,"'elppA' = B EREHW C ,B ,A TCELES" ,C:A!selaS(YREUQ

C QUERY(Sales!A:C, "SELECT A, B, C WHERE B = 'Apple'", 1)

D QUERY(Sales!A:C, "SELECT A, B, C WHERE B = \"Apple\"", 1)

💡 Hint

Check the operator used for equality in QUERY language.

In QUERY language, the equality operator is a single '=' sign. Using '==' causes a syntax error.

❓ optimization

advanced

Optimizing QUERY with multiple WHERE conditions

Which QUERY formula efficiently filters rows where Amount is greater than 50 and Product is 'Banana'?

A QUERY(Sales!A:C, "SELECT A, B, C WHERE C > 50 OR B = 'Banana'", 1)

B QUERY(Sales!A:C, "SELECT A, B, C WHERE C > 50 AND B = Banana", 1)

C QUERY(Sales!A:C, "SELECT A, B, C WHERE C > 50 AND B = 'Banana'", 1)

D QUERY(Sales!A:C, "SELECT A, B, C WHERE C > 50 AND B == 'Banana'", 1)

💡 Hint

Use AND to combine conditions and single '=' for equality.

Option C correctly uses AND to combine conditions and uses single '=' with quotes around 'Banana'.

🔧 Formula Fix

advanced

Why does this QUERY return no rows?

Given the formula QUERY(Sales!A:C, "SELECT A, B, C WHERE C > 100 AND B = 'Orange'", 1), why might it return no rows even if data exists?

A Because the QUERY function requires numeric columns to be referenced by name, not letter.

B Because the WHERE clause uses AND instead of OR, which is invalid syntax.

C Because column C is text, so '> 100' causes an error and returns no rows.

D Because the data has no rows where Amount is greater than 100 and Product is exactly 'Orange'.

💡 Hint

Check if the data actually matches both conditions at the same time.

If no rows satisfy both conditions simultaneously, QUERY returns no rows. The syntax and column references are correct.

🧠 Conceptual

expert

Understanding WHERE clause behavior with NULL or empty cells

In Google Sheets QUERY, what happens when you filter with WHERE C > 0 but some rows in column C are empty or contain text?

A Rows with empty or text values in column C are excluded because they do not satisfy the numeric condition.

B QUERY throws an error because of mixed data types in column C.

C Rows with empty cells are included, but rows with text are excluded.

D Rows with empty or text values are treated as zero and included if zero > 0 is true.

💡 Hint

Think about how QUERY treats non-numeric values in numeric comparisons.

QUERY excludes rows where the condition cannot be evaluated as true, so empty or text cells in numeric comparisons are excluded.

Practice

1. What does the WHERE clause do in a Google Sheets QUERY function?

easy

A. It sorts the data alphabetically.

B. It filters rows based on a condition.

C. It adds new columns to the data.

D. It changes the data format.

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 options with WHERE clause function

   Sorting, adding columns, or changing format are not done by WHERE; filtering rows is correct.

3. Final Answer:

   It filters rows based on a condition. -> Option B

4. Quick Check:

   WHERE clause = filters rows [OK]

Hint: WHERE filters rows by condition inside QUERY [OK]

Common Mistakes:

• Confusing WHERE with sorting or formatting
• Thinking WHERE adds columns
• Assuming WHERE changes data types

2. Which of the following is the correct syntax to filter rows where column A equals 100 in a QUERY function?

easy

A. SELECT * WHERE A = 100

B. SELECT * WHERE A == 100

C. SELECT * WHERE A equals 100

D. SELECT * WHERE A := 100

Solution

1. Step 1: Recall QUERY syntax for equality

   In QUERY, single equals sign (=) is used to compare values.

2. Step 2: Check each option's syntax

   A uses word 'equals', invalid; B uses '==', invalid; C uses '=', correct; D uses ':=', invalid in QUERY.

3. Final Answer:

   SELECT * WHERE A = 100 -> Option A

4. Quick Check:

   Use '=' for equality in WHERE [OK]

Hint: Use single '=' for equality in QUERY WHERE [OK]

Common Mistakes:

• Using '==' instead of '='
• Writing 'equals' as word
• Using ':=' or other operators

3. Given this QUERY: =QUERY(A1:C5, "SELECT A, B WHERE C > 50") and data:
A=Name, B=Score, C=Age
Rows:
1: John, 80, 45
2: Mary, 90, 55
3: Alex, 70, 60
4: Lisa, 85, 40
What rows will the query return?

medium

A. Mary and Alex

B. No rows

C. All rows

D. John and Lisa

Solution

1. Step 1: Understand the WHERE condition

   The query filters rows where column C (Age) is greater than 50.

2. Step 2: Check each row's Age value

   John (45) and Lisa (40) are not >50; Mary (55) and Alex (60) are >50, so only Mary and Alex are included.

3. Final Answer:

   Mary and Alex -> Option A

4. Quick Check:

   Filter C > 50 returns Mary, Alex [OK]

Hint: Check numeric condition on column values carefully [OK]

Common Mistakes:

• Including rows with C ≤ 50
• Confusing column letters
• Assuming all rows return

4. What is wrong with this QUERY formula? =QUERY(A1:B10, "SELECT A, B WHERE B > 20 AND")

medium

A. SELECT must include C column

B. Incorrect column letters

C. WHERE clause cannot use AND

D. Missing condition after AND

Solution

1. Step 1: Analyze the WHERE clause syntax

   The WHERE clause ends with 'AND' but no condition follows, which is invalid syntax.

2. Step 2: Check other options

   Column letters A and B are valid; AND is allowed but must be followed by a condition; SELECT does not require column C.

3. Final Answer:

   Missing condition after AND -> Option D

4. Quick Check:

   AND must have conditions on both sides [OK]

Hint: Ensure AND has conditions on both sides [OK]

Common Mistakes:

• Leaving AND at end without condition
• Misnaming columns
• Thinking AND is not allowed

5. You want to filter rows where column B contains the text 'apple' (case insensitive) using QUERY. Which WHERE clause is correct?

hard

A. WHERE lower(B) = 'apple'

B. WHERE B contains 'apple'

C. WHERE lower(B) contains 'apple'

D. WHERE B = 'apple'

Solution

1. Step 1: Understand case-insensitive text filtering

   To ignore case, convert text to lowercase using lower(), then check if it contains 'apple'.

2. Step 2: Evaluate each option

   A uses equality after lower() for exact match only; C uses contains without lower(), case-sensitive; D is case-sensitive exact equality; B uses lower() with contains correctly for case-insensitive partial match.

3. Final Answer:

   WHERE lower(B) contains 'apple' -> Option C

4. Quick Check:

   Use lower() with contains for case-insensitive filter [OK]

Hint: Use lower() with contains for case-insensitive text filter [OK]

Common Mistakes:

• Using = instead of contains for partial match
• Ignoring case sensitivity
• Using contains without lower()