WHERE clause for filtering in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to filter rows where the value in Col1 is greater than 10.

Google Sheets

SELECT * WHERE Col1 [1] 10

Drag options to blanks, or click blank then click option'

A=

B>

C<=

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' will filter the wrong rows.

Using '=' will only select rows equal to 10, not greater.

The WHERE clause filters rows where Col1 is greater than 10, so the operator should be >.

2fill in blank

medium

Complete the code to filter rows where the value in Col2 is exactly 'Yes'.

Google Sheets

SELECT * WHERE Col2 [1] 'Yes'

Drag options to blanks, or click blank then click option'

A=

B>

C<>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '>' or '<' will not check for exact equality.

Using '<>' means 'not equal', which is the opposite.

The WHERE clause checks if Col2 equals 'Yes', so the operator should be =.

3fill in blank

hard

Fix the error in the code to filter rows where Col3 is not equal to 5.

Google Sheets

SELECT * WHERE Col3 [1] 5

Drag options to blanks, or click blank then click option'

A=

B>

C<>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' will select rows equal to 5, not different.

Using '>' or '<' will filter based on greater or less, not inequality.

The operator <> means 'not equal to' in Google Sheets queries.

4fill in blank

hard

Fill both blanks to filter rows where Col4 is less than 20 and Col5 equals 'Active'.

Google Sheets

SELECT * WHERE Col4 [1] 20 AND Col5 [2] 'Active'

Drag options to blanks, or click blank then click option'

A<

B>

C=

D<>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '>' instead of '<' for Col4.

Using '<>' instead of '=' for Col5.

Col4 should be less than 20, so use '<'. Col5 should equal 'Active', so use '='.

5fill in blank

hard

Fill all three blanks to filter rows where Col6 is greater than 50, Col7 is not equal to 'No', and Col8 equals 100.

Google Sheets

SELECT * WHERE Col6 [1] 50 AND Col7 [2] 'No' AND Col8 [3] 100

Drag options to blanks, or click blank then click option'

A<

B<>

C=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up '<' and '>' operators.

Using '=' instead of '<>' for 'not equal'.

Col6 should be greater than 50, so use '>'. Col7 should not equal 'No', so use '<>'. Col8 should equal 100, so use '='.

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()