Index

RDBMS
N Particular Date Sign
o
1 Write a function which will return total maturity 9/02/2022
amount of policies of a particular client.Relation
between Client and Policy_info is Many to Many
2 Write a cursor which will display policy date wise 15/02/2022
client details.
3 Write function to print the total number of suppliers of 21/02/2022
a particular item
Relationship between Item and Supplier is many-to-
many with descriptive attribute rate and quantity
4 Write a trigger which will fire before insert or update 3/03/2022
on rate and quantity less thanor equal to zero. (Raise
user defined exception and give appropriate
message)
5 Write a trigger which will fire before insert on the 14/03/2022
cities table which check that the pincode must be of 6
digit. (Raise user defined exception and give
appropriate message).Relationship between Newspaper
and Cities is many-to-many with descriptiveattribute
daily required
6 Write a procedure to calculate city wise total cost of 26/03/2022
each newspaper
7 Write a function which will accept plan number from 30/03/2022
user and display all the details
of the selected plan
Relation between Plan and Customer is One to Many.
8 Write a cursor which will display customer wise plan 01/04/2022
details.(Use Parameterized
Cursor)
9 Write a function which accept department name and 07/04/2022
display total number of projectswhose status is
“p”(progressive).The relationship between Project and
Department is Many to One.
10 Write a cursor which will display status wise project 18/04/2022
details of each department.
Que 1)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:
SQL> create table client(cno number(8)primary key,cname varchar2(20),addr
varchar2(20),bdate date);
Table created.
SQL> desc client;
Name Null?Type

CNO NOT NULL NUMBER(8) CNAME VARCHAR2(20)

ADDR VARCHAR2(20)
BDATE DATE
SQL> create table p_info(pno number(8)primary key,des varchar2(20),m_amt number(9)check
(m_amt>0),p_amt number(9)check (p_amt>0),pdate date);
Table created.
SQL>descp_info;
Name Null?Type

PNO NOT NULL NUMBER(8) DES VARCHAR2(20)

M_AMT NUMBER(9)
P_AMT NUMBER(9)
PDATE DATE
SQL> create table c_p(pno number(8)references p_info(pno),cno number(8)references
client(cno)); Table created.
SQL>descc_p;
Name Null?Type

PNO NUMBER(8)
CNO NUMBER(8)
SQL> insert into client values(&cno,'&cname','&addr','&bdate’);
SQL> select * from client;
CNOCNAME ADDR BDATE
 1 seema pune 12-JAN-20
2 priya nashik 12-FEB-90
3 heena satana 23-JUN-85

SQL> insert into p_infovalues(&pno,'&des',&m_amt,&p_amt,'&pdate’);

SQL> select * from p_info;
PNO DES M_AMTP_AMT PDATE
11 medical 1000 5000 12-JAN-20
12 saving 1000 1500 26-JUL-83
SQL> insert into c_p values(&pno,&cno);
SQL> select * from c_p;
PNO CNO
11 1
12 1
11 2
1)Write a function which will return total maturity amount of policies of a
particularclient.
----------------------------------------------------
create or replace function f1(abc in varchar)return number is xyz number;
begin
select sum(p_info.m_amt)into xyz from client,p_info,c_p where client.cno=c_p.cno and p_info.
pno=c_p.pno and cname='seema';
if sql%found then return(xyz);
else return null; endif;
end f1;
/
Function created. SQL> begin
dbms_output.put_line('Amt'||f1('seema'));
end;
4 / Amt120000
PL/SQL procedure successfully completed.

2) Write a cursor which will display policy date wise clientdetails.

Declare
cursor c1 is select cname,addr,pdate from client,p_info,c_p where client.cno=c_p.cno and
p_info.pno=c_p.pno order by pdate;
c c1%rowtype; begin
open c1; loop
fetch c1 into c;
exit when c1%notfound; dbms_output.put_line(c.cname||' '||c.addr||' '||c.pdate); end loop;
close c1; end;
seemapune 26-JUL-83 priya nashik 12-JAN-20 seemapune 12-JAN-20
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:

SQL> select * from item; INO INAME

keyboard
pendrive
monitor
SQL> select * fromsup;

SNOSNAME ADD CITY

11heena clgroad nashik 12 piyu aurangabadroad nashik 13asha

peth road pune
SQL> select * from i_sup;

INO SNO RATE QUN

1 11 100 12
2 12 50 300
1 11 40 200

3)Write function to print the total number of suppliers of a particularitem

SQL> create or replace function f1(abc in varchar) return number is
xyznumber;
begin
select sum(sup.sno) intoxyz
fromitem,sup,i_sup
where item.ino=i_sup.ino andsup.sno=i_sup.sno
andiname='keyboard';
if sql%foundthen
return(xyz);
else
returnnull;
end if;
end f1; 14 /
Function created.
SQL> begin
dbms_output.put_line ('total number of suppliers:'||f1('keyboard'));
end;
4/
total number of suppliers: 22
PL/SQL procedure successfully completed.

4)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 appropriatemessage)

SQL> create or replace trigger rate

before delete or update or insert oni_sup
for eachrow
begin
if:new.rate<=0then
raise_application_error('-20010','the rate must be more thenzero');
end if;
end;
9/

Trigger created.

SQL> insert into i_supvalues(3,13,0,30); insert into i_sup values(3,13,0,30)

*
ERROR at line 1:
ORA-20010: the rate must be more then zero ORA-06512: at "SCOTT.RATE", line 3
ORA-04088: error during execution of trigger 'SCOTT.RATE'
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
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

SQL> create table newspaper(name varchar2(20)primary key,lang varchar2(20),pub

varchar2(20),cost num
ber(10)); Tablecreated.
SQL> create table cities(pcode number(10)primary key,city varchar2(20),state varchar2(20));
Table created.
SQL> create table n_c(name varchar2(20)references newspaper(name),pcode
number(10)references cities(
pcode),dreq varchar2(20)); Table created.
SQL> insert into newspaper values('&newspaper','&lang','&pub',&cost); SQL> select * from
newspaper;

NAME LANG PUB COST

sakal marathi xyz 5
times english abc 7
lokmat marathi pqr 10
SQL> insert into cities values(&pcode,'&city','&state'); SQL> select *from cities;
PCODECITY STATE

423204nampur mh
296112daman gujarat
123456nashik mhSQL> select * fromn_c;

NAMEPCODEDREQ

sakal 423204yes
times 296112 no
times 296112yes
5)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 appropriatemessage).

create or replace trigger p_cod

before insert or update or delete on cities for each row
begin
if(:new.pcode>= 6) then raise_application_error(-20005,'pincode should note be more or less
then 6 digits');
end if; end;

6)Write a procedure to calculate city wise total cost of eachnewspaper

SQL> create or replace procedure sd (t in varchar)is

cursor v2 is select city,cost fromnewspaper,cities,n_c
where newspaper.name=n_c.name and cities.pcode = n_c.pcode; 4 zv2%rowtype;
lnumber;
begin
openv2;
loop
fetch v2 into z;
exit whenv2%notfound;
dbms_output.put_line(' city: ' || z.city || ' cost: ' || z.cost ); 12 endloop;
closev2;
end;
15 /

Procedure created.
SQL> execute sd('nampur'); city: nampur cost: 5
city: daman cost: 7 city: daman cost: 7

PL/SQL procedure successfully completed.

4)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:

SQL> create table Plan(Pno NUMBER(10)primary key,pname varchar2(20),nfreecall

NUMBER(10),fcalltime N
UMBER(10),famt NUMBER(10));

Table created.
SQL> create table cust1(cno NUMBER(10)primary key,cname VARCHAR2(20),mobno
NUMBER(10),Pno NUMBER(10)
references Plan(Pno));

Table created.

SQL> select * from Plan;

PNO PNAME NFREECALL FCALLTIME FAMT

---------- -------------------- ---------- ---------- ----------
 1 data 55 100 300
2 monthly 55 250 281
SQL> select *from cust1;

CNO CNAME MOBNO PNO

---------- -------------------- ---------- ----------
11 riya 1234567834 1
12 seema 3456789123 1

7) Write a function which will accept plan number from user and display all the details
of the selected plan
SQL> create or replace function fun1(nocomp in varchar)return varchar as detalis varchar(10);
2 begin
3 select (plan.pname)intodetalis from
4 plan,cust1 where plan.pno=cust1.pno and
5 plan.pno='1';
6 if sql %found then
7 return(detalis);
8 else
9 return null;
10 end if;
11 end fun1;
12 /

Function created.

Begin
dbms_output.put_line('detalis-'||fun1('1'));

end;
/
2
8) Write a cursor which will display customer wise plan details.(Use Parameterized
Cursor)

SQL> declare
2 cursor c1(yyyy cust1.cname %type)is select cname,pname from plan,cust1 where
plan.pno=cust1.pno
order by cname;
3 c c1%rowtype;
4 begin
5 open c1('&cname');
6 loop
7 fetch c1 into c;
8 exit when c1%notfound;
9 dbms_output.put_line(c.cname|| ' ' ||c.pname);
10 end loop;
11 close c1;
12 end;
13 /

Enter value for cname: riya

old 5: open c1('&cname');
new 5: open c1('riya');
riya data
seema data
PL/SQL procedure successfully completed.

Que 5) 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:

SQL> select * from project;

PNO PNAME START_DAT BUDGET STATUS

---------- ---------- --------- ---------- ------------------------------
D_NO
----------
1 hotel mgmt 10-JAN-15 10000 c-complete
11

2 EVS 01-JAN-15 4000 p-progressive

12

3 RTO MGMT 25-DEC-14 3000 I-Incomplete

13

SQL> select * from dept10;

D_NO DNAME HOD LOC

---------- -------------------- -------------------- --------------------
11 computer bhat mam nashik
12 math priyapune
13 physics kulkarnisatana

9) Write a function which accept department name and display total number of projects
whose status is “p”(progressive).

SQL> create or replace function fg(d in varchar)

2 return number as
3 bs number(10);
4 begin
5 select count(pname) into bs from project,dept10
6 where project.d_no=dept10.d_no
7 and dname='computer'
8 and status='p';
9 return bs;
10 end;
11 /
Function created.
Calling Program
SQL> begin
dbms_output.put_line('output:'||fg('math'));
end;
/
output:0
PL/SQL procedure successfully completed.

10) Write a cursor which will display status wise project details of each department.

SQL> declare
cursor s1 is
selectpno,pname,start_date,budget,status from project
where start_date='10-jan-2015';
s s1%rowtype;
begin
open s1;
loop
fetch s1 into s;
exit when s1%notfound;
dbms_output.put_line(s.pno||' '||s.pname||' '||s.start_date||' '||s.budget||' '||s.status);
end loop;
close s1;
end;
/
1 hotel mgmt 10-JAN-15 10000 c-complete

PL/SQL procedure successfully completed.