WHERE clause for filtering in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the WHERE clause in a database query?

The WHERE clause is used to filter records and return only those that meet a specific condition.

beginner

How do you write a WHERE clause to find rows where the value in column A is greater than 10?

You write: WHERE A > 10

intermediate

Can the WHERE clause be used with multiple conditions? How?

Yes, by using AND or OR to combine conditions, for example: WHERE A > 10 AND B = 'Yes'

beginner

In Google Sheets QUERY function, how do you write a WHERE clause to filter rows where column B equals 'Apple'?

Use: WHERE B = 'Apple' inside the QUERY function's query string.

beginner

What happens if you omit the WHERE clause in a query?

The query returns all rows without filtering any data.

What does the WHERE clause do in a query?

A Sorts the rows

B Joins two tables

C Filters rows based on a condition

D Deletes rows

The WHERE clause filters rows to include only those that meet the condition.

Which of these is a valid WHERE clause condition in Google Sheets QUERY to filter rows where column A is greater than 5?

A WHERE 5 > A

B WHERE > 5 A

C WHERE A =

D WHERE A > 5

The correct syntax is 'WHERE A > 5' to filter rows where column A is greater than 5.

How do you combine two conditions in a WHERE clause to require both to be true?

A Use AND

B Use OR

C Use NOT

D Use THEN

AND combines conditions so both must be true for a row to be included.

In Google Sheets QUERY, how do you filter rows where column C is exactly 'Yes'?

A WHERE C == 'Yes'

B WHERE C = 'Yes'

C WHERE C equals 'Yes'

D WHERE C contains 'Yes'

The correct syntax is WHERE C = 'Yes' to match exact text.

What will happen if you write a query without a WHERE clause?

A All rows are returned

B No rows are returned

C Only the first row is returned

D An error occurs

Without WHERE, the query returns all rows without filtering.

Explain how the WHERE clause helps in filtering data in a query.

Think about how you pick only certain items from a list.

Describe how to use multiple conditions in a WHERE clause.

Consider how you might say 'I want this AND that' or 'this OR that'.

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