PostgreSQL & SQL Practice Question Bank

Schema Design, Queries, Views, Functions, Procedures & Cursors — LJIET's Students Club

Question 1
Write down the SQL query considering below schema of Apple store database
Customer (Customer_id, first_name, Last_name)
Payment (Customer_id, Product_id, Payment_id, amount, Payment_date)
Product (Product_id, Product_name, Product_type, Color)
Create tables with Primary Key, foreign key constraints in given schemas.
i. Give the name of customers who have made the payment in the middle of 1st Aug 2023 to 10th Aug 2023.
ii. Give the list of product name whose color is red and type is iPhone.
iii. Give all the product names and product types which were bought by Shyam Patel.
iv. Give the first name of customers whose total payment is greater than 2 lac rupees.

Answer

CREATE TABLE Customer (

Customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
Last_name VARCHAR(50));

CREATE TABLE Product (

Product_id INT PRIMARY KEY,
Product_name VARCHAR(100),
Product_type VARCHAR(50),
Color VARCHAR(20));

CREATE TABLE Payment (

Payment_id INT PRIMARY KEY,
Customer_id INT,
Product_id INT,
amount DECIMAL(10, 2),
Payment_date DATE,
FOREIGN KEY (Customer_id) REFERENCES Customer(Customer_id),
FOREIGN KEY (Product_id) REFERENCES Product(Product_id));

(i) SELECT DISTINCT C.first_name, C.Last_name

FROM Customer C
JOIN Payment P ON C.Customer_id = P.Customer_id
WHERE P.Payment_date BETWEEN '2023-08-01' AND '2023-08-10';

(ii) SELECT Product_name

FROM Product
WHERE Color = 'Red' AND Product_type = 'iPhone';

(iii) SELECT P.Product_name, P.Product_type

FROM Product P
JOIN Payment Pay ON P.Product_id = Pay.Product_id
JOIN Customer C ON C.Customer_id = Pay.Customer_id
WHERE C.first_name = 'Shyam' AND C.Last_name = 'Patel';

(iv) SELECT C.first_name

FROM Customer C
JOIN Payment P ON C.Customer_id = P.Customer_id
GROUP BY C.Customer_id, C.first_name
HAVING SUM([Link]) > 200000;
 Question 2
Write SQL query considering below schema of database
Film(film_id, title, legth, rental_rate), Actor(film_id, actor_id, first_name, last_name)
Category(film_id, rating, language, release_year)
Create tables with Primary Key, foreign key constraints in given schemas.
i. Give the name of actors whose actor id is 23.
ii. Give the title of films whose id is between 25 and 40.
iii. Give the name of actor whose last name contains Kapoor.
iv. Give the title of film which was released in 2022.
v. Give the name of actors played who have role in film title 'Chhello divas'.

Answer

CREATE TABLE Film (

film_id INT PRIMARY KEY,
title VARCHAR(100),
length INT,
rental_rate DECIMAL(5, 2));

CREATE TABLE Actor (

actor_id INT PRIMARY KEY,
film_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
FOREIGN KEY (film_id) REFERENCES Film(film_id));

CREATE TABLE Category (

film_id INT PRIMARY KEY,
rating VARCHAR(10),
language VARCHAR(50),
release_year INT,
FOREIGN KEY (film_id) REFERENCES Film(film_id));

(i) SELECT first_name, last_name

FROM Actor
WHERE actor_id = 23;

(ii) SELECT title

FROM Film
WHERE film_id BETWEEN 25 AND 40;

(iii) SELECT first_name, last_name

FROM Actor
WHERE last_name LIKE '%Kapoor%';

(iv) SELECT title

FROM Film F
JOIN Category C ON F.film_id = C.film_id
WHERE C.release_year = 2022;

(v) SELECT A.first_name, A.last_name

FROM Actor A
JOIN Film F ON A.film_id = F.film_id
WHERE [Link] = 'Chhello Divas';
 Question 3
Consider following schema and write query for given statement
Emp (eid,ename,city,dname,salary) Project(eid,pid,pname,location)
Create tables with Primary Key, foreign key constraints in given schemas.
(1) Display name of employees who belongs to Computer department.
(2) Display employee id whose name starts from letter J.
(3) Display all details of employees whose salary is from 10000 to 20000.
(4) Display name of employees who are having maximum salary.
(5) Display name of employees whose salary is higher than average salary of the employee.
(6) Display name of employees whose project id is 3 and location is Mumbai.

Answer

CREATE TABLE Emp (

eid INT PRIMARY KEY,
ename VARCHAR(100),
city VARCHAR(50),
dname VARCHAR(50),
salary DECIMAL(10, 2));

CREATE TABLE Project (

pid INT,
eid INT,
pname VARCHAR(100),
location VARCHAR(50),
PRIMARY KEY (pid, eid),
FOREIGN KEY (eid) REFERENCES Emp(eid));

(i) SELECT ename

FROM Emp
WHERE dname = 'Computer';

(ii) SELECT eid

FROM Emp
WHERE ename LIKE 'J%';

(iii) SELECT *
FROM Emp
WHERE salary BETWEEN 10000 AND 20000;

(iv) SELECT ename

FROM Emp
WHERE salary = (SELECT MAX(salary) FROM Emp);

(v) SELECT ename

FROM Emp
WHERE salary > (SELECT AVG(salary) FROM Emp);

(vi) SELECT [Link]

FROM Emp E
JOIN Project P ON [Link] = [Link]
WHERE [Link] = 3 AND [Link] = 'Mumbai';
 Question 4
A) We have following relation orders(order_id,customer_id,order_date,amount). Create table with Primary
Key, other relevant constraints in given schema.
1) Find out the number of orders for each customer by customer_id and show only customer_id with number
of orders above 5.
2) Find out the total amount by order_id and order_date.
B) Find the sum of a user-inserted number's first and last digits using PL/pgSQL function.

Answer

CREATE TABLE orders (

order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10, 2) NOT NULL);

[A] (i) SELECT customer_id, COUNT(order_id) AS number_of_orders

FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5;

(ii) SELECT order_id, order_date, SUM(amount) AS total_amount

FROM orders
GROUP BY order_id, order_date;

[B]
CREATE OR REPLACE FUNCTION sum_first_last_digits(input_number INT)
RETURNS INT AS $$
DECLARE
first_digit INT;
last_digit INT;
num INT;
BEGIN
num := input_number;
-- Find last digit
last_digit := num % 10;
-- Find first digit
WHILE num >= 10 LOOP
num := num / 10;
END LOOP;
first_digit := num;
-- Return the sum of the first and last digits
RETURN first_digit + last_digit;
END;
$$ LANGUAGE plpgsql;

Question 5
Write a query for the following.
employee(eid,name, salary, address) department(d_id, d_name, eid)
Give Primary Key, foreign key constraints after creating table with constraint names in given schemas.
(1) Create a view department_details of department table.
(2) Join two existing tables using inner join.
(3) To drop a view

Answer
 CREATE TABLE employee (
eid INT PRIMARY KEY CONSTRAINT pk_employee_eid,
name VARCHAR(100),
salary DECIMAL(10, 2),
address VARCHAR(255));

CREATE TABLE department (

d_id INT PRIMARY KEY CONSTRAINT pk_department_did,
d_name VARCHAR(100),
eid INT,
CONSTRAINT fk_department_eid FOREIGN KEY (eid) REFERENCES employee(eid));

(i) CREATE VIEW department_details AS

SELECT *
FROM department;

(ii) SELECT [Link], [Link], [Link], [Link], d.d_id, d.d_name

FROM employee e
INNER JOIN department d ON [Link] = [Link];

(iii) DROP VIEW IF EXISTS department_details;

Question 6
A) Write PL/pgSQL function to increment the employee's salary by 10% if his/her department is 'HR' for
given(inputted) employee_id.
Schema: Employee(employee_id, first_name, last_name, department, salary)
B) Write PL/pgSQL function to find the number of Sundays between given dates.

Answer

[A]
CREATE OR REPLACE FUNCTION increment_salary_if_hr(emp_id INT)
RETURNS VOID AS $$
BEGIN
-- Update salary by 10% if the employee's department is 'HR'
UPDATE Employee
SET salary = salary * 1.10
WHERE employee_id = emp_id AND department = 'HR';
END;
$$ LANGUAGE plpgsql;

[B]
CREATE OR REPLACE FUNCTION count_sundays(start_date DATE, end_date DATE)
RETURNS INT AS $$
DECLARE
sunday_count INT := 0;
current_date DATE;
BEGIN
current_date := start_date;
WHILE current_date <= end_date LOOP
IF EXTRACT(DOW FROM current_date) = 0 THEN -- 0 represents Sunday
sunday_count := sunday_count + 1;
END IF;
current_date := current_date + INTERVAL '1 day';
END LOOP;
RETURN sunday_count;
END;
$$ LANGUAGE plpgsql;
 Question 7
Write pl/pgSQL block using Explicit cursor to insert the whole tuple from Film table to Film_pay table if
amount is greater than $10.
Schema: Film (film_id, title, length, amount,rating)

Answer

DO $$
DECLARE
-- Define a record to hold each row from the cursor
film_record RECORD;
-- Declare the explicit cursor for selecting rows with amount > $10
film_cursor CURSOR FOR
SELECT film_id, title, length, amount, rating
FROM Film
WHERE amount > 10;
BEGIN
-- Open the cursor
OPEN film_cursor;
-- Loop through each row returned by the cursor
LOOP
-- Fetch the next row into the record
FETCH film_cursor INTO film_record;
-- Exit the loop if no more rows are found
EXIT WHEN NOT FOUND;
-- Insert the fetched row into the Film_pay table
INSERT INTO Film_pay (film_id, title, length, amount, rating)
VALUES (film_record.film_id, film_record.title, film_record.length,
film_record.amount, film_record.rating);
END LOOP;
-- Close the cursor
CLOSE film_cursor;
END $$;

Question 8
Calculate the total price (numeric) of all products with a product price greater than Rs. 500 using a cursor
from following
Schema: Products (p_id int, p_name varchar (100), price numeric). Display all product name and price with
total price of products.

Answer
 DO $$
DECLARE
-- Define a record to hold each row from the cursor
product_record RECORD;
-- Variable to hold the total price
total_price NUMERIC := 0;
-- Declare the explicit cursor for selecting products with price > 500
product_cursor CURSOR FOR
SELECT p_name, price
FROM Products
WHERE price > 500;
BEGIN
-- Open the cursor
OPEN product_cursor;
-- Loop through each row returned by the cursor
LOOP
-- Fetch the next row into the record
FETCH product_cursor INTO product_record;
-- Exit the loop if no more rows are found
EXIT WHEN NOT FOUND;
-- Add the current product's price to the total price
total_price := total_price + product_record.price;
-- Display the current product's name and price
RAISE NOTICE 'Product: %, Price: %', product_record.p_name, product_record.price;
END LOOP;
-- Display the total price of all products
RAISE NOTICE 'Total Price of Products with Price > 500: %', total_price;
-- Close the cursor
CLOSE product_cursor;
END $$;

Question 9
Create PL/pgSQL procedure for the increment of employees where in salary less than 35000 will get hike of
15% in their previous salary and other will get 10% hike in their previous salary. Using following schema,
Employees (id, name, department, salary) call the procedure by id and print employee's name with their
updated salary.

Answer

CREATE OR REPLACE PROCEDURE increment_salary_by_id(emp_id INT)

LANGUAGE plpgsql
AS $$
BEGIN
-- Update salary by 15% if salary is less than 35000
UPDATE Employees
SET salary = salary * 1.15
WHERE id = emp_id AND salary < 35000;
-- Update salary by 10% if salary is 35000 or more
UPDATE Employees
SET salary = salary * 1.10
WHERE id = emp_id AND salary >= 35000;
-- Print the employee's name with their updated salary
RAISE NOTICE 'Employee: %, Updated Salary: %',
(SELECT name FROM Employees WHERE id = emp_id),
(SELECT salary FROM Employees WHERE id = emp_id);
END;
$$;
 Question 10
A) Write PL/pgSQL program that calculates the factorial of a number=5 using a loop.
B) Write PL/pgSQL program to calculate the sum of first 100 even numbers.

Answer

[A]
DO $$
DECLARE
num INT := 5; -- The number for which to calculate the factorial
factorial BIGINT := 1; -- Variable to store the factorial result
BEGIN
-- Loop from 1 to the given number
FOR i IN 1..num LOOP
factorial := factorial * i; -- Multiply each value of i to the factorial
END LOOP;
-- Display the factorial result
RAISE NOTICE 'The factorial of % is %', num, factorial;
END $$;

[B]
DO $$
DECLARE
sum_even INT := 0; -- Variable to store the sum of even numbers
BEGIN
-- Loop through the first 100 even numbers
FOR i IN 1..100 LOOP
sum_even := sum_even + (i * 2); -- Add the current even number to the sum
END LOOP;
-- Display the sum of the first 100 even numbers
RAISE NOTICE 'The sum of the first 100 even numbers is %', sum_even;
END $$;

Question 11
Write a PL/pgSQL block using an explicit cursor that will transfer the record of account no, customer name
and balance from the "account" table to the "branch_surat" table if the branch name is surat in the "account"
table. Furthermore, delete the record from the "account" table whichever record transfers to the
"branch_surat" table.
Use the following tables:
account (ano, customer_name, balance, branch_name)
branch_surat (ano, customer_name, balance)
Create tables with Primary Key, foreign key constraints in given schemas.

Answer
 CREATE TABLE account (
ano INT PRIMARY KEY,
customer_name VARCHAR(100),
balance NUMERIC(10, 2),
branch_name VARCHAR(100));

CREATE TABLE branch_surat (

ano INT PRIMARY KEY,
customer_name VARCHAR(100),
balance NUMERIC(10, 2));

-- plpgsql Block
DO $$
DECLARE
-- Define a record to hold each row from the cursor
account_record RECORD;
-- Declare the explicit cursor for selecting records with branch_name = 'Surat'
account_cursor CURSOR FOR
SELECT ano, customer_name, balance
FROM account
WHERE branch_name = 'Surat';
BEGIN
-- Open the cursor
OPEN account_cursor;
-- Loop through each row returned by the cursor
LOOP
-- Fetch the next row into the record
FETCH account_cursor INTO account_record;
-- Exit the loop if no more rows are found
EXIT WHEN NOT FOUND;
-- Insert the fetched row into the branch_surat table
INSERT INTO branch_surat (ano, customer_name, balance)
VALUES (account_record.ano, account_record.customer_name, account_record.balance);
-- Delete the record from the account table
DELETE FROM account
WHERE ano = account_record.ano;
END LOOP;
-- Close the cursor
CLOSE account_cursor;
END $$;

Question 12
In PostgreSQL, create a PL/pgSQL block that defines a procedure named update_employee_salary which
takes two parameters: employee_id and new_salary. This procedure should update the salary of the
employee with the given employee_id to the new salary value. Assume "employee" table with fields - eid,
ename and salary.

Answer
 CREATE OR REPLACE PROCEDURE update_employee_salary(
employee_id INT,
new_salary NUMERIC(10, 2)
)
LANGUAGE plpgsql
AS $$
BEGIN
-- Update the salary of the employee with the given employee_id
UPDATE employee
SET salary = new_salary
WHERE eid = employee_id;
-- Optional: Raise a notice to confirm the update (for debugging or confirmation
purposes)
RAISE NOTICE 'Salary updated for employee ID %: New Salary is %', employee_id,
new_salary;
END;
$$;

CALL update_employee_salary(101, 60000);

Question 13
Consider following schema and write SQL for given statements.
title (id,designation,DOJ), bonus(id,bonus_date,amount)
Create tables with Primary Key, foreign key constraints in given schemas.
1) Retrieve the employees who haven't received any bonuses.
2) Retrieve the total bonus amount received by each employee.
3) Retrieve the highest bonus amount received.
4) List out id's whose bonus amount is at most 4000 and designation is admin.

Answer

CREATE TABLE title (

id INT PRIMARY KEY,
designation VARCHAR(100),
DOJ DATE);

CREATE TABLE bonus (

id INT,
bonus_date DATE,
amount NUMERIC(10, 2),
PRIMARY KEY (id, bonus_date),
FOREIGN KEY (id) REFERENCES title(id));

(i) SELECT [Link], [Link], [Link]

FROM title t
LEFT JOIN bonus b ON [Link] = [Link]
WHERE [Link] IS NULL;

(ii) SELECT [Link], [Link], COALESCE(SUM([Link]), 0) AS total_bonus

FROM title t
LEFT JOIN bonus b ON [Link] = [Link]
GROUP BY [Link], [Link];

(iii) SELECT MAX(amount) AS highest_bonus

FROM bonus;

(iv) SELECT DISTINCT [Link]

FROM title t
JOIN bonus b ON [Link] = [Link]
WHERE [Link] <= 4000 AND [Link] = 'admin';
 Question 14
Consider following schema and write SQL for given statements.
Student (RollNo, Name, DeptCode, City), Department (DeptCode, DeptName), Result (RollNo, Semester,
SPI)
Create tables with Primary Key, foreign key constraints in given schemas.
1) Retrieve all student names and their respective department names.
2) Retrieve the average SPI (Semester Performance Index) for each student.
3) Retrieve the students who have the highest SPI semester-wise.
4) Retrieve the students who belong to a Ahmedabad city with their department names.

Answer

CREATE TABLE Student (

RollNo INT PRIMARY KEY,
Name VARCHAR(100),
DeptCode INT,
City VARCHAR(100),
FOREIGN KEY (DeptCode) REFERENCES Department(DeptCode));

CREATE TABLE Department (

DeptCode INT PRIMARY KEY,
DeptName VARCHAR(100));

CREATE TABLE Result (

RollNo INT,
Semester INT,
SPI NUMERIC(4, 2),
PRIMARY KEY (RollNo, Semester),
FOREIGN KEY (RollNo) REFERENCES Student(RollNo));

(i) SELECT [Link], [Link]

FROM Student s
JOIN Department d ON [Link] = [Link];

(ii) SELECT [Link], [Link], AVG([Link]) AS average_SPI

FROM Student s
JOIN Result r ON [Link] = [Link]
GROUP BY [Link], [Link];

(iii) WITH MaxSPI AS (

SELECT Semester, MAX(SPI) AS Max_SPI
FROM Result
GROUP BY Semester
)
SELECT [Link], [Link], [Link], [Link]
FROM Result r
JOIN MaxSPI m ON [Link] = [Link] AND [Link] = m.Max_SPI
JOIN Student s ON [Link] = [Link];

(iv) SELECT [Link], [Link]

FROM Student s
JOIN Department d ON [Link] = [Link]
WHERE [Link] = 'Ahmedabad';
 Question 15
Consider following relations:
Supplier(S#,sname,status,city), Parts(P#,pname,color,weight,city), SP(S#,P#,quantity)
Create tables with Primary Key, foreign key constraints in given schemas.
Answer the following queries.
(1) Find name of suppliers who supply 'GREEN' parts.
(2) Count number of suppliers who supply 'RED' parts and deliver in Surat city only.
(3) Sort the supplier table by sname.
(4) List suppliers who supply parts to more than one city.

Answer

CREATE TABLE Supplier (

S# INT PRIMARY KEY,
sname VARCHAR(100),
status INT,
city VARCHAR(100));

CREATE TABLE Parts (

P# INT PRIMARY KEY,
pname VARCHAR(100),
color VARCHAR(50),
weight NUMERIC(10, 2),
city VARCHAR(100));

CREATE TABLE SP (
S# INT,
P# INT,
quantity INT,
PRIMARY KEY (S#, P#),
FOREIGN KEY (S#) REFERENCES Supplier(S#),
FOREIGN KEY (P#) REFERENCES Parts(P#));

(i) SELECT DISTINCT [Link]

FROM Supplier s
JOIN SP sp ON s.S# = sp.S#
JOIN Parts p ON sp.P# = p.P#
WHERE [Link] = 'GREEN';

(ii) SELECT COUNT(DISTINCT s.S#)

FROM Supplier s
JOIN SP sp ON s.S# = sp.S#
JOIN Parts p ON sp.P# = p.P#
WHERE [Link] = 'RED'
AND [Link] = 'Surat';

(iii) SELECT *
FROM Supplier
ORDER BY sname;

(iv) WITH SupplierCities AS (

SELECT s.S#, [Link], COUNT(DISTINCT [Link]) AS city_count
FROM Supplier s
JOIN SP sp ON s.S# = sp.S#
JOIN Parts p ON sp.P# = p.P#
GROUP BY s.S#, [Link]
)
SELECT s.S#, [Link]
FROM SupplierCities s
WHERE s.city_count > 1;
 Question 16
A) Find the sum of a user-inserted number's first and last digits using PL/pgSQL function.
B) Find out Armstrong Number from 1 to 10000 using PL/pgSQL.

Answer

[A]
CREATE OR REPLACE FUNCTION sum_first_last_digit(num INT)
RETURNS INT LANGUAGE plpgsql AS $$
DECLARE
first_digit INT;
last_digit INT;
temp_num INT;
BEGIN
-- Get the last digit
last_digit := num % 10;
-- Find the first digit
temp_num := num;
WHILE temp_num >= 10 LOOP
temp_num := temp_num / 10;
END LOOP;
first_digit := temp_num;
-- Return the sum of first and last digits
RETURN first_digit + last_digit;
END;
$$;

SELECT sum_first_last_digit(12345);

[B]
CREATE OR REPLACE FUNCTION find_armstrong_numbers()
RETURNS TABLE (armstrong_number INT) LANGUAGE plpgsql AS $$
DECLARE
num INT;
digit_count INT;
temp_num INT;
digit INT;
sum_of_powers INT;
BEGIN
FOR num IN 1..10000 LOOP
-- Initialize variables for each number
sum_of_powers := 0;
temp_num := num;
digit_count := LENGTH(temp_num::TEXT); -- Number of digits in the number
-- Calculate the sum of digits each raised to the power of digit_count
WHILE temp_num > 0 LOOP
digit := temp_num % 10;
sum_of_powers := sum_of_powers + digit ^ digit_count;
temp_num := temp_num / 10;
END LOOP;
-- Check if the number is an Armstrong number
IF sum_of_powers = num THEN
RETURN NEXT num;
END IF;
END LOOP;
END;
$$;

SELECT * FROM find_armstrong_numbers();

 Question 17
Write a PL/pgSQL block using explicit cursor considering following Schemas and perform the given task.
Add 10 rows in a data set.
Consider the following schema
employee (eid, ename, did), dep (did, dname, dmanager)
Display the department name, name of the manager, number of employees in each department

Answer
 CREATE TABLE employee (
eid INT PRIMARY KEY,
ename VARCHAR(100),
did INT);

CREATE TABLE dep (

did INT PRIMARY KEY,
dname VARCHAR(100),
dmanager VARCHAR(100));

INSERT INTO dep (did, dname, dmanager) VALUES

(1, 'HR', 'Alice'),
(2, 'IT', 'Bob'),
(3, 'Finance', 'Charlie'),
(4, 'Marketing', 'David'),
(5, 'Sales', 'Eva');

INSERT INTO employee (eid, ename, did) VALUES

(1, 'John', 1),
(2, 'Jane', 1),
(3, 'Mike', 2),
(4, 'Anna', 2),
(5, 'Paul', 2),
(6, 'Chris', 3),
(7, 'Lisa', 3),
(8, 'Tom', 4),
(9, 'Emma', 4),
(10, 'James', 5);

-- plpgsql block
DO $$
DECLARE
-- Define a record to hold data from the cursor
dept_record RECORD;
-- Declare a cursor to select department details and count employees
CURSOR dept_cursor IS
SELECT [Link], [Link], COUNT([Link]) AS num_employees
FROM dep d
LEFT JOIN employee e ON [Link] = [Link]
GROUP BY [Link], [Link];
BEGIN
-- Open the cursor
OPEN dept_cursor;
-- Fetch and display each record from the cursor
LOOP
FETCH dept_cursor INTO dept_record;
EXIT WHEN NOT FOUND; -- Exit loop when no more records
-- Display the department name, manager name, and number of employees
RAISE NOTICE 'Department: %, Manager: %, Number of Employees: %',
dept_record.dname, dept_record.dmanager, dept_record.num_employees;
END LOOP;
-- Close the cursor
CLOSE dept_cursor;
END $$;

Question 18
A) Write PL/pgSQL function that will reverse the given number and return it.
B) Write PL/pgSQL program to find the sum of digits of a number.

Answer
 [A]
CREATE OR REPLACE FUNCTION reverse_number(num INT)
RETURNS INT LANGUAGE plpgsql AS $$
DECLARE
reversed INT := 0;
digit INT;
BEGIN
-- Process the number to reverse it
WHILE num > 0 LOOP
digit := num % 10; -- Get the last digit
reversed := reversed * 10 + digit; -- Append digit to reversed number
num := num / 10; -- Remove the last digit from the original number
END LOOP;
RETURN reversed;
END;
$$;

SELECT reverse_number(12345);

[B]
CREATE OR REPLACE FUNCTION sum_of_digits(num INT)
RETURNS INT LANGUAGE plpgsql AS $$
DECLARE
sum INT := 0;
digit INT;
BEGIN
-- Process the number to find the sum of its digits
WHILE num > 0 LOOP
digit := num % 10; -- Get the last digit
sum := sum + digit; -- Add digit to sum
num := num / 10; -- Remove the last digit from the number
END LOOP;
RETURN sum;
END;
$$;

SELECT sum_of_digits(12345);

Question 19
The following tables form part of a database held in a relational DBMS:
Hotel (HotelNo, Name, City) Room (RoomNo, HotelNo, Type, Price) Booking (HotelNo, GuestNo, DateFrom,
DateTo, RoomNo) Guest (GuestNo, GuestName, GuestAddress)
Create tables with Primary Key, foreign key constraints in given schemas.
Solve following queries by SQL
1. List the price and type of all rooms at the Grosvenor Hotel.
2. List all guests currently staying at the Grosvenor Hotel.
3. How many guests have made bookings for August, 2023?
4. What is the total income from bookings for the Grosvenor Hotel?
5. Update the price of all rooms by 5%.

Answer
 CREATE TABLE Hotel (
HotelNo INT PRIMARY KEY,
Name VARCHAR(100),
City VARCHAR(100));

CREATE TABLE Room (

RoomNo INT,
HotelNo INT,
Type VARCHAR(50),
Price NUMERIC(10, 2),
PRIMARY KEY (RoomNo, HotelNo),
FOREIGN KEY (HotelNo) REFERENCES Hotel(HotelNo));

CREATE TABLE Booking (

HotelNo INT,
GuestNo INT,
DateFrom DATE,
DateTo DATE,
RoomNo INT,
PRIMARY KEY (HotelNo, GuestNo, RoomNo, DateFrom),
FOREIGN KEY (HotelNo, RoomNo) REFERENCES Room(HotelNo, RoomNo),
FOREIGN KEY (GuestNo) REFERENCES Guest(GuestNo));

CREATE TABLE Guest (

GuestNo INT PRIMARY KEY,
GuestName VARCHAR(100),
GuestAddress VARCHAR(255));

(i) SELECT [Link], [Link]

FROM Room r
JOIN Hotel h ON [Link] = [Link]
WHERE [Link] = 'Grosvenor Hotel';

(ii) SELECT [Link], [Link]

FROM Guest g
JOIN Booking b ON [Link] = [Link]
JOIN Hotel h ON [Link] = [Link]
WHERE [Link] = 'Grosvenor Hotel'
AND CURRENT_DATE BETWEEN [Link] AND [Link];

(iii) SELECT COUNT(DISTINCT [Link]) AS num_guests

FROM Booking b
WHERE EXTRACT(YEAR FROM [Link]) = 2023
AND EXTRACT(MONTH FROM [Link]) = 8;

(iv) SELECT SUM([Link]) AS total_income

FROM Booking b
JOIN Room r ON [Link] = [Link] AND [Link] = [Link]
JOIN Hotel h ON [Link] = [Link]
WHERE [Link] = 'Grosvenor Hotel'
AND [Link] <= CURRENT_DATE
AND [Link] >= CURRENT_DATE;

(v) UPDATE Room

SET Price = Price * 1.05;

Question 20
Write PL/pgSQL block for an organization that has decided to increase the salary of employees by 10% of
existing salary, who are having salary less than average salary of organization.
Schema: Employee (emp_id, emp_name, salary, incremented_salary).

Answer
 CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
salary NUMERIC(10, 2),
incremented_salary NUMERIC(10, 2));

DO $$
DECLARE
avg_salary NUMERIC(10, 2);
BEGIN
-- Calculate the average salary
SELECT AVG(salary) INTO avg_salary
FROM Employee;
-- Update the salary for employees earning less than the average salary
UPDATE Employee
SET incremented_salary = salary * 1.10
WHERE salary < avg_salary;
-- Optional: Print a message to indicate completion
RAISE NOTICE 'Salaries have been updated for employees earning less than the average
salary.';
END $$;

Question 21
You are responsible for maintaining a database for a shipping company. Write a PL/pgSQL Stored
Procedure to insert data in status_logs. Let name of Procedure be 'update_shipment_status'
Consider relation: status_logs(shipment_id, new_status, update_time)
Insert below data with the help of Procedure you created shipment_id=123, new_status=delivered,
update_time=2023-09-15 10:30:00.

Answer

CREATE TABLE status_logs (

shipment_id INT,
new_status VARCHAR(50),
update_time TIMESTAMP);

-- plpgsql Block
CREATE OR REPLACE PROCEDURE update_shipment_status(
p_shipment_id INT,
p_new_status VARCHAR,
p_update_time TIMESTAMP
)
LANGUAGE plpgsql
AS $$
BEGIN
-- Insert data into the status_logs table
INSERT INTO status_logs (shipment_id, new_status, update_time)
VALUES (p_shipment_id, p_new_status, p_update_time);
-- Optional: Print a message indicating the insert was successful
RAISE NOTICE 'Shipment status updated: ID = %, Status = %, Time = %',
p_shipment_id, p_new_status, p_update_time;
END;
$$;

CALL update_shipment_status(123, 'delivered', '2023-09-15 10:30:00');