GROUP BY Clause MCQs: 10 Solved SQL Questions with Explanations

GROUP BY questions test whether you can separate SQL's written clause order from its logical execution order. WHERE removes rows before grouping; HAVING removes completed groups after aggregation. A reliable trace starts with the surviving rows, buckets them by the grouping key, calculates each aggregate, applies HAVING, and only then projects and sorts. That sequence exposes the traps behind legal select lists, NULL-aware averages, strict comparisons such as > 80, and joins that multiply rows before grouping. COUNT(*) counts surviving rows, whereas AVG(column) ignores NULL values in its denominator. Use the Sales query as a reference while solving the exam questions. The CS Fundamentals for Placements path adds wider SQL and DBMS revision.

1. Worked GROUP BY query

Written order: SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY. Logical order: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. WHERE filters rows; HAVING filters groups. See the DBMS map.

SELECT region, COUNT(*) AS orders, SUM(amount) AS total, AVG(amount) AS average
FROM Sales
WHERE amount >= 50
GROUP BY region
HAVING SUM(amount) >= 150
ORDER BY region;

WHERE removes South | Dev | 30. North then has 50 and 70: COUNT=2, SUM=120, AVG=60. South has 90 and 80: COUNT=2, SUM=170, AVG=85. HAVING removes North because 120 is below 150:

2. GROUP BY MCQs 1-3: purpose, WHERE versus HAVING, and clause order

Question 1, IBPS 2024

What is the primary purpose of the GROUP BY clause in SQL?

• A. To sort rows in ascending or descending order

• B. To group rows with identical values in specified columns for aggregate processing

• C. To permanently remove duplicate rows from a table

• D. To filter rows before retrieval

• E. To combine records from multiple tables

Correct answer: B. To group rows with identical values in specified columns for aggregate processing.

GROUP BY creates one group per distinct key so aggregates work per group. ORDER BY sorts, WHERE filters, joins combine tables, and DISTINCT removes duplicate result rows. Only B describes grouping.

Question 2, Deloitte 2025

What is the difference between "WHERE" and "HAVING" clauses in SQL?

• A. Both are the same

• B. WHERE filters rows before grouping, HAVING filters groups after aggregation

• C. HAVING filters rows before grouping, WHERE filters groups after aggregation

• D. WHERE can be used with aggregate functions

Correct answer: B. WHERE filters rows before grouping, HAVING filters groups after aggregation.

WHERE amount >= 50 removes Dev before grouping. After North sums to 120, HAVING SUM(amount) >= 150 removes the group. Thus WHERE filters rows; HAVING filters groups.

Question 3, Kendriya Vidyalaya Sangathan 2023

Which of the following is correct sequence in a SELECT query ?

• A. SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

• B. SELECT, WHERE, FROM, GROUP BY, HAVING, ORDER BY

• C. SELECT, FROM, WHERE, HAVING, GROUP BY, ORDER BY

• D. SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING

Correct answer: A. SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

This asks for written syntax. FROM follows SELECT; WHERE precedes grouping; HAVING follows GROUP BY; sorting is last. Mnemonic S F W G H O identifies A.

3. GROUP BY MCQs 4-6: legal grouping and aggregate columns

Question 4, GATE 2012

Which of the following statements are TRUE about an SQL query?

P : An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause

Q: An SQL query can contain a HAVING clause only if it has a GROUP BY clause

R : All attributes used in the GROUP BY clause must appear in the SELECT clause

S : Not all attributes used in the GROUP BY clause need to appear in the SELECT clause

• A. P and R

• B. P and S

• C. Q and R

• D. Q and S

Correct answer: B. P and S.

Without GROUP BY, qualifying rows can form one group, allowing HAVING: P true, Q false. A grouping column need not be selected; SELECT COUNT(*) FROM Sales GROUP BY region makes S true and R false. Therefore B.

Question 5, UGC NET 2016

Consider a database table R with attributes A and B. Which of the following SQL queries is illegal ?

• A. SELECT A FROM R;

• B. SELECT A, COUNT(*) FROM R;

• C. SELECT A, COUNT(*) FROM R GROUP BY A;

• D. SELECT A, B, COUNT(*) FROM R GROUP BY A, B;

Correct answer: B. SELECT A, COUNT(*) FROM R;

In B, COUNT(*) produces one aggregate while A requests an ungrouped value, so A is undefined. C groups by A; D groups by both selected ordinary columns. B is illegal.

Question 6, Navodaya Vidyalaya Samiti 2017

Based on table CLUB, which SQL query will display earliest and latest DOJ under each TYPE?

• A. SELECT MIN(DOJ), MAX(DOJ) FROM CLUB;

• B. SELECT MIN(DOJ), MAX(DOJ) FROM CLUB GROUP BY TYPE;

• C. SELECT MIN(DOJ), MAX(DOJ), TYPE FROM CLUB GROUP BY TYPE;

• D. SELECT MIN(DOJ), MAX(DOJ), TYPE GROUP BY TYPE FROM CLUB;

Correct answer: C. SELECT MIN(DOJ), MAX(DOJ), TYPE FROM CLUB GROUP BY TYPE;

A returns one overall pair; B omits type labels. C groups by and selects TYPE, labelling each pair. D misplaces FROM. Only C satisfies both requirements.

4. GROUP BY MCQs 7-8: tracing rows, NULL, and the complete clause chain

Question 7, Navodaya Vidyalaya Samiti 2023

Which of the following command can display the SEC-wise average of the students?

• A. SELECT SEC, AVG(MARKS) FROM MYSTUDENT ORDER BY SEC;

• B. SELECT SEC, AVG(MARKS) FROM MYSTUDENT GROUP BY MARKS;

• C. SELECT SEC, AVG(MARKS) FROM MYSTUDENT GROUP BY SEC;

• D. SELECT AVG(MARKS) FROM MYSTUDENT ORDER BY SEC;

Correct answer: C. SELECT SEC, AVG(MARKS) FROM MYSTUDENT GROUP BY SEC;

For section A, 83.0+92.0+81.0=256, so AVG=256/3=85.33 approximately. Section B has 44.0 and NULL; AVG(MARKS) ignores NULL, giving 44.0. Only C groups these results by SEC; A and D sort, and B groups by marks.

Question 8, Eklavya Model Residential Schools 2023

Which of the following MySQL query is syntactically correct and most preferred one?

• A.

  SELECT SECTION, COUNT(*) FROM STUDENT
  ORDER BY SECTION
  GROUP BY SECTION
  WHERE MARKS < 33 AND COUNT(*) > 0;

• B.

  SELECT SECTION, COUNT(*) FROM STUDENT
  WHERE MARKS < 33
  HAVING COUNT(*) > 0
  GROUP BY SECTION
  ORDER BY SECTION;

• C.

  SELECT SECTION, COUNT(*) FROM STUDENT
  WHERE MARKS < 33
  GROUP BY SECTION
  HAVING COUNT(*) > 0
  ORDER BY SECTION;

• D.

  SELECT SECTION, COUNT(*) FROM STUDENT
  GROUP BY SECTION
  WHERE MARKS < 33 AND COUNT(*) > 0
  ORDER BY SECTION;

Correct answer: C.

C filters marks below 33, groups by section, applies HAVING, then sorts. A and D misplace WHERE; B misplaces HAVING. COUNT(*) > 0 is redundant for formed groups but valid.

5. GROUP BY MCQs 9-10: HAVING output and a full join-group trace

Question 9, Kendriya Vidyalaya Sangathan 2023

Find the output of the MySQL query based on the given Table-STUDENT (ignore the output header) (Table Data Provided) Query:

SELECT SEC, AVG (MARKS) FROM STUDENT GROUP BY SEC HAVING MIN (MARKS) > 80;

• A. B 83

• B. A 84

• C. A 84 B 83

• D. A 83 B 80

Correct answer: B. A 84.

For A, MIN(83,85)=83>80 and AVG(83,85)=168/2=84, so A survives. For B, MIN(86,80)=80 fails the strict test, although AVG(86,80)=166/2=83. The only output is A 84; HAVING tests each complete group.

Question 10, UGC NET 2016

Consider the following ORACLE relations:

R (A, B, C) = { <1, 2, 3>, <1, 2, 0>, <1, 3, 1>, <6, 2, 3>, <1, 4, 2>, <3, 1, 4> }

S (B, C, D) = { <2, 3, 7>, <1, 4, 5>, <1, 2, 3>, <2, 3, 4>, <3, 1, 4> }

Consider the following two SQL queries SQ₁ and SQ₂:

SQ₁:

SELECT R.B, AVG(S.B)

FROM R, S

WHERE R.A = S.C AND S.D < 7

GROUP BY R.B;

SQ₂:

SELECT DISTINCT S.B, MIN(S.C)

FROM S

GROUP BY S.B

HAVING COUNT(DISTINCT S.D) > 1;

If M is the number of tuples returned by SQ₁ and N is the number of tuples returned by SQ₂, then

• A. M = 4, N = 2

• B. M = 5, N = 3

• C. M = 2, N = 2

• D. M = 3, N = 3

Correct answer: A. M = 4, N = 2.

For SQ₁, discard S=<2,3,7> because D<7 fails. S=<2,3,4> has C=3, matching the R row with A=3 and producing R.B=1. S=<3,1,4> has C=1, matching four R rows and producing R.B=2,2,3,4. Other eligible S rows have no match. Grouped keys are {1,2,3,4}, so M=4.

For SQ₂, distinct D sets are S.B=1:{5,3}, S.B=2:{7,4}, and S.B=3:{4}. Groups 1 and 2 pass COUNT(DISTINCT S.D)>1, so N=2. DISTINCT changes nothing after grouping by S.B.

6. Common GROUP BY traps

List survivors, bucket by key, aggregate, then apply HAVING. AVG(column) ignores NULL; COUNT(*) counts rows; >80 rejects 80. Continue with DBMS MCQs.

7. GROUP BY Clause MCQs: the next practice step

Redo Questions 7, 9, and 10 for null-aware averages, group minima, and join-group tracing. Use Computer Science Fundamentals for Placements by Sanchit Sir for placements or GATE Guidance by Sanchit Sir for GATE CS.

The sequence is: filter rows, form groups, aggregate, filter groups, project, then sort. Keeping these stages separate makes GROUP BY tracing mechanical.