🏦 Oracle SQL for Core Banking Systems (CBS)

Role Perspective: Senior Oracle Database Architect
Target Role: Banking SQL / PL/SQL Developer
Focus: Production safety, compliance, performance, auditability

---

🔷 BIG PICTURE (How SQL is used in Banking)

In a Core Banking System, Oracle SQL is used to:

• Onboard customers
• Maintain accounts
• Process fund transfers (ACID critical)
• Manage loans & repayments
• Detect fraud patterns
• Maintain audit trails (RBI / SOX / ISO compliance)
• Enforce data security and segregation of duties

One wrong SQL = financial loss + regulatory violation

---

PART 1️⃣ — DDL COMMANDS (STRUCTURE & GOVERNANCE)

DDL defines the bank’s data foundation. Mistakes here are expensive and hard to reverse.

---

1. What is DDL in Banking?

DDL commands define:

• What data we store
• How it is related
• What is allowed / not allowed
• What must be audited

DDL is usually executed only by DBAs, not developers, in production.

---

2. Core Banking Data Model (Realistic)

🔹 CUSTOMERS

CREATE TABLE customers (
    customer_id     NUMBER GENERATED BY DEFAULT AS IDENTITY,
    customer_name   VARCHAR2(100) NOT NULL,
    dob             DATE NOT NULL,
    pan_number      VARCHAR2(10) NOT NULL,
    mobile_number   VARCHAR2(15) NOT NULL,
    status          VARCHAR2(10) DEFAULT 'ACTIVE'
        CHECK (status IN ('ACTIVE', 'BLOCKED', 'CLOSED')),
    created_date    DATE DEFAULT SYSDATE NOT NULL,

    CONSTRAINT pk_customers PRIMARY KEY (customer_id),
    CONSTRAINT uk_customers_pan UNIQUE (pan_number)
);

🔹 Why these constraints matter in banking

Constraint	Banking Reason
PK	Every customer must be uniquely traceable
UNIQUE	PAN duplication = regulatory violation
CHECK	Invalid status causes transaction errors
NOT NULL	Mandatory KYC fields

---

3. ACCOUNTS Table (FK + DEFERRABLE)

CREATE TABLE accounts (
    account_id      NUMBER GENERATED BY DEFAULT AS IDENTITY,
    customer_id     NUMBER NOT NULL,
    account_number  VARCHAR2(20) NOT NULL,
    account_type    VARCHAR2(10) CHECK (account_type IN ('SAVINGS','CURRENT','LOAN')),
    balance         NUMBER(15,2) DEFAULT 0 NOT NULL,
    status          VARCHAR2(10) DEFAULT 'ACTIVE',
    opened_date     DATE DEFAULT SYSDATE,

    CONSTRAINT pk_accounts PRIMARY KEY (account_id),
    CONSTRAINT uk_accounts_acno UNIQUE (account_number),

    CONSTRAINT fk_accounts_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        DEFERRABLE INITIALLY IMMEDIATE
);

🔹 Why DEFERRABLE is important

• Used in batch onboarding
• Parent & child records inserted in same transaction
• Avoids false FK violations

---

4. TRANSACTIONS Table (High Volume, Audit-Critical)

CREATE TABLE transactions (
    txn_id          NUMBER GENERATED BY DEFAULT AS IDENTITY,
    from_account_id NUMBER,
    to_account_id   NUMBER,
    amount          NUMBER(15,2) CHECK (amount > 0),
    txn_type        VARCHAR2(10) CHECK (txn_type IN ('DEBIT','CREDIT','TRANSFER')),
    txn_date        DATE DEFAULT SYSDATE,
    status          VARCHAR2(10) CHECK (status IN ('SUCCESS','FAILED','PENDING')),

    CONSTRAINT pk_txn PRIMARY KEY (txn_id)
);

---

5. CTAS – Reporting & Analytics (Safe Method)

CREATE TABLE daily_txn_summary
AS
SELECT account_id,
       COUNT(*) txn_count,
       SUM(amount) total_amount
FROM transactions
GROUP BY account_id;

🔹 Used for:

• End-of-day reconciliation
• BI reporting
• Avoids hitting OLTP tables

---

6. Temporary Tables (Session-Safe Processing)

CREATE GLOBAL TEMPORARY TABLE tmp_fraud_check (
    account_id NUMBER,
    txn_count  NUMBER
)
ON COMMIT DELETE ROWS;

Used in:

• Fraud detection
• AML batch jobs
• Risk scoring

---

7. Safe ALTER Operations (Production Rule)

❌ Wrong (Locks table, risky):

ALTER TABLE accounts MODIFY balance NUMBER(20,2);

✅ Correct approach:

1. Add new column
2. Backfill
3. Switch logic
4. Drop old column

---

8. Common DDL Mistakes (Interview Favorite)

Mistake	Impact
No FK	Orphan transactions
No CHECK	Invalid account states
ALTER during peak hours	Blocking transactions
CTAS without indexes	Slow reports

---

PART 2️⃣ — DML COMMANDS (MONEY MOVEMENT)

DML is where money actually moves. Precision matters.

---

1. INSERT – Customer Onboarding

INSERT INTO customers (
    customer_name, dob, pan_number, mobile_number
)
VALUES (
    'Rahul Sharma',
    DATE '1990-05-15',
    'ABCDE1234F',
    '9000000000'
);

---

2. UPDATE – Account Status Change

UPDATE accounts
SET status = 'BLOCKED'
WHERE account_number = 'SBIN00012345';

Used in:

• Fraud detection
• Compliance freezes

---

3. DELETE – Rare & Controlled

DELETE FROM tmp_fraud_check;

❌ Never delete financial transactions
Use logical delete (status flag).

---

4. Fund Transfer (Critical Flow)

-- Debit
UPDATE accounts
SET balance = balance - 10000
WHERE account_id = 101
AND balance >= 10000;

-- Credit
UPDATE accounts
SET balance = balance + 10000
WHERE account_id = 202;

-- Record transaction
INSERT INTO transactions
(from_account_id, to_account_id, amount, txn_type, status)
VALUES (101, 202, 10000, 'TRANSFER', 'SUCCESS');

🔹 Why balance check matters

Prevents negative balance and double debit.

---

5. Wrong vs Correct Fund Transfer

❌ Wrong

UPDATE accounts SET balance = balance - 10000;

✅ Correct

WHERE account_id = :id AND balance >= :amount;

---

6. Performance Tips

• Avoid SELECT *
• Use indexed columns in WHERE
• Batch inserts with array processing

---

PART 3️⃣ — TCL COMMANDS (ACID GUARANTEE)

TCL protects money integrity.

---

1. COMMIT & ROLLBACK

COMMIT;
ROLLBACK;

---

2. Savepoints – Partial Rollback

SAVEPOINT before_debit;

UPDATE accounts SET balance = balance - 5000 WHERE account_id = 101;

-- If credit fails
ROLLBACK TO before_debit;

Used in:

• Multi-leg transactions
• Loan disbursement

---

3. Real Incident Example

Bank outage case:

• Debit succeeded
• Credit failed
• No savepoint
  ➡ Customer lost money temporarily

---

PART 4️⃣ — DCL COMMANDS (SECURITY & COMPLIANCE)

SQL security = regulatory compliance

---

1. Roles in Banking

CREATE ROLE teller_role;
CREATE ROLE auditor_role;

---

2. Grant Privileges

GRANT SELECT, INSERT ON transactions TO teller_role;
GRANT SELECT ON audit_logs TO auditor_role;

---

3. Revoke Dangerous Access

REVOKE DELETE ON transactions FROM teller_role;

---

4. Audit Logs Table

CREATE TABLE audit_logs (
    log_id       NUMBER GENERATED BY DEFAULT AS IDENTITY,
    action       VARCHAR2(50),
    username     VARCHAR2(30),
    action_date  DATE DEFAULT SYSDATE
);

---

5. Why This Matters

• RBI inspections
• SOX compliance
• Internal audits

---

INTERVIEW QUESTIONS (WITH REAL ANSWERS)

Q1. Why is COMMIT critical in banking?

Answer:
Ensures atomicity. Without commit, debit & credit may not persist consistently.

---

Q2. Why avoid DELETE in financial tables?

Answer:
Audit compliance requires historical data. Use status flags instead.

---

Q3. Why DEFERRABLE FK?

Answer:
Batch inserts need temporary referential flexibility.

---

Q4. How do you prevent double debit?

Answer:
Row-level locking + balance check in UPDATE condition.

---

Q5. Biggest SQL production mistake?

Answer:
Running DDL or mass UPDATE without WHERE clause.

Banking SQL Interview Simulation (Oracle – CBS)

Role: Oracle SQL / PL/SQL Developer
Domain: Core Banking System
Expectation: You think in money, risk, compliance, and performance, not just syntax.

---

🔹 INTERVIEW TASK 1 — Customer Onboarding (DDL + DML)

Scenario

A new customer is being onboarded.

• PAN must be unique
• Mobile number mandatory
• Default customer status = ACTIVE

Task

1. Design the table
2. Insert a customer
3. Prevent duplicate PAN

Expected SQL (Production Grade)

CREATE TABLE customers (
    customer_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    customer_name VARCHAR2(100) NOT NULL,
    pan_number VARCHAR2(10) NOT NULL,
    mobile_number VARCHAR2(15) NOT NULL,
    status VARCHAR2(10) DEFAULT 'ACTIVE'
        CHECK (status IN ('ACTIVE','BLOCKED','CLOSED')),
    created_date DATE DEFAULT SYSDATE,

    CONSTRAINT pk_customers PRIMARY KEY (customer_id),
    CONSTRAINT uk_customers_pan UNIQUE (pan_number)
);

INSERT INTO customers (customer_name, pan_number, mobile_number)
VALUES ('Ankit Verma', 'ABCDE1234F', '900000000');

Interview Follow-up Question

Q: Why not validate PAN uniqueness in application code only?
A: DB constraint is the final gatekeeper; app validation can be bypassed.

---

🔹 INTERVIEW TASK 2 — Account Creation (FK + DEFERRABLE)

Scenario

Accounts are created in bulk after customer onboarding.

Task

Create ACCOUNTS table linked to CUSTOMERS.

CREATE TABLE accounts (
    account_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    customer_id NUMBER NOT NULL,
    account_number VARCHAR2(20) NOT NULL,
    account_type VARCHAR2(10)
        CHECK (account_type IN ('SAVINGS','CURRENT')),
    balance NUMBER(15,2) DEFAULT 0 NOT NULL,

    CONSTRAINT pk_accounts PRIMARY KEY (account_id),
    CONSTRAINT uk_accounts_acno UNIQUE (account_number),

    CONSTRAINT fk_accounts_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        DEFERRABLE INITIALLY IMMEDIATE
);

Interview Insight

DEFERRABLE avoids FK failure during batch onboarding.

---

🔹 INTERVIEW TASK 3 — Fund Transfer (TCL + DML)

Scenario

Transfer ₹25,000 from Account A to Account B.

Task

Ensure:

• No negative balance
• Atomicity
• Rollback on failure

SAVEPOINT before_transfer;

UPDATE accounts
SET balance = balance - 25000
WHERE account_id = 101
AND balance >= 25000;

IF SQL%ROWCOUNT = 0 THEN
    ROLLBACK TO before_transfer;
END IF;

UPDATE accounts
SET balance = balance + 25000
WHERE account_id = 202;

INSERT INTO transactions
(from_account_id, to_account_id, amount, txn_type, status)
VALUES (101, 202, 25000, 'TRANSFER', 'SUCCESS');

COMMIT;

Interview Follow-up

Q: What happens if COMMIT is missed?
A: Entire transaction rolls back → no money movement.

---

🔹 INTERVIEW TASK 4 — Fraud Detection Query

Scenario

Identify accounts with more than 10 transactions in 1 hour.

SELECT account_id, COUNT(*) txn_count
FROM transactions
WHERE txn_date >= SYSDATE - (1/24)
GROUP BY account_id
HAVING COUNT(*) > 10;

Why This Matters

Used in:

• AML systems
• Real-time fraud alerts

---

🔹 INTERVIEW TASK 5 — Logical Delete (Compliance)

Scenario

A customer closes account. Data must not be deleted.

UPDATE accounts
SET status = 'CLOSED'
WHERE account_number = 'XBIN00056789';

Interview Trap Question

Q: Why not DELETE?
A: Regulatory audits require historical records.

---

🔹 INTERVIEW TASK 6 — Role-Based Access Control (DCL)

Scenario

Teller should not delete transactions.

CREATE ROLE teller_role;

GRANT SELECT, INSERT ON transactions TO teller_role;

REVOKE DELETE ON transactions FROM teller_role;

Interview Expectation

You understand least privilege principle.

---

🔹 INTERVIEW TASK 7 — Safe ALTER in Production

Scenario

Increase balance column precision.

❌ Wrong

ALTER TABLE accounts MODIFY balance NUMBER(20,2);

✅ Correct Approach

1. Add new column
2. Backfill
3. Switch code
4. Drop old column

---

🔹 INTERVIEW TASK 8 — Audit Trail

CREATE TABLE audit_logs (
    log_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    action VARCHAR2(100),
    username VARCHAR2(30),
    action_date DATE DEFAULT SYSDATE
);

Used for:

• RBI inspection
• SOX audits

---

COMMON INTERVIEW QUESTIONS (Rapid Fire)

Question	Expected Banking Answer
Why use SAVEPOINT?	Partial rollback in multi-step transactions
Why avoid TRUNCATE?	Non-recoverable, no rollback
Difference COMMIT vs AUTO-COMMIT	Auto-commit breaks atomicity
Why indexes on FK?	Prevent locking issues
Biggest SQL risk?	Mass UPDATE without WHERE