Bright Tutorials Bright Tutorials
CBSE Class 12 Informatics Practices Question 85 of 103

Review of Database Concepts & SQL — Question 34

Back to all questions
34
Question

Question 23

Consider the given table and answer the questions.

Table: SchoolBus

RtnoArea_CoveredCapacityNoofstudentsDistanceTransporterCharges
1Vasant Kunj10012010Shivam Travels3500
2Hauz Khas808010Anand Travels3000
3Pitampura605530Anand Travels4500
4Rohini1009035Anand Travels5000
5Yamuna Vihar506020Bhalla Travels3800
6Krishna Nagar708030Yadav Travels4000
7Vasundhara10011020Yadav Travels3500
8Paschim Vihar404020Speed Travels3200
9Saket12012010Speed Travels3500
10Janakpuri10010020Kisan Tours3500

(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 display the details of school Bus having no. of students less than 50.

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

Answer

(a)

SELECT * FROM SCHOOLBUS WHERE CAPACITY > 70;
Output
+------+--------------+----------+--------------+----------+----------------+---------+
| 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 |
|    4 | ROHINI       |      100 |           90 |       35 | ANAND TRAVELS  |    5000 |
|    7 | VASUNDHARA   |      100 |          110 |       20 | YADAV TRAVELS  |    3500 |
|    9 | SAKET        |      120 |          120 |       10 | SPEED TRAVELS  |    3500 |
|   10 | JANAKPURI    |      100 |          100 |       20 | KISAN TOURS    |    3500 |
+------+--------------+----------+--------------+----------+----------------+---------+

(b)

SELECT AREA_COVERED FROM SCHOOLBUS WHERE DISTANCE > 20 AND CHARGES < 4000;

(c)

SELECT * FROM SCHOOLBUS WHERE NOOFSTUDENTS < 50;
Output
+------+---------------+----------+--------------+----------+---------------+---------+
| RTNO | AREA_COVERED  | CAPACITY | NOOFSTUDENTS | DISTANCE | TRANSPORTER   | CHARGES |
+------+---------------+----------+--------------+----------+---------------+---------+
|    8 | PASCHIM VIHAR |       40 |           40 |       20 | SPEED TRAVELS |    3200 |
+------+---------------+----------+--------------+----------+---------------+---------+

(d)

SELECT RTNO, AREA_COVERED, (CHARGES/NOOFSTUDENTS) AS AVERAEG_COST 
FROM SCHOOLBUS;
Output
+------+---------------+--------------+
| RTNO | AREA_COVERED  | AVERAEG_COST |
+------+---------------+--------------+
|    1 | VASANT KUNJ   |      29.1667 |
|    2 | HAUZ KHAS     |      37.5000 |
|    3 | PITAMPURA     |      81.8182 |
|    4 | ROHINI        |      55.5556 |
|    5 | YAMUNA VIHAR  |      63.3333 |
|    6 | KRISHNA NAGAR |      50.0000 |
|    7 | VASUNDHARA    |      31.8182 |
|    8 | PASCHIM VIHAR |      80.0000 |
|    9 | SAKET         |      29.1667 |
|   10 | JANAKPURI     |      35.0000 |
+------+---------------+--------------+

(e)

INSERT INTO SCHOOLBUS 
VALUES(11, "MOTIBAGH", 35, 32, 10, "KISAN TOURS", 3500);