DBMS Practical Slips

14.Person_Car

create table pers
(
did number(10) primary key,
dname varchar2(20),
address varchar2(20)
);

create table car
(
lno number(10) primary key,
model varchar2(20),
year number(5)
);

create table p_c
(
did number(10) references pers(did),
lno number(10) references car(lno),
dt date,
time number(10)
);

insert into pers values(21,'samir','pune');
insert into pers values(22,'pradip','mumbai');
insert into pers values(23,'rajan','nasik');
insert into pers values(24,'sumit','pune');
insert into pers values(25,'saif','mumbai');

insert into car values(1234,'alto',2014);
insert into car values(2345,'vista',2000);
insert into car values(789,'desire',2000);
insert into car values(34,'innova',2017);
insert into car values(5647,'alto',2015);

insert into p_c values(21,1234,'2 jan 2017',8);
insert into p_c values(22,2345,'13 mar 2017',9);
insert into p_c values(23,789,'12 feb 2017',7);
insert into p_c values(24,34,'9 jul 2017',6);
insert into p_c values(25,5647,'2 jun 2017',8);

1.select pers.* from pers,car,p_c
where pers.did=p_c.did and
car.lno=p_c.lno and
car.model='alto';

      DID DNAME                ADDRESS
--------- -------------------- ------------
       21 samir                pune
       25 saif                 mumbai

2.select count(car.lno) from pers,car,p_c
where  pers.did=p_c.did and
car.lno=p_c.lno group by dname;

COUNT(CAR.LNO)
--------------
             1
             1
             1
             1
             1

3.select car.*from pers,car,p_c
where  pers.did=p_c.did and
car.lno=p_c.lno  and
car.year<2017;

       LNO MODEL                      YEAR
---------- -------------------- ----------
      1234 alto                       2014
      2345 vista                      2000
       789 desire                     2000
      5647 alto                       2015

4.select p_c.dt from pers,car,p_c
where  pers.did=p_c.did and
car.lno=p_c.lno  and



5. select count(pers.did)from pers,car,p_c
where  pers.did=p_c.did and
car.lno=p_c.lno group by year;

COUNT(PERS.DID)
---------------
              1
              1
              2
              1