DBMS-Slip18

Slip18
Consider the following Entities and Relationships                                   
Bus (bus_no, capacity, depot_no)
Route (rout_no, source, destination, no_of_stations)
Relation between Bus and Route is Many to One.
Constraint: Primary key.

Solution:-
Create a Database in 3NF & write queries for following.
•Find out the route details on which buses whose capacity is 20 runs.
SQL>Select  *  from Bus,Route where Bus. route_no=Route. rout_no   and capacity =20;

•Display number of stations from 'Chinchwad'  to  ' Katraj'.
SQL> Select no_of_stations from Route  where   source= ' Chinchwad ' and  destination=' Katraj ';

•Display the route on which more than 3 buses runs.
SQL> Select Route.rout_no,source, destination from Bus,Route  where Route.rout_no=Bus.rout_no group by Route.rout_no,source, destination  having count (Bus.rout_no)>3;

•Display number of buses of route ‘Swargate’ to ‘Hadapsar’.
SQL>select count(bus_no)from Bus,Route where Route.rout_no=Bus.rout_no  and
source=' Swargate ' and destination=' Hadapsar ';

•Findthe bust having maximum capacity from  ‘Nigadi’ to 'Kothrud'..
SQL>Select bus_no,capacity from Bus,Route  where Route.rout_no=Bus.rout_no
and source='Nigadi' and destination='Kothrud' group by bus_no,capacity
having capacity=(select max(capacity)) from Bus,Route;