Database Query using SQL — Question 19
Back to all questionsWrite 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:
- SELECT SUM(Distance) FROM SCHOOLBUS WHERE Transporter= "Yadav Co.";
- SELECT MIN(NoOfStudents) FROM SCHOOLBUS;
- SELECT AVG(Charges) FROM SCHOOLBUS WHERE Transporter = "Anand Travels";
- SELECT DISTINCT Transporter FROM SCHOOLBUS;
(a)
SELECT *
FROM SCHOOLBUS
WHERE Capacity > NoOfStudents
ORDER BY Rtno;+------+--------------+----------+--------------+----------+---------------+---------+
| 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;+--------------+
| Area_Covered |
+--------------+
| PITAMPURA |
+--------------+
(c)
SELECT Transporter, SUM(NoOfStudents) AS Total_Students
FROM SCHOOLBUS
GROUP BY Transporter;+----------------+----------------+
| 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;+------+---------------+--------------------------+
| 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.";
+---------------+
| SUM(Distance) |
+---------------+
| 50 |
+---------------+
2. SELECT MIN(NoOfStudents) FROM SCHOOLBUS;
+-------------------+
| MIN(NoOfStudents) |
+-------------------+
| 40 |
+-------------------+
3. SELECT AVG(Charges) FROM SCHOOLBUS WHERE Transporter = "Anand Travels";
+--------------+
| AVG(Charges) |
+--------------+
| 81666.6667 |
+--------------+
4. SELECT DISTINCT Transporter FROM SCHOOLBUS;
+----------------+
| Transporter |
+----------------+
| SHIVAM TRAVELS |
| ANAND TRAVELS |
| BHALLA CO. |
| YADAV CO. |
| SPEED TRAVELS |
| KISAN TOURS |
+----------------+