(a) Consider the following tables Student and Sport : Table : Student ADMNO NAME CLASS 1100 MEENA X 1101 VANI

Question 88 of 95

Q. (a) Consider the following tables Student and Sport : Table : Student ADMNO NAME CLASS 1100 MEENA X 1101 VANI XI Table : Sport ADMNO GAME 1100 CRICKET 1103 FOOTBALL What will be the output of the following statement ? SELECT * FROM Student, Sport;

(b) Write the output of the queries

(i) to

(iv) based on the table, GARMENT given below : TABLE : GARMENT GCODE TYPE PRICE FCODE ODR_DATE G101 EVENING GOWN 850 F03 2008-12-19 G102 SLACKS 750 F02 2020-10-20 G103 FROCK 1000 F01 2021-09-09 G104 TULIP SKIRT 1550 F01 2021-08-10 G105 BABY TOP 1500 F02 2020-03-31 G106 FORMAL PANT 1250 F01 2019-01-06

(i) SELECT DISTINCT(COUNT(FCODE)) FROM GARMENT;

(ii) SELECT FCODE, COUNT(), MIN(PRICE) FROM GARMENT GROUP BY FCODE HAVING COUNT()>1;

(iii) SELECT TYPE FROM GARMENT WHERE ODR_DATE >'2021-02-01' AND PRICE <1500;

(iv) SELECT * FROM GARMENT WHERE TYPE LIKE 'F%';

Yanam CbseCBSE Class XII Board 2023Subjective· 3mImportance★★★★★

93% · 88/95 Questions

🔒 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 →

Concept understanding — SQL Aggregate Functions

Imagine you have a giant box of receipts from a month of sales at a small shop. You don't want to look at every single receipt one by one — you want a single, useful number that summarises the whole pile. How much total money came in? What was the average sale? How many customers bought something? That instinct — to collapse a pile of raw details into one meaningful number — is exactly what an SQL aggregate function does.

In a database, a table might hold thousands of rows of data: one row for every sale, every student, every product. An aggregate function takes all those rows in a column and boils them down to a single value. It doesn't change the data; it answers a question about the whole set. The most common ones are COUNT, SUM, AVG, MIN, and MAX. Each one has a clear, everyday meaning.

COUNT tells you how many rows there are — like counting how many students are in a class list. SUM adds up all the numbers in a column — like totalling the marks of every student in an exam. AVG gives the average of those numbers. MIN and MAX find the smallest and largest values — the lowest temperature recorded or the highest sale of the day.

Note

Aggregate functions work on a column of data, not across rows. You always write them as FUNCTION_NAME(column_name). For example, AVG(price) gives the average of all prices in that column.

The real power comes when you combine them with the GROUP BY clause. Without GROUP BY, an aggregate function summarises the entire table. With GROUP BY, you split the table into groups first, then apply the function to each group separately. Think of it like this: you don't just want the average sale for the whole year — you want the average sale per month. GROUP BY month creates twelve groups, and AVG(sale_amount) runs once for each group, giving you twelve averages.

This is why aggregate functions matter in business, economics, and any data-driven decision. A manager doesn't ask "what was every single transaction?" They ask "what was the total revenue last quarter?" or "which product category had the highest average rating?" Aggregate functions turn raw, overwhelming data into actionable summaries. …

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

The first query produces a Cartesian product (every row of Student paired with every row of Sport), while the second set of queries tests your understanding of aggregate functions, GROUP BY, HAVING, date comparisons, and pattern matching in SQL.

(a) The Cartesian Product

The statement SELECT * FROM Student, Sport; is a cross join — it combines every row from the first table with every row from the second table. This is called a Cartesian product.

Student has two rows (ADMNO 1100 and 1101). Sport also has two rows (ADMNO 1100 and 1103). So the output will have 2 × 2 = 4 rows. Each row of Student is paired with each row of Sport, regardless of whether the ADMNO values match.

The result will look like this:

ADMNO	NAME	CLASS	ADMNO	GAME
1100	MEENA	X	1100	CRICKET
1100	MEENA	X	1103	FOOTBALL
1101	VANI	XI	1100	CRICKET
1101	VANI	XI	1103	FOOTBALL

Notice that the ADMNO column appears twice — once from each table. This is rarely useful in practice; you would normally add a WHERE clause to match related rows.

Important

A Cartesian product multiplies every row of one table with every row of another. If Student had 100 rows and Sport had 50, the output would be 5000 rows — often unintended and computationally expensive.

(b) Queries on the GARMENT table

Let us examine each query one by one.

(i) SELECT DISTINCT(COUNT(FCODE)) FROM GARMENT;

This query first counts the number of non-null FCODE values in the entire table. There are 6 rows, and every row has an FCODE, so COUNT(FCODE) returns 6. That single number (6) is then passed to DISTINCT. Since there is only one value, the output is simply:

COUNT(FCODE)
6

Note

DISTINCT here is redundant — it applies to the single aggregated value. The parentheses around COUNT(FCODE) are also unnecessary; DISTINCT COUNT(FCODE) would work the same way.

(ii) SELECT FCODE, COUNT(*), MIN(PRICE) FROM GARMENT GROUP BY FCODE HAVING COUNT(*)>1;

This groups rows by FCODE. Let us see how many rows each FCODE has:

F01: G103 (FROCK), G104 (TULIP SKIRT), G106 (FORMAL PANT) → 3 rows
F02: G102 (SLACKS), G105 (BABY TOP) → 2 rows
F03: G101 (EVENING GOWN) → 1 row

The HAVING COUNT(*)>1 keeps only groups with more than one row — so F03 is dropped. For the remaining groups, the query selects the FCODE, the count of rows, and the minimum price in that group.

For F01: minimum price among 1000, 1550, 1250 is 1000.

For F02: minimum price among 750, 1500 is 750.

Output:

FCODE	COUNT(*)	MIN(PRICE)
F01	3	1000
F02	2	750

(iii) SELECT TYPE FROM GARMENT WHERE ODR_DATE >'2021-02-01' AND PRICE <1500;

This filters rows where the order date is after 1 February 2021 AND the price is less than 1500. Let us check each row:

G101: date 2008-12-19 — before 2021, so fails.
G102: date 2020-10-20 — before 2021, fails. …

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

←Previous questionQ30 — (a) Differentiate between IN and BETWEEN operators in SQL with appropr…→Next questionQ32 — Write the output of any three SQL queries (i) to (iv) based on the tab…

Q.(a) Consider the following tables Student and Sport : Table : Student ADMNO NAME CLASS 1100 MEENA X 1101 VANI XI Table : Sport ADMNO GAME 1100 CRICKET 1103 FOOTBALL What will be the output of the following statement ? SELECT * FROM Student, Sport;

🎓Unlock everything free for 14 days

Unlock everything free for 14 days