#BuildWithHussain SQL Essentials DB setup

commands.sql

-- Create Customer table
CREATE TABLE Customer (
    ID INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    PAN VARCHAR(10),
    DOB DATE
);

-- Create Branch table
CREATE TABLE Branch (
    Code INT PRIMARY KEY,
    ifs_code VARCHAR(20),
    city VARCHAR(50),
    state VARCHAR(50),
    address VARCHAR(255)
);

-- Create Bank Account table
CREATE TABLE BankAccount (
    ac_number INT PRIMARY KEY,
    branch INT,
    customer_id INT,
    type ENUM('Savings', 'Current'),
    balance DECIMAL(10, 2),
    FOREIGN KEY (branch) REFERENCES Branch (Code),
    FOREIGN KEY (customer_id) REFERENCES Customer (ID)
);

-- Insert sample data into Customer table with Indian names
INSERT INTO Customer (ID, first_name, last_name, PAN, DOB)
VALUES
    (1, 'Rahul', 'Kumar', 'AEIPN0313F', '1990-05-15'),
    (2, 'Sneha', 'Sharma', 'BCKLM1245G', '1985-08-22'),
    (3, 'Vikram', 'Joshi', 'XYPQR5678H', '1995-02-10'),
    (4, 'Ananya', 'Patil', 'LMNOU9012I', '1988-03-05'),
    (5, 'Amit', 'Mishra', 'PQRST3456J', '1992-11-30'),
    (6, 'Priya', 'Rao', 'UVWXY7890K', '2002-03-12'),
    (7, 'Rajesh', 'Nair', 'ABCD1234E', '1998-07-18'),
    (8, 'Deepika', 'Chatterjee', 'FGHI5678L', '2001-04-25'),
    (9, 'Neha', 'Kulkarni', 'JKLM9012M', '1994-03-08'),
    (10, 'Arjun', 'Desai', 'NOPQ3456N', '1999-12-03'),
    (11, 'Aishwarya', 'Menon', 'RSTU7890O', '2005-02-20'),
    (12, 'Rohan', 'Iyer', 'VWXY1234P', '1986-09-14'),
    (13, 'Varun', 'Gupta', 'YZAB5678Q', '2003-07-01'),
    (14, 'Meera', 'Reddy', 'CDEF9012R', '1991-03-28'),
    (15, 'Aditi', 'Singh', 'GHIJ3456S', '2000-06-05');

-- Insert sample data into Branch table with Indian states and cities
INSERT INTO Branch (Code, ifs_code, city, state, address)
VALUES
    (101, 'IFSC1234', 'Mumbai', 'Maharashtra', '123 Main St'),
    (102, 'IFSC5678', 'Delhi', 'Delhi', '456 Oak St'),
    (103, 'IFSC9101', 'Bangalore', 'Karnataka', '789 Pine St'),
    (104, 'IFSC2345', 'Chennai', 'Tamil Nadu', '567 Maple St'),
    (105, 'IFSC6789', 'Hyderabad', 'Telangana', '890 Cedar St'),
    (106, 'IFSC1122', 'Kolkata', 'West Bengal', '111 Birch St'),
    (107, 'IFSC3344', 'Pune', 'Maharashtra', '222 Pine St'),
    (108, 'IFSC5566', 'Ahmedabad', 'Gujarat', '333 Oak St'),
    (109, 'IFSC7788', 'Jaipur', 'Rajasthan', '444 Cedar St'),
    (110, 'IFSC9900', 'Lucknow', 'Uttar Pradesh', '555 Birch St'),
    (111, 'IFSC1122', 'Chandigarh', 'Punjab', '666 Pine St'),
    (112, 'IFSC3344', 'Bhopal', 'Madhya Pradesh', '777 Oak St'),
    (113, 'IFSC5566', 'Patna', 'Bihar', '888 Cedar St'),
    (114, 'IFSC7788', 'Guwahati', 'Assam', '999 Birch St'),
    (115, 'IFSC9900', 'Bhubaneswar', 'Odisha', '000 Pine St');

-- Insert sample data into BankAccount table
INSERT INTO BankAccount (ac_number, branch, customer_id, type, balance)
VALUES
    -- Customer with 5 bank accounts (2 savings, 3 current)
    (2001, 101, 1, 'Savings', 5000.00),
    (2002, 101, 1, 'Savings', 7000.00),
    (2003, 102, 1, 'Current', 10000.00),
    (2004, 102, 1, 'Current', 12000.00),
    (2005, 103, 1, 'Current', 15000.00),

    -- Customer with 3 bank accounts
    (2006, 104, 2, 'Savings', 3000.00),
    (2007, 105, 2, 'Current', 8000.00),
    (2008, 106, 2, 'Current', 6000.00),

    -- Other customers
    (2009, 107, 3, 'Savings', 4500.00),
    (2010, 108, 4, 'Savings', 6000.00),
    (2011, 109, 5, 'Current', 9500.00),
    (2012, 110, 6, 'Savings', 3000.00),
    (2013, 111, 7, 'Current', 7000.00),
    (2014, 112, 8, 'Savings', 5500.00),
    (2015, 113, 9, 'Current', 12000.00),
    (2016, 114, 10, 'Savings', 4000.00),
    (2017, 115, 11, 'Savings', 9000.00),
    (2018, 104, 12, 'Current', 11000.00),
    (2019, 105, 13, 'Savings', 7500.00),
    (2020, 106, 14, 'Savings', 4800.00);