Assume that you are the Manager of the Loans department of a Finance House. To keep track of the loans you hav

Question 69 of 95

Q. Assume that you are the Manager of the Loans department of a Finance House. To keep track of the loans you have created two tables : CUSTOMERS and LOANS. The sample data in these tables is given below : Table : CUSTOMERS C_ID C_Name Phone 00001 Raj Malhotra 1234567890 00003 David Xavier 3456789012 00004 Damini Iyer 3156789012 00008 Abdul 2345678901 Table : LOANS SNo C_ID L_Amt L_Date Terms RoI 1 00003 200000 2025-12-06 60 7.80 2 00008 2500000 2023-08-09 60 9.00 3 00001 500000 2025-08-13 48 6.00 4 00003 300000 2026-12-07 36 8.00 5 00004 600000 2026-12-07 60 6.00 Note : The tables may contain more records than shown here. The management of the Finance House needs certain reports from you. Write the queries to extract the following data to create the reports :

(i) Number of records from LOANS table where Rate of Interest (RoI) is above 7.0.

(ii) Names of the customers whose loan amount (L_Amt) is above 1000000.

(iii) C_ID, C_Name and Terms of all those records where Loan Date (L_Date) is after 31st December, 2024.

(iv)

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

(OR)

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

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

73% · 69/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 →

Part (a)Concept understanding — Database Querying

Database Querying: Asking Questions of a Data Collection

Think of a library. You walk in, and instead of wandering through endless shelves, you go to the librarian and say: "I need all books by R. K. Narayan published after 1980." The librarian knows exactly where to look, finds the relevant books, and hands you a short list. That act of asking — of specifying exactly what you want from a large, organised collection — is the essence of querying.

A database is like that library, but for digital information. It stores data in a structured way, typically in tables with rows and columns. A query is simply a question you ask of that database. You don't need to know where the data lives or how it's stored internally. You just need to state what you want.

The Precise Meaning

In formal terms, a database query is a request for data or information from a database. The request is written in a special language that the database understands. The most common such language is SQL (Structured Query Language), but the concept of querying exists in every database system.

When you query a database, you are doing one of four things:

Retrieving data (the most common — "show me the customers who live in Delhi")
Inserting new data ("add this new student record")
Updating existing data ("change this address")
Deleting data ("remove this old order")

For a commerce or humanities student, the retrieval part is where the real power lies. You are not just pulling out raw data — you are filtering, sorting, and combining it to get answers.

Why It Matters

A database without querying is like a library with the lights off. The data exists, but you cannot use it. Querying turns stored facts into actionable information.

Consider a small business owner who keeps customer records in a spreadsheet. Without querying, they might scroll through hundreds of rows to find customers who haven't purchased in six months. With a query, they type one request and get the answer instantly. That answer might lead to a targeted marketing campaign, which brings in revenue.

Note

Querying is not about memorising commands. It is about thinking in terms of conditions and relationships. The skill is learning to translate a real-world question — "Which products are out of stock?" — into a precise, unambiguous request the database can process.

The Intuition Behind It

Every query has a simple structure at its core:

What data do you want? (Which columns or fields?)
Where is it? (Which table or collection?)
Under what conditions? (Which rows match your criteria?)

If you can answer those three questions in plain English, you are already thinking like someone who queries databases. The technical language (SQL) is just a way to write those answers down so the computer understands.

A Real-World Example …

Part (b)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 …

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

Part (a)

Reports (i)-(iii) are common; (iv) here is option (a).

(i) Count of loans with RoI above 7.0 (7.80, 9.00, 8.00 -> 3)

SELECT COUNT(*) FROM LOANS WHERE RoI > 7.0;

(ii) Names of customers whose loan amount exceeds 1000000 (needs a join; only Abdul, 2500000)

SELECT DISTINCT C.C_Name
FROM CUSTOMERS C JOIN LOANS L ON C.C_ID = L.C_ID
WHERE L.L_Amt > 1000000;

(iii) C_ID, C_Name, Terms where L_Date is after 31-Dec-2024

SELECT C.C_ID, C.C_Name, L.Terms
FROM CUSTOMERS C JOIN LOANS L ON C.C_ID = L.C_ID
WHERE L.L_Date > '2024-12-31';

(iv)(a) Details of all loans in descending order of RoI

SELECT * FROM LOANS ORDER BY RoI DESC; …

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

Part (a): reports (i) COUNT RoI>7.0=3, (ii) join for names with L_Amt>1000000 (Abdul), (iii) join for C_ID/C_Name/Terms with L_Date>'2024-12-31', (iv)(a) all loans ORDER BY RoI DESC. Part (b): the (iv) alternative — C_ID and AVG(Terms) grouped by C_ID.

Two tables: CUSTOMERS(C_ID, C_Name, Phone) and LOANS(SNo, C_ID, L_Amt, L_Date, Terms, RoI). Reports (i), (ii) and (iii) are the same for both choices; task (iv) is where option (a) and option (b) differ.

Part (a)

(i) Count rows, not list them, so use COUNT(*). RoI values 7.80, 9.00 and 8.00 exceed 7.0.

SELECT COUNT(*) FROM LOANS WHERE RoI > 7.0;

Expected output:

(ii) The name lives in CUSTOMERS, the amount in LOANS, so join on C_ID. Only loan 2500000 (C_ID 00008) exceeds 1000000.

SELECT DISTINCT C.C_Name
FROM CUSTOMERS C JOIN LOANS L ON C.C_ID = L.C_ID
WHERE L.L_Amt > 1000000;

Expected output:

Abdul

(iii) Join again and filter dates after 31-Dec-2024 (i.e. 2025 onward).

SELECT C.C_ID, C.C_Name, L.Terms
FROM CUSTOMERS C JOIN LOANS L ON C.C_ID = L.C_ID
WHERE L.L_Date > '2024-12-31';

Expected output:

00003  David Xavier   60
00001  Raj Malhotra   48
00003  David Xavier   36
00004  Damini Iyer    60

(iv)(a) "Details" = all columns of LOANS, sorted highest interest first.

SELECT * FROM LOANS ORDER BY RoI DESC;
``` …

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 2024Set 91/41 markMCQ
$Q. The SELECT statement when combined with ____ clause, returns records without repetition. (A) DISTINCT (B) DESCRIBE (C) UNIQUE (D) NULL$
›Reveal solutionSolution
SQL's DISTINCT keyword filters out duplicate rows from query results, ensuring each record appears only once. The answer is (A).

When you query a database table, you often retrieve multiple rows that may contain identical values across all selected columns. Think of a customer orders table where the same customer ID appears dozens of times—one for each order. If you only want to see which customers have placed orders (not how many times), you need a mechanism to collapse duplicates into a single representative row.

The SELECT statement by itself returns every row that matches your conditions, duplicates and all. SQL provides the DISTINCT keyword specifically to eliminate repetition: it compares the entire result set row-by-row and keeps only unique combinations.

How each option relates to SQL

DISTINCT – This is the standard SQL keyword placed immediately after SELECT to remove duplicate rows. For example:

SELECT DISTINCT customer_id FROM orders;

returns each customer ID exactly once, no matter how many orders they placed.

DESCRIBE – A command (or keyword in some databases) used to show the structure of a table—its column names, data types, constraints—not to filter query results. It's a metadata inspection tool, not a result-set modifier.

UNIQUE – In SQL, UNIQUE is a constraint applied to columns during table creation to enforce that no two rows have the same value in that column. It's not a clause you combine with SELECT to filter results. You might see it in:

CREATE TABLE users (email VARCHAR(100) UNIQUE); …
Start your 14-day free trial to see the full solution →
CBSE 2020Set 91/D1 mark
$Q. Which clause is used with a SELECT command in SQL to display the records in ascending order of an attribute?$
›Reveal solutionSolution
The ORDER BY clause is used with the SELECT command in SQL to display records in ascending order of an attribute.

When we work with databases using SQL (Structured Query Language), the SELECT command is our primary tool for retrieving information. Imagine a vast library of books; SELECT is like asking the librarian to fetch specific books or details about them. However, when the librarian hands you the books, they might not be in any particular order – perhaps by the order they were found, or by some internal system. For us to make sense of the data, especially when dealing with many records, we often need them arranged in a logical sequence.

This need for structured presentation is where ordering comes in. Displaying records in ascending order of an attribute means arranging them from the smallest value to the largest, or alphabetically from A to Z for text. For instance, if you're looking at a list of students, you might want them sorted by their roll numbers from lowest to highest, or by their names alphabetically. This makes the data much easier to read, analyze, and understand.

To achieve this specific ordering in SQL, we use a special clause called ORDER BY. This clause is appended to the SELECT statement and tells the database system exactly how we want the retrieved records to be sorted.

Important

The ORDER BY clause is crucial for presenting query results in a meaningful and organized manner, making data analysis and reporting much more efficient.

Here's how the ORDER BY clause works:

Specifying the Attribute: You must specify one or more attributes (columns) by which you want to sort the data. For example, if you have a table of Students with columns like RollNo, Name, and Marks, you could choose to sort by RollNo or Name.

Ascending Order (ASC): By default, if you just specify an attribute with ORDER BY, the records will be sorted in ascending order. However, it's good practice to explicitly use the ASC keyword to make your intention clear. ASC stands for Ascending. This means numbers will go from smallest to largest, and text will go from A to Z. …
Start your 14-day free trial to see the full solution →

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 →

←Previous questionQ11 — Abhishek has created a table, named STOCK, with a set of records to ma…→Next questionQ13 — The SELECT statement when combined with __ clause, returns records wit…

Part (a)

🎓Unlock everything free for 14 days

Unlock everything free for 14 days