QUERY function basics in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of QUERY with simple SELECT

Given a table in range A1:B5 with headers in row 1:

A1: Name
A2: Alice
A3: Bob
A4: Carol
A5: Dave

B1: Score
B2: 85
B3: 90
B4: 78
B5: 92

What is the output of this formula?

=QUERY(A1:B5, "SELECT A")

A Name<br>Alice<br>Bob<br>Carol<br>Dave

B Alice<br>Bob<br>Carol<br>Dave

C Score<br>85<br>90<br>78<br>92

D Name<br>Score

💡 Hint

Remember QUERY includes headers by default.

The QUERY function returns the selected column including the header row. Selecting column A returns the header 'Name' and all names below it.

📊 Formula Result

intermediate

Filtering rows with QUERY

Using the same table as before, what is the output of this formula?

=QUERY(A1:B5, "SELECT A, B WHERE B > 80")

A Name | Score<br>Alice | 85<br>Bob | 90<br>Dave | 92

B Name | Score<br>Bob | 90<br>Carol | 78<br>Dave | 92

C Alice | 85<br>Bob | 90<br>Dave | 92

D Name | Score<br>Alice | 85<br>Bob | 90<br>Carol | 78<br>Dave | 92

💡 Hint

Look for scores greater than 80 only.

The QUERY filters rows where Score (column B) is greater than 80, including the header row.

❓ Function Choice

advanced

Choosing correct QUERY to sort data

You want to list all names and scores sorted by score descending from the same table.
Which QUERY formula will do this correctly?

A =QUERY(A1:B5, "SELECT A, B ORDER BY A DESC")

B =QUERY(A1:B5, "SELECT A, B SORT BY B DESC")

C =QUERY(A1:B5, "SELECT A, B ORDER BY B DESC")

D =QUERY(A1:B5, "SELECT A, B ORDER B DESC")

💡 Hint

The correct syntax uses ORDER BY to sort.

Only option C uses the correct syntax 'ORDER BY B DESC' to sort by score descending.

❓ data_analysis

advanced

Counting rows with QUERY

Using the same table, which QUERY formula returns the number of students with scores above 80?

A =QUERY(A1:B5, "SELECT COUNT(B) LABEL COUNT(B) 'Count' WHERE B > 80")

B =QUERY(A1:B5, "SELECT COUNT(A) WHERE B > 80")

C =QUERY(A1:B5, "SELECT COUNT(B) GROUP BY B > 80")

D =QUERY(A1:B5, "SELECT COUNT(B) WHERE B > 80")

💡 Hint

COUNT counts non-empty cells in the selected column.

Option D correctly counts the number of rows where B > 80. The WHERE clause filters first, then COUNT counts.

🎯 Scenario

expert

Extracting unique names with QUERY

You have a table with duplicate names in column A and scores in column B. You want to get a list of unique names only.
Which QUERY formula will produce a list of unique names including the header?

A =QUERY(A1:B10, "SELECT UNIQUE(A)")

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

C =QUERY(A1:B10, "SELECT A WHERE A IS UNIQUE")

D =QUERY(A1:B10, "SELECT A LABEL A 'Unique Names'")

💡 Hint

Use GROUP BY to get unique values in QUERY.

QUERY does not support UNIQUE function inside. GROUP BY on column A returns unique names with header.

Practice

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

easy

A. It deletes rows based on conditions.

B. It creates charts automatically from your data.

C. It lets you ask questions to your data using a simple language.

D. It sorts data alphabetically only.

Solution

1. Step 1: Understand the purpose of QUERY

   The QUERY function is designed to let you ask questions to your data using a language similar to SQL.

2. Step 2: Compare options with QUERY's function

   The other options describe other functions or actions, but QUERY specifically helps to filter, select, and group data by asking questions.

3. Final Answer:

   It lets you ask questions to your data using a simple language. -> Option C

4. Quick Check:

   QUERY = ask questions to data [OK]

Hint: QUERY lets you ask questions to your data in words [OK]

Common Mistakes:

• Thinking QUERY creates charts
• Confusing QUERY with sorting only
• Assuming QUERY deletes data

2. Which of these is the correct syntax to select column A and B from range A1:C10 using QUERY?

easy

A. =QUERY(A1:C10, "SELECT Col1, Col2")

B. =QUERY(A1:C10, SELECT A, B)

C. =QUERY(A1:C10, 'SELECT A, B')

D. =QUERY(A1:C10, "SELECT 1, 2")

Solution

1. Step 1: Check the query string format

   The query must be a text string inside double quotes, so "SELECT Col1, Col2" is correct.

2. Step 2: Validate the column references

   Columns are referenced by ColN (Col1 for column A, Col2 for B), not plain numbers in QUERY, so "SELECT 1, 2" is invalid.

3. Final Answer:

   =QUERY(A1:C10, "SELECT Col1, Col2") -> Option A

4. Quick Check:

   Query string in quotes + ColN = correct syntax [OK]

Hint: Always put query inside double quotes with Col1, Col2 [OK]

Common Mistakes:

• Forgetting quotes around the query
• Using single quotes instead of double quotes
• Using numbers instead of ColN

3. Given this data in A1:B4:

A | B
1 | 10
2 | 20
3 | 30
4 | 40

What is the result of =QUERY(A1:B4, "SELECT A WHERE B > 20")?

medium

A. [3, 4]

B. [1, 2]

C. [2, 3, 4]

D. [10, 20, 30, 40]

Solution

1. Step 1: Understand the query condition

   The query selects column A where column B is greater than 20. Values in B are 10, 20, 30, 40.

2. Step 2: Filter rows where B > 20

   Only rows with B=30 and B=40 satisfy this, so corresponding A values are 3 and 4.

3. Final Answer:

   [3, 4] -> Option A

4. Quick Check:

   Filter B > 20 gives A = 3,4 [OK]

Hint: Filter rows by condition, then pick column values [OK]

Common Mistakes:

• Including rows where B equals 20
• Returning values from wrong column
• Confusing output format

4. What is wrong with this QUERY formula?
=QUERY(A1:C10, "SELECT A WHERE B = 'Apple')"

medium

A. Using single quotes inside double quotes is not allowed.

B. Missing closing double quote for the query string.

C. Column B cannot be used in WHERE clause.

D. The range A1:C10 is too large for QUERY.

Solution

1. Step 1: Check the quotes in the query string

   The query string starts with a double quote but ends with a single quote before the closing double quote, causing a syntax error.

2. Step 2: Identify correct quote usage

   Inside the query string, single quotes are allowed around text values, but the entire query must be enclosed properly in double quotes.

3. Final Answer:

   Missing closing double quote for the query string. -> Option B

4. Quick Check:

   Query string must start and end with double quotes [OK]

Hint: Check matching quotes around the whole query string [OK]

Common Mistakes:

• Confusing inner single quotes with outer quotes
• Thinking column B can't be used in WHERE
• Assuming range size causes errors

5. You have sales data in A1:C6:

Product | Region | Sales
Apple   | East   | 100
Banana  | West   | 150
Apple   | West   | 200
Banana  | East   | 120
Apple   | East   | 130
Banana  | West   | 170

Which QUERY formula correctly shows total sales per product?

hard

A. =QUERY(A1:C6, "SELECT A, SUM(C) GROUP BY A")

B. =QUERY(A1:C6, "SELECT A, SUM(C) GROUP BY B")

C. =QUERY(A1:C6, "SELECT A, SUM(C) WHERE Sales > 100")

D. =QUERY(A1:C6, "SELECT Product, SUM(Sales) GROUP BY Product")

Solution

1. Step 1: Identify correct column labels and aggregation

   Headers are Product, Region, Sales. To get total sales per product, select Product and SUM(Sales) grouped by Product.

2. Step 2: Check query syntax and grouping

   =QUERY(A1:C6, "SELECT Product, SUM(Sales) GROUP BY Product") uses correct header labels and groups by product. =QUERY(A1:C6, "SELECT A, SUM(C) GROUP BY A") uses letters A,C which do not match headers. =QUERY(A1:C6, "SELECT A, SUM(C) WHERE Sales > 100") misses GROUP BY. =QUERY(A1:C6, "SELECT A, SUM(C) GROUP BY B") groups by Region (B).

3. Final Answer:

   =QUERY(A1:C6, "SELECT Product, SUM(Sales) GROUP BY Product") -> Option D

4. Quick Check:

   Use header labels + GROUP BY product for totals [OK]

Hint: Use column header names and GROUP BY to sum per group [OK]

Common Mistakes:

• Using column letters instead of header names
• Forgetting GROUP BY clause
• Grouping by wrong column