Practice Character, Date & Numeric Functions (Banking & FinTech)

By /

📖 Introduction

In Banking and FinTech applications, Oracle SQL functions are used daily for:

  • Customer data standardization
  • Interest & EMI calculation
  • Transaction analysis
  • Inactive account detection
  • Compliance and reporting

This blog provides a complete hands-on business case study to practice Oracle SQL Character, Date, and Numeric functions, exactly how they are used in real banking databases.

This guide is ideal for:

  • Freshers (0–2 years)
  • Experienced professionals (3–8 years)
  • Oracle interview preparation
  • Real project practice

🏗️ Business Scenario – FinTrust Digital Bank

FinTrust Bank is a digital bank offering:

  • Savings & Current accounts
  • Digital transactions
  • Loan products

As a SQL Developer / Data Analyst, your responsibility is to write SQL queries using built-in Oracle functions to generate reports and insights.

🗄️ Database Schema

1️⃣ CUSTOMER_MASTER Table

📌 Purpose

Stores customer demographic and KYC information.

CREATE TABLE customer_master (
    customer_id       NUMBER PRIMARY KEY,
    first_name        VARCHAR2(50) NOT NULL,
    last_name         VARCHAR2(50),
    email_id          VARCHAR2(100) UNIQUE,
    mobile_number     VARCHAR2(15),
    date_of_birth     DATE NOT NULL,
    gender            CHAR(1) CHECK (gender IN ('M','F')),
    created_date      DATE DEFAULT SYSDATE
);

🔹 Sample Data

INSERT INTO customer_master VALUES
(101, 'Rahul', 'Sharma', 'rahul.sharma@fintrust.com', '98xxxxxxxx', DATE '1990-05-15', 'M', SYSDATE-500);

INSERT INTO customer_master VALUES
(102, 'Anita', 'Verma', 'anita.verma@fintrust.com', '98xxxxxxxx', DATE '1985-12-10', 'F', SYSDATE-800);

INSERT INTO customer_master VALUES
(103, 'Vijay', NULL, 'vijay@fintrust.com', '98xxxxxxxx', DATE '2000-01-25', 'M', SYSDATE-200);

2️⃣ ACCOUNT_MASTER Table

CREATE TABLE account_master (
    account_id     NUMBER PRIMARY KEY,
    customer_id    NUMBER REFERENCES customer_master(customer_id),
    account_number VARCHAR2(20) UNIQUE NOT NULL,
    account_type   VARCHAR2(20) CHECK (account_type IN ('SAVINGS','CURRENT','LOAN')),
    balance        NUMBER(15,2),
    open_date      DATE,
    status         VARCHAR2(10)
);

🔹 Sample Data

INSERT INTO account_master VALUES
(201, 101, 'FTSAV1001', 'SAVINGS', 125000.75, SYSDATE-400, 'ACTIVE');

INSERT INTO account_master VALUES
(202, 102, 'FTCUR2001', 'CURRENT', 850000.00, SYSDATE-1000, 'ACTIVE');

INSERT INTO account_master VALUES
(203, 103, 'FTSAV1002', 'SAVINGS', 4500.25, SYSDATE-50, 'INACTIVE');

3️⃣ TRANSACTION_DETAILS Table

CREATE TABLE transaction_details (
    txn_id          NUMBER PRIMARY KEY,
    account_id      NUMBER REFERENCES account_master(account_id),
    txn_date        DATE,
    txn_type        VARCHAR2(10) CHECK (txn_type IN ('CREDIT','DEBIT')),
    amount          NUMBER(12,2),
    remarks         VARCHAR2(100)
);

🔹 Sample Data

INSERT INTO transaction_details VALUES
(301, 201, SYSDATE-10, 'CREDIT', 50000, 'Salary Credit');

INSERT INTO transaction_details VALUES
(302, 201, SYSDATE-5, 'DEBIT', 1200.50, 'ATM Withdrawal');

INSERT INTO transaction_details VALUES
(303, 203, SYSDATE-2, 'DEBIT', 500, 'UPI Payment');

🧪 Hands-On Lab – Oracle SQL Functions

🔤 LAB 1: Character / String Functions

🧑‍💼 Task 1: Display Full Customer Name in Uppercase

SELECT customer_id,
       UPPER(first_name || ' ' || NVL(last_name,''))
FROM customer_master;

📧 Task 2: Mask Customer Email ID

SELECT email_id,
       SUBSTR(email_id,1,3) || '*****' ||
       SUBSTR(email_id, INSTR(email_id,'@'))
FROM customer_master;

📱 Task 3: Format Mobile Numbers to 12 Digits

SELECT LPAD(mobile_number, 12, '0')
FROM customer_master;

🧪 Exercise – Character Functions

Try writing SQL queries to:

  1. Show first name in INITCAP
  2. Find length of email IDs
  3. Replace fintrust.com with bank.com
  4. Display customers whose last name is NULL

📅 LAB 2: Date Functions

🎂 Task 4: Calculate Customer Age

SELECT customer_id,
       FLOOR(MONTHS_BETWEEN(SYSDATE, date_of_birth)/12) AS age
FROM customer_master;

🕒 Task 5: Find Accounts Older Than 1 Year

SELECT account_number
FROM account_master
WHERE open_date < ADD_MONTHS(SYSDATE, -12);

📆 Task 6: Current Month Transactions

SELECT *
FROM transaction_details
WHERE TRUNC(txn_date,'MM') = TRUNC(SYSDATE,'MM');

🧪 Exercise – Date Functions

  1. Find customers created in last 6 months
  2. Show last day of account opening month
  3. Display day name of each transaction

🔢 LAB 3: Numeric Functions

💰 Task 7: Calculate Yearly Interest (4%)

SELECT account_number,
       ROUND(balance * 0.04, 2) AS yearly_interest
FROM account_master
WHERE account_type = 'SAVINGS';

🧮 Task 8: EMI Approximation

SELECT account_number,
       ROUND((balance * 0.01 * POWER(1.01,12)) /
             (POWER(1.01,12)-1),2) AS EMI
FROM account_master;

📊 Task 9: Absolute Transaction Amount

SELECT txn_id, ABS(amount)
FROM transaction_details;

🧪 Exercise – Numeric Functions

  1. Show CEIL & FLOOR of transaction amounts
  2. Calculate monthly interest from yearly rate
  3. Find MOD of transaction amount with 100

🧠 Advanced Real-World Banking Exercises

🚨 Exercise 1: Identify Inactive Accounts (No Txn in 90 Days)

SELECT a.account_number
FROM account_master a
WHERE NOT EXISTS (
    SELECT 1
    FROM transaction_details t
    WHERE t.account_id = a.account_id
    AND t.txn_date > SYSDATE - 90
);

🔍 Exercise 2: Low Balance Alert (< ₹5000)

SELECT account_number, balance
FROM account_master
WHERE balance < 5000;

🧾 Exercise 3: Monthly Statement Report

  • Customer Name
  • Account Number
  • Month
  • Total Credit
  • Total Debit

(Hint: Use TRUNC, SUM, GROUP BY)

✅ Interview-Important Functions Covered

🔤 Character Functions

UPPER, LOWER, INITCAP, SUBSTR, INSTR, LENGTH, LPAD, NVL, REPLACE, TRIM

📅 Date Functions

SYSDATE, ADD_MONTHS, MONTHS_BETWEEN, TRUNC, LAST_DAY, NEXT_DAY

🔢 Numeric Functions

ROUND, CEIL, FLOOR, ABS, POWER, MOD

📌 Disclaimer

This content is created for educational and interview preparation purposes only. Database structures and data are simplified and do not represent any real banking system.

Scroll to Top