A relation Vehicles is given below:

V_no	Type	Company	Price	Qty
AW125	WagonR	Maruti	250000	25
J0083	Jeep	Mahindra	4000000	15
S9090	SUV	Mitsubishi	2500000	18
M0892	Mini van	Datsun	1500000	26
W9760	SUV	Maruti	2500000	18
R2409	Mini van	Mahindra	350000	15

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.

(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 |
+-------------+