DBMS Practical Slips

3.Train_Passenger

create table tra201

(
tr_no number(5) primary key,
tr_name varchar(10),
dep_time number(10),
ar_time number(10),
sr_stn varchar2(10),
dst_stn varchar2(10)
);

create table pass202
(
p_id number(5) primary key,
p_name varchar(10),
address varchar2(10),
age number(5),
gen varchar2(10)
);

create table tra_pass203
(
tr_no number(5) references tra201 (tr_no),
p_id  number(5) references pass202 (p_id),
seat_no number(5),
amt number(10),
dt date
);

insert into tra201 values (5267,'kamayani',11,10,'pune','jammu');
insert into tra201 values (5276,'kamayani',10,11,'jammu','pune');
insert into tra201 values (123,'kokan',4,8,'pune','kokan');
insert into tra201 values (345,'shatabdi',9,10,'pune','mumbai');

insert into pass202 values (10,'ankit','katraj',70,'male');
insert into pass202 values (12,'rohit','Vadgaon',60,'male');
insert into pass202 values (13,'hitesh','pune',65,'male');
insert into pass202 values (14,'pooja','nasik',70,'female');

insert into tra_pass203 values (5267,10,26,400,'2 jan 2013');
insert into tra_pass203 values (5276,12,28,400,'2 feb 2013');
insert into tra_pass203 values (123,13,23,500,'5 jan 2013');
insert into tra_pass203 values (345,14,33,600,'21 jan 2013');

1. select * from pass202 where age>50;

      P_ID P_NAME     ADDRESS           AGE GEN
---------- ---------- ---------- ---------- ----------
        10 ankit      katraj             70 male
        12 rohit      Vadgaon            60 male
        13 hitesh     pune               65 male
        14 pooja      nasik              70 female

2. select SUM(amt) from tra201,pass202,tra_pass203
where tra201.tr_no=tra_pass203.tr_no and pass202.p_id=tra_pass203.p_id and
tra201.tr_name='kokan' and tra_pass203.dt='5 jan 2013';

  SUM(AMT)
----------
       500

3.  select count(pass202.p_id) from tra201,pass202,tra_pass203
where tra201.tr_no=tra_pass203.tr_no and pass202.p_id=tra_pass203.p_id and
tra201.sr_stn='pune' and tra201.dst_stn='mumbai';

COUNT(PASS202.P_ID)
-------------------
                  1

4. delete tra_pass203
where tra_pass203.dt='21 jan 2013';

5. select COUNT(pass202.p_id) from tra201,pass202,tra_pass203
where tra201.tr_no=tra_pass203.tr_no AND
pass202.p_id=tra_pass203.p_id AND
tra201.tr_name='shatabdi express' AND
pass202.gen='male';

COUNT(PASS202.P_ID)
-------------------
                  0