LJIET’s Students Club 1
IPE_1----------------------------------------------------------------------------
Write a SQL query considering below schema. Create tables with Primary Key, foreign key constraints
in given schemas.
Manager (mid, eid, mname)
Department (did, mid, dname, location)
Employee (eid, ename, mobile, salary, joining_date, mid)
[Link] the name and salary of employees whose salary are greater than each and every employees
who are working under manager id 14.
SELECT [Link], [Link]
FROM Employee e
WHERE [Link] > ALL (
SELECT [Link]
FROM Employee e2
WHERE [Link] = 14
);
[Link] the name of employees who have not assigned any department.
SELECT [Link]
FROM Employee e
LEFT JOIN Department d ON [Link] = [Link]
WHERE [Link] IS NULL;
[Link] the employee names whose location is Kota.
SELECT [Link]
FROM Employee e
JOIN Department d ON [Link] = [Link]
WHERE [Link] = 'Kota';
LJIET’s Students Club 2
[Link] the name of manager along with count of employees assigned to him/her in descending
order.
SELECT [Link], COUNT([Link]) AS employee_count
FROM Manager m
JOIN Employee e ON [Link] = [Link]
GROUP BY [Link]
ORDER BY employee_count DESC;
IPE_2----------------------------------------------------------------------------
Write PL/pgSQL using trigger for insertion of first_name, last_name, amount and payment_id into
Customer_backup table when deletion happens from Customer table considering below schema.
Customer_backup (first_name, last_name, amount, payment_id)
Customer (customer_id, payment_id, first_name, last_name, amount)
Create tables with Primary Key, foreign key constraints in given schemas.
CREATE OR REPLACE FUNCTION backup_customer_before_delete()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO Customer_backup
VALUES (OLD.first_name, OLD.last_name, [Link], OLD.payment_id);
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE or Replace TRIGGER trigger_backup_customer
BEFORE DELETE ON Customer
FOR EACH ROW
LJIET’s Students Club 3
EXECUTE FUNCTION backup_customer_before_delete();
IPE_3----------------------------------------------------------------------------
Consider an art gallery having a database with four tables paintings, artists, Customer, and sales as:
Paintings (p_id, name, a_id, listed_price)
Artists (a_id, first_name, last_name)
Customer (c_id, first_name, last_name)
Sales (s_id, date, p_id, a_id, c_id, sales_price).
Create tables with Primary Key, foreign key constraints in given schemas.
Write queries to retrieve the following data.
1) Find the paintings that were sold in first thirteen days of month August,2023.
SELECT [Link]
FROM Paintings p
JOIN Sales s ON p.p_id = s.p_id
WHERE [Link] BETWEEN '2023-08-01' AND '2023-08-13';
2) Display painting name and its listed price with its sales_price which was sold out at a price higher
than the listed price to any customer.
SELECT [Link], Paintings.listed_price, Sales.sales_price
FROM Paintings
INNER JOIN Sales ON Paintings.p_id = Sales.p_id
WHERE Sales.sales_price > Paintings.listed_price;
3) Retrieve details of paintings that were sold at a price equal to the listed price to all customers.
SELECT [Link], p.listed_price, s.sales_price
FROM Paintings p
JOIN Sales s ON p.p_id = s.p_id
LJIET’s Students Club 4
WHERE s.sales_price = p.listed_price;
4) Display the name of customers who has bought at least one painting.
SELECT DISTINCT first_name,last_name
FROM Customer
JOIN Sales ON Customer.c_id = Sales.c_id;
5) Find the total sales value for each artist.
SELECT first_name, last_name, SUM(sales_price) AS total_sales_value
FROM Artists
JOIN Sales ON Artists.a_id = Sales.a_id
GROUP BY Artists.first_name, Artists.last_name;
IPE_4----------------------------------------------------------------------------
Using the following schema, write SQL statement to fetch the correct data.
Insurance_Company (c_id, c_name, city, state, policy_no, policy_name, premium, cust_id, a_id)
Agent (a_id, a_name, address, a_city, a_state, c_name, salary, incentive)
Customer (cust_id, name, age, cust_address, cust_city, cust_state, a_id, policy_no)
Create tables with Primary Key, foreign key constraints in given schemas.
1) Display the details of customer who have taken policy from ‘Ahmedabad Insurance Company’.
SELECT * FROM Customer
INNER JOIN Insurance_Company ON Customer.policy_no = Insurance_Company.policy_no WHERE
Insurance_Company.c_name = 'Ahmedabad Insurance Company';
2) Display agent name who have not sold even a single policy.
SELECT a_name FROM Agent
LJIET’s Students Club 5
LEFT JOIN Customer ON Agent.a_id = Customer.a_id
WHERE cust_id IS NULL;
3) Display the name of Insurance company who has maximum number of customers.
SELECT Insurance_Company.c_name FROM Insurance_Company
INNER JOIN Customer ON Insurance_Company.policy_no = Customer.policy_no
GROUP BY Insurance_Company.c_name
ORDER BY COUNT(Customer.cust_id) DESC
LIMIT 1;
IPE_5----------------------------------------------------------------------------
Consider following schema and write SQL statements for given queries using subqueries.
Faculty (f_id, f_name, address, city, state, age, salary, d_id)
Department (dep_id, d_name, course, location, sub_code, subject)
Student (enrl_no, s_name, s_address, s_city, s_state, s_age, course, f_id, hobby)
Create tables with Primary Key, foreign key constraints in given schemas.
1) Display name of faculties and students who are not living in the same city.
SELECT f_name
FROM Faculty
WHERE city NOT IN (
SELECT s_city
FROM Student
WHERE f_id = Faculty.f_id
UNION
SELECT s_name
FROM Student
LJIET’s Students Club 6
WHERE s_city NOT IN (
SELECT city
FROM Faculty
WHERE f_id = Student.f_id
);
2) Find out how much amount is spent on faculties as their salary who are working in 'IT
Engineering' Department.
SELECT SUM(salary)
FROM Faculty
WHERE d_id = (
SELECT dep_id
FROM Department
WHERE d_name = 'IT Engineering'
);
3) Display department name, course, subject code and subject of person who have enrolled as
a student.
SELECT Department.d_name, [Link], Department.sub_code, [Link]
FROM Department
WHERE Department.dep_id IN (
SELECT Faculty.d_id
FROM Faculty
INNER JOIN Student ON Faculty.f_id = Student.f_id
);
4) Display id and name of faculty who are working in 'IT Engineering' department and have a
salary more than Rs. 60000.
LJIET’s Students Club 7
SELECT Faculty.f_id, Faculty.f_name
FROM Faculty
WHERE Faculty.d_id = (
SELECT Department.dep_id
FROM Department
WHERE Department.d_name = 'IT Engineering'
AND [Link] > 60000;
IPE_6----------------------------------------------------------------------------
Prepare a PL/pgSQL block that simulates an ATM transaction system.
•If the withdrawal amount is less than or equal to 0, the block should display an "Invalid withdrawal
amount" message.
•If the withdrawal amount is greater than the account balance, the block should display an
"Insufficient funds" message.
•If the withdrawal amount is up to Rs. 20000, the block should perform a withdrawal and deduct the
amount from the account balance.
•If the withdrawal amount is up to Rs. 40000, the block should perform a withdrawal, deduct the
amount, and apply a transaction fee 5%.
•For withdrawal amounts greater than Rs. 40000, the block should perform a withdrawal, deduct the
amount, and apply a higher transaction fee 18%.
The block should also display the current account balance after each scenario.
DO $$
DECLARE withdrawal_amount NUMERIC
:= 25000; account_balance NUMERIC
:= 50000; transaction_fee NUMERIC :=
0;
BEGIN
LJIET’s Students Club 8
IF withdrawal_amount <= 0 THEN
RAISE NOTICE 'Invalid withdrawal amount';
ELSIF withdrawal_amount > account_balance THEN
RAISE NOTICE 'Insufficient funds';
ELSIF withdrawal_amount <= 20000 THEN
account_balance := account_balance - withdrawal_amount;
RAISE NOTICE 'Withdrawal successful. Your current balance is Rs. %', account_balance;
ELSIF withdrawal_amount >= 20000 AND withdrawal_amount <= 40000 THEN
transaction_fee := withdrawal_amount * 0.05; account_balance :=
account_balance - withdrawal_amount - transaction_fee;
RAISE NOTICE 'Withdrawal successful. A 5%% fee of Rs. % was applied. Your current balance is
Rs. %', transaction_fee, account_balance;
ELSE transaction_fee := withdrawal_amount * 0.18; account_balance
:= account_balance - withdrawal_amount - transaction_fee;
RAISE NOTICE 'Withdrawal successful. An 18%% fee of Rs. % was applied. Your current balance is
Rs. %', transaction_fee, account_balance;
END IF;
END $$;
IPE_7----------------------------------------------------------------------------
Design a database for a library management system. Each book in the library has multiple copies,
and you want to implement a
LJIET’s Students Club 9
feature that allows users to check the availability of a specific book by its ISBN (International
Standard Book Number).
Write PL/pgSQL block using stored function. If book is available then it must return 1 else return 0.
Consider relation:Books ( books_isbn,book_name) Now what you will write to check availability of
book having ISBN9780451524935
-- Books Table CREATE TABLE Books (
books_isbn VARCHAR(13) PRIMARY KEY,
book_name VARCHAR(255)
);
-- BookCopies Table CREATE TABLE BookCopies ( copy_id
PRIMARY KEY, books_isbn VARCHAR(13) REFERENCES
Books(books_isbn), is_available BOOLEAN
);
CREATE OR REPLACE FUNCTION check_book_availability(books_isbn VARCHAR(13))
RETURNS INT AS $$ DECLARE
available_count INT;
BEGIN
-- Count the number of available copies for the given ISBN
SELECT COUNT(*) INTO available_count
FROM BookCopies
WHERE books_isbn = check_book_availability.books_isbn
AND is_available = TRUE;
-- If there are available copies, return 1, otherwise return 0
IF available_count > 0 THEN
RETURN 1;
ELSE
LJIET’s Students Club 10
RETURN 0;
END IF;
END;
$$ LANGUAGE plpgsql;
IPE_8----------------------------------------------------------------------------
In PostgreSQL, create a PL/pgSQL block that defines a function named calculate_salary_bonus which
takes two parameters:
employee_id and bonus_percentage. This function should calculate the bonus amount for the
employee with the given
employee_id based on their current salary and the provided bonus percentage. Assume “employee”
table with fields – eid, ename and salary
CREATE OR REPLACE FUNCTION calculate_bonus(employee_id INT, bonus_percentage NUMERIC)
RETURNS NUMERIC AS $$
DECLARE current_salary
NUMERIC; bonus_amount
NUMERIC;
BEGIN
SELECT salary INTO current_salary FROM employee WHERE eid = employee_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Employee with ID % not found', employee_id;
END IF;
bonus_amount = current_salary * (bonus_percentage / 100.0);
RETURN bonus_amount;
END;
$$ LANGUAGE plpgsql;
LJIET’s Students Club 11
IPE_9----------------------------------------------------------------------------
An expression below creates a procedure emp_infor that accept two parameters: employeeno and
job_title, then pass them to
explicit cursor named emp_list to retrieve the firstname, lastname, hiredate, job_id and salary, of all
employees earning a salary
less that 11000 and have an “O” as the second letter in the lastname. Create an anonymous block
that will call the procedure to
display the details of employee as per entry of job_title and employeeno parameters. Use this table
Employee (employeeno, firstname, lastname, hiredate, job_id ,salary, job_title).
CREATE OR REPLACE PROCEDURE emp_infor(
p_employeeno IN [Link]%TYPE,
p_job_title IN Employee.job_title%TYPE
LANGUAGE plpgsql
AS $$
DECLARE
CURSOR emp_list IS
SELECT firstname, lastname, hiredate, job_id, salary
FROM Employee
WHERE salary < 11000
AND SUBSTRING(lastname, 2, 1) = 'O'
AND employeeno = p_employeeno
AND job_title = p_job_title;
v_firstname [Link]%TYPE;
v_lastname [Link]%TYPE;
v_hiredate [Link]%TYPE;
LJIET’s Students Club 12
v_job_id Employee.job_id%TYPE; v_salary
[Link]%TYPE;
BEGIN
OPEN emp_list;
FETCH emp_list INTO v_firstname, v_lastname, v_hiredate, v_job_id, v_salary;
IF NOT FOUND THEN
RAISE NOTICE 'No employee found with employeeno % and job_title %', p_employeeno,
p_job_title;
ELSE
RAISE NOTICE 'Firstname: %, Lastname: %, Hire Date: %, Job ID: %, Salary: Rs. %',
v_firstname, v_lastname, v_hiredate, v_job_id, v_salary; END IF;
CLOSE emp_list;
END $$;
DO $$
BEGIN
-- Call the emp_infor procedure with specific employeeno and job_title parameters
PERFORM emp_infor(101, 'Software Engineer');
END $$;
IPE_10----------------------------------------------------------------------------
Write a PL/pgSQL block to find out count of completed month between two dates enter by the user.
DO $$
DECLARE
LJIET’s Students Club 13
start_date DATE := '2024-01-15';
end_date DATE := '2024-08-12';
completed_months INT;
BEGIN
IF end_date < start_date THEN
RAISE EXCEPTION 'End date must be after start date';
END IF;
completed_months := (EXTRACT(YEAR FROM age(end_date, start_date)) * 12) +
EXTRACT(MONTH FROM age(end_date, start_date));
IF EXTRACT(DAY FROM end_date) < EXTRACT(DAY FROM start_date) THEN
completed_months := completed_months - 1;
END IF;
RAISE NOTICE 'Completed Months: %', completed_months;
END $$;
IPE_11----------------------------------------------------------------------------
Writer a PL/pgSQL block to generate marksheet. Take 1st five column values and find out
TOTAL_MARKS, PERCENTAGE,
GRADE using cursor. Assume suitable data
Student_Marksheet (ROLLNO, SNAME, DIV, DBMS, DS, TOTAL_MARKS, PERCENTAGE, GRADE)
CREATE TABLE Student_Marksheet (
ROLLNO SERIAL PRIMARY KEY,
SNAME VARCHAR(100),
DIV CHAR(1),
DBMS INT,
LJIET’s Students Club 14
DS INT,
TOTAL_MARKS INT,
PERCENTAGE NUMERIC,
GRADE CHAR(1)
);
INSERT INTO Student_Marksheet (SNAME, DIV, DBMS, DS)
VALUES
('Alice', 'A', 78, 85),
('Bob', 'B', 65, 75),
('Charlie', 'A', 90, 95),
('David', 'B', 50, 60),
('Eve', 'A', 88, 92);
DO $$
DECLARE
student_cursor Cursor for
SELECT ROLLNO, SNAME, DBMS, DS
FROM Student_Marksheet;
v_rollno Student_Marksheet.ROLLNO%TYPE;
v_sname Student_Marksheet.SNAME%TYPE;
v_dbms Student_Marksheet.DBMS%TYPE; v_ds
Student_Marksheet.DS%TYPE; v_total_marks INT;
v_percentage NUMERIC; v_grade CHAR(1);
BEGIN
-- Open the cursor
OPEN student_cursor;
-- Loop through each record in the cursor
LJIET’s Students Club 15
LOOP
FETCH student_cursor INTO v_rollno, v_sname, v_dbms, v_ds;
-- Exit the loop when no more rows are returned
EXIT WHEN NOT FOUND;
-- Calculate total marks and percentage v_total_marks := v_dbms + v_ds;
v_percentage := (v_total_marks * 100) / 200; -- Assuming each subject is out of 100 marks
-- Determine the grade based on percentage
IF v_percentage >= 90 THEN
v_grade := 'A';
ELSIF v_percentage >= 75 THEN
v_grade := 'B';
ELSIF v_percentage >= 60 THEN
v_grade := 'C';
ELSIF v_percentage >= 50 THEN
v_grade := 'D';
ELSE v_grade
:= 'F'; END IF;
-- Update the student record with total marks, percentage, and grade
UPDATE Student_Marksheet
SET TOTAL_MARKS = v_total_marks,
PERCENTAGE = v_percentage,
GRADE = v_grade
WHERE ROLLNO = v_rollno;
-- Display the result
LJIET’s Students Club 16
RAISE NOTICE 'ROLLNO: %, SNAME: %, TOTAL MARKS: %, PERCENTAGE: %, GRADE: %',
v_rollno, v_sname, v_total_marks, v_percentage, v_grade;
END LOOP;
-- Close the cursor
CLOSE student_cursor;
END $$;
IPE_12----------------------------------------------------------------------------
Write a code in PL/pgSQL to create a trigger for following conditions
1. Create table for products ( product_id, product_name)
2. Check for duplicate entry in a product_name and raise an error before insertion of duplicate
value. Also show trigger by inserting the value.
CREATE OR REPLACE FUNCTION check_duplicate_product()
RETURNS TRIGGER AS $$
BEGI
-- SELECT 1 is a simple query that returns a constant value of 1
-- for each row that matches the conditions of the query
IF EXISTS (SELECT 1 FROM products WHERE product_name = NEW.product_name) THEN
RAISE EXCEPTION 'Duplicate product name: %', NEW.product_name;
END IF;
-- If no duplicate, allow the insertion
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_check_duplicate_product
LJIET’s Students Club 17
BEFORE INSERT ON products
FOR EACH ROW
EXECUTE FUNCTION check_duplicate_product();
IPE_13----------------------------------------------------------------------------
Write trigger in PL/pgSQL for a library book management system. This includes:
Schema creation:
Books (bid, btitle,book_count), Book_issue (id, sid, btitle)
Create tables with Primary Key, foreign key constraints in given schemas.
Create relevant database and if any student borrows a book from library then the count of that
specified book should be decremented.
CREATE OR REPLACE FUNCTION decrement_book_count()
RETURNS TRIGGER AS $$
BEGIN
-- Check if there are enough copies of the book to decrement
IF (SELECT book_count FROM Books WHERE btitle = [Link]) <= 0 THEN
RAISE EXCEPTION 'No copies available for book: %', [Link];
END IF;
-- Decrement the book count
UPDATE Books
SET book_count = book_count - 1
WHERE btitle = [Link];
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
LJIET’s Students Club 18
CREATE TRIGGER trigger_decrement_book_count
BEFORE INSERT ON Book_issue
FOR EACH ROW
EXECUTE FUNCTION decrement_book_count();
IPE_14----------------------------------------------------------------------------
Write a PL/pgSQL Trigger program that logs the detail of each new order, including the product
associated information , into a
order_logs table whenever a new order is inserted into order table. Create tables with Primary Key,
foreign key constraints in given schemas.
Products(product_id, product_name, price) ,
Orders(order_id, product_id, quantity),
Order_logs(log_id, product_id, product_name, price, quantity, order_date)
CREATE OR REPLACE FUNCTION log_order_details()
RETURNS TRIGGER AS $$
BEGIN
-- Insert a new log entry into the Order_logs table
INSERT INTO Order_logs (product_id, product_name, price, quantity, order_date)
SELECT
NEW.product_id,
p.product_name,
[Link],
[Link],
CURRENT_TIMESTAMP
FROM Products p
WHERE p.product_id = NEW.product_id;
RETURN NEW;
END;
LJIET’s Students Club 19
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_log_order
AFTER INSERT ON Orders
FOR EACH ROW
EXECUTE FUNCTION log_order_details();
IPE_15----------------------------------------------------------------------------
You are tasked with creating a trigger in PL/pgSQL to monitor updates to employee salaries in a table
named Employees. The
Employees table has the following columns: EmployeeID, Name, and Salary.
Write an AFTER UPDATE trigger that:
Fires after an UPDATE operation on the Salary column.
Calculate and display the old salary, the new salary, and the difference between the two for each
updated record
CREATE OR REPLACE FUNCTION log_salary_update()
RETURNS TRIGGER AS $$
BEGIN
RAISE NOTICE 'EmployeeID: %, Old Salary: %, New Salary: %, Difference: %',
[Link], [Link], [Link], [Link] - [Link];
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_log_salary_update
AFTER UPDATE OF Salary ON Employees
LJIET’s Students Club 20
FOR EACH ROW
EXECUTE FUNCTION log_salary_update();
IPE_16----------------------------------------------------------------------------
Write a PL/pgSQL function that converts temperatures between Fahrenheit, Celsius and Kelvin.
The function should take three parameters:
1. The temperature value to convert.
2. The scale of the input temperature.
3. The scale to convert the temperature. Use the following Conversion Logic:
• Fahrenheit to Celsius: C= (F−32) × 5/9
• Celsius to Fahrenheit: F=(C × 9/5) + 32
• Fahrenheit to Kelvin: K=(F − 32)×5/9+273.15
• Celsius to Kelvin: K=C+273.15
• Kelvin to Fahrenheit: F=(K−273.15)×9/5+32
• Kelvin to Celsius: C=K−273.15
CREATE OR REPLACE FUNCTION convert_temperature( temperature_value
NUMERIC,
input_scale CHAR(1), output_scale
CHAR(1)
RETURNS NUMERIC AS $$
DECLARE temp_in_celsius
NUMERIC; temp_in_fahrenheit
NUMERIC; temp_in_kelvin
NUMERIC; BEGIN
IF input_scale = 'F' THEN temp_in_celsius :=
(temperature_value - 32) * 5/9; ELSIF input_scale =
'C' THEN temp_in_celsius := temperature_value;
LJIET’s Students Club 21
ELSIF input_scale = 'K' THEN temp_in_celsius :=
temperature_value - 273.15;
ELSE
RAISE EXCEPTION 'Invalid input scale: %', input_scale;
END IF;
IF output_scale = 'F' THEN temp_in_fahrenheit
:= (temp_in_celsius * 9/5) + 32;
RETURN temp_in_fahrenheit;
ELSIF output_scale = 'C' THEN RETURN
temp_in_celsius; ELSIF output_scale = 'K'
THEN temp_in_kelvin := temp_in_celsius +
273.15;
RETURN temp_in_kelvin;
ELSE
RAISE EXCEPTION 'Invalid output scale: %', output_scale;
END IF;
END;
$$ LANGUAGE plpgsql;
IPE_17----------------------------------------------------------------------------
Write a PL/pgSQL stored procedure that uses a cursor to fetch and print the detail of each order,
including the associated product
information ordered by order_id. Create tables with Primary Key, foreign key constraints in given
schemas.
Products(product_id, product_name, price), Orders(order_id, product_id, quantity).
CREATE OR REPLACE PROCEDURE fetch_order_details()
LANGUAGE plpgsql
LJIET’s Students Club 22
AS $$ DECLARE
order_cursor Cursor for
SELECT o.order_id, o.product_id, [Link], p.product_name, [Link]
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
ORDER BY o.order_id;
v_order_id Orders.order_id%TYPE;
v_product_id Products.product_id%TYPE;
v_quantity [Link]%TYPE;
v_product_name Products.product_name%TYPE;
v_price [Link]%TYPE; v_total_cost
NUMERIC(10, 2);
BEGIN
-- Open the cursor
OPEN order_cursor;
-- Loop through each record in the cursor
LOOP
FETCH order_cursor INTO v_order_id, v_product_id, v_quantity, v_product_name, v_price;
-- Exit the loop when no more rows are returned
EXIT WHEN NOT FOUND;
-- Calculate the total cost for the current order
v_total_cost := v_quantity * v_price;
-- Display the order details
LJIET’s Students Club 23
RAISE NOTICE 'Order ID: %, Product ID: %, Product Name: %, Quantity: %, Price: Rs. %, Total
Cost: Rs. %', v_order_id, v_product_id, v_product_name, v_quantity, v_price,
v_total_cost; END LOOP;
-- Close the cursor
CLOSE order_cursor;
END $$;
IPE_18----------------------------------------------------------------------------
Consider the following relational schema that manages the assignment of scholarship to students.
APPLICATION (StudentID, Date, State)
COURSE (CourseID, Title, Credits)
RANKING (StudentID, Avg_score, Credits, Rank)
EXAM (CourseID, StudentID, Date, Grade)
Create tables with Primary Key, foreign key constraints in given schemas.
We want to manage through a system of triggers the assignment of scholarship to students. The
scholarship are awarded to
students who apply and, at the date of the application, have taken exams for at least 50 credits, with
an average score of at least 27.
CREATE OR REPLACE FUNCTION check_scholarship()
RETURNS TRIGGER AS $$ DECLARE
total_credits INT;
avg_score NUMERIC(5, 2);
BEGIN
-- Calculate the total credits for the student
SELECT SUM(Credits) INTO total_credits
FROM EXAM
WHERE StudentID = [Link];
-- Get the average score for the student
LJIET’s Students Club 24
SELECT Avg_score INTO avg_score
FROM RANKING
WHERE StudentID = [Link];
-- Check if the student meets the criteria for a scholarship
IF total_credits >= 50 AND avg_score >= 27 THEN
RAISE NOTICE 'Student % is eligible for a scholarship.', [Link];
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
IPE_19----------------------------------------------------------------------------
Write a stored procedure named update_marks that updates the marks of a student in the
Result_SEM_II table. Also handle error
if Enrollment_no is not found. The procedure should take three parameters:
1. Enrollment_no 2. Subject_name 3. New_marks
Result_SEM_II (EN_NO , NAME, DBMS_MARKS, DS_MARKS, JAVA_MARKS, FEE_MARKS,
MATHS_MARKS)
CREATE OR REPLACE PROCEDURE update_marks(
p_enrollment_no INT, p_subject_name TEXT,
p_new_marks INT
LANGUAGE plpgsql
AS $$ DECLARE
row_count INT;
BEGIN
-- Count the number of rows with the given enrollment number
LJIET’s Students Club 25
SELECT COUNT(*) INTO row_count
FROM Result_SEM_II
WHERE EN_NO = p_enrollment_no;
-- Check if the enrollment number exists
IF row_count = 0 THEN
RAISE EXCEPTION 'Enrollment number % not found', p_enrollment_no;
ELSE
-- Update the marks based on the subject name
IF p_subject_name = 'DBMS' THEN
UPDATE Result_SEM_II
SET DBMS_MARKS = p_new_marks
WHERE EN_NO = p_enrollment_no;
ELSIF p_subject_name = 'DS' THEN
UPDATE Result_SEM_II
SET DS_MARKS = p_new_marks
WHERE EN_NO = p_enrollment_no;
ELSIF p_subject_name = 'JAVA' THEN
UPDATE Result_SEM_II
SET JAVA_MARKS = p_new_marks
WHERE EN_NO = p_enrollment_no;
ELSIF p_subject_name = 'FEE' THEN
UPDATE Result_SEM_II
SET FEE_MARKS = p_new_marks
WHERE EN_NO = p_enrollment_no;
ELSIF p_subject_name = 'MATHS' THEN
UPDATE Result_SEM_II
SET MATHS_MARKS = p_new_marks
WHERE EN_NO = p_enrollment_no;
ELSE
LJIET’s Students Club 26
RAISE EXCEPTION 'Invalid subject name %', p_subject_name;
END IF;
END IF;
END;
$$;
IPE_20----------------------------------------------------------------------------
Prepare a trigger while updating the mobile number of employee and store old and new updated
number into a table named
Emp_log with details of employee id, fname, old_mno, new_mno and update_time using following
schemas:
Emp(id, fname, mno)
Emp_log(id, fname, old_mno, new_mno, update_time)
Create tables and also add five data in the Emp table. Also update mobile number of any employee
and if updated mobile number
is same as old number, display a notice that "mobile number can not be updated" and Emp_log
should not update.
CREATE TABLE Emp (
id INT PRIMARY KEY,
fname VARCHAR(100),
mno VARCHAR(15)
);
CREATE TABLE Emp_log (
id INT,
fname VARCHAR(100), old_mno VARCHAR(15),
new_mno VARCHAR(15), update_time TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
);
LJIET’s Students Club 27
INSERT INTO Emp (id, fname, mno) VALUES
(1, 'John', '1234567890'),
(2, 'Alice', '9876543210'),
(3, 'Bob', '5555555555'),
(4, 'Charlie', '6666666666'),
(5, 'David', '7777777777');
CREATE OR REPLACE FUNCTION update_mobile_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF [Link] = [Link] THEN
RAISE NOTICE 'Mobile number cannot be updated to the same value';
RETURN NULL; -- Prevent the update and logging
ELSE
INSERT INTO Emp_log (id, fname, old_mno, new_mno, update_time)
VALUES ([Link], [Link], [Link], [Link], CURRENT_TIMESTAMP);
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
IPE_21----------------------------------------------------------------------------
Write a PL/pgSQL cursor to fetch the titles of movies that were rented out in the last one month
from current date. Consider schema: movies (movie_id,title,release_date) rentals ( rental_id,
movie_id, rental_date)
LJIET’s Students Club 28
-- Create the movies table
CREATE TABLE movies (
movie_id SERIAL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
release_date DATE
);
-- Create the rentals table CREATE TABLE rentals
( rental_id SERIAL PRIMARY KEY, movie_id
INT REFERENCES movies(movie_id),
rental_date DATE NOT NULL
);
-- Insert data into movies table
INSERT INTO movies (title, release_date)
VALUES
('The Matrix', '1999-03-31'),
('Inception', '2010-07-16'),
('Interstellar', '2014-11-07'),
('The Godfather', '1972-03-24');
-- Insert data into rentals table
INSERT INTO rentals (movie_id, rental_date)
VALUES
(1, CURRENT_DATE - INTERVAL '10 days'),
(2, CURRENT_DATE - INTERVAL '15 days'),
(3, CURRENT_DATE - INTERVAL '40 days'),
(4, CURRENT_DATE - INTERVAL '5 days');
DO $$
LJIET’s Students Club 29
DECLARE movie_cursor
Cursor FOR
SELECT title
FROM movies
INNER JOIN rentals ON movies.movie_id = rentals.movie_id
WHERE rentals.rental_date >= CURRENT_DATE - INTERVAL '1 month'
ORDER BY rentals.rental_date DESC;
v_title [Link]%TYPE;
BEGIN
-- Open the cursor
OPEN movie_cursor;
-- Loop through each record in the cursor
LOOP
FETCH movie_cursor INTO v_title;
-- Exit the loop when no more rows are returned
EXIT WHEN NOT FOUND;
-- Display the movie title
RAISE NOTICE 'Movie Title: %', v_title;
END LOOP;
-- Close the cursor
CLOSE movie_cursor;
END $$;
IPE_22----------------------------------------------------------------------------
Create a function to calculate the average salary of employees of deptid given by the user.
LJIET’s Students Club 30
Consider schema:
employees ( employee_id, name, department_id departments, salary, hire_date );
CREATE OR REPLACE FUNCTION avg_salary_by_dept(dept_id INT)
RETURNS NUMERIC AS $$
DECLARE avg_salary
NUMERIC;
BEGIN
-- Calculate the average salary for the given department
SELECT AVG(salary) INTO avg_salary
FROM employees
WHERE department_id = dept_id;
-- Return the calculated average salary
RETURN avg_salary;
END;
$$ LANGUAGE plpgsql;
We are very thankful to Kartik Dafda to make this PDF.