Bright Tutorials Bright Tutorials
CBSE Class 12 Informatics Practices Question 12 of 18

Practice Paper — Question 3

Back to all questions
3
Question

Question 28(a)

A relation Vehicles is given below:

V_noTypeCompanyPriceQty
AW125WagonRMaruti25000025
J0083JeepMahindra400000015
S9090SUVMitsubishi250000018
M0892Mini vanDatsun150000026
W9760SUVMaruti250000018
R2409Mini vanMahindra35000015

Write SQL commands to:

(i) Display the average price of each type of vehicle having a quantity of more than 20.

(ii) Count the type of vehicles manufactured by each company.

(iii) Display the total price of all types of vehicles.

Answer

(i)

SELECT Type, AVG(Price) AS Average_Price
FROM Vehicles
WHERE Qty > 20
GROUP BY Type;
Output
+----------+---------------+
| Type     | Average_Price |
+----------+---------------+
| WagonR   |   250000.0000 |
| Mini van |  1500000.0000 |
+----------+---------------+

(ii)

SELECT Company, COUNT(DISTINCT Type) AS Num_Vehicle_Types
FROM Vehicles
GROUP BY Company;
Output

+------------+-------------------+
| Company    | Num_Vehicle_Types |
+------------+-------------------+
| Datsun     |                 1 |
| Mahindra   |                 2 |
| Maruti     |                 2 |
| Mitsubishi |                 1 |
+------------+-------------------+

(iii)

SELECT SUM(Price * Qty) AS Total_Price
FROM Vehicles;
Output
+-------------+
| Total_Price |
+-------------+
|   200500000 |
+-------------+