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.