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.
Course
Curriculum

What will you Learn in the Project?

This project will cover 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 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

+2 enrolled
Not Enrolled
or

Skills you will develop

Implement ER diagram

Constraint implementation

Views, Synonym, Sequence

Built-in functions

Joins

Subqueries

Share with Friends and earn points!!