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;
/

---------------------------------------------