SQL – Create Banking Software

In this project, we need to create a Banking Software using SQL. As input we will get the business requirement and the data, and we need to implement those business requirements SQL, or Structured Query Language, is a powerful programming language that can be used to create banking software. SQL is designed specifically for managing data in databases, and it offers a number of advantages over other programming languages.

What will you Learn in the SQL – Create Banking Software?

This project will cover a lot of database operations, starting from basic table creation to:

1. How to insert data in the table and how to implement different constraints
2. How to join two tables
3. How to create views
4. How to use subqueries
5. How to extensively use Select
6. How to create an index
7. How to use various built-in functions and many more…

Tools Used

Server – Oracle Database Server 21C

Client – PL/SQL Developer tool

Tasks Performed

As part of this project, we will be performing the following tasks:

Task-1: Create ER diagram

Task-2: Using ER diagram, create the tables in the Oracle database for the given data taking into consideration all given constraints

Task-3: Create Btree indexes on customer_loans(branch_id), customer_loans(cust_id)

Task-4: Create bitmap index on account(acc_type)

Task-5: Create Public Synonym Cust_loan_branch_map for the table Customer_loans

Task-6: Create a sequence Customer_seq_id for audit purposes, such that it’s incremented by 1, starts with 9907 and ends with 100001, cycles back to the initial value, and caches 20 values

Task-7: Create a view for retrieving cust_id, name, phone, and their account types, account number, and balances

Task-8: Create a materialized view for retrieving branch_id, branch_name along with loan_ids for the branches

Task-9: Display customer names and length of characters in their names

Task-10: For each branch_id get the number of customers it maintains and the number of loans disbursed

Task-11: Get the list of customer details who don’t have branch_id assigned

Task-12: Get the list of customer details who don t have loan_id assigned

Task-13: Display the result of FULL OUTER JOIN of customer loans table with loans table

Task-14: Display the result of the Branch table RIGHT OUTER JOIN with the customer loans table

Task-15: Calculate the sum of balances for each customer id, irrespective of the account types

Task-16: For customers opting for the Home Loans, display the salary account balance, if no salary account, then display “Salary Account Not Opened” for that respective customer_ids.

Task-17: Display the customer’s name, phone number, and address for the customer holding the maximum balance among all the customers

Task-18: For each branch name, display the count of the number of customers, only those branches having more than 1 customer.