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 |
Based on the data given above, write the SQL query: To display Type and the maximum Price for each Type of milk.
SELECT Type, MAX(Price) FROM STOCK GROUP BY Type; F 38.00
T 66.00
D 52.00Write the SQL query: For each record, increase the Price by 0.5 where Type is 'F'.
UPDATE STOCK SET Price = Price + 0.5 WHERE Type = 'F'; Write the SQL query: To display the total value of the stock (total of Qty × Price).
SELECT SUM(Qty * Price) FROM STOCK; Write the SQL query: To display the details of all records where Code starts with 'A'.
SELECT * FROM STOCK WHERE Code LIKE 'A%'; Considering the table STOCK as given above, write the output on execution of the query:
SELECT Volume, Qty, Price FROM STOCK WHERE Type IN ('F','D');Volume Qty Price
0.5 300 38.00
0.5 250 36.50
1.0 50 52.00Write the output on execution of the query:
SELECT Code, Qty FROM STOCK WHERE Price BETWEEN 30 AND 50;Code Qty
AF0.5 300
MF0.5 250
PT0.5 78Write the output on execution of the query:
SELECT DISTINCT Type FROM STOCK;Type
F
T
DWrite the output on execution of the query:
SELECT Volume, count(*) FROM STOCK GROUP BY Volume;Volume count(*)
0.5 3
1.0 3