Write SQL Query commands based on the following table:

Question

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

b) To show Area_Covered for busses covering more than 20 km, but charge less than 80000. 

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

d) To show Rt no, Area_covered and average cost per student for all routes where average cost per student is – charges/no of students. 

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 : 

(i) Select sum(distance) from school where transporter= “Yadav Co.”; 

(ii) Select min(no of students) from school bus; 

(iii) Select avg (charges) from school bus where transporter = “Anand Travels”;

Answer 1

 

 a.Select * from SchoolBus order by Rtno where Capacity > Noofstudent. 

 

b.Select Area_cover from SchoolBus where Distance > 20 and  Charges < 80000.

c.Select Transporter , Noofstudent from SchoolBus group by Transporter

d.Select rtno , area_covered , Charges / Noofstudent as Average from SchoolBus

e.Insert into SchoolBus values (11, "Motibagh", 35, 32, 10, "kisan tours", 35000) 

f. 50

   40

   83000