BANKING TRAINING SCHEMA – DESIGN OVERVIEW

By /

We will use one common schema:

Schema Owner: BANKING
Tablespace  : TRAIN_DATA_TS
Access      : READ ONLY for trainees

user will:

  • Query data
  • Write joins, subqueries, analytics
  • Write PL/SQL on their own schema, not this one

📐 ENTITY DESIGN (CORE BANKING)

BRANCHES
   |
CUSTOMERS
   |
ACCOUNTS
   |
TRANSACTIONS
   |
LOANS
   |
PAYMENTS

Plus:

  • USERS (for login simulation)
  • AUDIT_LOG (for triggers practice)

🔹 STEP 1: Create BANKING Schema (DBA / Trainer)

CREATE USER banking
IDENTIFIED BY banking
DEFAULT TABLESPACE train_data_ts
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON train_data_ts;

GRANT create session, create table, create sequence TO banking;

🔹 STEP 2: Core Master Tables

1️⃣ BRANCHES

CREATE TABLE banking.branches (
  branch_id     NUMBER PRIMARY KEY,
  branch_name   VARCHAR2(100),
  city          VARCHAR2(50),
  state         VARCHAR2(50)
);

2️⃣ CUSTOMERS

CREATE TABLE banking.customers (
  customer_id   NUMBER PRIMARY KEY,
  first_name    VARCHAR2(50),
  last_name     VARCHAR2(50),
  dob           DATE,
  mobile_no     VARCHAR2(15),
  email         VARCHAR2(100),
  city          VARCHAR2(50),
  created_date  DATE DEFAULT SYSDATE
);

3️⃣ ACCOUNTS

CREATE TABLE banking.accounts (
  account_id     NUMBER PRIMARY KEY,
  customer_id    NUMBER REFERENCES banking.customers(customer_id),
  branch_id      NUMBER REFERENCES banking.branches(branch_id),
  account_type   VARCHAR2(20),
  balance        NUMBER(15,2),
  status         VARCHAR2(20),
  opened_date    DATE
);

🔹 STEP 3: Transaction Tables (Very Important)

4️⃣ TRANSACTIONS

CREATE TABLE banking.transactions (
  txn_id        NUMBER PRIMARY KEY,
  account_id    NUMBER REFERENCES banking.accounts(account_id),
  txn_date      DATE,
  txn_type      VARCHAR2(10), -- CREDIT / DEBIT
  amount        NUMBER(12,2),
  description   VARCHAR2(200)
);

5️⃣ LOANS

CREATE TABLE banking.loans (
  loan_id       NUMBER PRIMARY KEY,
  customer_id   NUMBER REFERENCES banking.customers(customer_id),
  loan_type     VARCHAR2(30),
  loan_amount   NUMBER(15,2),
  interest_rate NUMBER(5,2),
  start_date    DATE,
  status        VARCHAR2(20)
);

6️⃣ PAYMENTS

CREATE TABLE banking.loan_payments (
  payment_id    NUMBER PRIMARY KEY,
  loan_id       NUMBER REFERENCES banking.loans(loan_id),
  payment_date  DATE,
  amount_paid   NUMBER(12,2),
  payment_mode  VARCHAR2(20)
);

🔹 STEP 4: Sequences (Production Style)

CREATE SEQUENCE banking.branch_seq START WITH 1;
CREATE SEQUENCE banking.customer_seq START WITH 1001;
CREATE SEQUENCE banking.account_seq START WITH 1000001;
CREATE SEQUENCE banking.txn_seq START WITH 1;
CREATE SEQUENCE banking.loan_seq START WITH 50001;
CREATE SEQUENCE banking.payment_seq START WITH 1;

🔹 STEP 5: Sample Data

INSERT INTO banking.branches VALUES (1, 'Main Branch', 'Mumbai', 'MH');
INSERT INTO banking.branches VALUES (2, 'IT Park Branch', 'Bangalore', 'KA');

INSERT INTO banking.customers VALUES
(1001, 'Rahul', 'Sharma', DATE '1990-05-10', '9999999999', 'rahul@gmail.com', 'Mumbai', SYSDATE);

INSERT INTO banking.accounts VALUES
(1000001, 1001, 1, 'SAVINGS', 50000, 'ACTIVE', SYSDATE);

INSERT INTO banking.transactions VALUES
(1, 1000001, SYSDATE, 'CREDIT', 10000, 'Salary');

COMMIT;

🔹 STEP 6: Grant Access to Trainees (READ ONLY)

GRANT SELECT ON banking.branches     TO trainee_role;
GRANT SELECT ON banking.customers    TO trainee_role;
GRANT SELECT ON banking.accounts     TO trainee_role;
GRANT SELECT ON banking.transactions TO trainee_role;
GRANT SELECT ON banking.loans        TO trainee_role;
GRANT SELECT ON banking.loan_payments TO trainee_role;

🔹 STEP 7: Advanced (Optional – PRO LEVEL)

🔐 Audit Table for Trigger Practice

CREATE TABLE banking.audit_log (
  log_id     NUMBER PRIMARY KEY,
  table_name VARCHAR2(30),
  action     VARCHAR2(10),
  action_date DATE,
  username   VARCHAR2(30)
);

Developer can write triggers in their schema referencing this idea.

🏦 ORACLE SQL PRACTICE QUESTIONS

Schema Used: BANKING
Target: SQL mastery for Developer (Practice + Interview)

🔹 LEVEL 1: BASIC SQL (Warm-Up) – 1️⃣ to 1️⃣2️⃣

  1. Display all branches available in the bank.
  2. List all customers with their first name, last name, and city.
  3. Show customer details who belong to Mumbai city.
  4. Display all accounts with balance greater than 25,000.
  5. Show all ACTIVE accounts.
  6. Display unique account types available in the bank.
  7. List all transactions done today.
  8. Show transactions with amount greater than 5,000.
  9. Display customer IDs who have opened accounts.
  10. Show total number of customers in the bank.
  11. Display customer names in uppercase.
  12. Show current system date and time.

🔹 LEVEL 2: JOINS & RELATIONSHIPS – 1️⃣3️⃣ to 2️⃣2️⃣

  1. Display customer name along with their account number.
  2. Display customer name, account type, and balance.
  3. Show branch name where each account is opened.
  4. Display all customers who do not have any account.
  5. Display customers and their total number of accounts.
  6. Display account number and total transaction amount.
  7. Display customer name, transaction type, and amount.
  8. Display branches where at least one account exists.
  9. Display customers who have done DEBIT transactions.
  10. Display account details where no transactions exist.

🔹 LEVEL 3: AGGREGATES & GROUP BY – 2️⃣3️⃣ to 3️⃣0️⃣

  1. Display total balance available in the bank.
  2. Display branch-wise total account balance.
  3. Display average balance per account type.
  4. Display customer-wise total transaction amount.
  5. Display total number of transactions per account.
  6. Display branches having total balance greater than 50,000.
  7. Display account types having more than 1 account.
  8. Display the maximum transaction amount per account.

🔹 LEVEL 4: SUBQUERIES – 3️⃣1️⃣ to 3️⃣6️⃣

  1. Display customers who have balance greater than the average balance.
  2. Display accounts having maximum balance.
  3. Display customers who have never done any transaction.
  4. Display accounts where total debit amount is greater than total credit amount.
  5. Display customers who have taken loans.
  6. Display customers who have more than one account.

🔹 LEVEL 5: ADVANCED SQL (Analytics / Real-World) – 3️⃣7️⃣ to 4️⃣0️⃣

  1. Display top 3 accounts based on highest balance.
  2. Rank customers based on total account balance.
  3. Display each transaction with running balance per account.
  4. Display customer name and last transaction date.

🧠 INTERVIEW-FOCUSED CHALLENGE QUESTIONS

  1. Find customers whose first transaction was a DEBIT.
  2. Find accounts that became zero balance after transactions.
  3. Detect possible fraud: transactions above average transaction amount of that account.
  4. Display customers with inactive accounts but active loans.
  5. Identify branches with no customers from Mumbai.
Scroll to Top