Abhishek has created a table, named STOCK, with a set of records to maintain the data of packaged milk in his

Question 68 of 95

Q. Abhishek has created a table, named STOCK, with a set of records to maintain the data of packaged milk in his shop. After creating the table, he entered the data and the table looked as follows : Code Type Volume Qty Price AF0.5 F 0.5 300 38.00 MF0.5 F 0.5 250 36.50 MT1.0 T 1.0 150 64.00 AT1.0 T 1.0 100 66.00 PD1.0 D 1.0 50 52.00 PT0.5 T 0.5 78 30.00

(a) Based on the data given above, write the SQL queries for the following tasks :

(i) To display Type and the maximum Price for each Type of milk.

(ii) For each record, increase the Price by 0.5 where Type is 'F'.

(iii) To display the total value of the stock (total of Qty * Price).

(iv) To display the details of all records where Code starts with 'A'.

(OR)

(b) Considering the table STOCK as given above, write the output on execution of the following queries :

(i) SELECT Volume, Qty, Price FROM STOCK WHERE Type IN ('F','D');

(ii) SELECT Code, Qty FROM STOCK WHERE Price BETWEEN 30 AND 50;

(iii) SELECT DISTINCT Type FROM STOCK;

(iv) SELECT Volume, count(*) FROM STOCK GROUP BY Volume;

Puducherry TnboardCBSE Class XII Board 2026Subjective· 4mImportance★★★★★

72% · 68/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 — Database Querying

Database Querying: Asking Questions of a Data Collection

The Precise Meaning

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.

Note

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?)

A Real-World Example …

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

Part (a)

STOCK data is used for four query tasks.

(i) Type and maximum Price per Type

SELECT Type, MAX(Price) FROM STOCK GROUP BY Type;

(ii) Increase Price by 0.5 where Type is 'F'

UPDATE STOCK SET Price = Price + 0.5 WHERE Type = 'F';

(iii) Total value of stock (sum of Qty*Price)

SELECT SUM(Qty * Price) AS TotalValue FROM STOCK;

The total = 11400 + 9125 + 9600 + 6600 + 2600 + 2340 = 41665.

(iv) All records whose Code starts with 'A'

SELECT * FROM STOCK WHERE Code LIKE 'A%'; …

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

Part (a): write SQL using GROUP BY+MAX, UPDATE, SUM(Qty*Price)=41665, and LIKE 'A%'. Part (b): predict outputs for IN, BETWEEN, DISTINCT and GROUP BY count queries.

The STOCK table:

Code	Type	Volume	Qty	Price
AF0.5	F	0.5	300	38.00
MF0.5	F	0.5	250	36.50
MT1.0	T	1.0	150	64.00
AT1.0	T	1.0	100	66.00
PD1.0	D	1.0	50	52.00
PT0.5	T	0.5	78	30.00

Part (a)

(i) Group by Type and take the maximum Price of each group.

SELECT Type, MAX(Price) FROM STOCK GROUP BY Type;

Expected output:

F  38.00
T  66.00
D  52.00

(ii) Modify rows with UPDATE, restricted to Type 'F' by the WHERE clause.

UPDATE STOCK SET Price = Price + 0.5 WHERE Type = 'F';

This raises AF0.5 to 38.50 and MF0.5 to 37.00.

(iii) Multiply Qty by Price per row and total with SUM.

SELECT SUM(Qty * Price) AS TotalValue FROM STOCK;

Row values: 30038=11400, 25036.50=9125, 15064=9600, 10066=6600, 5052=2600, 7830=2340. Sum:

(iv) Pattern-match with LIKE; 'A%' means "starts with A".

SELECT * FROM STOCK WHERE Code LIKE 'A%';

Expected output (AF0.5 and AT1.0):

AF0.5  F  0.5  300  38.00
AT1.0  T  1.0  100  66.00 …

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

←Previous questionQ10 — Ms. Zoya is a Production Manager in a factory which packages mineral w…→Next questionQ12 — Assume that you are the Manager of the Loans department of a Finance H…

Part (a)

🎓Unlock everything free for 14 days

Unlock everything free for 14 days