Consider the given table and answer the questions.

Q. Consider the given table and answer the questions.

Table: School Bus

 
 
  
 
	 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 travels 
	 55000 
 
 
	 6 
	 Krishna Nagar 
	 70 
	 80 
	 30 
	 Yadav travels 
	 80000 
 
 
	 7 
	 Vasundhara 
	 100 
	 110 
	 20 
	 Yadav travels 
	 100000 
 
 
	 8 
	 Paschim Vihar 
	 40 
	 40 
	 20 
	 Speed travels 
	 55000 
 
 
	 9 
	 Saket 
	 120 
	 120 
	 10 
	 Speed travels 
	 100000 
 
 
	 10 
	 Janakpuri 
	 100 
	 100 
	 20 
	 Kisan Tours 
	 95000

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 travels	55000
6	Krishna Nagar	70	80	30	Yadav travels	80000
7	Vasundhara	100	110	20	Yadav travels	100000
8	Paschim Vihar	40	40	20	Speed travels	55000
9	Saket	120	120	10	Speed travels	100000
10	Janakpuri	100	100	20	Kisan Tours	95000

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

(ii) To show area_covered for buses covering more than 20 km., but charges less than 80000.

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

(iv) To show rtno, area_covered and average cost per student for allroutes where average cost per student is --- charge / noofstudents.

(v) Add a new record with the following data:

(11, "Motibagh", 35, 32, 10, "kisan tours", 35000)

(vi) Give the output considering the original relation as given:

(a) Select sum(distance) from school bus where transporter= "Yadav travels";

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

(c) Select avg(charges) from school bus where transporter = "Anand travels";

(d) Select distinct transporter from school bus;

Answer =

(i)

Select * from SchoolBus order by Rtno where Capacity > Noofstudent ;

(ii)

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

(iii)

SELECT TRANSPORTER , SUM(NoOfStudents) FROM SCHOOLBUS GROUP BY TRANSPORTER;

(iv)

Select rtno , area_covered , Charges / Noofstudent as Average from SchoolBus ;

(v)

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

(vi)

(a)

sum(distance)

(b)

min(no of students

(c)

avg(charges)

83000.0

(d)

Transporter

Shivam travels

Anand travels

Bhalla travels

Yadav travels

Speed travels

Kisan Tours

12 Comments

You can help us by Clicking on ads. ^_^
Please do not send spam comment : )

Unknown28 March 2021 at 22:48
Thank you sooo much. It helped me a lot
Unknown27 April 2021 at 12:15
Write SQL command to create the above table with constraints.
Insert 2 records with relevant information, in the table student
Display all the records of the table Student.
Delete the Student whose roll no. is 100
Change the Stream and Student to Computer whose roll no. in 536
View the structure of the table created by you.
Drop the table student.
Make all changes permanently
I wanted this Ans
Anonymous13 May 2021 at 11:30
b. Write sql query for following
(i) Display area_covered in uppercase along with distance
(ii) Display area_covered with largest length(number of characters)
(iii) Display area_covered with largest distance
(iv) Display the list of schoolbus where rightmost part of transporter
is travels
(v) Display the total distance covered by ‘Anand travels’
(vi)Display the transporter which charges maximum with regards to
all area covered taken together.
Anonymous5 November 2022 at 21:53
In part 3rd can we write order by rtno after no of students?
Anonymous28 June 2023 at 16:17
To show transporter-wise total no. of students travelling.
In this particular part your answer (Select Transporter , Noofstudent from SchoolBus group by Transporter ;) does not execute on mysql (shows error 1055). Moreover as total no. of students have been asked we should go for the sum aggregate function. If feel the actual command shd be like this
SELECT TRANSPORTER,SUM(NoOfStudents) FROM SCHOOLBUS GROUP BY TRANSPORTER;
Kindly give your views on this !!!!!!!

Path Walla