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.

Practice

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

easy

A. It adds new columns to the data.

B. It sorts the data in ascending order.

C. It filters rows based on a condition.

D. It chooses which columns to show from the data.

Solution

1. Step 1: Understand the role of SELECT in QUERY

   The SELECT clause specifies which columns to display from the data range.

2. Step 2: Differentiate from other clauses

   Sorting is done by ORDER BY, filtering by WHERE, and adding columns is not done by SELECT.

3. Final Answer:

   It chooses which columns to show from the data. -> Option D

4. Quick Check:

   SELECT = choose columns [OK]

Hint: SELECT picks columns to show, not rows or sorting [OK]

Common Mistakes:

• Confusing SELECT with WHERE clause
• Thinking SELECT sorts data
• Assuming SELECT adds new columns

2. Which of the following is the correct syntax to select columns A and C using the QUERY function?

easy

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

B. =QUERY(A1:C10, "SELECT 1, 3")

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

D. =QUERY(A1:C10, "SELECT A C")

Solution

1. Step 1: Check correct column letters in SELECT

   Columns are referenced by letters like A, B, C. To select columns A and C, use "SELECT A, C" with a comma.

2. Step 2: Verify syntax correctness

   =QUERY(A1:C10, "SELECT A, C") uses correct syntax with comma and column letters. =QUERY(A1:C10, "SELECT 1, 3") uses numbers which is invalid. =QUERY(A1:C10, "SELECT B, D") uses B and D which are wrong columns. =QUERY(A1:C10, "SELECT A C") misses comma between columns.

3. Final Answer:

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

4. Quick Check:

   Use column letters with commas in SELECT [OK]

Hint: Use column letters with commas in SELECT clause [OK]

Common Mistakes:

• Using numbers instead of letters for columns
• Omitting commas between columns
• Selecting columns not in the data range

3. Given the data in A1:C4 as:

A | B | C
1 | 2 | 3
4 | 5 | 6
7 | 8 | 9

What will be the output of =QUERY(A1:C4, "SELECT B, C")?

medium

A. 2 3
5 6
8 9

B. B C
2 3
5 6
8 9

C. A B
1 2
4 5
7 8

D. 1 2
4 5
7 8

Solution

1. Step 1: Identify columns B and C data

   Column B values are 2, 5, 8 and column C values are 3, 6, 9 from rows 2 to 4.

2. Step 2: Understand QUERY output format

   QUERY includes headers by default, so output shows header row "B C" followed by data rows.

3. Final Answer:

   B C 2 3 5 6 8 9 -> Option B

4. Quick Check:

   QUERY shows headers and selected columns [OK]

Hint: QUERY outputs headers by default with selected columns [OK]

Common Mistakes:

• Ignoring headers in output
• Selecting wrong columns
• Mixing up row and column data

4. The formula =QUERY(A1:C10, "SELECT A B") returns an error. What is the fix?

medium

A. Add a comma between A and B: "SELECT A, B"

B. Change A and B to numbers: "SELECT 1 2"

C. Remove the SELECT keyword

D. Use lowercase letters: "select a b"

Solution

1. Step 1: Identify syntax error in SELECT clause

   The columns A and B must be separated by a comma in the SELECT clause.

2. Step 2: Correct the syntax

   Adding a comma between A and B fixes the syntax: "SELECT A, B".

3. Final Answer:

   Add a comma between A and B: "SELECT A, B" -> Option A

4. Quick Check:

   Columns must be comma-separated in SELECT [OK]

Hint: Separate columns with commas in SELECT clause [OK]

Common Mistakes:

• Omitting commas between columns
• Using numbers instead of letters
• Removing SELECT keyword

5. You have a table with columns Date (A), Product (B), and Sales (C). You want to use QUERY to show only the Product and Sales columns but sorted by Sales descending. Which formula correctly uses SELECT and ORDER BY?

hard

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

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

C. =QUERY(A1:C100, "SELECT B, C ORDER BY B DESC")

D. =QUERY(A1:C100, "SELECT B, C ORDER BY A DESC")

Solution

1. Step 1: Identify columns to select

   You want to show Product (B) and Sales (C), so SELECT B, C is correct.

2. Step 2: Sort by Sales descending

   ORDER BY C DESC sorts by Sales column descending.

3. Step 3: Verify full formula correctness

   =QUERY(A1:C100, "SELECT B, C ORDER BY C DESC") correctly combines SELECT and ORDER BY clauses. Other options select wrong columns or order by wrong columns.

4. Final Answer:

   =QUERY(A1:C100, "SELECT B, C ORDER BY C DESC") -> Option A

5. Quick Check:

   SELECT columns + ORDER BY sales DESC = correct [OK]

Hint: Use SELECT for columns, ORDER BY for sorting in QUERY [OK]

Common Mistakes:

• Ordering by wrong column
• Selecting wrong columns
• Missing ORDER BY clause