Why QUERY is Google Sheets' most powerful function - Challenge Your Understanding

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this QUERY formula?

Given the data range A1:B5 with values:

A1: Name, B1: Score
A2: Alice, B2: 85
A3: Bob, B3: 90
A4: Carol, B4: 78
A5: Dave, B5: 92

What is the output of the formula:

=QUERY(A1:B5, "SELECT Col1 WHERE Col2 > 80 ORDER BY Col2 DESC")

A

Dave
Bob
Alice

B

Alice
Bob
Dave

C

Bob
Dave
Alice

D

Dave
Alice
Bob

💡 Hint

Think about filtering scores greater than 80 and sorting by score descending.

The QUERY selects names where scores are above 80 and orders them from highest to lowest score. Dave (92), Bob (90), and Alice (85) meet the condition, sorted descending.

❓ Function Choice

intermediate

Which QUERY formula sums scores by name?

You have data in A1:B6:

A1: Name, B1: Score
A2: Alice, B2: 10
A3: Bob, B3: 15
A4: Alice, B4: 20
A5: Bob, B5: 25
A6: Carol, B6: 30

Which QUERY formula correctly sums scores for each name?

A =QUERY(A1:B6, "SELECT Col1, SUM(Col2) ORDER BY Col2")

B =QUERY(A1:B6, "SELECT Col1, SUM(Col2) GROUP BY Col1")

C =QUERY(A1:B6, "SELECT Col1, Col2 SUM GROUP BY Col1")

D =QUERY(A1:B6, "SELECT Col1, SUM(Col2) WHERE Col2 > 10")

💡 Hint

Grouping is needed to sum scores by name.

Option B uses correct syntax: SELECT Col1, SUM(Col2) GROUP BY Col1. Others have syntax errors or wrong clauses.

❓ data_analysis

advanced

How many rows does this QUERY return?

Given data in A1:C5:

A1: Product, B1: Category, C1: Price
A2: Pen, B2: Stationery, C2: 1.5
A3: Pencil, B3: Stationery, C3: 0.5
A4: Mug, B4: Kitchen, C4: 5
A5: Plate, B5: Kitchen, C5: 7

What is the number of rows returned by:

=QUERY(A1:C5, "SELECT Col1, Col3 WHERE Col2 = 'Stationery' AND Col3 > 1")

A 1

B 4

C 3

D 2

💡 Hint

Filter rows where Category is Stationery and Price is greater than 1.

Only 'Pen' matches both conditions (Stationery and Price > 1). Pencil is Stationery but price is 0.5, so excluded.

🎯 Scenario

advanced

Which QUERY formula extracts unique categories sorted alphabetically?

You have data in A1:B6:

A1: Item, B1: Category
A2: Chair, B2: Furniture
A3: Table, B3: Furniture
A4: Apple, B4: Fruit
A5: Banana, B5: Fruit
A6: Pen, B6: Stationery

Which QUERY formula returns a list of unique categories sorted A to Z?

A =QUERY(A1:B6, "SELECT Col2 ORDER BY Col2")

B =QUERY(A1:B6, "SELECT UNIQUE(Col2) ORDER BY Col2")

C =QUERY(A1:B6, "SELECT Col2 GROUP BY Col2 ORDER BY Col2")

D =QUERY(A1:B6, "SELECT Col2 WHERE Col2 IS UNIQUE ORDER BY Col2")

💡 Hint

Use GROUP BY to get unique values in QUERY.

Option C correctly groups by category and orders them alphabetically. UNIQUE() and IS UNIQUE are not valid in QUERY syntax.

🧠 Conceptual

expert

What error does this QUERY formula produce?

Given data in A1:B4:

A1: Name, B1: Age
A2: John, B2: 25
A3: Jane, B3: 30
A4: Joe, B4: 22

What error occurs when using:

=QUERY(A1:B4, "SELECT A, SUM(B) GROUP BY A")

A #VALUE! error

B #DIV/0! error

C #REF! error

D #NAME? error

💡 Hint

QUERY does not support column letters like A or B; use Col1, Col2, or header names.

The query uses invalid column identifiers 'A' and 'B'. Valid options are 'Col1', 'Col2', etc., or exact header labels like 'Name'. This produces a #NAME? error.