Project ,

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. Hint videos, Q&A and step-by-step solution will also be available.

View Project details

What Will You Learn in the Project?

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

How to insert data in the table and how to implement different constraints
How to join two tables
How to create views
How to use subqueries
How to extensively use Select
How to create an index
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 following tasks:

Task-1: Create ER diagram

Task-2: Using ER diagram, create the tables in 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 purpose, such that it’s incremented by 1, starts with 9907 and ends with 100001, cycles back to 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 Branch table RIGHT OUTER JOIN with customer loans table

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

Task-16: For customers opting 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 maximum balance among all the customers

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