Question

Informatics Practices SQL Assignment: 1 2017 class XII
Q1. 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
Vasundhara
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


To show all information of students where capacity is more than the no of student in
order of rtno.
To show area_covered for buses more than 20 km., but charges less than 80000.
To show the area covered by Bhalla Co. and kisan tours
To show the names of all the transporters without repeating their names.
To show all the bus routes number where the distance is less than 30.
To show the area whose names end with r in decreasing order of the area covered.
To show rtno, area_covered and average cost per student for all routes where average
cost per student is - charges/noofstudents
To show the number of areas covered by all the bus routes
To show the number of area covered by each transporter.
To show the maximum capacity of a bus
To show transporter wise total number of students traveling.
Add new record with data: (11, Moti bagh,35,32,10, kisan tours , 35000)
Give the output considering the original relation as given :
(i) select sum(distance) from schoolbus where transporter= Yadav Co.;
(ii) select min (noofstudents) from schoolbus;
(iii) select avg(charges) from schoolbus where transporter= Anand travels;
(iv) select distinct transporter from schoolbus ;
(v) select count(distinct transporter) from schoolbus ;
(vi) select count(*) from schoolbus ;
(vii) select transporter, count(*) from schoolbus group by transporter ;
(viii) select transporter, max(charges) from schoolbus group by transporter ;

Answer 1

Answer:

select sum(distance) from schoolbus where transporter= Yadav Co.;

(ii) select min (noofstudents) from schoolbus;

(iii) select avg(charges) from schoolbus where transporter= Anand travels;

(iv) select distinct transporter from schoolbus ;

(v) select count(distinct transporter) from schoolbus ;

(vi) select count(*) from schoolbus ;

(vii) select transporter, count(*) from schoolbus group by transporter ;

(viii) select transporter, max(charges) from schoolbus group by transporter ;