Consider a banking database with three labels and primary key underlined as given below: Customer (CustomerID , CustomerName, Address, Phone, Email) Borrows (CustomerID, LoanNumber ) Loan (…

Relations: Customer(CustomerID, CustomerName, Address, Phone, Email), Borrows(CustomerID, LoanNumber), Loan(LoanNumber, LoanType, Amount).

1. Names of customers who live in "Lalitpur", in ascending order of name

Relational algebra (ordering is not part of basic relational algebra, so it is applied in SQL):

π CustomerName (σ Address = 'Lalitpur' (Customer))

SELECT CustomerName
FROM Customer
WHERE Address = 'Lalitpur'
ORDER BY CustomerName ASC;

2. Total number of customers having a loan

Relational algebra:

𝒢 count(CustomerID) (π CustomerID (Borrows))

SELECT COUNT(DISTINCT CustomerID) AS total_customers
FROM Borrows;

DISTINCT counts a customer with several loans only once.

3. Names of customers whose loan amount is at least 500000

Relational algebra:

π CustomerName (σ Amount ≥ 500000 (Customer ⋈ Borrows ⋈ Loan))

SELECT DISTINCT C.CustomerName
FROM Customer C
JOIN Borrows B ON B.CustomerID = C.CustomerID
JOIN Loan L    ON L.LoanNumber = B.LoanNumber
WHERE L.Amount >= 500000;

4. Average loan amount of each loan type

Relational algebra:

LoanType 𝒢 avg(Amount) (Loan)

SELECT LoanType, AVG(Amount) AS average_amount
FROM Loan
GROUP BY LoanType;