DBMS Practical Slips

10. Plan_Customer

create table plan
(
pno number(10) primary key,
pname varchar2(20),
no_free_call number(20),
fr_call_tim number(20),
f_amt number(20)
);

create table custm
(
cno number(10) primary key,
cname varchar2(20),
mo_no number(20),
pno number(10) references plan(pno)
);

insert into plan values(11,'lets rock',12,4,500);
insert into plan values(12,'jeevan',18,2,700);
insert into plan values(13,'life',10,5,900);
insert into plan values(14,'jio',12,5,800);
insert into plan values(15,'go max',15,6,1000);

insert into custm values(1,'jiya',9890339055,11);
insert into custm values(2,'sudhir',9860266786,12);
insert into custm values(3,'shweta',9834562781,13);
insert into custm values(4,'sushma',8889234516,14);
insert into custm values(5,'john',9890342670,15);

1. select min(no_free_call) from plan;

MIN(NO_FREE_CALL)
-----------------
               10


2.select custm.* from custm where mo_no LIKE '98%';

       CNO CNAME                     MO_NO        PNO
---------- -------------------- ---------- ----------
         1 jiya                 9890339055         11
         2 sudhir               9860266786         12
         3 shweta               9834562781         13
         5 john                 9890342670         15


3.select custm.* from plan,custm
where plan.pno=custm.pno and
plan.no_free_call>12;

       CNO CNAME                     MO_NO        PNO
---------- -------------------- ---------- ----------
         2 sudhir               9860266786         12
         5 john                 9890342670         15


4. delete from custm
where cname='john';

1 row deleted.


5. select pname from plan
where f_amt > 500;

PNAME
--------------------
jeevan
life
jio
go max