18)
Consider the following Entities and Relationships [30 Marks]
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.
Create a Database in 3NF & write queries for following.
• Find out the route details on which buses whose capacity is 20 runs.
• Display number of stations from 'Chinchwad' to ' Katraj'.
• Display the route on which more than 3 buses runs.
• Display number of buses of route ‘Swargate’ to ‘Hadapsar’.
• Find the bust having maximum capacity from ‘Nigadi’ to 'Kothrud'..
Solution:-
Create a Database in3NF & 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> Selecct 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.route_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 ';
• Find the 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;
/
• Display number of stations from 'Chinchwad' to ' Katraj'. SQL> Selecct 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.route_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 ';
• Find the 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;
Search
Recent Posts
Mail Management System
Posted on 2019-07-18Online food ordering system
Posted on 2019-07-18Library Management System
Posted on 2019-07-17Health center system project
Posted on 2019-07-17Gym Management System
Posted on 2019-07-17furniture management system
Posted on 2019-07-17Electronic shop management system
Posted on 2019-07-17Automobile Workshop Management
Posted on 2019-07-17Online Visa Processing System
Posted on 2019-07-17Inventory management System
Posted on 2019-07-17petrol-management system
Posted on 2019-07-17Cloths management system
Posted on 2019-07-17Society Management system
Posted on 2019-07-17Mall management system
Posted on 2019-07-17school management system
Posted on 2019-07-17Sales Order Processing System
Posted on 2019-07-17Retail sales management
Posted on 2019-07-17Raw Materials Management
Posted on 2019-07-17railway reservation system
Posted on 2019-07-17purchase and sales management system
Posted on 2019-07-17Placement Management System
Posted on 2019-07-17Pet Shop Management System
Posted on 2019-07-17petrol pump management system
Posted on 2019-07-17Patient Information System
Posted on 2019-07-17news agency system
Posted on 2019-07-17Cinema Booking System
Posted on 2019-07-17Medical Store System
Posted on 2019-07-17leave management System
Posted on 2019-07-17Laboratory Information Management System
Posted on 2019-07-17content management system
Posted on 2019-07-17Inventory management System
Posted on 2019-07-17Institute Management System
Posted on 2019-07-17Hotel management System
Posted on 2019-07-17Gym Management System
Posted on 2019-07-17Garage Management System
Posted on 2019-07-17Furniture shop management system
Posted on 2019-07-17Fisheries management System
Posted on 2019-07-17Fertilizer scheduling system
Posted on 2019-07-17online eye care system
Posted on 2019-07-17Dental Clinic Management System
Posted on 2019-07-17Cyber Café Management
Posted on 2019-07-17Milk Billing System
Posted on 2019-07-17Colddrink management system
Posted on 2019-07-17Cable management System
Posted on 2019-07-17Beauty parlor management system
Posted on 2019-07-17Facebook Clone
Posted on 2019-05-28Dance Class Management System
Posted on 2019-05-24Library Management System
Posted on 2019-05-24Cab Management System
Posted on 2019-05-23Blood Bank Management system
Posted on 2019-05-23Beauty Parlour Management System
Posted on 2019-05-23vissa proccesing system
Posted on 2019-05-23Toll Plazza
Posted on 2019-05-23BILLING APPLICATION
Posted on 2019-05-23FLORICULTURE MANAGEMENT SYSTEM
Posted on 2019-05-23Car On Rent
Posted on 2019-05-23E-commers Shop
Posted on 2019-05-23