Assignment No. 4: Function
SET A:
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.
ANS
--Create Table
create table client (
client_no int primary key,
client_name varchar(50),
address varchar(50),
birthdate date
);
insert into client values (1, 'Om', 'Shrirampur', '01/jan/2004');
insert into client values (2, 'Rahul', 'Mumbai', '01/feb/2003');
insert into client values (3, 'Sahil', 'Pune', '01/mar/2004');
select * from client;
create table policy_info (
policy_no int primary key,
des varchar(50),
maturity_amt int check(maturity_amt > 0),
prem_amt int check(prem_amt > 0),
dates date
);
insert into policy_info values (101, 'Health', 500000, 500, '01/jan/2020');
insert into policy_info values (102, 'Car', 300000, 400, '01/feb/2021');
insert into policy_info values (103, 'Life', 100000, 100, '01/jan/2020');
select * from policy_info;
create table client_policy (
client_no int,
foreign key (client_no ) references client (client_no),
policy_no int,
foreign key (policy_no) references policy_info (policy_no),
Constraint pk_ClientPolicy primary key (client_no, policy_no)
);
insert into client_policy values (1, 101);
insert into client_policy values (1, 102);
insert into client_policy values (2, 101);
insert into client_policy values (2, 102);
insert into client_policy values (3, 101);
--Create Function
create or replace Function clientM_amt(cno IN client.client_no%type)
return number is
total policy_info.maturity_amt%type;
Begin
select sum(policy_info.maturity_amt) into total from client, policy_info, client_policy where
client_policy.client_no = client.client_no and
client_policy.policy_no = policy_info.policy_no and
client_policy.client_no = cno;
return total;
End clientM_amt;
-----------------------------------------------
-- Calling Program
Declare
v_amount policy_info.maturity_amt%type;
Begin
v_amount := clientM_amt(1);
dbms_output.put_line('Total Maturity Amount = '|| v_amount);
End;
/
--------------------------------------------------
2) Write a function which will return minimum maturity amount of all policies.
ANS
--Create Function
create or replace Function Min_Mamount
return number is
total policy_info.maturity_amt%type;
Begin
select min(maturity_amt) into total from policy_info;
return total;
End Min_Mamount;
-- Calling Program
Declare
v_total policy_info.maturity_amt%type;
Begin
v_total := Min_Mamount();
dbms_output.put_line('Minimun Policy Amount = '|| v_total);
End;
/
--------------------------------------------------------
3) Write a function which will return total number of policies opened no “1-
Jan-2020”
ANS
--Create Function
create or replace Function policy_open (f_date IN policy_info.dates%type)
return number is
cnt number;
Begin
select count(policy_no) into cnt from policy_info where dates = f_date;
return cnt;
End policy_open;
-- Calling Program
Declare
p_cnt number;
Begin
p_cnt := policy_open('01/jan/2020');
dbms_output.put_line('No of Policy is = '|| p_cnt);
End;
/
-------------------------------------------------------
SET B :
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 who
supplies “Keyboard”.
ANS
--Create Tables
CREATE TABLE Item (
itemno INT PRIMARY KEY,
itemname VARCHAR(255)
);
insert into item values (1, 'Mouse');
insert into item values (2, 'Monitor');
insert into item values (3, 'Keyboard');
select * from item;
CREATE TABLE Suppliers (
supplier_No INT PRIMARY KEY,
supplier_name VARCHAR(255),
address VARCHAR(255),
city VARCHAR(255)
);
insert into suppliers values (101, 'Ram', 'MG Road', 'Mumbai');
insert into suppliers values (102, 'Sanju', 'kk Road', 'Pune');
insert into suppliers values (103, 'Rutu', 'SG Road', 'Delhi');
CREATE TABLE Item_Suppliers (
itemno INT,
supplier_No INT,
rate DECIMAL(10, 2),
quantity INT,
PRIMARY KEY (itemno, supplier_No),
FOREIGN KEY (itemno) REFERENCES Item(itemno),
FOREIGN KEY (supplier_No) REFERENCES Suppliers(supplier_No)
);
insert into item_suppliers values (1, 101, 250, 50);
insert into item_suppliers values (1, 102, 160, 10);
insert into item_suppliers values (2, 102, 1360, 110);
insert into item_suppliers values (3, 101, 60, 10);
insert into item_suppliers values (2, 103, 1360, 10);
insert into item_suppliers values (3, 103, 60, 10);
select * from item_suppliers;
--Create Function
create or replace Function total_suppliers (f_item IN item.itemname%type)
return number is
total number;
Begin
select count(item_suppliers.supplier_No) into total from item, suppliers, item_suppliers
where item_suppliers.itemno = item.itemno and
item_suppliers.supplier_No = suppliers.supplier_No and
itemname = f_item;
return total;
End total_suppliers;
-- Calling Program
Declare
v_total number;
Begin
v_total := total_suppliers('Keyboard');
dbms_output.put_line('Total Suppliers is = '|| v_total);
End;
/
-----------------------------------------------------
2) Write function which will return rate of “Harddisk” supplied by
“Mr. Patil”.
ANS
--Create Function
create or replace Function item_name
return number is
f_rate item_suppliers.rate%type;
Begin
select rate into f_rate from item, suppliers, item_suppliers
where item_suppliers.itemno = item.itemno and
item_suppliers.supplier_No = suppliers.supplier_No and
itemname = 'Harddisk' and supplier_name = 'Mr.Patil';
return f_rate;
End item_name;
-- Calling Program
Declare
v_rate item_suppliers.rate%type;
Begin
v_rate := item_name();
dbms_output.put_line('Rate of Harddisk is = '|| v_rate);
End;
/
------------------------------------------------
3) Write function which will take supplier number as input and
print the total number of items supplied by him
ANS
--Create Function
create or replace Function sup_items (sno IN suppliers.supplier_No%type)
return number is
f_items number;
Begin
select count(item_suppliers.itemno) into f_items from item, suppliers,
item_suppliers
where item_suppliers.itemno = item.itemno and
item_suppliers.supplier_No = suppliers.supplier_No and
item_suppliers.supplier_No = sno;
return f_items;
End sup_items;
-- Calling Program
Declare
v_items number;
Begin
v_items := sup_items(103);
dbms_output.put_line('Number of Items Supplied are = '|| v_items);
End;
/
---------------------------------------------
0 Comments