Rdbms Practical Silps

 RDBMS-3 Slips

Q.1) Consider the following entities and their relationship. [Marks 40]

Owner (o_no, o_name, o_city, o_ph_no)

Estate (e_no, e_type, e_city, e_price)

Relationship between Owner and Estate is one-to-many.

Constraints : primary key, foreign key,

o_name should not be null,

e_type can be flat, bungalow or land.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a procedure which will accept owner number and display details of all estates

of given owner which belongs to pune city.

2) Write a cursor which will display type wise estate details. (Use parameterized cursor)

3) Write a function which will count and returns number of owners who have purchase

estate in the same city in which they live.

4) Write a trigger that restricts insertion or updation of estate having price less than

1 lakh. (Raise user defined exception and give appropriate message)

Solution

create table ow011
(
ono number(10) primary key,
oname varchar2(20) NOT NULL,
ocity varchar2(10),
phno number(10)
);

create table est022
(
eno number(10) primary key,
etype varchar2(10) check(etype in('flat','bunglow','land')),
ecity varchar2(20),
eprice number(20),
ono number(10) references ow(ono)
);

insert into ow011 values(11,'mr.varma','pune',9890339055);
insert into ow011 values(12,'mr.sharma','pune',9890654320);
insert into ow011 values(13,'mr.seth','mumbai',9678901455);
insert into ow011 values(14,'mr.yadav','pune',9860345265);
insert into ow011 values(15,'mr.sinha','nasik',9890256789);

insert into est022 values(21,'flat','mumbai',2500000,11);
insert into est022 values(22,'land','pune',2200000,12);
insert into est022 values(23,'bunglow','nasik',3000000,13);
insert into est022 values(24,'land','nagar',2100000,14);
insert into est022 values(25,'flat','mumbai',3000000,15);



Q.1) Write a procedure which will accept owner number and display details of all estates
of given owner which belongs to pune city.

PROCEDURE

create or replace procedure p3
(pno in number)
as
cursor c3 is select est022.*  from ow011,est022
where ow011.ono=est022.ono and
ow011.oname='mr.sharma' and ecity='pune';
x c3%rowtype;
begin
for x in c3
loop
dbms_output.put_line(x.eno||'  '||x.etype||'    '||x.ecity||'    '||x.eprice);
end loop;
end p3;
/
Procedure created.

OUTPUT-
SQL> execute p3(12);
22  land    pune    2200000

PL/SQL procedure successfully completed.




Q.2) Write a cursor which will display type wise estate details. (Use parameterized cursor)

CURSOR
Declare
cursor c1(etyp est022.etype%type)is select etype,ecity,eprice from est022
where etype='land';
ctype est022.etype%type;
c c1%rowtype;
begin
ctype:=&ctype;
for c in c1(ctype)
loop
dbms_output.put_line(c.etype||'   '||c.ecity||'     '||c.eprice);
end loop;
end;
/

Output-
Enter value for ctype: 'land'
old   8: ctype:=&ctype;
new   8: ctype:='land';
land   pune     2200000
land   nagar     2100000

PL/SQL procedure successfully completed.

Q.3) Write a trigger that restricts insertion or updation of estate having price less than
1 lakh. (Raise user defined exception and give appropriate message)

 TRIGGER
create or replace trigger t054
before insert or update on est022
for each row
begin
if inserting then
if:new.eprice<100000 then
raise_application_error('-20010','eprice should be less than 100000');
end if;
end if;
if updating then
if:new.eprice<100000 then
raise_application_error('-20010','eprice should be less than 100000');
end if;
end if;
end;
/
Trigger created.

OUTPUT-
insert into est022 values(25,'flat','mumbai',80000,15);

SQL> insert into est values(25,'flat','mumbai',80000,15);
insert into est values(25,'flat','mumbai',80000,15)
            *
ERROR at line 1:
ORA-20010: eprice should be less than 100000
ORA-06512: at "SYSTEM.T054", line 4
ORA-04088: error during execution of trigger 'SYSTEM.T054'

SQL> update est022 set eprice=80000
  2  where eno=25;
update est022 set eprice=80000
       *
ERROR at line 1:
ORA-20010: eprice should be less than 100000
ORA-06512: at "SYSTEM.T054", line 9
ORA-04088: error during execution of trigger 'SYSTEM.T054'

Q.4) Write a function which will count and returns number of owners who have purchase
estate in the same city in which they live.

FUNCTION
create or replace function f3
(vcity in varchar2)
return number
as
cnt number;
begin
select count(ow011.ono) into cnt from ow011,est022
where ow011.ono=est022.ono;
if SQL%found then
return cnt;
else
return 0;
end if;
end f3;
/
Function created.

OUTPUT-
SQL> select f3('pune') from dual;

F3('PUNE')
----------
         1

//CALLING//
declare
a number;
b number;
begin
a:=&a;
b:= f3(a);
if(b>0) then
dbms_output.put_line('total no.of owners purchase estate in same city is'||b);
else
dbms_output.put_line('no data found');
end if;
end;
/

OUTPUT-
Enter value for a: 22
old   5: a:=&a;
new   5: a:=22;
total no.of owners purchase estate in same city is1

PL/SQL Procedure successfully completed.