Write the SQL query commands based on the following table:

Table: SCHOOLBUS

Rtno	Area_Covered	Capacity	NoOfStudents	Distance	Transporter	Charges
1	Vasant Kunj	100	120	10	Shivam Travels	100000
2	Hauz Khas	80	80	10	Anand Travels	85000
3	Pitampura	60	55	30	Anand Travels	60000
4	Rohini	100	90	35	Anand Travels	100000
5	Yamuna Vihar	50	60	20	Bhalla Co.	55000
6	Krishna Nagar	70	80	30	Yadav Co.	80000
7	Vasundhra	100	110	20	Yadav Co.	100000
8	Paschim Vihar	40	40	20	Speed Travels	55000
9	Saket	120	120	10	Speed Travels	100000
10	Janak Puri	100	100	20	Kisan Tours	95000

(a) To show all information of students where capacity is more than the number of students in order of rtno.

(b) To show area_covered for buses covering more than 20 km, but charge is less than 80000.

(c) To show transporter-wise total no. of students travelling.

(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, "Moti Bagh", 35,32,10, "Kisan Tours", 35000)

(f) Give the output considering the original relation as given below:

1. SELECT SUM(Distance) FROM SCHOOLBUS WHERE Transporter= "Yadav Co.";
2. SELECT MIN(NoOfStudents) FROM SCHOOLBUS;
3. SELECT AVG(Charges) FROM SCHOOLBUS WHERE Transporter = "Anand Travels";
4. SELECT DISTINCT Transporter FROM SCHOOLBUS;

(a)

SELECT * 
FROM SCHOOLBUS 
WHERE Capacity > NoOfStudents 
ORDER BY Rtno;

Output

+------+--------------+----------+--------------+----------+---------------+---------+
| RTNO | AREA_COVERED | CAPACITY | NOOFSTUDENTS | DISTANCE | TRANSPORTER   | CHARGES |
+------+--------------+----------+--------------+----------+---------------+---------+
|    3 | PITAMPURA    |       60 |           55 |       30 | ANAND TRAVELS |   60000 |
|    4 | ROHINI       |      100 |           90 |       35 | ANAND TRAVELS |  100000 |
+------+--------------+----------+--------------+----------+---------------+---------+

(b)

SELECT Area_Covered 
FROM SCHOOLBUS 
WHERE Distance > 20 AND Charges < 80000;

Output

+--------------+
| Area_Covered |
+--------------+
| PITAMPURA    |
+--------------+

(c)

SELECT Transporter, SUM(NoOfStudents) AS Total_Students 
FROM SCHOOLBUS 
GROUP BY Transporter;

Output

+----------------+----------------+
| Transporter    | Total_Students |
+----------------+----------------+
| SHIVAM TRAVELS |            120 |
| ANAND TRAVELS  |            225 |
| BHALLA CO.     |             60 |
| YADAV CO.      |            190 |
| SPEED TRAVELS  |            160 |
| KISAN TOURS    |            100 |
+----------------+----------------+

(d)

SELECT Rtno, Area_Covered, (Charges / NoOfStudents) AS Average_Cost_Per_Student 
FROM SCHOOLBUS;

Output

+------+---------------+--------------------------+
| Rtno | Area_Covered  | Average_Cost_Per_Student |
+------+---------------+--------------------------+
|    1 | VASANT KUNJ   |                 833.3333 |
|    2 | HAUZ KHAS     |                1062.5000 |
|    3 | PITAMPURA     |                1090.9091 |
|    4 | ROHINI        |                1111.1111 |
|    5 | YAMUNA VIHAR  |                 916.6667 |
|    6 | KRISHNA NAGAR |                1000.0000 |
|    7 | VASUNDHARA    |                 909.0909 |
|    8 | PASCHIM VIHAR |                1375.0000 |
|    9 | SAKET         |                 833.3333 |
|   10 | JANAKPURI     |                 950.0000 |
+------+---------------+--------------------------+

(e)

INSERT INTO SCHOOLBUS 
VALUES (11, 'Moti Bagh', 35, 32, 10, 'Kisan Tours', 35000);

(f)

1. SELECT SUM(Distance) FROM SCHOOLBUS WHERE Transporter= "Yadav Co.";

Output

+---------------+
| SUM(Distance) |
+---------------+
|            50 |
+---------------+

2. SELECT MIN(NoOfStudents) FROM SCHOOLBUS;

Output

+-------------------+
| MIN(NoOfStudents) |
+-------------------+
|                40 |
+-------------------+

3. SELECT AVG(Charges) FROM SCHOOLBUS WHERE Transporter = "Anand Travels";

Output

+--------------+
| AVG(Charges) |
+--------------+
|   81666.6667 |
+--------------+

4. SELECT DISTINCT Transporter FROM SCHOOLBUS;

Output

+----------------+
| Transporter    |
+----------------+
| SHIVAM TRAVELS |
| ANAND TRAVELS  |
| BHALLA CO.     |
| YADAV CO.      |
| SPEED TRAVELS  |
| KISAN TOURS    |
+----------------+