FYBBA(CA) Semester-II Practical Lab Assignment RDBMS

1

FYBBA(CA) Semester-II Practical Lab Assignment

RDBMS

Q1. Consider the following entities and their relationships.
Client (client_no, client_name, address, birthdate)
Policy_info (policy_no, desc, maturity_amt, prem_amt, date)
Relation between Client and Policy_info is Many to Many
Constraint: Primary key, prem_amt and maturity_amt should be > 0
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a function which will return total maturity amount of policies of a particular client.

create or replace function getmamt (n IN varchar2) return number as

res number(20);

begin

select sum(m_amt) into res from client c,p_info p,c_p cp where c_name=n and c.c_no=cp.c_no and p.p_no=cp.p_no;

return res;

end;

2) Write a cursor which will display policy date wise client details.
declare
cursor c1 is select pdate,c.c_no,c_name,addr,bdate from client c,p_info p,c_p cp where c.c_no=cp.c_no and p.p_no=cp.p_no order by pdate ;
srec c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into srec;
exit when c1%NOTFOUND;
dbms_output.put_line(srec.pdate||' '||srec.c_no||' '||srec.c_name||' '||srec.addr||' '||srec.bdate);
end loop;
close c1;
end;
3) Write a procedure which will display all policy details having premium amount less than 5000.
create or replace procedure getdet as
res p_info%ROWTYPE;
cursor c2 is select * from p_info where p_amt<=5000;
begin
open c2;
loop
fetch c2 into res;
exit when c2%NOTFOUND;
dbms_output.put_line(res.p_no||' '||res.descr||' '||res.m_amt||' '||res.p_amt||' '||res.pdate);
end loop;
close c2;
end;
4) Write a trigger which will fire before insert or update on policy_info having maturity amount less than premium amount. (Raise user defined exception and give appropriate message)
create or replace trigger t2 before insert or update on p_info for each row
begin
if(:new.m_amt<:new.p_amt) then
raise_application_error(-20003,'ERROR:m_amt should be greater than p_amt');
end if;
end;

Q2. Consider the following Item_Supplier database
Item (itemno, itemname )
Supplier (supplier_No , supplier_name, address, city )
Relationship between Item and Supplier is many-to-many with descriptive attribute rate and quantity
Constraints: itemno ,supplier_No primary key
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write function to print the total number of suppliers of a particular item.
create or replace function getssup (n IN varchar2) return number as
res number(10);
begin
select count(s.s_no) into res from item i,supplier s,i_s ir where i_name=n and i.i_no=ir.i_no and s.s_no=ir.s_no;
return res;
end;
2)Write a trigger which will fire before insert or update on rate and quantity less than or equal to zero. (Raise user defined exception and give appropriate message)
create or replace trigger t3 before insert or update on i_s for each row
begin
if(:new.rate<=0 or :new.quantity<=0) then
raise_application_error(-20008,'ERROR:Rate and quantity should be greater than zero');
end if;
end;

Q3. Consider the following entities and their relationship.
Newspaper (name,language , publisher , cost )
Cities (pincode , city, state)
Relationship between Newspaper and Cities is many-to-many with descriptive
attribute daily required
Constraints: name and pincode primary key 2
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a trigger which will fire before insert on the cities table which check that the pincode must be of 6 digit. (Raise user defined exception and give appropriate message).

create or replace trigger t4 before insert or update on cities for each row
begin
if(length(:new.pincode)<6 or length(:new.pincode)>6) then
raise_application_error(-20001,'ERROR::Pincode should be 6 digits');
end if;
end;
2) Write a procedure to calculate city wise total cost of each newspaper
create or replace procedure getcity as
cursor c1 is select city,cost,n.name from newspaper n,cities c,n_c nc where n.name=nc.name and c.pincode=nc.pincode order by city;
res c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into res;
exit when c1%NOTFOUND;
dbms_output.put_line(res.city||' '||res.cost||' '||res.name);
end loop;
close c1;
end;

Q4. Consider the following entities and their relationships.
Library(Lno, Lname, Location, Librarian, no_of_books)
Book(Bid, Bname, Author_Name, Price, publication)
Relation between Library and Book is one to many.
Constraint: Primary key, Price should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a function which will accept publication name from user and display total price of books of that publication.
create or replace function getprice(n IN varchar2) return number as
res number(10);
begin
select sum(price) into res from library l,book b where publicat=n and l.l_no=b.l_no;
return res;
end;
2) Write a cursor which will display library wise book details.(Use Parameterized Cursor)
declare
cursor c1(n varchar2) is select l.l_no,b_id,b_name,a_name,price,publicat from library l,book b where l.l_no=b.l_no and l_name=n order by l_name;
res c1%ROWTYPE;
begin
open c1(’&n’);
loop
fetch c1 into res;
exit when c1%NOTFOUND;
dbms_output.put_line(res.l_no||' '||res.b_id||' '||res.b_name||' '||res.a_name||' '||res.price||' '||res.publicat);
end loop;
close c1;
end;
3) Write a procedure to display names of book written by “Mr. Patil” and are from “DPU Library”.
create or replace procedure getdis as
cursor c1 is select b.b_name from library l, book b where a_name='Mr.Patil' and l_name='DPU' and l.l_no=b.l_no;
res c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into res;
exit when c1%NOTFOUND;
dbms_output.put_line(res.b_name);
end loop;
close c1;
end;
4) Write a trigger which will fire before insert or update on book having price less than or equal to zero. (Raise user defined exception and give appropriate message)
create or replace trigger t5 before insert or update on book for each row
begin
if(:new.price<=0) then
raise_application_error(-20003,'ERROR::Price should be greater than zero');
end if;
end;

Q5. Consider the following entities and their relationships.
Employee (emp_id, emp_name, address)
Investment (inv_no, inv_name, inv_date, inv_amount)
Relation between Employee and Investment is One to Many.
Constraint: Primary key, inv_amount should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a procedure which will display details of employees invested amount in “Mutual Fund”
create or replace procedure getdet as
res emp%ROWTYPE;
cursor c1 is select e.emp_id,emp_name,addr from emp e,inv i where i.i_name='Mutual Fund' and e.emp_id=i.emp_id;
begin
open c1;
loop
fetch c1 into res;
exit when c1%NOTFOUND;
dbms_output.put_line(res.emp_id||' '||res.emp_name||' '||res.addr);
end loop;
close c1;
end;
2) Write a cursor which will display date wise investment details.
declare
cursor c1 is select i_date,i_no,i_name,i_amt from inv order by i_date;
res c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into res;
exit when c1%NOTFOUND;
dbms_output.put_line(res.i_date||' '||res.i_no||' '||res.i_name||' '||res.i_amt);
end loop;
close c1;
end;
3) Write a function which will return total investment amount of a particular client.
create or replace function getamt (n IN varchar2) return number as
res number(10);
begin
select sum(i_amt) into res from emp e,inv i where e.emp_name=n and e.emp_id=i.emp_id;
return res;
end;
4) Write a trigger which will fire before insert or update on Investment having investment amount less than 50000. (Raise user defined exception and give appropriate message)
create or replace trigger t6 before insert or update on inv for each row
begin
if(:new.i_amt<50000) then
raise_application_error(-20001,'ERROR::Investment amt should be greater than 50000');
end if;
end;

Q6. Consider the following entities and their relationships.
Bill (billno, day, tableno, total)
Menu (dish_no, dish_desc, price)
The relationship between Bill and Menu is Many to Many with quantity as descriptive attribute.
Constraint: Primary key, price should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a procedure to display menu details having price between 200 to 500 which were order on „Saturday‟ .
create or replace procedure getmenu as
cursor c1 is select m.dno,ddesc,price from bill1 b,menu1 m, bm where price between 200 and 500 and day='Saturday' and b.bno=bm.bno and m.dno=bm.dno;
res c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into res;
exit when c1%NOTFOUND;
dbms_output.put_line(res.dno||' '||res.ddesc||' '||res.price);
end loop;
close c1;
end;
2) Write a trigger which will fire before insert or update on Menu having price less than or equal to zero. (Raise user defined exception and give appropriate message)
create or replace trigger t7 before insert or update on menu1 for each row
begin
if(:new.price<=0) then
raise_application_error(-20001,'ERROR::Price should be greater than or equal to zero');
end if;
end;
3) Write a function which accept a table number and display total amount of bill for a specific table
create or replace function gettotal (n IN number) return number as
res number(10);
begin
select total into res from bill1 where tno=n;
return res;
end;
4) Write a cursor which will display table wise menu details.
declare
cursor c1 is select tno,m.dno,ddesc,price from bill1 b,menu1 m,bm where b.bno=bm.bno and m.dno=bm.dno order by tno;
res c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into res;
exit when c1%NOTFOUND;
dbms_output.put_line(res.tno||' '||res.dno||' '||res.ddesc||' '||res.price);
end loop;
close c1;
end;

Q7. Consider the following entities and their relationships.
Plan (plan_no, plan_name, nooffreecalls, freecalltime, fix_amt)
Customer (cust_no, cust_name, mobile_no)
Relation between Plan and Customer is One to Many.
Constraint: Primary key, fix_amt should be greater than 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a function which will accept plan number from user and display all the details of the selected plan
create or replace function getplan (n IN number) return varchar as
res varchar2(20);
begin
select pname into res from plan where pno=n;
return res;
end;
2) Write a cursor which will display customer wise plan details.(Use Parameterized Cursor)
declare
n number;
cursor c1(n number) is select p.pno,pname,fix_amt from plan p, cust c where p.pno=c.pno and c.cno=n;
res c1%ROWTYPE;
11
begin
n:=&n;
open c1(n);
loop
fetch c1 into res;
exit when c1%NOTFOUND;
dbms_output.put_line(res.pno||' '||res.pname||' '||res.fix_amt);
end loop;
close c1;
end;
3) Write a procedure to display the plan having minimum response.
create or replace procedure getresponse as
p plan.pname%TYPE;
begin
select pname into p from plan where no_of_free_calls=(select min(no_of_free_calls) from plan);
end;
4) Write a trigger which will fire before insert or update on mobile number having length less than or greater than10. (Raise user defined exception and give appropriate message)
create or replace trigger t8 before insert or update on cust for each row
begin
if(length(:new.mobile_no)<10 or length(:new.mobile_no)>10) then
raise_application_error(-20004,'ERROR::Mobile no should be 10 digits');
end if;
end;

Q8 Consider the following entities and their relationships.
Project (pno, pname, start_date, budget, status)
Department (dno, dname, HOD, loc)
The relationship between Project and Department is Many to One.
Constraint: Primary key.
Project Status Constraints: C – Completed,
P - Progressive,
I – Incomplete
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a function which accept department name and display total number of projects whose status is “p”(progressive).
create or replace function getproject (n IN varchar2) return number as
res number(10);
begin
select count(p.p_no) into res from project1 p,dep d where status=’P’ and p.d_no=d.d_no and d_name=n;
return res;
end;
2) Write a cursor which will display status wise project details of each department.
declare
cursor c1 is select status,p_no,p_name,budget from project1 order by status;
res c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into res;
exit when c1%NOTFOUND;
dbms_output.put_line(res.status||' '||res.p_no||' '||res.p_name||' '||res.budget);
end loop;
close c1;
end;
4) Write a trigger which will fire before insert or update on project having budget less than or equal to zero. (Raise user defined exception and give appropriate message)
create or replace trigger t9 before insert or update on project1 for each row
begin
If(:new.budget<=0) then
raise_application_error(-20001,'ERROR::Budget should be greater than zero');
end if;
end;

Q9 Consider the following entities and their relationships.
Gym (Name, city, charges, scheme)
Member (ID, Name, phoneNo, address)
Relation between Gym and member is one to many.
Constraint: Primary Key, charges must be greater than 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a function which will accept member id and scheme from user and display charges paid by that member.
create or replace function getprise (n IN number) return number as
res number(10);
begin
select charges into res from gym1 g,member m where id=n and g.g_name=m.g_name;
return res;
end;
2) Write a trigger which will fire before insert or update on Gym having charges less than 1000. (Raise user defined exception and give appropriate message)
create or replace trigger t11 before insert or update on gym1 for each row
begin
if(:new.charges<1000) then
raise_application_error(-20002,'ERROR::Charges should be greater than 1000');
end if;
end;
3) Write a procedure to display member details of gym located at “Pimpri‟”
create or replace procedure getgym as
cursor c1 is select id,m_name,phono_no,addr from gym1 g,member m where city='Pimpri' and g.g_name=m.g_name;
res c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into res;
exit when c1%NOTFOUND;
dbms_output.put_line(res.id||' '||res.m_name||' '||res.phono_no||' '||res.addr);
end loop;
close c1;
end;
4) Write a cursor which will display gym wise member details.(Use Parametrized Cursor)
DECLARE
res member%ROWTYPE;
CURSOR getsmem(n varchar2) IS SELECT m.id,m.m_name,m.phono_no,m.addr,m.g_name FROM gym1 g,member m where g.g_name=n and g.g_name=m.g_name;
BEGIN
OPEN getsmem('&n');
LOOP
FETCH getsmem into res;
EXIT WHEN getsmem%NOTFOUND;
dbms_output.put_line(res.id||' '||res.m_name||' '||res.phono_no||' '||res.addr||' '||res.g_name);
END LOOP;
CLOSE getsmem;
END;

Q10 Consider the following entities and their relationships.
Student (rollno, sname, class, timetable)
Lab (LabNo, LabName, capacity, equipment)
Relation between Student and Lab is Many to One.
Constraint: Primary Key, capacity should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a function which will accept Lab number from user and display total number of student allocated in that lab.
create or replace function getcap (n IN number) return number as
res number(10);
begin
select cap into res from lab4 where l_no=n;
return res;
end;
2) Write a cursor which will display lab wise student details.
declare
cursor c1 is select l_name,r_no,s_name from lab4 l,stud5 s where l.l_no=s.l_no order by l_name;
res c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into res;
exit when c1%NOTFOUND;
dbms_output.put_line(res.l_name||' '||res.r_no||' '||res.s_name);
end loop;
close c1;
end;
3) Write a procedure to display details of students which perform practical sessions in a given Lab.
create or replace procedure getstud (n IN number) as
r_no stud5.r_no%TYPE;
s_name stud5.s_name%TYPE;
class stud5.class%TYPE;
cursor c1 is select r_no,s_name,class from lab4 l,stud5 s where l.l_no=s.l_no and l.l_no=n;
begin
open c1;
loop
fetch c1 into r_no,s_name,class;
exit when c1%NOTFOUND;
dbms_output.put_line(r_no||' '||s_name||' '||class);
end loop;
close c1;
end;
4) Write a trigger which will fire before delete on Lab (Raise user defined exception and give appropriate message)
create or replace trigger t12 before delete on lab4 for each row
declare
del_lab exception;
begin
raise del_lab;
exception
when del_lab then
raise_application_error(-20001,'ERROR::Record can not be deleted');
end;

Q11 Consider the following entities and their relationships.
Wholesaler (w_no, w_name, address, city)
Product (product_no, product_name, rate)
Relation between Wholesaler and Product is Many to Many with quantity as descriptive attribute.
Constraint: Primary key, rate should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a function which will accept wholesaler name from user and will display total number of items supplied by him.
create or replace FUNCTION getitem (n IN number) return number as
res number;
begin
select count(wp.p_no) into res from wholesaler10 w,product10 p,w_p10 wp where w.w_no=wp.w_no and p.p_no=wp.p_no and w.w_no=n;
return res;
end;
2) Write a trigger which will fire before insert or update on product having rate less than or equal to zero (Raise user defined exception and give appropriate message)
create or replace trigger t15 before insert or update on product10 for each row
begin
if(:new.rate<=0) then
raise_application_error(-20001,'ERROR::Rate should be greater than zero');
end if;
end;
3) Write a procedure which will display details of products supplied by “Mr. Patil”
create or replace PROCEDURE getdet as
cursor c1 is select p.p_no,p_name,rate from wholesaler10 w,product10 p,w_p10 wp where w.w_name='Mr.Patil' and w.w_no=wp.w_no and p.p_no=wp.p_no;
c c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into c;
exit when c1%NOTFOUND;
dbms_output.put_line(c.p_no||' '||c.p_name||' '||c.rate);
end loop;
close c1;
end;
4) Write a cursor which will display wholesaler wise product details.(Use Parameterized cursor)
declare
cursor c1(n IN number) is select w_name,p.p_no,p_name,rate from wholesaler10 w,product10 p,w_p10 wp where w.w_no=wp.w_no and p.p_no=wp.p_no and w.w_no=n order by w_name;
c c1%ROWTYPE;
begin
open c1(&n);
loop
fetch c1 into c;
exit when c1%NOTFOUND;
dbms_output.put_line(c.w_name||' '||c.p_no||' '||c.p_name||' '||c.rate);
end loop;
close c1;
end;

Q12 Consider the following entities and their relationships. 6
Country (CId, CName , no_of_states, area, location, population)
Citizen( Id, Name, mother_toung, state_name)
Relation between Country and Citizen is one to many.
Constraint: Primary key, area should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a function which will display name of the country having minimum population.
create or replace FUNCTION getmin return varchar2 as
res varchar(220);
begin
select cname into res from country where pop=(select min(pop) from country);
return res;
end;
2) Write a cursor which will display county wise citizen details.
declare
cursor c1 is select cname,id,name,m_toung,state from country co,citizen c where co.cid=c.cid order by cname;
d c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into d;
exit when c1%NOTFOUND;
dbms_output.put_line(d.cname||' '||d.id||' '||d.name||' '||d.m_toung||' '||d.state);
end loop;
close c1;
end;
3) Write a procedure to display name of citizens having mother toung “Marathi “ and from “India”;
create or replace PROCEDURE getname as
cursor c1 is select name from country co,citizen c where co.cid=c.cid and m_toung='Marathi' and cname='India';
d c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into d;
exit when c1%NOTFOUND;
dbms_output.put_line(d.name);
end loop;
close c1;
end;
4) Write a trigger which will fire before insert or update on country having no_of_state less than equal to zero. (Raise user defined exception and give appropriate message)
create or replace trigger t16 before insert or update on country for each row
begin
if(:new.nos<=0) then
raise_application_error(-20001,'ERROR::state number should be greater than zero');
end if;
end;

Q13. Consider the following entities and their relationships.
College (code, college_name, address)
Teacher (teacher_id, teacher_name, Qualification, specialization, salary, Desg)
Relation between Teacher and College is Many to One.
Constraint: Primary Key, qualification should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a procedure which will accept teacher name from user and display his/her college details.
create or replace PROCEDURE getclg as
res varchar2(20);
begin
select cname into res from clg c,teachers t where c.code=t.code and tname=’Patil’;
end;
2) Write a trigger which will fire before insert or update on Teacher having salary less than or equal to zero (Raise user defined exception and give appropriate message)
create or replace trigger t17 before insert or update on teachers for each row
begin
if(:new.salary<=0) then
raise_application_error(-20001,'ERROR::Salary should be greater than zero');
end if;
end;
3) Write a function which will accept college name from user and display total number of “Ph.D” qualified teachers.
create or replace FUNCTION getteach return number as
res number(10);
begin
select count('t.qua') into res from clg c,teachers t where c.code=t.code and t.qua='PHD' and c.cname='FC' ;
return res;
end;
4) Write a cursor which will display college wise teacher details.
declare
cursor c1 is select cname,tid,tname,qua,spec,salary,desg from clg c,teachers t where c.code=t.code order by cname;
d c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into d;
exit when c1%NOTFOUND;
dbms_output.put_line(d.cname||' '||d.tid||' '||d.tname||' '||d.qua||' '||d.spec||' '||d.salary||' '||d.desg);
end loop;
close c1;
end;

Q14. Consider the following entities and their relationships.
Driver (driver_id, driver_name, address)
Car (license_no, model, year)
Relation between Driver and Car is Many to Many with date and time as descriptive attribute.
Constraint: Primary key, driver_name should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a function which will display the total number of person who are using “Swift” car
create or replace FUNCTION getnumber return number as
res number(10);
begin
select count(model) into res from car where model='Swift';
return res;
end;
2) Write a trigger which will fire before insert or update on year. If year value is more than current year. (Raise user defined exception and give appropriate message)
create or replace trigger t30 before insert or update on car for each row
begin
if(:new.year>sysdate) then
raise_application_error(-20001,'ERROR::Year should be greater than current date');
end if;
end;
3) Write a procedure to display car details used on specific day.
create or replace PROCEDURE getdet as
cursor c1 is select c.lno,model,year from car c, d_c dc where c.lno=dc.lno and pdate='01-Jan-2020';
d c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into d;
exit when c1%NOTFOUND;
dbms_output.put_line(d.lno||' '||d.model||' '||d.year);
end loop;
close c1;
end;
4) Write a cursor which will display driver wise car details in the year 2018.
declare
cursor c1 is select dname,c.lno,model,year from driver d,car c,d_c dc where d.did=dc.did and c.lno=dc.lno and year=2018 order by dname;
res c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into res;
exit when c1%NOTFOUND;
dbms_output.put_line(res.dname||' '||res.lno||' '||res.model||' '||res.year);
end loop;
close c1;
end;

Q15. Consider the following entities and their relationships.
Game (game_name, no_of_players, coach_name)
Player (pid, pname, address, club_name)
Relation between Game and Player is Many to Many.
Constraint: Primary key, no_of_players should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a procedure which will display games details having number of players more than 5.
create or replace PROCEDURE getplayer as
cursor c1 is select * from game where nop>5;
c c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into c;
exit when c1%NOTFOUND;
dbms_output.put_line(c.gname||' '||c.nop||' '||c.cname);
end loop;
close c1;
end;
2) Write a trigger which will fire before insert or update on Game having no_of_players less than or equal to zero. (Raise user defined exception and give appropriate message)
create or replace trigger t20 before insert or update on game for each row
begin
if(:new.nop<=0) then
raise_application_error(-20001,'ERROR::No.Of Player should be greater than zero');
end if;
end;
3) Write a function which will return total number of football players of “Sports Club”.
create or replace FUNCTION getnum return number as
res number;
begin
select count(p.pid) as pid into res from player p,gp where p.pid=gp.pid and gp.gname='Football' and clubname='Sports club';
return res;
end;
4) Write a cursor which will display club wise details of players.
declare
cursor c1 is select clubname,pid,pname,addr from player order by clubname;
c c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into c;
exit when c1%NOTFOUND;
dbms_output.put_line(c.clubname||' '||c.pid||' '||c.pname||' '||c.addr);
end loop;
close c1;
end;

Q16. Consider the following Item_Supplier database
Company (name , address , city , phone , share_value)
Person (pname ,pcity )
Relationship between Company and Person is M to M relationship with descriptive attribute No_of_shares i
Constraints: name,pname primary key
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a trigger before insert or update on No_of_shares field should not be zero.(Raise user defined exception and give appropriate message)
create or replace trigger t13 before insert or update on c_p1 for each row
begin
if(:new.no_of_shares<=0) then
raise_application_error(-20001,'ERROR::Shares must be greater than zero');
end if;
end;
2) Write a function to display total no_of_shares of a specific person.
create or replace FUNCTION getshare return number as
res number(10);
BEGIN
select sum(no_of_shares) into res from c_p1 where pname='Dinesh';
return res;
END;

Q17. Consider the following entities and their relationship.
Student (s_reg_no, s_name, s_class)
Competition (comp_no, comp_name, comp_type)
Relationship between Student and Competition is many-to-many with descriptive attribute rank and year.
Constraints: primary key, foreign key, primary key for third table(s_reg_no, comp_no, year),s_name and comp_name should not be null,comp_type can be sports or academic.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a function which will accept s_reg_no of student and returns total number of competition in which student has participated in a given year.
create or replace function getparti (n IN number) return number as
res number(10);
begin
select count(cno) into res from sc where regno=n;
return res;
end;
2) Write a cursor which will display year wise details of competitions.
(Use parameterized cursor)
declare
cursor c1(n int) is select year,c.cno,cname,ctype from student5 s,comp c,sc where s.regno=sc.regno and c.cno=sc.cno and year=n order by year;
c c1%ROWTYPE;
begin
open c1(&n);
loop
fetch c1 into c;
exit when c1%NOTFOUND;
dbms_output.put_line(c.year||' '||c.cno||' '||c.cname||' '||c.ctype);
end loop;
close c1;
end;