DBMS 15 SLIPS
Q.15) Route_Bus
create table route017
(rout_no number(10)primary key,
source varchar(20),
destination varchar2(20),
no_of_stations number(10)
);
create table bus016
(
bus_no number(10)primary key,
capacity number(10),
depot_no number(5),
rout_no number(10) references route017(rout_no)
);
insert into route017 values(3,'chinchwad','katraj',8);
insert into route017 values(4,'swargate','hadapsar',4);
insert into route017 values(5,'nigadi','kothrud',3);
insert into route017 values(8,'narhe','pune',6);
insert into bus016 values(101,20,5012,3);
insert into bus016 values(102,15,6445,4);
insert into bus016 values(103,5,6352,5);
insert into bus016 values(104,10,8579,8);
1. select route017.* from route017,bus016
where route017.rout_no=bus016.rout_no and
bus016.capacity=20;
ROUT_NO SOURCE DESTINATION NO_OF_STATIONS
------------------ ------------ ----------------------------- ------------------------------------
3 chinchwad katraj 8
2. select no_of_stations from route017
where source='chinchwad' and
destination='katraj';
NO_OF_STATIONS
---------------------------
8
3. select route017.rout_no from route017,bus016
where route017.rout_no=bus016.rout_no and
bus016.bus_no>3;
ROUT_NO
-----------------
3
4
5
8
4. select COUNT(bus016.bus_no) from route017,bus016
where route017.rout_no=bus016.rout_no and
route017.source='swargate' and
route017.destination='hadapsar';
COUNT(BUS016.BUS_NO)
-----------------------------------
1
5. select MAX(bus016.capacity) from route017,bus016
where route017.rout_no=bus016.rout_no and
route017.source='nigadi' and
route017.destination='kothrud';
MAX(BUS016.CAPACITY)
----------------------------------
5
Tags:
Dbms