SELECT clause in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of SELECT clause with column filtering

Given the data range A1:C4 with headers in row 1, what is the output of this formula?

=QUERY(A1:C4, "SELECT A, C WHERE B > 50")

Data:
A1: Name, B1: Score, C1: Grade
A2: Alice, B2: 45, C2: B
A3: Bob, B3: 75, C3: A
A4: Carol, B4: 60, C4: B+

A

Score | Grade
75    | A
60    | B+

B

Name | Grade
Bob  | A
Carol| B+

C

Name | Grade
Alice| B
Bob  | A
Carol| B+

D

Name | Score | Grade
Bob  | 75    | A
Carol| 60    | B+

💡 Hint

Remember SELECT chooses columns, WHERE filters rows.

The formula selects columns A and C only, and filters rows where column B (Score) is greater than 50. Alice has 45, so excluded. Bob and Carol remain with their Name and Grade.

❓ Function Choice

intermediate

Choosing correct SELECT clause for multiple conditions

Which QUERY formula correctly selects Name and Score for rows where Score is between 50 and 80 (inclusive)?

A =QUERY(A1:C4, "SELECT A, B WHERE B >= 50 AND B <= 80")

B =QUERY(A1:C4, "SELECT A, B WHERE B > 50 OR B < 80")

C )"08 =< B DNA 05 => B EREHW B ,A TCELES" ,4C:1A(YREUQ=

D =QUERY(A1:C4, "SELECT A, B WHERE B = 50 AND B = 80")

💡 Hint

Use AND to combine conditions for a range.

Option A correctly uses AND with >= and <= to filter scores between 50 and 80 inclusive. Option A uses OR which includes all rows. Option A uses BETWEEN which is not supported in Google Sheets QUERY. Option A is impossible condition.

❓ data_analysis

advanced

Count rows with specific SELECT and WHERE

Using QUERY on range A1:C6 with headers Name, Score, Grade, which formula returns the count of rows where Grade is 'A'?

A =QUERY(A1:C6, "SELECT COUNT(A) WHERE C = 'A'", 1)

B =QUERY(A1:C6, "SELECT COUNT(B) WHERE C = 'A'", 1)

C =QUERY(A1:C6, "SELECT COUNT(C) WHERE C = 'A'", 1)

D =QUERY(A1:C6, "SELECT COUNT(*) WHERE C = 'A'", 1)

💡 Hint

COUNT(column) counts non-empty values in that column.

COUNT(C) counts non-empty values in column C where Grade = 'A'. COUNT(A) counts names but may be empty if missing. COUNT(*) is not supported in Google Sheets QUERY.

🎯 Scenario

advanced

Selecting and sorting with QUERY

You want to select columns Name and Score from A1:C10 and sort by Score descending. Which QUERY formula achieves this?

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

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

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

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

💡 Hint

ORDER BY is the correct syntax for sorting.

Option A uses correct syntax: ORDER BY column DESC. Others have syntax errors or missing keywords.

📊 Formula Result

expert

Output of QUERY with aggregation and GROUP BY

Given data in A1:C7 with columns Name, Department, Salary, what is the output of:

=QUERY(A1:C7, "SELECT B, SUM(C) GROUP BY B ORDER BY SUM(C) DESC", 1)

Data:
A2: John, B2: Sales, C2: 5000
A3: Jane, B3: HR, C3: 6000
A4: Mike, B4: Sales, C4: 7000
A5: Anna, B5: HR, C5: 4000
A6: Tom, B6: IT, C6: 3000
A7: Sue, B7: IT, C7: 3500

A

Name | SUM
John | 5000
Jane | 6000
Mike | 7000
Anna | 4000
Tom  | 3000
Sue  | 3500

B

Department | SUM
IT        | 6500
HR        | 10000
Sales     | 12000

C

Department | Salary
Sales     | 7000
HR        | 6000
IT        | 3500

D

Department | SUM
Sales     | 12000
HR        | 10000
IT        | 6500

💡 Hint

GROUP BY groups rows by Department, SUM adds salaries, ORDER BY sorts descending.

The formula groups by Department (column B), sums Salary (column C), and orders by sum descending. Sales total 12000, HR 10000, IT 6500.