Question 401 markShort AnswerSection D

(a) Details of all the loans in the descending order of RoI.

OR

(b) CID and average term for each CID from the LOANS table.

Structured Query Language (SQL)
SQL ORDER BY and GROUP BY with AVG()

Official answer

Two independent OR options, both applied only to the single LOANS table.


Part (a):

``sql

SELECT * FROM LOANS ORDER BY RoI DESC;

`


Part (b):

`sql

SELECT CID, AVG(Terms) AS AverageTerm FROM LOANS GROUP BY C_ID;

``

Answer keywords:ORDER BY DESCGROUP BYAVG() aggregate functionLOANS tablesorting SQLaverage term

Marking scheme

  1. 1.Part (a): 1 mark for SELECT * FROM LOANS ORDER BY RoI DESC;
  2. 2.Part (b): 1 mark for SELECT C_ID, AVG(Terms) FROM LOANS GROUP BY C_ID;
  3. 3.Either OR option, correctly written, earns the full 1 mark.

Hint

ORDER BY ... DESC sorts high-to-low; GROUP BY groups rows so AVG() can be computed per group.

Quick oral answer

Part (a) sorts all loans by RoI from highest to lowest; part (b) groups loans by C_ID and computes the average Terms for each customer using AVG().

Common mistakes

  1. 1.Omitting DESC in part (a), which sorts loans in ascending (default) order of RoI instead of descending.
  2. 2.In part (b), forgetting GROUP BY C_ID, which would compute a single overall average instead of one average per customer.

Analysis and explanation

Both options use only the LOANS table — no join is required here.


Part (a)

  • ORDER BY RoI DESC sorts every loan record from the highest interest rate down to the lowest.

Part (b)

  • GROUP BY C_ID groups all loan records belonging to the same customer; AVG(Terms) then computes the mean loan term within each group.

Exam trap

  • Forgetting DESC in part (a) gives ascending order by default, the opposite of what is asked.
  • In part (b), selecting a non-aggregated, non-grouped column alongside AVG(Terms) (other than C_ID) is invalid in strict SQL mode.

Interesting facts

ORDER BY sorts ascending (ASC) by default in SQL — DESC must always be explicitly written to reverse the order.

AVG() automatically ignores NULL values in the column being averaged, so a customer with a missing Terms value would not skew their group's average.

Question details

Q40 of 48 questions

Board
CBSE
Class
Class 12
Subject
Computer Science
Year
2026
Section
Section D
Marks
1
Type
Short Answer
Chapter
Structured Query Language (SQL)
Topic
SQL ORDER BY and GROUP BY with AVG()
Bloom's level
apply
Syllabus topic
Unit 2: Database Management — SQL: ORDER BY DESC for sorting, and GROUP BY with AVG() aggregate function
Author
CBSE
Reviewed by
Padhantu Academic Team

Spotted a mistake or something unclear?

Tell us and we will fix it.

Frequently asked questions

What is the default sort order if DESC is not written?

Ascending (ASC) — SQL always sorts in ascending order by default unless DESC is explicitly specified.

Why is GROUP BY C_ID required in part (b)?

Because AVG(Terms) needs to be computed separately for each customer; GROUP BY C_ID tells SQL to form one group per distinct customer before averaging.