(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 x 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;
(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 x 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;
Official answer
Two OR options are given in full; part (b)'s exact row-by-row output cannot be certified without the STOCK table's image data (not captured by OCR), so its expected output structure is explained instead of invented numbers.
Part (a) — SQL queries on STOCK
``sql
-- (i) Type and maximum Price for each Type
SELECT Type, MAX(Price) AS 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 (Qty x Price summed over all rows)
SELECT SUM(Qty * Price) AS Total_Value FROM STOCK;
-- (iv) All records where Code starts with 'A'
SELECT * FROM STOCK WHERE Code LIKE 'A%';
``
Part (b) — meaning of the given queries (exact values depend on the STOCK image data)
- Query (i) returns only the Volume, Qty and Price columns, restricted to rows where Type is 'F' or 'D'.
- Query (ii) returns only Code and Qty, restricted to rows whose Price lies between 30 and 50 inclusive.
- Query (iii) returns one column listing each distinct value that appears in the Type column, with duplicates removed.
- Query (iv) returns each distinct Volume value alongside a count of how many STOCK records share that Volume.
Marking scheme
- 1.Part (a): 1 mark each for (i) SELECT Type, MAX(Price) ... GROUP BY Type; (ii) UPDATE STOCK SET Price=Price+0.5 WHERE Type='F'; (iii) SELECT SUM(Qty*Price) FROM STOCK; (iv) SELECT * FROM STOCK WHERE Code LIKE 'A%'.
- 2.Part (b): 1 mark each for correctly stating the columns/rows returned by each of the four given SELECT queries, based on the STOCK table shown in the question.
- 3.Minor syntax variants (e.g. an equivalent WHERE Type='F' OR Type='D' instead of IN) are accepted if the intent is preserved.
Hint
GROUP BY pairs with aggregate functions; UPDATE always needs a WHERE to target specific rows; BETWEEN and IN simplify multi-value/range conditions.
Quick oral answer
MAX(Price) grouped by Type gives the costliest item per category, UPDATE with a WHERE clause changes only matching rows, SUM(Qty*Price) totals the stock value, and LIKE 'A%' filters codes starting with A.
Common mistakes
- 1.Omitting GROUP BY Type while using MAX(Price), which is a logical/syntax error when a non-aggregated column is also selected.
- 2.Forgetting WHERE Type='F' in the UPDATE statement, which would incorrectly increase the price of every record in the table.
- 3.Writing SUM(Qty)*SUM(Price) instead of SUM(Qty*Price) — these give completely different (and wrong) totals.
Analysis and explanation
This question tests four core SQL skills together — grouped aggregates, conditional UPDATE, an aggregate over a computed expression, and pattern matching.
Part (a) — building the queries
- MAX(Price) grouped by Type needs GROUP BY Type in the same query as the aggregate function.
- The UPDATE statement must include WHERE Type='F' — omitting it would raise every row's price, not just type F.
- SUM(Qty*Price) computes a derived total by multiplying two columns row-wise before summing, unlike SUM(Qty) or SUM(Price) alone.
- LIKE 'A%' matches any Code beginning with 'A', regardless of what follows.
Part (b) — reading queries
- IN ('F','D') is shorthand for Type='F' OR Type='D'.
- BETWEEN 30 AND 50 is inclusive of both endpoints.
- DISTINCT removes duplicate Type values from the result.
- GROUP BY Volume with COUNT(*) tallies records per distinct Volume.
Exam trap
- A common error is trying to filter an aggregate function in a WHERE clause; aggregates can only be filtered using HAVING, not WHERE.
Interesting facts
SQL's UPDATE without a WHERE clause is one of the most common real-world data-loss incidents — it silently modifies every single row in the table.
The wildcard % in LIKE matches zero or more characters, while _ matches exactly one character — a distinction frequently tested in CBSE papers.
BETWEEN...AND... in SQL is always inclusive of both boundary values, unlike range() in Python which excludes its upper bound.
Question details
Q35 of 48 questions
- Board
- CBSE
- Class
- Class 12
- Subject
- Computer Science
- Year
- 2026
- Section
- Section D
- Marks
- 4
- Type
- Short Answer
- Chapter
- Structured Query Language (SQL)
- Topic
- SQL Queries — GROUP BY, UPDATE, Aggregate SUM, and Pattern Matching (LIKE)
- Bloom's level
- apply
- Syllabus topic
- Unit 2: Computer Networks & Database Management — SQL: GROUP BY with aggregate functions, UPDATE with WHERE, SUM() over a computed expression, LIKE pattern matching
- Author
- CBSE
- Reviewed by
- Padhantu Academic Team
Spotted a mistake or something unclear?
Tell us and we will fix it.
Frequently asked questions
Why is GROUP BY needed in query (i)?
Because MAX(Price) is an aggregate function computed separately for each Type; GROUP BY Type tells SQL to compute one MAX value per distinct Type rather than one MAX for the whole table.
What happens if WHERE Type='F' is left out of the UPDATE query?
Every record in the STOCK table would have its Price increased by 0.5, not just the Type 'F' records — a serious logical error.
Can the exact rows returned by part (b)'s queries be given here?
Only their structure and filtering logic can be given confidently, since the STOCK table's actual record values were embedded as an image in the original paper and are not available in the extracted OCR text.