(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;

ORDER BY DESCGROUP BYAVG() aggregate functionLOANS tablesorting SQLaverage term

Marking Scheme

1Part (a): 1 mark for SELECT * FROM LOANS ORDER BY RoI DESC;
2Part (b): 1 mark for SELECT C_ID, AVG(Terms) FROM LOANS GROUP BY C_ID;
3Either 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().

Analysis & 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.

Common Mistakes

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

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.

Spotted a mistake or something unclear?

Tell us — we fix reported answers fast.

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.