Question

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
MFO.5	F	0.5	250	36.50
MT1.О	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

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

1. To display Type and the maximum Price for each Type of milk.
2. For each record, increase the Price by 0.5 where Type is 'F'.
3. To display the total value of the stock (total of Qty x Price).
4. To display the details of all records where Code starts with 'A'.

Answer 1

1. SELECT Type, MAX(Price) FROM STOCK GROUP BY Type;
   • Why: GROUP BY is essential here to categorise the maximums by each specific Type.
2. UPDATE STOCK SET Price = Price + 0.5 WHERE Type = 'F';
   1. Why: UPDATE Is the correct DML command for modifying existing data
3. SELECT SUM(Qty * Price) FROM STOCK;
   1. Why: SQL allows arithmetic operations inside aggregate functions like SUM().
4. SELECT * FROM STOCK WHERE Code LIKE 'A%';
   1. Why: The % A wildcard is used with LIKE to match any characters following the letter 'A'.