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:
- 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 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.