DBMS Practical Slips

8. Politician_Party

create table  party
(
pcode number(10) primary key,
pname varchar2(20),
no_memb number(20)
);

create table politi1
(
pno number(10) primary key,
pname varchar2(20),
tele_no number(20),
pcode number(10) references party(pcode)
);



insert into party values(11,'congress',25);
insert into party values(12,'mns',15);
insert into party values(13,'bjp',28);
insert into party values(14,'ncp',30);
insert into party values(15,'aap',18);

insert into politi1 values(20,'sharad',9890339552,11);
insert into politi1 values(30,'sagar',8484948899,12);
insert into politi1 values(40,'suraj',9860367892,13);
insert into politi1 values(50,'kavita',9890339055,14);
insert into politi1 values(60,'reshma',9856432712,15);


1] select pname from party order by pname ASC;
PNAME
--------------------
aap
bjp
congress
mns
ncp

2] select party.*  from party,politi1
where party.pcode=politi1.pcode and
party.no_memb<=25;
    PCODE PNAME                   NO_MEMB
--------- -------------------- ----------
       11 congress                     25
       12 mns                          15
       15 aap                          18

3] select party.pname,politi1.pname from party,politi1
where party.pcode=politi1.pcode;
PNAME                PNAME
-------------------- --------------------
congress             sharad
mns                  sagar
bjp                  suraj
ncp                  kavita
aap                  reshma

4] select party.* from party,politi1 where party.pcode=politi1.pcode and politi1.pname ='suraj';
     PCODE PNAME                   NO_MEMB
---------- -------------------- ----------
        13 bjp                          28

5] select max(party.no_memb) from party, politi1
where party.pcode=politi1.pcode;
MAX(PARTY.NO_MEMB)
------------------
                30