Comprehension

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:

CodeTypeVolumeQtyPrice
AF0.5F0.530038.00
MF0.5F0.525036.50
MT1.0T1.015064.00
AT1.0T1.010066.00
PD1.0D1.05052.00
PT0.5T0.57830.00
Question: 1

Based on the data given above, write the SQL query: To display Type and the maximum Price for each Type of milk.

Show Hint

Use the aggregate function MAX on Price and group the rows by Type with GROUP BY.
Updated On: Oct 20, 2026
Show Solution
collegedunia
Verified By Collegedunia

Solution and Explanation

Step 1: Find what the query must show.
We need each Type of milk once, along with the highest Price of that Type.
So we need a group for every Type and a maximum value of Price inside each group.

Step 2: Choose the clauses.
MAX(Price) is an aggregate function. It returns the largest value of a column.
GROUP BY Type puts all rows with the same Type in one group. MAX is then worked out for each group.

Step 3: Write the query.
SELECT Type, MAX(Price) FROM STOCK GROUP BY Type;

Step 4: Check with the data.
Type F has prices 38.00 and 36.50, so the maximum is 38.00.
Type T has prices 64.00, 66.00 and 30.00, so the maximum is 66.00.
Type D has one price, 52.00, so the maximum is 52.00.

Final Answer:
The query uses MAX(Price) with GROUP BY Type. It gives these rows:
F  38.00
T  66.00
D  52.00
Was this answer helpful?
0
0
Question: 2

Write the SQL query: For each record, increase the Price by 0.5 where Type is 'F'.

Show Hint

Use UPDATE with SET Price = Price + 0.5 and add a WHERE condition on Type.
Updated On: Oct 20, 2026
Show Solution
collegedunia
Verified By Collegedunia

Solution and Explanation

Step 1: Find the command.
We must change the value stored in a column of existing rows. In SQL, UPDATE is the command that changes existing data.

Step 2: Find the change.
The new Price must be the old Price plus 0.5. So the SET part is Price = Price + 0.5.

Step 3: Find which rows change.
Only the rows where Type is 'F' must change. This is the WHERE condition: Type = 'F'.
Without WHERE, every row would get the increase.

Step 4: Write the query.
UPDATE STOCK SET Price = Price + 0.5 WHERE Type = 'F';

Step 5: Check with the data.
Two rows have Type 'F'. AF0.5 changes from 38.00 to 38.50. MF0.5 changes from 36.50 to 37.00.
All other rows stay the same.

Final Answer:
UPDATE STOCK SET Price = Price + 0.5 WHERE Type = 'F';
Was this answer helpful?
0
0
Question: 3

Write the SQL query: To display the total value of the stock (total of Qty × Price).

Show Hint

Multiply Qty and Price inside the SUM() function and read from the table STOCK.
Updated On: Oct 20, 2026
Show Solution
collegedunia
Verified By Collegedunia

Solution and Explanation

Step 1: Understand the value needed.
The value of one record is Qty multiplied by Price. The total stock value is the sum of this product over all records.

Step 2: Choose the function.
SUM() is the aggregate function that adds the values of a column or an expression.
We pass the expression Qty * Price inside it, so each row is multiplied first and then added.

Step 3: Write the query.
SELECT SUM(Qty * Price) FROM STOCK;

Step 4: Check with the data.
AF0.5: 300 x 38.00 = 11400.
MF0.5: 250 x 36.50 = 9125.
MT1.0: 150 x 64.00 = 9600.
AT1.0: 100 x 66.00 = 6600.
PD1.0: 50 x 52.00 = 2600.
PT0.5: 78 x 30.00 = 2340.
Total = 11400 + 9125 + 9600 + 6600 + 2600 + 2340 = 41665.

Final Answer:
SELECT SUM(Qty * Price) FROM STOCK;
The result is 41665.
Was this answer helpful?
0
0
Question: 4

Write the SQL query: To display the details of all records where Code starts with 'A'.

Show Hint

Use the LIKE operator with the pattern 'A%' on the Code column and select all columns.
Updated On: Oct 20, 2026
Show Solution
collegedunia
Verified By Collegedunia

Solution and Explanation

Step 1: Understand the need.
We need all columns of the rows whose Code begins with the letter A. This is a pattern match on text, not an exact match.

Step 2: Choose the operator.
The LIKE operator matches text against a pattern. In the pattern, % stands for any number of characters (even none).
The pattern 'A%' means: starts with A, followed by anything.

Step 3: Write the query.
SELECT * FROM STOCK WHERE Code LIKE 'A%';

The star (*) shows all columns, so the full details of each record appear.

Step 4: Check with the data.
Codes are AF0.5, MF0.5, MT1.0, AT1.0, PD1.0 and PT0.5.
Only AF0.5 and AT1.0 start with A.

Final Answer:
SELECT * FROM STOCK WHERE Code LIKE 'A%';
It shows the rows AF0.5 (F, 0.5, 300, 38.00) and AT1.0 (T, 1.0, 100, 66.00).
Was this answer helpful?
0
0
Question: 5

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');

Show Hint

IN ('F','D') keeps rows of Type F or D. Show only Volume, Qty and Price for those rows.
Updated On: Oct 20, 2026
Show Solution
collegedunia
Verified By Collegedunia

Solution and Explanation

Step 1: Read the query.
The query shows three columns: Volume, Qty and Price. It keeps only the rows where Type is F or D.
The operator IN ('F','D') is a short way to write Type = 'F' OR Type = 'D'.

Step 2: Test each row against the condition.
AF0.5 has Type F, so it is selected.
MF0.5 has Type F, so it is selected.
MT1.0 has Type T, so it is rejected.
AT1.0 has Type T, so it is rejected.
PD1.0 has Type D, so it is selected.
PT0.5 has Type T, so it is rejected.

Step 3: Keep only the asked columns.
From each selected row we show Volume, Qty and Price.
AF0.5 gives 0.5, 300, 38.00.
MF0.5 gives 0.5, 250, 36.50.
PD1.0 gives 1.0, 50, 52.00.

Final Answer:
The output has 3 rows:
Volume  Qty  Price
0.5     300  38.00
0.5     250  36.50
1.0      50  52.00
Was this answer helpful?
0
0
Question: 6

Write the output on execution of the query:

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

Show Hint

BETWEEN 30 AND 50 includes 30 and 50. Check the Price of every row and show Code and Qty.
Updated On: Oct 20, 2026
Show Solution
collegedunia
Verified By Collegedunia

Solution and Explanation

Step 1: Read the query.
The query shows Code and Qty of those rows where Price is from 30 to 50.
BETWEEN 30 AND 50 includes both end values. It is the same as Price >= 30 AND Price <= 50.

Step 2: Test the Price of each row.
AF0.5 has Price 38.00. It lies in the range, so it is selected.
MF0.5 has Price 36.50. It lies in the range, so it is selected.
MT1.0 has Price 64.00. It is above 50, so it is rejected.
AT1.0 has Price 66.00. It is above 50, so it is rejected.
PD1.0 has Price 52.00. It is above 50, so it is rejected.
PT0.5 has Price 30.00. It equals the lower end, so it is selected.

Step 3: Show the asked columns.
AF0.5 gives 300, MF0.5 gives 250 and PT0.5 gives 78.

Final Answer:
The output has 3 rows:
Code    Qty
AF0.5   300
MF0.5   250
PT0.5    78
Was this answer helpful?
0
0
Question: 7

Write the output on execution of the query:

SELECT DISTINCT Type FROM STOCK;

Show Hint

DISTINCT removes repeated values. List the Type of each row and keep each letter once.
Updated On: Oct 20, 2026
Show Solution
collegedunia
Verified By Collegedunia

Solution and Explanation

Step 1: Read the query.
SELECT DISTINCT Type FROM STOCK shows the Type column only. DISTINCT removes repeated values, so each value appears once.

Step 2: List the Type of every row.
The Types in table order are F, F, T, T, D, T.

Step 3: Remove the repeats.
F appears first at row 1, so it is kept. The second F is a repeat and is dropped.
T appears first at row 3, so it is kept. The other T values are dropped.
D appears only once, so it is kept.
The values stay in order of first appearance: F, T, D.

Final Answer:
The output has one column and 3 rows:
Type
F
T
D
Was this answer helpful?
0
0
Question: 8

Write the output on execution of the query:

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

Show Hint

Group the rows by Volume and count the rows in each group with count(*).
Updated On: Oct 20, 2026
Show Solution
collegedunia
Verified By Collegedunia

Solution and Explanation

Step 1: Read the query.
GROUP BY Volume puts the rows with the same Volume in one group. COUNT(*) then counts the rows of each group.
The output shows the Volume and the number of rows for it.

Step 2: Sort the rows into groups.
Volume 0.5: AF0.5, MF0.5 and PT0.5.
Volume 1.0: MT1.0, AT1.0 and PD1.0.

Step 3: Count each group.
The group with Volume 0.5 has 3 rows.
The group with Volume 1.0 has 3 rows.

Final Answer:
The output has 2 rows:
Volume  count(*)
0.5     3
1.0     3
Was this answer helpful?
0
0