Banking Database (PostgreSQL)

CREATE DATABASE banking_cs;
----------------------------------------
DROP TABLE IF EXISTS Customer_Accounts;
DROP TABLE IF EXISTS Transactions;
DROP TABLE IF EXISTS Accounts;
DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS Employees;
----------------------------------------

CREATE TABLE products
(
	prod_id		VARCHAR(10) PRIMARY KEY,
	prod_name	VARCHAR(100) NOT NULL
);
----------------------------------------
CREATE TABLE Employees
(
	Emp_ID		VARCHAR(10),
	Emp_Name	VARCHAR(100),
	Salary		FLOAT,
	gender		VARCHAR(10) CHECK(gender IN ('M', 'F')),
	CONSTRAINT pk_emp PRIMARY KEY (emp_id)
);
----------------------------------------
CREATE TABLE Customers
(
	Customer_ID		VARCHAR(10),
	First_Name		VARCHAR(40),
	Last_Name		VARCHAR(40),
	Phone			BIGINT,
	Address			VARCHAR(200),
	dob				DATE,
	CONSTRAINT pk_cust PRIMARY KEY(customer_id)
);
----------------------------------------
CREATE TABLE Accounts
(
	Account_No			BIGINT PRIMARY KEY,
	Balance				INT,
	Account_Status		VARCHAR(10) CHECK (Account_Status IN ('Active', 'Inactive', 'Suspended', 'On hold')),
	Date_of_Opening		DATE
);
----------------------------------------
CREATE TABLE Transactions
(
	Transaction_ID		INT GENERATED ALWAYS AS IDENTITY,
	Transaction_Date	DATE,
	Transaction_amount	FLOAT,
	Credit_Debit_flag	VARCHAR(1),
	Account_No			BIGINT,
	CONSTRAINT fk_acc FOREIGN KEY (Account_No) REFERENCES Accounts(Account_No)
);
----------------------------------------
CREATE TABLE Customer_Accounts
(
	Customer_ID		VARCHAR(10),
	Account_No		bigint,
	prod_id			VARCHAR(10),
	CONSTRAINT fk_acc1 FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID),
	CONSTRAINT fk_acc2 FOREIGN KEY (Account_No) REFERENCES Accounts(Account_No),
	CONSTRAINT fk_acc3 FOREIGN KEY (prod_id) REFERENCES Products(prod_id)
);
----------------------------------------

INSERT INTO products VALUES ('PRD0001', 'Savings Account');
INSERT INTO products (prod_id, prod_name) VALUES ('PRD0002', 'Current Account');
INSERT INTO products VALUES ('PRD0003', 'Home Loan');
INSERT INTO products VALUES ('PRD0004', 'Personal Loan');
----------------------------------------
INSERT INTO Employees VALUES
	('E1', 'Mohan Kumar', 5000, 'M'),
	('E2', 'James Bond', 6000, 'M'),
	('E3', 'David Smith', 7000, 'M'),
	('E4', 'Alice Mathew', 5000, 'F');
----------------------------------------
INSERT INTO Customers VALUES ('C1', 'Satya', 'Sharma', 9900889911, 'Bangalore', to_date('01-03-1990', 'dd-mm-yyyy'));
INSERT INTO Customers VALUES ('C2', 'Jaswinder', 'Singh', 9900889922, 'Mumbai', to_date('024-03-1980', 'dd-mm-yyyy'));
INSERT INTO Customers VALUES ('C3', 'Satya', 'Sharma', 9900889933, 'Pune', to_date('11-08-1992', 'dd-mm-yyyy'));
INSERT INTO Customers VALUES ('C4', 'Maryam', 'Parveen', 9900889944, 'Delhi', to_date('01-12-1993', 'dd-mm-yyyy'));
INSERT INTO Customers VALUES ('C5', 'Steven', 'Smith', null, 'Chennai', to_date('20-12-1994', 'dd-mm-yyyy'));
INSERT INTO Customers VALUES ('C6', 'Jason', 'Holder', null, 'Chennai', to_date('01-02-1995', 'dd-mm-yyyy'));
----------------------------------------
INSERT INTO Accounts VALUES (1100444101, 100, 'Active', to_date('01-01-2020','dd-mm-yyyy'));
INSERT INTO Accounts VALUES (1100444102, 900, 'Active', to_date('10-01-2020','dd-mm-yyyy'));
INSERT INTO Accounts VALUES (1100444103, 500, 'Active', to_date('21-11-2021','dd-mm-yyyy'));
INSERT INTO Accounts VALUES (1100444104, 1100, 'Active', to_date('15-10-2022','dd-mm-yyyy'));
INSERT INTO Accounts VALUES (1100444105, 2200, 'Active', to_date('10-12-2022','dd-mm-yyyy'));
INSERT INTO Accounts VALUES (1100444106, 3300, 'Active', to_date('05-11-2022','dd-mm-yyyy'));
----------------------------------------
INSERT INTO Transactions VALUES (default,current_date, 200, 'C', 1100444101);
INSERT INTO Transactions (Transaction_Date, Transaction_Amount, Credit_Debit_flag, Account_No) VALUES (current_date-1, 100, 'C', 1100444101);
INSERT INTO Transactions (Transaction_Date, Transaction_Amount, Credit_Debit_flag, Account_No) VALUES (current_date-1, 50, 'D', 1100444101);
INSERT INTO Transactions (Transaction_Date, Transaction_Amount, Credit_Debit_flag, Account_No) VALUES (current_date-1, 100, 'C', 1100444102);
INSERT INTO Transactions (Transaction_Date, Transaction_Amount, Credit_Debit_flag, Account_No) VALUES (current_date-2, 200, 'C', 1100444102);
INSERT INTO Transactions (Transaction_Date, Transaction_Amount, Credit_Debit_flag, Account_No) VALUES (current_date-2, 100, 'D', 1100444102);
INSERT INTO Transactions (Transaction_Date, Transaction_Amount, Credit_Debit_flag, Account_No) VALUES (current_date-3, 100, 'C', 1100444103);
INSERT INTO Transactions (Transaction_Date, Transaction_Amount, Credit_Debit_flag, Account_No) VALUES (current_date-4, 200, 'C', 1100444104);
INSERT INTO Transactions (Transaction_Date, Transaction_Amount, Credit_Debit_flag, Account_No) VALUES (current_date-5, 100, 'C', 1100444105);
INSERT INTO Transactions (Transaction_Date, Transaction_Amount, Credit_Debit_flag, Account_No) VALUES (current_date-5, 200, 'D', 1100444105);
----------------------------------------
INSERT INTO Customer_Accounts VALUES ('C1', 1100444101, 'PRD0001');
INSERT INTO Customer_Accounts VALUES ('C1', 1100444102, 'PRD0003');
INSERT INTO Customer_Accounts VALUES ('C1', 1100444103, 'PRD0004');
INSERT INTO Customer_Accounts VALUES ('C2', 1100444105, 'PRD0002');
INSERT INTO Customer_Accounts VALUES ('C3', 1100444106, 'PRD0002');
INSERT INTO Customer_Accounts VALUES ('C1', 1100444104, 'PRD0004');

----------------------------------------
SELECT * FROM products;
SELECT * FROM Employees;
SELECT * FROM Customers;
SELECT * FROM Accounts;
SELECT * FROM Transactions;
SELECT * FROM Customer_Accounts;

-- PROBLEM STATEMENTS ---

--Using the given Banking dataset, solve following problems:

-- 1) Fetch the transaction id, date and amount of all debit transactions.
SELECT transaction_id, transaction_date AS date, transaction_amount AS amount
FROM transactions
WHERE credit_debit_flag='D';

-- 2) Fetch male employees who earn more than 5000 salary.
SELECT gender AS male_employees, salary
FROM Employees
WHERE salary > 5000
AND gender = 'M';

-- 3) Fetch employees whose name starts with J or whose salary is greater than or equal to 7000.
SELECT emp_name, salary
FROM employees
WHERE emp_name LIKE 'J%'
OR salary >= 7000;

-- 4) Fetch accounts with balance in between 1000 to 3000
SELECT account_no, balance
FROM accounts
WHERE balance BETWEEN 1000 AND 3000;

-- 5) Using SQL, find out if a given number is even or odd ? (Given numbers are 432, 77)
SELECT number,
       CASE WHEN number % 2 = 0 THEN 'Even'ELSE 'Odd' END AS parity
FROM (VALUES (432), (77)) AS numbers(number);

-- 6) Find customers who did not provide a phone no.
SELECT customer_id, first_name, phone
FROM customers
WHERE phone IS NULL;

-- 7) Find all the different products purchased by the customers.
SELECT DISTINCT prod_id
FROM customer_accounts

-- 8) Sort all the active accounts based on highest balance and based on the earliest opening date.
SELECT *
FROM accounts
WHERE account_status = 'Active'
ORDER BY balance DESC, date_of_opening ASC;

-- 9) Fetch the oldest 5 transactions.
SELECT transaction_date, transaction_amount
FROM transactions
ORDER BY transaction_date ASC
LIMIT 5;

-- 10) Find customers who are either from Bangalore/Chennai and their phone number is available OR those who were born before 1990.
SELECT *
FROM customers
WHERE 
(address IN ('Bangalore', 'Chennai') AND phone IS NOT NULL)
 OR EXTRACT(YEAR FROM dob) < '1990';

-- 11) Find total no of transactions in Feb 2023.
SELECT COUNT(*) AS total_transactions
FROM transactions
WHERE transaction_date >= '2023-02-01'
AND transaction_date < '2023-03-01';
  
-- 12) How many products were purchased by the customer "Satya Sharma".
SELECT COUNT(*)
FROM customers AS c
INNER JOIN customer_accounts AS ca on ca.customer_id = c.customer_id
WHERE CONCAT(c.first_name, ' ', c.last_name) = 'Satya Sharma';


-- 13) Display the full names of all employees and customers.
SELECT customer_id, CONCAT(first_name, ' ', last_name) AS customer_names
FROM customers 
UNION
SELECT emp_id, emp_name
FROM employees;

-- 14) Categorise accounts based on their balance.
-- [Below 1k is Low balance, between 1k to 2k is average balance, above 2k is high balance]
SELECT account_no, account_status, balance,
CASE WHEN balance < 1000 THEN 'Low'
     WHEN balance BETWEEN 1001 AND 2000 THEN 'Average Balance'
	 ELSE 'High Balance' END AS balance_category
FROM accounts;

-- 15) Find the total balance of all savings account.
SELECT p.prod_id, p.prod_name, SUM(a.balance) AS total_balance_savings
FROM products AS p
INNER JOIN customer_accounts AS ca ON ca.prod_id = p.prod_id
INNER JOIN accounts AS a ON a.account_no = ca.account_no
WHERE p.prod_name = 'Savings Account'
GROUP BY p.prod_id;

-- 16) Display the total account balance in all the current and savings account.
SELECT p.prod_id, p.prod_name, SUM(a.balance) AS total_balance
FROM products AS p
INNER JOIN customer_accounts AS ca ON ca.prod_id = p.prod_id
INNER JOIN accounts AS a ON a.account_no = ca.account_no
WHERE p.prod_name IN ('Savings Account', 'Current Account')
GROUP BY p.prod_id;