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