Consider the following table School-data: Table : School-data | Adm-no | Name | Grade | Club | Marks | Gender | | --- | --- | --- | --- | --- | --- | | 20150001 | Sargam Singh | 12 | STEM | 86 | Male | | 20140212 | Alok Kumar | 10 | SPACE | 75 | Male | | 20090234 | Mohit Gaur | 11 | SPACE | 84 | Male | | 20130216 | Romil Malik | 10 | READER | 91 | Male | | 20190227 | Tanvi Batra | 11 | STEM | 70 | Female | | 20120200 | Nomita Ranjan | 12 | STEM | 64 | Female | Write SQL queries for the following: (i) Display the average Marks secured by each Gender. (ii) Display the minimum Marks secured by the students of Grade 10. (iii) Display the total number of students in each Club where number of students are more than 1. OR (Option for Part (iii) only) (iii) Display the maximum and minimum marks secured by each gender.

Consider the following table School-data: Table : Schoo — CBSE Class 12 Informatics Practices 2023 PYQ

Question

Consider the following table School-data:

Table : School-data

Adm-no	Name	Grade	Club	Marks	Gender
20150001	Sargam Singh	12	STEM	86	Male
20140212	Alok Kumar	10	SPACE	75	Male
20090234	Mohit Gaur	11	SPACE	84	Male
20130216	Romil Malik	10	READER	91	Male
20190227	Tanvi Batra	11	STEM	70	Female
20120200	Nomita Ranjan	12	STEM	64	Female

Write SQL queries for the following: (i) Display the average Marks secured by each Gender. (ii) Display the minimum Marks secured by the students of Grade 10. (iii) Display the total number of students in each Club where number of students are more than 1. OR (Option for Part (iii) only) (iii) Display the maximum and minimum marks secured by each gender.

CBSECBSE Class XII Board 2023Subjective· 4mImportance★★★★★

🔒 Locked · start free trial →

You're viewing a preview — the full solution, concept, methods & PYQ mapping are locked.

Start your 14-day free trial to unlock the full solution →

Part (a)Concept understanding — Group By Aggregation

Group By Aggregation: A First Look

Think of a pile of laundry. You have socks, shirts, towels — all mixed together. If someone asks "how many socks are there?", you first separate the socks from everything else, then count them. That act of separating first, then summarising — that is the core of Group By Aggregation.

In data, we often have rows of information about many different things mixed together. Group By Aggregation is the operation that says: "Take all these rows, sort them into meaningful groups based on some common property, and then for each group, compute a single summary value."

The Two Steps, Always

Every Group By Aggregation has exactly two parts, and they happen in order:

Grouping — You pick a column (or columns) whose values will define the groups. All rows that share the same value in that column become one group.
Aggregating — You pick a summary operation to apply to each group. Common operations include counting how many rows are in the group, finding the total or average of a numeric column within the group, or picking the smallest or largest value.

The result is a new table: one row per group, with the group's label and the computed summary.

Why It Matters

Without Group By, you can only summarise the entire dataset at once — one total, one average for everything. That hides almost all the interesting variation. Group By lets you ask questions like:

For each state, what is the total number of schools?
For each year, what was the average rainfall?
For each product category, how many items were sold?

Each of these questions picks a grouping column (state, year, category) and an aggregation (total, average, count). The answer reveals patterns that a single overall number would bury.

A Concrete Example (Without Numbers)

Imagine a table of exam scores for students from different cities. The table has columns: Student Name, City, Score.

If you want to know "how many students appeared from each city", you:

Group by the City column — all rows with the same city name become one group.
Apply the count aggregation — count how many rows are in each group.

The result is a table with two columns: City and Number of Students.

If instead you want "the highest score in each city", you:

Group by City again.
Apply the maximum aggregation to the Score column.

The result is a table with City and Highest Score.

Important

The grouping column and the aggregation column are often different. The grouping column defines who is in each group; the aggregation column defines what you summarise about that group. You can also aggregate the same column you grouped by — for example, counting how many rows are in each group — but that is a special case, not the general rule.

Common Aggregation Operations

These are the standard summaries you can compute for each group:

Count — How many rows are in the group.
Sum — Total of a numeric column across all rows in the group.
Average — Mean of a numeric column across the group.
Minimum — Smallest value in a numeric column within the group.
Maximum — Largest value in a numeric column within the group.

Each of these gives a different lens on the same grouped data.

What the NCERT Textbook Says …

Part (b)Concept understanding — Group By Aggregation

Group By Aggregation: A First Look

The Two Steps, Always

Every Group By Aggregation has exactly two parts, and they happen in order:

Grouping — You pick a column (or columns) whose values will define the groups. All rows that share the same value in that column become one group.
Aggregating — You pick a summary operation to apply to each group. Common operations include counting how many rows are in the group, finding the total or average of a numeric column within the group, or picking the smallest or largest value.

The result is a new table: one row per group, with the group's label and the computed summary.

Why It Matters

Without Group By, you can only summarise the entire dataset at once — one total, one average for everything. That hides almost all the interesting variation. Group By lets you ask questions like:

For each state, what is the total number of schools?
For each year, what was the average rainfall?
For each product category, how many items were sold?

Each of these questions picks a grouping column (state, year, category) and an aggregation (total, average, count). The answer reveals patterns that a single overall number would bury.

A Concrete Example (Without Numbers)

Imagine a table of exam scores for students from different cities. The table has columns: Student Name, City, Score.

If you want to know "how many students appeared from each city", you:

Group by the City column — all rows with the same city name become one group.
Apply the count aggregation — count how many rows are in each group.

The result is a table with two columns: City and Number of Students.

If instead you want "the highest score in each city", you:

Group by City again.
Apply the maximum aggregation to the Score column.

The result is a table with City and Highest Score.

Important

Common Aggregation Operations

These are the standard summaries you can compute for each group:

Count — How many rows are in the group.
Sum — Total of a numeric column across all rows in the group.
Average — Mean of a numeric column across the group.
Minimum — Smallest value in a numeric column within the group.
Maximum — Largest value in a numeric column within the group.

Each of these gives a different lens on the same grouped data.

What the NCERT Textbook Says …

Start your 14-day free trial to unlock the full solution →

The table name School-data contains a hyphen, so it is wrapped in backticks. Parts (i) and (ii) are common; part (iii) differs.

Part (a)

(i) Average marks per gender:

SELECT Gender, AVG(Marks) FROM `School-data` GROUP BY Gender;

Male avg = (86+75+84+91)/4 = 84.0; Female avg = (70+64)/2 = 67.0.

(ii) Minimum marks in Grade 10:

SELECT MIN(Marks) FROM `School-data` WHERE Grade = 10;

Grade-10 marks are 75 and 91, so the result is 75.

(iii) Clubs with more than one student:

SELECT Club, COUNT(*) FROM `School-data` GROUP BY Club HAVING COUNT(*) > 1;
``` …

Start your 14-day free trial to unlock the full solution →

Part (a): AVG(Marks) GROUP BY Gender; MIN(Marks) WHERE Grade=10 (=75); COUNT() GROUP BY Club HAVING COUNT()>1 (STEM 3, SPACE 2).

Part (b): MAX(Marks) and MIN(Marks) GROUP BY Gender — Male (91, 75), Female (70, 64).

The table School-data has a hyphen in its name, so it must be enclosed in backticks; otherwise SQL reads the hyphen as a minus operator. Parts (i) and (ii) are common to both options.

Part (a)

(i) Average marks by gender. GROUP BY Gender forms one bucket per gender and AVG(Marks) averages within each.

SELECT Gender, AVG(Marks) FROM `School-data` GROUP BY Gender;

Output:

Gender  AVG(Marks)
Male    84.0
Female  67.0

(ii) Minimum marks in Grade 10. Filter to Grade 10 with WHERE, then take MIN. No grouping is needed for a single value.

SELECT MIN(Marks) FROM `School-data` WHERE Grade = 10;

Grade-10 students score 75 (Alok) and 91 (Romil):

MIN(Marks)
75

(iii) Clubs having more than one student. Count per club with GROUP BY Club, then keep groups whose count exceeds 1 using HAVING (a WHERE cannot filter on an aggregate).

SELECT Club, COUNT(*) FROM `School-data` GROUP BY Club HAVING COUNT(*) > 1;

STEM has 3 (Sargam, Tanvi, Nomita), SPACE has 2 (Alok, Mohit), READER has 1:

Club   COUNT(*)
STEM   3
SPACE  2 …

Start your 14-day free trial to unlock the full solution →

Same / Similar Concept — real previous-year questions on the same or a closely similar concept, not this exact question.

CBSE 2019Set 90/1/11 mark

Q. Consider the following table ‘Transporter’ that stores the order details about items to be transported. Table : TRANSPORTER ORDERNO DRIVERNAME DRIVERGRADE ITEM TRAVELDATE DESTINATION 10012 RAM YADAV A TELEVISION 2019-04-19 MUMBAI 10014 SOMNATH SINGH FURNITURE 2019-01-12 PUNE 10016 MOHAN VERMA B WASHING MACHINE 2019-06-06 LUCKNOW 10018 RISHI SINGH A REFRIGERATOR 2019-04-07 MUMBAI 10019 RADHE MOHAN TELEVISION 2019-05-30 UDAIPUR 10020 BISHEN PRATAP B REFRIGERATOR 2019-05-02 MUMBAI 10021 RAM TELEVISION 2019-05-03 PUNE Write the output for the following SQL query :

ORDERNO	DRIVERNAME	DRIVERGRADE	ITEM	TRAVELDATE	DESTINATION
10012	RAM YADAV	A	TELEVISION	2019-04-19	MUMBAI
10014	SOMNATH SINGH		FURNITURE	2019-01-12	PUNE
10016	MOHAN VERMA	B	WASHING MACHINE	2019-06-06	LUCKNOW
10018	RISHI SINGH	A	REFRIGERATOR	2019-04-07	MUMBAI
10019	RADHE MOHAN		TELEVISION	2019-05-30	UDAIPUR
10020	BISHEN PRATAP	B	REFRIGERATOR	2019-05-02	MUMBAI
10021	RAM		TELEVISION	2019-05-03	PUNE

(ix) SELECT ITEM, COUNT() FROM TRANSPORTER GROUP BY ITEM HAVING COUNT() >1;

›Reveal solution

This query groups orders by the item being transported and then lists only those items that appear in more than one order.

When working with databases, we often need to summarise information rather than just retrieve raw data. The GROUP BY clause in SQL is a powerful tool for this, allowing us to aggregate rows that share common values into a set of summary rows. This particular query asks us to identify items that have been ordered multiple times, providing a count for each.

Let's break down the query step by step to understand its logic:

FROM TRANSPORTER: This is the starting point. The query begins by looking at all the records in the TRANSPORTER table.
GROUP BY ITEM: This is the core of the aggregation. The database will go through the TRANSPORTER table and collect all rows that have the same value in the ITEM column into a single group. For example, all rows where ITEM is 'TELEVISION' will form one group, all rows where ITEM is 'FURNITURE' will form another, and so on.
SELECT ITEM, COUNT(*): After grouping, for each distinct group formed by the ITEM column, the query will select two pieces of information:
- The ITEM itself (which is the common value for that group).
- COUNT(*): This is an aggregate function that counts the total number of rows within each specific group. So, for the 'TELEVISION' group, COUNT(*) will tell us how many times 'TELEVISION' appears in the table.
HAVING COUNT(*) > 1: This is a crucial filtering condition. While WHERE clauses filter individual rows before grouping, HAVING clauses filter entire groups after they have been formed and aggregate functions (like COUNT(*)) have been calculated. In this case, HAVING COUNT(*) > 1 means that only those groups where the count of items is strictly greater than 1 will be included in the final output. Groups with a count of 1 (meaning the item appeared only once) will be discarded.

Note

The distinction between WHERE and HAVING is fundamental. WHERE filters individual rows before GROUP BY processes them, while HAVING filters the groups themselves after GROUP BY has aggregated the data. You cannot use aggregate functions directly in a WHERE clause.

Let's apply this logic to the TRANSPORTER table:

Identify unique ITEM values and group them:
- TELEVISION
- FURNITURE
- WASHING MACHINE …

Start your 14-day free trial to see the full solution →

Part (a)

🎓Unlock everything free for 14 days

Unlock everything free for 14 days