CBSE Class 11 Informatics Practices
Question 69 of 87
Structured Query Language (SQL) — Question 30
Back to all questions 30
Question Consider the given table and answer the questions.
Table: SCHOOLBUS
| Rtno | Area_Covered | Capacity | Noofstudents | Distance | Transporter | Charges |
|---|---|---|---|---|---|---|
| 1 | Vasant Kunj | 100 | 120 | 10 | Shivam Travels | 3500 |
| 2 | Hauz Khas | 80 | 80 | 10 | Anand Travels | 3000 |
| 3 | Pitampura | 60 | 55 | 30 | Anand Travels | 4500 |
| 4 | Rohini | 100 | 90 | 35 | Anand Travels | 5000 |
| 5 | Yamuna Vihar | 50 | 60 | 20 | Bhalla Travels | 3800 |
| 6 | Krishna Nagar | 70 | 80 | 30 | Yadav Travels | 4000 |
| 7 | Vasundhara | 100 | 110 | 20 | Yadav Travels | 3500 |
| 8 | Paschim Vihar | 40 | 40 | 20 | Speed Travels | 3200 |
| 9 | Saket | 120 | 120 | 10 | Speed Travels | 3500 |
| 10 | Janakpuri | 100 | 100 | 20 | Kisan Tours | 3500 |
(a) To show all information of schoolbus where capacity is more than 70.
(b) To show area_covered for buses covering more than 20 km but charges less than 4000.
(c) To show transporter-wise details along with their charges.
(d) To show Rtno, Area_Covered and Average cost per student for all routes where average cost per student is—Charges/Noofstudents.
(e) Add a new record with the following data:
(11, "Motibagh", 35, 32, 10, "Kisan Tours", 3500)
(a)
SELECT *
FROM SCHOOLBUS
WHERE CAPACITY > 70;+------+--------------+----------+--------------+----------+----------------+---------+
| Rtno | Area_Covered | Capacity | Noofstudents | Distance | Transporter | Charges |
+------+--------------+----------+--------------+----------+----------------+---------+
| 1 | Vasant Kunj | 100 | 120 | 10 | Shivam Travels | 3500.00 |
| 2 | Hauz Khas | 80 | 80 | 10 | Anand Travels | 3000.00 |
| 4 | Rohini | 100 | 90 | 35 | Anand Travels | 5000.00 |
| 7 | Vasundhara | 100 | 110 | 20 | Yadav Travels | 3500.00 |
| 9 | Saket | 120 | 120 | 10 | Speed Travels | 3500.00 |
| 10 | Janakpuri | 100 | 100 | 20 | Kisan Tours | 3500.00 |
+------+--------------+----------+--------------+----------+----------------+---------+
(b)
SELECT AREA_COVERED
FROM SCHOOLBUS
WHERE DISTANCE > 20 AND CHARGES < 4000;(c)
SELECT Transporter, SUM(Charges) AS Total_Charges
FROM SCHOOLBUS
GROUP BY Transporter;+----------------+---------------+
| Transporter | Total_Charges |
+----------------+---------------+
| Shivam Travels | 3500 |
| Anand Travels | 12500 |
| Bhalla Travels | 3800 |
| Yadav Travels | 7500 |
| Speed Travels | 6700 |
| Kisan Tours | 3500 |
+----------------+---------------+
(d)
SELECT RTNO, AREA_COVERED, (CHARGES/NOOFSTUDENTS) AS AVERAEG_COST
FROM SCHOOLBUS;+------+---------------+--------------+
| RTNO | AREA_COVERED | AVERAEG_COST |
+------+---------------+--------------+
| 1 | Vasant Kunj | 29.166667 |
| 2 | Hauz Khas | 37.500000 |
| 3 | Pitampura | 81.818182 |
| 4 | Rohini | 55.555556 |
| 5 | Yamuna Vihar | 63.333333 |
| 6 | Krishna Nagar | 50.000000 |
| 7 | Vasundhara | 31.818182 |
| 8 | Paschim Vihar | 80.000000 |
| 9 | Saket | 29.166667 |
| 10 | Janakpuri | 35.000000 |
+------+---------------+--------------+
(e)
INSERT INTO SCHOOLBUS
VALUES(11, "MOTIBAGH", 35, 32, 10, "KISAN TOURS", 3500);