Bright Tutorials Bright Tutorials
CBSE Class 12 Informatics Practices Question 71 of 81

MySQL Functions — Question 1

Back to all questions
1
Question

Question 1

Based on the SQL table CAR_SALES, write suitable queries for the following :

NUMBERSEGMENTFUELQT1QT2
1Compact HatchBackPetrol5600070000
2Compact HatchBackDiesel3400040000
3MUVPetrol3300035000
4MUVDiesel1400015000
5SUVPetrol2700054000
6SUVDiesel1800030000
7SedanPetrol800010000
8SedanDiesel10005000

(i) Display fuel wise average sales in the first quarter.

(ii) Display segment wise highest sales in the second quarter.

(iii) Display the records in the descending order of sales in the second quarter.

Answer

(i)

SELECT FUEL, AVG(QT1) AS Avg_Sales_QT1 
FROM CAR_SALES 
GROUP BY FUEL;
Output
+--------+---------------+
| FUEL   | Avg_Sales_QT1 |
+--------+---------------+
| Petrol |    31000.0000 |
| Diesel |    16750.0000 |
+--------+---------------+

(ii)

SELECT SEGMENT, MAX(QT2) AS HIGHEST_SALES
FROM CAR_SALES
GROUP BY SEGMENT;
Output
+-------------------+---------------+
| SEGMENT           | HIGHEST_SALES |
+-------------------+---------------+
| Compact HatchBack |         70000 |
| MUV               |         35000 |
| SUV               |         54000 |
| Sedan             |         10000 |
+-------------------+---------------+

(iii)

SELECT * FROM CAR_SALES
ORDER BY QT2 DESC;
Output
+--------+-------------------+--------+-------+-------+
| NUMBER | SEGMENT           | FUEL   | QT1   | QT2   |
+--------+-------------------+--------+-------+-------+
|      1 | Compact HatchBack | Petrol | 56000 | 70000 |
|      5 | SUV               | Petrol | 27000 | 54000 |
|      2 | Compact HatchBack | Diesel | 34000 | 40000 |
|      3 | MUV               | Petrol | 33000 | 35000 |
|      6 | SUV               | Diesel | 18000 | 30000 |
|      4 | MUV               | Diesel | 14000 | 15000 |
|      7 | Sedan             | Petrol |  8000 | 10000 |
|      8 | Sedan             | Diesel |  1000 |  5000 |
+--------+-------------------+--------+-------+-------+