Database Management System
LAB REPORT
SQL Queries on Bank Database Relations
SQL Query Practice using Account, Branch, Customer,
Experiment Name
Depositor, Loan and Borrower Relations
Course Title Database Management System
Name: __________________________ ID:
Submitted By
__________________________
Submitted To ____________________________________________
Department ____________________________________________
Date 09/06/2026
Prepared as a complete lab report with corrected SQL queries and verified outputs.
Database SQL Lab Report
1. Introduction
This lab report is based on a bank database containing six relations: account, branch, customer, depositor, loan and
borrower. The main task is to retrieve required information by using SQL selection, projection, join, aggregate functions
and set operations.
2. Objectives
• To understand how data is stored in relational tables.
• To write SQL queries using WHERE, LIKE, JOIN, GROUP BY, AVG, COUNT, EXCEPT and INTERSECT.
• To retrieve accurate output from multiple related tables.
• To practice branch-wise, customer-wise and loan-wise query operations.
3. Relational Schema
Relation Attributes
account account_number, branch_name, balance
branch branch_name, branch_city, assets
customer customer_name, customer_street, customer_city
depositor customer_name, account_number
loan loan_number, branch_name, amount
borrower customer_name, loan_number
Assumption: For SQL compatibility, hyphenated column names from the textbook image are written with underscores,
such as account_number, branch_name and customer_name. The first problem is solved using the given loan table
range 900 to 1000.
4. Input Relations / Data Tables
4.1 Account Relation
account_number branch_name balance
A-101 Downtown 500
A-102 Perryridge 400
A-201 Brighton 900
A-215 Mianus 700
A-217 Brighton 750
A-222 Redwood 700
A-305 Round Hill 350
4.2 Branch Relation
branch_name branch_city assets
Brighton Brooklyn 7100000
Downtown Brooklyn 9000000
Mianus Horseneck 400000
North Town Rye 3700000
Database SQL Lab Report
Perryridge Horseneck 1700000
Pownal Bennington 300000
Redwood Palo Alto 2100000
Round Hill Horseneck 8000000
4.3 Customer Relation
customer_name customer_street customer_city
Adams Spring Pittsfield
Brooks Senator Brooklyn
Curry North Rye
Glenn Sand Hill Woodside
Green Walnut Stamford
Hayes Main Harrison
Johnson Alma Palo Alto
Jones Main Harrison
Lindsay Park Pittsfield
Smith North Rye
Turner Putnam Stamford
Williams Nassau Princeton
4.4 Depositor Relation
customer_name account_number
Hayes A-102
Johnson A-101
Johnson A-201
Jones A-217
Lindsay A-222
Smith A-215
Turner A-305
4.5 Loan Relation
loan_number branch_name amount
L-11 Round Hill 900
L-14 Downtown 1500
L-15 Perryridge 1500
L-16 Perryridge 1300
L-17 Downtown 1000
L-23 Redwood 2000
L-93 Mianus 500
4.6 Borrower Relation
customer_name loan_number
Adams L-16
Curry L-93
Hayes L-15
Jackson L-14
Jones L-17
Smith L-11
Smith L-23
Williams L-17
Database SQL Lab Report
5. SQL Query Solutions and Outputs
1. Find the name of customers who have loans with loan amounts between 900 Tk and 1000
Tk.
SQL Query:
SELECT DISTINCT b.customer_name
FROM borrower AS b
JOIN loan AS l
ON b.loan_number = l.loan_number
WHERE [Link] BETWEEN 900 AND 1000
ORDER BY b.customer_name;
Output:
customer_name
Jones
Smith
Williams
2. Find all loan numbers for loans made at the Main Branch with loan amounts greater than
1200 Tk.
SQL Query:
SELECT loan_number
FROM loan
WHERE branch_name = 'Main Branch'
AND amount > 1200
ORDER BY loan_number;
Output:
loan_number
No rows returned
Note: No rows are returned because there is no matching loan for Main Branch with amount greater than 1200 in the
given loan relation.
Database SQL Lab Report
3. Find the names of all customers whose street address includes the substring Main.
SQL Query:
SELECT customer_name
FROM customer
WHERE customer_street LIKE '%Main%'
ORDER BY customer_name;
Output:
customer_name
Hayes
Jones
4. For all customers who have a loan from the bank, find their names, loan numbers and loan
amount.
SQL Query:
SELECT b.customer_name, b.loan_number, [Link]
FROM borrower AS b
JOIN loan AS l
ON b.loan_number = l.loan_number
ORDER BY b.customer_name, b.loan_number;
Output:
customer_name loan_number amount
Adams L-16 1300
Curry L-93 500
Hayes L-15 1500
Jackson L-14 1500
Jones L-17 1000
Smith L-11 900
Smith L-23 2000
Williams L-17 1000
Database SQL Lab Report
5. Find the average account balance at each branch.
SQL Query:
SELECT branch_name, AVG(balance) AS average_balance
FROM account
GROUP BY branch_name
ORDER BY branch_name;
Output:
branch_name average_balance
Brighton 825
Downtown 500
Mianus 700
Perryridge 400
Redwood 700
Round Hill 350
Database SQL Lab Report
6. Find the number of depositors for each branch.
SQL Query:
SELECT a.branch_name,
COUNT(DISTINCT d.customer_name) AS number_of_depositors
FROM account AS a
JOIN depositor AS d
ON a.account_number = d.account_number
GROUP BY a.branch_name
ORDER BY a.branch_name;
Output:
branch_name number_of_depositors
Brighton 2
Downtown 1
Mianus 1
Perryridge 1
Redwood 1
Round Hill 1
7. Find all customers who have an account but no loan at the bank.
SQL Query:
SELECT DISTINCT customer_name
FROM depositor
EXCEPT
SELECT DISTINCT customer_name
FROM borrower
ORDER BY customer_name;
Output:
customer_name
Johnson
Lindsay
Turner
Database SQL Lab Report
8. Find all customers who have both a loan and an account at the bank.
SQL Query:
SELECT DISTINCT customer_name
FROM depositor
INTERSECT
SELECT DISTINCT customer_name
FROM borrower
ORDER BY customer_name;
Output:
customer_name
Hayes
Jones
Smith
Database SQL Lab Report
9. Find the names of all branches with customers who have an account in the bank and who
live in Harrison.
SQL Query:
SELECT DISTINCT a.branch_name
FROM account AS a
JOIN depositor AS d
ON a.account_number = d.account_number
JOIN customer AS c
ON d.customer_name = c.customer_name
WHERE c.customer_city = 'Harrison'
ORDER BY a.branch_name;
Output:
branch_name
Brighton
Perryridge
10. Find the names of all branches that have assets greater than those of at least one branch
located in Brooklyn.
SQL Query:
SELECT DISTINCT t.branch_name
FROM branch AS t
JOIN branch AS b
ON [Link] > [Link]
WHERE b.branch_city = 'Brooklyn'
ORDER BY t.branch_name;
Output:
branch_name
Downtown
Round Hill
Database SQL Lab Report
6. Table Creation Commands
The following commands may be used to create the required relations before inserting the data.
CREATE TABLE account (
account_number VARCHAR(10),
branch_name VARCHAR(30),
balance INT
);
CREATE TABLE branch (
branch_name VARCHAR(30),
branch_city VARCHAR(30),
assets INT
);
CREATE TABLE customer (
customer_name VARCHAR(30),
customer_street VARCHAR(30),
customer_city VARCHAR(30)
);
CREATE TABLE depositor (
customer_name VARCHAR(30),
account_number VARCHAR(10)
);
CREATE TABLE loan (
loan_number VARCHAR(10),
branch_name VARCHAR(30),
amount INT
);
CREATE TABLE borrower (
customer_name VARCHAR(30),
loan_number VARCHAR(10)
);
7. Conclusion
In this experiment, the required SQL queries were written and checked using the given bank database relations. The
queries demonstrate filtering, pattern matching, joins, grouping, aggregate functions and set operations. The outputs
show the correct results based on the provided account, branch, customer, depositor, loan and borrower tables.
Database SQL Lab Report