Assignment 2 - Stored Functions
SET A
Using If.-Then-else,case,for,while and unconditional loops
1. Find maximum and minimum from two numbers
create or replace function max_min (x int , y int )returns int as $$
declare
max int;
min int;
begin
if x>y then
max:=x;
min:=y;
else
max:=y;
min:=x;
end if;
raise NOtice ' Max:=% Min:=% ',max,min;
return 1;
end;
$$ language plpgsql;
select max_min(10,5);
NOTICE: Max:=10 Min:=5
max_min
---------
1
(1 row)
postgres=# select max_min(2,5);
NOTICE: Max:=5 Min:=2
max_min
---------
1
(1 row)
2. Check the number is positive, negative or zero.
create or replace function chk_no (n int )returns int as $$
begin
if n>0 then
raise Notice ' % is Positive',n;
else if n<0 then
raise Notice ' % is Negative',n;
else
raise Notice ' % is Zero',n;
end if;
end if;
return 1;
end;
$$ language plpgsql;
postgres=# select chk_no(5);
NOTICE: 5 is Positive
chk_no
--------
1
(1 row)
postgres=# select chk_no(-2);
NOTICE: -2 is Negative
chk_no
--------
1
(1 row)
postgres=# select chk_no(0);
NOTICE: 0 is Zero
chk_no
--------
1
(1 row)
3. Find maximum and minimum from three numbers
create or replace function max_min (x int , y int, z int )returns int as $$
declare
max int;
min int;
begin
if x>y AND x>z then
max:=x;
else if y>x AND y>z then
max:=y;
else
max:=z;
end if;
end if;
return max;
end;
$$ language plpgsql;
4. Find number is even or odd
create or replace function even_odd (n int )returns int as $$
declare
rem int;
begin
rem:=n%2;
if rem=0 then
raise Notice ' % is Even',n;
else
raise Notice ' % is Odd',n;
end if;
return 1;
end;
$$ language plpgsql;
postgres=# select even_odd(4);
NOTICE: 4 is Even
even_odd
----------
1
(1 row)
postgres=# select even_odd(9);
NOTICE: 9 is Odd
even_odd
----------
1
(1 row)
5. Find sum of first 10 numbers (using unconditional loop)
CREATE OR REPLACE FUNCTION getSum() RETURNS int AS $$
DECLARE
SUM Int := 0;
BEGIN
FOR I IN 1 .. 10
LOOP
SUM := SUM + I;
END LOOP;
Return SUM;
END;
$$ language 'plpgsql';
CREATE FUNCTION
assignment=# select getSum();
getsum
--------
55
(1 row)
6. Display all odd numbers from 1 to 50.
create or replace function oddno ()returns void as $$
declare
rem int;
begin
FOR I IN 1 .. 50
LOOP
rem:=I%2;
if rem=1 then
raise Notice ' % is odd',I;
end if;
End Loop;
end;
$$ language plpgsql;
CREATE FUNCTION
assignment=# select oddno();
NOTICE: 1 is odd
NOTICE: 3 is odd
NOTICE: 5 is odd
NOTICE: 7 is odd
NOTICE: 9 is odd
NOTICE: 11 is odd
NOTICE: 13 is odd
NOTICE: 15 is odd
NOTICE: 17 is odd
NOTICE: 19 is odd
NOTICE: 21 is odd
NOTICE: 23 is odd
NOTICE: 25 is odd
NOTICE: 27 is odd
NOTICE: 29 is odd
NOTICE: 31 is odd
NOTICE: 33 is odd
NOTICE: 35 is odd
NOTICE: 37 is odd
NOTICE: 39 is odd
NOTICE: 41 is odd
NOTICE: 43 is odd
NOTICE: 45 is odd
NOTICE: 47 is odd
NOTICE: 49 is odd
oddno
-------
(1 row)
7. Find sum and average of first n numbers using conditional loop(while)
CREATE OR REPLACE FUNCTION nautral_sum(num integer) RETURNS int AS $$
DECLARE
sum integer = 0;
avg float =0.0;
i integer = 1;
BEGIN
IF (num <= 1) THEN
RETURN 1;
END IF;
WHILE(i <= num) LOOP
sum = sum + i;
i = i+1;
END LOOP;
avg=sum/num;
raise notice 'Sum = %',sum;
raise Notice 'Avg = %',avg;
return 0;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
assignment=# select nautral_sum(5);
NOTICE: Sum = 15
NOTICE: Avg = 3
nautral_sum
-------------
0
(1 row)
8. Count even numbers from given range(m to n) (for)
create or replace function Even_No (m int , n int )returns void as $$
declare
rem int;
begin
FOR I IN m .. n
LOOP
rem:=I%2;
if rem=0 then
raise Notice ' % is even',I;
end if;
End Loop;
end;
$$ language plpgsql;
CREATE FUNCTION
select Even_No(5,10);
NOTICE: 6 is even
NOTICE: 8 is even
NOTICE: 10 is even
even_no
---------
(1 row)
9. Search the given number is in given range.
create or replace function find_no (m int , n int, no int )returns int as $$
begin
FOR I IN m .. n
LOOP
if I=no then
raise notice '% is found', no;
return 1;
end if;
end loop;
raise notice '% is not found',no;
return 0;
end;
$$ language plpgsql;
CREATE FUNCTION
assignment=# select find_no(5,10,6);
NOTICE: 6 is found
find_no
---------
1
(1 row)
10.Display a number in word (Using Case) and loop.
CREATE OR REPLACE FUNCTION no_word (x integer) RETURNS text AS $$
DECLARE
num text;
BEGIN
CASE
WHEN x = 0 THEN num := 'ZERO';
WHEN x = 1 THEN num := 'ONE';
WHEN x = 2 THEN num := 'TWO';
WHEN x = 3 THEN num := 'THREE';
WHEN x = 4 THEN num := 'FOUR';
WHEN x = 5 THEN num := 'FIVE';
WHEN x = 6 THEN num := 'SIX';
WHEN x = 7 THEN num := 'SEVEN';
WHEN x = 8 THEN num := 'EIGHT';
WHEN x = 9 THEN num := 'NINE';
END CASE;
RETURN num;
END;
$$
LANGUAGE plpgsql;
CREATE FUNCTION
assignment=# select no_word(5);
no_word
---------
FIVE
SET B
Bank database
Consider the following database maintained by a Bank. The Bank maintains information about its
branches, customers and their loan applications.
Following are the tables:
branch (bid integer, brname char (30), brcity char (10))
customer (cno integer, cname char (20), caddr char (35), city char(20))
loan_application (lno integer, lamtrequired money, lamtapproved money, l_date date)
The relationship is as follows: branch, customer, loan_application are related with ternary
relationship. TERNARY (bid integer, cno integer, lno integer).
a) Write a function that returns the total number of customers of a particular branch.(Accept
branch name as input parameter.)
b) Write a function to find the minimum loan amount approved.
c) Create a function which returns the total number of customers who have applied for a loan
more than Rs.200000. (Accept loan amount as input parameter)
Create Tables
CREATE TABLE branch (
bid INTEGER PRIMARY KEY,
brname CHAR(30),
brcity CHAR(10)
);
CREATE TABLE customer (
cno INTEGER PRIMARY KEY,
cname CHAR(20),
caddr CHAR(35),
city CHAR(20)
);
CREATE TABLE loan_application (
lno INTEGER PRIMARY KEY,
lamtrequired MONEY,
lamtapproved MONEY,
l_date DATE
);
CREATE TABLE ternary (
bid INTEGER REFERENCES branch(bid),
cno INTEGER REFERENCES customer(cno),
lno INTEGER REFERENCES loan_application(lno),
PRIMARY KEY (bid, cno, lno)
);
a) Function to Return Total Number of Customers of a Particular Branch (Input: Branch Name)
CREATE OR REPLACE FUNCTION total_customers_of_branch(bname CHAR)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
total INTEGER;
BEGIN
SELECT COUNT(DISTINCT t.cno)
INTO total
FROM ternary t
JOIN branch b ON b.bid = t.bid
WHERE b.brname = bname;
RETURN total;
END;
$$;
Execute
SELECT total_customers_of_branch('MainBranch');
b) Function to Find Minimum Loan Amount Approved
CREATE OR REPLACE FUNCTION min_loan_amount_approved()
RETURNS MONEY
LANGUAGE plpgsql
AS $$
DECLARE
min_amt MONEY;
BEGIN
SELECT MIN(lamtapproved)
INTO min_amt
FROM loan_application;
RETURN min_amt;
END;
$$;
Execute
SELECT min_loan_amount_approved();
c) Function to Return Total Number of Customers (Who Applied for Loan More Than Given Amount)
CREATE OR REPLACE FUNCTION customers_with_loan_above(amount MONEY)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
total INTEGER;
BEGIN
SELECT COUNT(DISTINCT t.cno)
INTO total
FROM ternary t
JOIN loan_application l ON l.lno = t.lno
WHERE l.lamtrequired > amount;
RETURN total;
END;
$$;
Execute
SELECT customers_with_loan_above(200000);
SET C
Student- Teacher database
Consider the following database maintained by a school. The school maintains information about
students and the teachers. It also gives information of the subject taught by the teacher.
Following are the tables:
student (sno integer, s_name char(30), s_class char(10), s_addr char(50))
teacher (tno integer, t_name char (20), qualification char (15), experience integer)
The relationship is as follows: STUDENT-TEACHER: Many to Many with descriptive attribute
SUBJECT.
a) Write a function to find name of the most experienced teacher for “Computer”.
b) Write a function to find the teacher teaching maximum number of subjects.
c) Write a function to find the number of teachers having qualification “NET”.
Create Tables
CREATE TABLE student (
sno INTEGER PRIMARY KEY,
s_name CHAR(30),
s_class CHAR(10),
s_addr CHAR(50)
);
CREATE TABLE teacher (
tno INTEGER PRIMARY KEY,
t_name CHAR(20),
qualification CHAR(15),
experience INTEGER
);
CREATE TABLE teaches (
sno INTEGER REFERENCES student(sno),
tno INTEGER REFERENCES teacher(tno),
subject CHAR(20),
PRIMARY KEY (sno, tno, subject)
);
a) Function to Find Name of the Most Experienced Teacher for “Computer”
CREATE OR REPLACE FUNCTION most_experienced_computer_teacher()
RETURNS CHAR(20)
LANGUAGE plpgsql
AS $$
DECLARE
teacher_name CHAR(20);
BEGIN
SELECT t.t_name
INTO teacher_name
FROM teacher t
JOIN teaches te ON t.tno = te.tno
WHERE te.subject = 'Computer'
ORDER BY t.experience DESC
LIMIT 1;
RETURN teacher_name;
END;
$$;
Execute
SELECT most_experienced_computer_teacher();
b) Function to Find the Teacher Teaching Maximum Number of Subjects
CREATE OR REPLACE FUNCTION teacher_with_max_subjects()
RETURNS CHAR(20)
LANGUAGE plpgsql
AS $$
DECLARE
teacher_name CHAR(20);
BEGIN
SELECT t.t_name
INTO teacher_name
FROM teacher t
JOIN teaches te ON t.tno = te.tno
GROUP BY t.tno, t.t_name
ORDER BY COUNT(DISTINCT te.subject) DESC
LIMIT 1;
RETURN teacher_name;
END;
$$;
Execute
SELECT teacher_with_max_subjects();
c) Function to Find Number of Teachers Having Qualification “NET”
CREATE OR REPLACE FUNCTION count_net_qualified_teachers()
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
total INTEGER;
BEGIN
SELECT COUNT(*)
INTO total
FROM teacher
WHERE qualification = 'NET';
RETURN total;
END;
$$;
Execute
SELECT count_net_qualified_teachers();
SET D
Project – Employee Database
Consider the following database
Project (pno int, pname char (30), ptype char (20), duration int)
Employee (empno int, ename char (20), joining_date date)
The relationship between Project and Employee is many to many with descriptive attribute
start_date.
Create the above database in PostGreSQL and insert sufficient records.
Execute any two of the following using PL/pgSQL
a. Write a stored function to accept project type as an input and display all project names of
that type.
b. Write a function which accepts employee name and prints the details of the project which
the employee works on.
c. Write a function to accept project name as input and returns the number of employees
working on the project.
Project Table
CREATE TABLE Project (
pno INT PRIMARY KEY,
pname CHAR(30),
ptype CHAR(20),
duration INT
);
Employee Table
CREATE TABLE Employee (
empno INT PRIMARY KEY,
ename CHAR(20),
joining_date DATE
);
Many-to-Many Relationship Table (Descriptive attribute: start_date)
CREATE TABLE Works_On (
pno INT REFERENCES Project(pno),
empno INT REFERENCES Employee(empno),
start_date DATE,
PRIMARY KEY (pno, empno)
);
2 ⃣ Insert Sample Records Insert into Project
INSERT INTO Project VALUES
(1, 'ERP System', 'Software', 12),
(2, 'Payroll App', 'Software', 6),
(3, 'Bridge Design', 'Civil', 18);
Insert into Employee
INSERT INTO Employee VALUES
(101, 'Amit', '2021-01-10'),
(102, 'Sneha', '2020-07-15'),
(103, 'Rahul', '2019-03-20');
Insert into Works_On
INSERT INTO Works_On VALUES
(1, 101, '2022-01-01'),
(1, 102, '2022-02-01'),
(2, 101, '2022-03-10'),
(3, 103, '2022-04-15');
a) Function to Accept Project Type and Display All Project Names
CREATE OR REPLACE FUNCTION projects_by_type(ptype_input CHAR)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN
SELECT pname
FROM Project
WHERE ptype = ptype_input
LOOP
RAISE NOTICE 'Project Name: %', rec.pname;
END LOOP;
IF NOT FOUND THEN
RAISE NOTICE 'No projects found for this type';
END IF;
END;
$$;
Execute
SELECT projects_by_type('Software');
b) Function Accepting Employee Name and Display Project Details
CREATE OR REPLACE FUNCTION project_details_by_employee(emp_name CHAR)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN
SELECT p.pno, p.pname, p.ptype, p.duration
FROM Project p
JOIN Works_On w ON p.pno = w.pno
JOIN Employee e ON e.empno = w.empno
WHERE e.ename = emp_name
LOOP
RAISE NOTICE 'Project No: %, Name: %, Type: %, Duration: % months',
rec.pno, rec.pname, rec.ptype, rec.duration;
END LOOP;
IF NOT FOUND THEN
RAISE NOTICE 'Employee not assigned to any project';
END IF;
END;
$$;
Execute
SELECT project_details_by_employee('Amit');
c) Function to Accept Project Name and Return Number of Employees
CREATE OR REPLACE FUNCTION employee_count_for_project(proj_name CHAR)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
total INTEGER;
BEGIN
SELECT COUNT(*)
INTO total
FROM Works_On w
JOIN Project p ON p.pno = w.pno
WHERE p.pname = proj_name;
RETURN total;
END;
$$;
Execute
SELECT employee_count_for_project('ERP System');
SET E
Student - Subject Database
Consider the following database
Student (roll_no integer, name varchar(30), address varchar(50), class varchar(10)) Subject
(scode varchar(10), subject_name varchar(20))
Student-Subject are related with Many to Many relationship with attributes marks_scored. Create
the above database in PostGreSQL and insert sufficient records.
a.Write a function which will accept the name and print all the details of that student.
b. Write a function to accept student roll_no as input and displays details of that student.
c.Write a stored function using cursors, to accept class from the user and display the details of the
students of that class.
Student Table
CREATE TABLE Student (
roll_no INTEGER PRIMARY KEY,
name VARCHAR(30),
address VARCHAR(50),
class VARCHAR(10)
);
Subject Table
CREATE TABLE Subject (
scode VARCHAR(10) PRIMARY KEY,
subject_name VARCHAR(20)
);
Many-to-Many Relationship Table (Descriptive attribute: marks_scored)
CREATE TABLE Student_Subject (
roll_no INTEGER REFERENCES Student(roll_no),
scode VARCHAR(10) REFERENCES Subject(scode),
marks_scored INTEGER,
PRIMARY KEY (roll_no, scode)
);
2 ⃣ Insert Sufficient Records Insert into Student
INSERT INTO Student VALUES
(1, 'Amit', 'Nashik', 'FY'),
(2, 'Sneha', 'Pune', 'FY'),
(3, 'Rahul', 'Mumbai', 'SY'),
(4, 'Priya', 'Nashik', 'SY');
Insert into Subject
INSERT INTO Subject VALUES
('CS101', 'Computer'),
('MA101', 'Maths'),
('EN101', 'English');
Insert into Student_Subject
INSERT INTO Student_Subject VALUES
(1, 'CS101', 78),
(1, 'MA101', 65),
(2, 'CS101', 82),
(3, 'EN101', 70),
(4, 'MA101', 88);
a) Function to Accept Student Name and Print All Details
CREATE OR REPLACE FUNCTION student_details_by_name(sname VARCHAR)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN
SELECT * FROM Student
WHERE name = sname
LOOP
RAISE NOTICE 'Roll No: %, Name: %, Address: %, Class: %',
rec.roll_no, rec.name, rec.address, rec.class;
END LOOP;
IF NOT FOUND THEN
RAISE NOTICE 'Student not found';
END IF;
END;
$$;
Execute
SELECT student_details_by_name('Amit');
b) Function to Accept Roll No and Display Student Details
CREATE OR REPLACE FUNCTION student_details_by_roll(rno INTEGER)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
rec Student%ROWTYPE;
BEGIN
SELECT * INTO rec
FROM Student
WHERE roll_no = rno;
IF NOT FOUND THEN
RAISE NOTICE 'Student not found';
ELSE
RAISE NOTICE 'Roll No: %, Name: %, Address: %, Class: %',
rec.roll_no, rec.name, rec.address, rec.class;
END IF;
END;
$$;
Execute
SELECT student_details_by_roll(2);
c) Stored Function Using Cursor
(Accept Class and Display Details of Students of that Class)
CREATE OR REPLACE FUNCTION students_by_class(class_input VARCHAR)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
cur_students CURSOR FOR
SELECT roll_no, name, address, class
FROM Student
WHERE class = class_input;
rec RECORD;
BEGIN
OPEN cur_students;
LOOP
FETCH cur_students INTO rec;
EXIT WHEN NOT FOUND;
RAISE NOTICE 'Roll No: %, Name: %, Address: %, Class: %',
rec.roll_no, rec.name, rec.address, rec.class;
END LOOP;
CLOSE cur_students;
END;
$$;
Execute
SELECT students_by_class('FY');
No comments:
Post a Comment