RDBMS 12 Slips
Q.1) Consider the following entities and their relationship. [Marks 40]
Donor (donor_no, donor_name, city)
Blood_donation_detail (bd_no, blood_group, qty, date_of_collection)
Relationship between Donor and Blood_donation_detail is one-to-many.
Constraints: primary key, foreign key,
donor_name should not be null,
blood_group can be A+, A-, B+, B-, AB+, AB-, O+, O
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a function which will count total amount of blood collected for a given blood
group on given date.
2) Write a cursor which will display donor wise blood donation details. (Use
parameterized cursor)
3) Write a procedure which will display blood group wise total amount of quantity of
blood available.
4) Write a trigger that restricts insertion of blood_donation_details having quantity
greater than 300ml. (Raise user defined exception and give appropriate message)
create table dono
(
dno number(10) primary key,
dname varchar2(20) NOT NULL,
city varchar2(20)
);
create table bl_don
(
bdno number(10) primary key,
bgroup varchar2(10)check(bgroup in('A+','A-','B+','B-','O+','O-','AB+','AB-')),
quantity number(10),
dt date,
dno number(10)references dono(dno)
);
insert into dono values(11,'rahul','pune');
insert into dono values(12,'komal','mumbai');
insert into dono values(13,'suraj','pune');
insert into dono values(14,'piyush','nasik');
insert into dono values(15,'poja','thane');
insert into bl_don values(21,'A+',20,'28 jul 2018',11);
insert into bl_don values(23,'O+',25,'25 aug 2018',12);
insert into bl_don values(24,'AB+',30,'28 aug 2018',13);
insert into bl_don values(25,'AB-',35,'26 jul 2018',14);
insert into bl_don values(26,'B+',28,'28 aug 2018',15);
Q.1) Write a function which will count total amount of blood collected for a given blood
group on given date.
FUNCTION
create or replace function f12
(vdt in date)
return number
as
cnt number;
begin
select count(bl_don.quantity) into cnt from dono,bl_don
where dono.dno=bl_don.dno and
bl_don.bgroup='A+';
if SQL%found then
return cnt;
else
return 0;
end if;
end f12;
/
Function created.
SQL> select f12('28 jul 2018')from dual;
F12('28JUL2018')
----------------
1
//calling//
declare
a date;
b number;
begin
a:=&a;
b:=f12(a);
if(b>0) then
dbms_output.put_line('total amount of blood collect on given date is'||b);
else
dbms_output.put_line('no data found');
end if;
end;
/
OUTPUT-
Enter value for a: '28 jul 2018'
old 5: a:=&a;
new 5: a:='28 jul 2018';
total amount of blood collect on given date is1
PL/SQL procedure successfully completed.
Q.2) Write a cursor which will display donor wise blood donation details. (Use
parameterized cursor)
CURSOR
declare
cursor c4(vname in varchar2) is select dono.dname, bl_don.* from dono,bl_don
where dono.dno=bl_don.dno order by dname;
x c4%rowtype;
cname dono.dname%type;
begin
cname:=&cname;
for x in c4(cname)
loop
dbms_output.put_line(x.dname||' '||x.bdno||' '||x.bgroup||' '||x.quantity||' '||x.dt);
end loop;
end;
/
OUTPUT-
Enter value for cname: 'rahul'
old 7: cname:=&cname;
new 7: cname:='rahul';
komal 23 O+ 25 25-AUG-18
piyush 25 AB- 35 26-JUL-18
poja 26 B+ 28 28-AUG-18
rahul 21 A+ 20 28-JUL-18
suraj 24 AB+ 30 28-AUG-18
PL/SQL procedure successfully completed.
Q.3) Write a procedure which will display blood group wise total amount of quantity of
blood available.
PROCEDURE
create or replace procedure p12
(vgroup in varchar2)
as
cursor c2 is select quantity,bgroup from dono,bl_don
where dono.dno=bl_don.dno order by bgroup;
x c2%rowtype;
begin
for x in c2
loop
dbms_output.put_line(x.quantity||' '||x.bgroup);
end loop;
end p12;
/
OUTPUT-
Procedure created.
SQL> execute p12('A+');
20 A+
30 AB+
35 AB-
28 B+
25 O+
PL/SQL procedure successfully completed.
Q.4) Write a trigger that restricts insertion of blood_donation_details having quantity
greater than 300ml. (Raise user defined exception and give appropriate message)
TRIGGER
create or replace trigger t18
before insert on bl_don
for each row
begin
if:new.quantity>300 then
raise_application_error('-20010','blood donation having quantity is greater than 300');
end if;
end t18;
/
OUTPUT-
insert into bl_don values(26,'B+',350,'28 aug 2018',15);
Trigger created.
SQL> insert into bl_don values(26,'B+',350,'28 aug 2018',15);
insert into bl_don values(26,'B+',350,'28 aug 2018',15)
*
ERROR at line 1:
ORA-20010: blood donation having quantity is greater than 300
ORA-06512: at "SYSTEM.T18", line 3
ORA-04088: error during execution of trigger 'SYSTEM.T18'
Tags:
RDBMS