Completely Solved C, C++ Programs Assignment.




SQL-Query performs on a Banking Database system.

Filed Under: ,

Problem Statement: - Query performs on a Banking Database system.

Consider the following employee database:-
I) Account (Branch_name, Account_number, Balance)
ii)Branch(Branch_name, Branch_city, Assets)
iii)Customer(Cust_name, Cust_street, Cust_city)
iv)Depositor(Cust_name, Account_number)
v)Loan(Branch_name, Loan_number, Amount)
vi)Borrower(Cust_name, Loan_number)

Given queries:-
1.Find all loan number for loans made at the Perryridge branch with loan amounts greater than
$1200.
2.Find the names and loan numbers of all customers who have a loan at the Perryridge branch.
3.Find the names of all customers whose street address includes the substring ‘Main’.
4.Find the average balance of each customer who lives in Harrison and has at least 3 accounts.
5.Find all customers who have both an account and a loan at the Perryridge branch.
6.Find the names of all branches that have assets greater than that of each branch in Brooklyn.
7.Find the branch that has the higher average balance.

Commands for table creations of the schemas in:-
i) SQL> create table Account
2 ( branch_name varchar2(15) NOT NULL,
3 account_number varchar2(10),
4 balance number(8),
5 PRIMARY KEY( account_number),
6 CHECK ( balance >= 0 ) );
Table Created.

Name Null? Type
----------------------------------------- --------
BRANCH_NAME NOT NULL VARCHAR2(15)
ACCOUNT_NUMBER NOT NULL VARCHAR2(10)
BALANCE NUMBER(8)

SQL> INSERT INTO Account VALUES (‘downtown’ , ‘a-101’ , 500 );
1 row created.
SQL> INSERT INTO Account VALUES (‘mianus’ , ‘a-215’ , 700 );
1 row created.
SQL> INSERT INTO Account VALUES (‘perryridge’ , ‘a-102’ , 400 );
1 row created.
SQL> INSERT INTO Account VALUES (‘roundhill’ , ‘a-305’ , 350 );
1 row created.
SQL> INSERT INTO Account VALUES (‘brighton’ , ‘a-201’ , 900 );
1 row created.
SQL> INSERT INTO Account VALUES (‘redwood’ , ‘a-222’ , 700 );
1 row created.
SQL> INSERT INTO Account VALUES (‘brighton’ , ‘a-217’ , 750 );
1 row created.

BRANCH_NAME ACCOUNT_NU BALANCE
--------------- ---------- --------------- -----------------
downtown a-101 500
mianus a-215 700
perryridge a-102 400
roundhill a-305 350
brighton a-201 900
redwood a-222 700
brighton a-217 750

ii) SQL> create table Branch
2 ( branch_name varchar2(15) NOT NULL,
3 branch_city varchar2(30),
4 assets number(8),
5 PRIMARY KEY(branch_name),
6 CHECK (assets >= 0) );
Table Created.

Name Null? Type
----------------------------------------- -------- ------------
BRANCH_NAME NOT NULL VARCHAR2(15)
BRANCH_CITY VARCHAR2(30)
ASSETS NUMBER(8)

SQL> INSERT INTO Branch VALUES (‘downtown’ , ‘brooklyn’ , 900000 );
1 row created.
SQL> INSERT INTO Branch VALUES (‘redwood’ , ‘palo alto’ , 210000 );
1 row created.
SQL> INSERT INTO Branch VALUES (‘perryridge’ , ‘horseneck’ , 170000 );
1 row created.
SQL> INSERT INTO Branch VALUES (‘mianus’ , ‘horseneck’ , 40000 );
1 row created.
SQL> INSERT INTO Branch VALUES (‘roundhill’ , ‘horseneck’ , 800000 );
1 row created.
SQL> INSERT INTO Branch VALUES (‘pownal’ , ‘bennignton’ , 30000 );
1 row created.
SQL> INSERT INTO Branch VALUES (‘north town’ , ‘rye’ , 370000);
1 row created.
SQL> INSERT INTO Branch VALUES (‘brighton’ , ‘brooklyn’ , 710000 );
1 row created.
BRANCH_NAME BRANCH_CITY ASSETS
--------------- ------------------------------ ----------
downtown brooklyn 900000
redwood palo alto 210000
perrybridge horseneck 170000
mianus horseneck 40000
perryridge horseneck 170000
roundhill horseneck 800000
pownal bennignton 30000
north town rye 370000
brighton brooklyn 710000


iii) SQL> create table Customer
2 ( cust_name varchar2(20) NOT NULL,
3 cust_street varchar2(30),
4 cust_city varchar2(30),
5 PRIMARY KEY(cust_name) );
Table Created.

Name Null? Type
----------------------------------------- -------- --------------
CUST_NAME NOT NULL VARCHAR2(20)
CUST_STREET VARCHAR2(30)
CUST_CITY VARCHAR2(30)

SQL> INSERT INTO Customer VALUES (‘jones’ , ‘main’ , ‘harrison’ );
1 row created.
SQL> INSERT INTO Customer VALUES (‘smith’ , ‘north’ , ‘rye’ );
1 row created.
SQL> INSERT INTO Customer VALUES (‘hayes’ , ‘main’ , ‘harrison’ );
1 row created.
SQL> INSERT INTO Customer VALUES (‘curry’ , ‘north’ , ‘rye’ );
1 row created.
SQL> INSERT INTO Customer VALUES (‘lindsay’ , ‘park’ , ‘pittsfield’ );
1 row created.
SQL> INSERT INTO Customer VALUES (‘turner’ , ‘putnam’ , ‘stamford’ );
1 row created.
SQL> INSERT INTO Customer VALUES (‘williams’ , ‘nassau’ , ‘princeton’ );
1 row created.
SQL> INSERT INTO Customer VALUES (‘adams’ , ‘spring’ , ‘pittsfield’ );
1 row created.
SQL> INSERT INTO Customer VALUES (‘johnson’ , ‘alma’ , ‘palo alto’ );
1 row created.
SQL> INSERT INTO Customer VALUES (‘glenn’ , ‘sand hill’ , ‘wood side’ );
1 row created.
SQL> INSERT INTO Customer VALUES (‘brooks’ , ‘senator’ , ‘brooklyn’ );
1 row created.
SQL> INSERT INTO Customer VALUES (‘green’ , ‘walnut’ , ‘stamford’ );
1 row created.

CUST_NAME CUST_STR
-------------------- --------
CUST_CITY
-----------------------------
jones main
harrison

smith north
rye

hayes main
harrison

CUST_NAME CUST_STR
-------------------- --------
CUST_CITY
-----------------------------
curry north
rye

lindsay park
pittsfield

turner putnam
stamford

CUST_NAME CUST_STR
-------------------- --------
CUST_CITY
-----------------------------
williams nassau
princeton

adams spring
pittsfield

johnson alma
palo alto

CUST_NAME CUST_STR
-------------------- --------
CUST_CITY
-----------------------------
glenn sand hil
wood side

brooks senator
brooklyn

green walnut
stamford

iv) SQL> create table Depositor
2 ( cust_name varchar2(20) NOT NULL,
3 account_number varchar2(10) NOT NULL,
4 PRIMARY KEY ( cust_name, account_number ));
Table Created.

Name Null? Type
----------------------------------------- -------- -------------
CUST_NAME VARCHAR2(20)
ACCOUNT_NUMBER NOT NULL VARCHAR2(10)

SQL> INSERT INTO Depositor VALUES (‘johnson’ , ‘a-101’ );
1 row created.
SQL> INSERT INTO Depositor VALUES (‘smith’ , ‘a-215’ );
1 row created.
SQL> INSERT INTO Depositor VALUES (‘hayes’ , ‘a-102’ );
1 row created.
SQL> INSERT INTO Depositor VALUES (‘turner’ , ‘a-305’ );
1 row created.
SQL> INSERT INTO Depositor VALUES (‘johnson’ , ‘a-201’ );
1 row created.
SQL> INSERT INTO Depositor VALUES (‘jones’ , ‘a-217’ );
1 row created.
SQL> INSERT INTO Depositor VALUES (‘lindsay’ , ‘a-222’ );
1 row created.


CUST_NAME ACCOUNT_NU
-------------------- ----------
johnson a-101
smith a-215
hayes a-102
turner a-305
johnson a-201
jones a-217
lindsay a-222

v) SQL> create table Loan
2 ( branch_name varchar2(15) NOT NULL,
3 loan_number varchar2(5),
4 amount number(8),
5 PRIMARY KEY ( loan_number),
6 CHECK ( amount >= 0 ) );
Table Created.

Name Null? Type
----------------------------------------- -------- -------------
BRANCH_NAME NOT NULL VARCHAR2(15)
LOAN_NUMBER VARCHAR2(5)
AMOUNT NUMBER(8)

SQL> INSERT INTO Loan VALUES (‘downtown’ , ‘L-17’ , 1000 );
1 row created.
SQL> INSERT INTO Loan VALUES (‘redwood’ , ‘L -23’ , 2000 );
1 row created.
SQL> INSERT INTO Loan VALUES (‘perryridge’ , ‘L-15’ , 1500 );
1 row created.
SQL> INSERT INTO Loan VALUES (‘downtown’ , ‘L-14’ , 1500 );
1 row created.
SQL> INSERT INTO Loan VALUES (‘mianus’ , ‘L-93’ , 500 );
1 row created.
SQL> INSERT INTO Loan VALUES (‘roundhill’ , ‘L-11’ , 900 );
1 row created.
SQL> INSERT INTO Loan VALUES (‘perryridge’ , ‘L-16’ , 1300 );
1 row created.


BRANCH_NAME LOAN_ AMOUNT
--------------- ----- ----------
downtown l-17 1000
redwood l-23 2000
perryridge l-15 1500
mianus l-93 500
round hill l-11 900


vi) SQL> create table Borrower
2 ( cust_name varchar2(20) NOT NULL,
3 loan_number varchar2(5) NOT NULL,
4 PRIMARY KEY ( cust_name, loan_number) );
Table Created.

Name Null? Type
----------------------------------------- -------- -------------
CUST_NAME NOT NULL VARCHAR2(20)
LOAN_NUMBER NOT NULL VARCHAR2(5)

SQL> INSERT INTO Borrower VALUES (‘jones’ , ‘L-17’ );
1 row created.
SQL> INSERT INTO Borrower VALUES (‘smith’ , ‘L-23’ );
1 row created.
SQL> INSERT INTO Borrower VALUES (‘hayes’ , ‘L-15’ );
1 row created.
SQL> INSERT INTO Borrower VALUES (‘jackson’ , ‘L-14’ );
1 row created.
SQL> INSERT INTO Borrower VALUES (‘curry’ , ‘L-93’ );
1 row created.
SQL> INSERT INTO Borrower VALUES (‘smith’ , ‘L-11’ );
1 row created.
SQL> INSERT INTO Borrower VALUES (‘williams’ , ‘L-16’ );
1 row created.
SQL> INSERT INTO Borrower VALUES (‘adams’ , ‘L-16’ );
1 row created.

CUST_NAME LOAN_
-------------------- -----
jones L-17
smith L-23
hayes L-15
jackson L-14
curry L-93
smith L-11
williams L-16
adams L-16


SOLUTION OF THE QUERIES:-
Query Number 1 :
Find all loan number for loans made at the Perryridge branch with loan amounts greater than
$1200.

SQL Statement
SQL> select loan_number
2 from Loan
3 where branch_name=’perryridge’ and amount > 1200;

Output to Query number 1

LOAN_NUMBER
------------------------
L-15
L-16


Query Number 2 :
Find the names and loan numbers of all customers who have a loan at the Perryridge branch.

SQL Statement
SQL> select distinct cust_name,borrower.loan_number
2 from Borrower,Loan
3 where Borrower.loan_number=Loan.loan_number and branch_name=’perryridge’;

Output to Query number 2

CUST-NAME LOAN_NUMBER
----------------- -----------------------
hayes L-15
adams L-16


Query Number 3 :
Find the names of all customers whose street address includes the substring ‘Main’.

SQL Statement
SQL> select cust_name
2 from Customer
3 where cust_street like ‘%main%’;

Output to Query number 3

CUST_NAME
-------------------
jones
hayes


Query Number 4 :
Find the average balance of each customer who lives in Harrison and has at least 3 accounts.

SQL Statement
SQL> select Depositor.cust_name,avg(balance)
2 from Depositor,Account,Customer
3 where Depositor.account_number=Account.account_number and
4 Depositor.cust_name=Customer.cust_name and
5 cust_city=’harrison’
6 group by Depositor.cust_name
7 having count(distinct Depositor.account_number) >= 3;


Output to Query number 4

CUST_NAME AVG(BALANCE)
----------------- --------------------
hayes 400


Query Number 5 :
Find the average balance of each customer who lives in Harrison and has at least 3 accounts.

SQL Statement
SQL> select distinct cust_name
2 from Borrower b,Loan l
3 where b.loan_number=l.loan_number and
4 branch_name=’perryridge’ and
5 (branch_name,cust_name) in
6 ( select branch_name,cust_name
7 from Depositor d,Account a
8 where d.account_name=a.account_number);


Output to Query number 5

CUST_NAME
------------------
hayes

Query Number 6 :
Find the names of all branches that have assets greater than that of each branch in Brooklyn.

SQL Statement
SQL> select branch_name
2 from Branch
3 where assets > all(select assets
4 from Branch
5 where branch_city=’brooklyn’);

Output to Query number 6

BRANCH_NAME
----------------------
roundhill

Query Number 7 :
Find the branch that has the higher average balance.

SQL Statement
SQL> select branch_name
2 from Account
3 group by branch_name
4 having avg(balance) >= all( select avg(balance)
5 from Account
6 group by branch_name);

Output to Query number 7

BRANCH_NAME
-----------------------
brighton


DISCUSSIONS:-
The above database created is used to find out different information about a bank’s accounts, customers, branches, loans, borrowers and depositors.
We have solved some given queries using this database, some of which have alternative methods.
Example:-
Query no. 2 as written above to find the names and loan numbers of all customers who have a loan at the Perryridge branch can also be done by searching out for the loan number from the loan numbers in table loan where branch name will be perryridge.
We can write this as follows:-
“select cust_name,loan_number from borrower where loan_number in(select loan_number from loan where branch_name='perryridge');”
This would also give us the same output we desire.
 Back to main directory: Software Practical | Structured Query Language | DBMS


Get Free Programming Tutorials and Solved assignments