Bank Schema
DROP TABLE customer_master;
DROP TABLE branch_master;
DROP TABLE account_master;
DROP TABLE transaction_master;
DROP TABLE loan_details;
CREATE TABLE customer_master(
CUSTOMER_NUMBER VARCHAR2(6),
FIRSTNAME VARCHAR2(30),
middlename VARCHAR2(30),
lastname VARCHAR2(30),
CUSTOMER_CITY VARCHAR2(15),
CUSTOMER_CONTACT_NO VARCHAR2(10),
occupation VARCHAR2(10),
CUSTOMER_DATE_OF_BIRTH DATE,
CONSTRAINT customer_custid_pk PRIMARY KEY (CUSTOMER_NUMBER));
CREATE TABLE branch_master(
branch_id VARCHAR2(6),
branch_name VARCHAR2(30),
branch_city VARCHAR2(30),
CONSTRAINT branch_bid_pk PRIMARY KEY (branch_id));
CREATE TABLE account_master
(account_number VARCHAR2(255),
customer_number VARCHAR2(255),
branch_id VARCHAR2(255),
opening_balance number(20),
account_opening_date DATE,
account_type VARCHAR2(25),
account_status VARCHAR2(10),
PRIMARY KEY (account_number),
FOREIGN KEY (customer_number) references customer_master(customer_number),
FOREIGN KEY (branch_id) references branch_master(branch_id));
CREATE TABLE transaction_details(
transaction_number VARCHAR2(6),
account_number VARCHAR2(6),
date_of_transaction DATE,
medium_of_transaction VARCHAR2(20),
transaction_type VARCHAR2(20),
transaction_amount number(7),
CONSTRAINT trans_details_tnumber_pk PRIMARY KEY (transaction_number),
CONSTRAINT trans_details_acnumber_fk FOREIGN KEY (account_number)
REFERENCES account_master (account_number));
CREATE TABLE loan_details
(customer_number varchar2(255),
branch_id varchar2(255),
loan_amount number(20),
foreign key(customer_number) references customer_master(customer_number));
insert into customer_master values('C00001', 'RAMESH', 'CHANDRA', 'SHARMA', 'DELHI', '9543198345', 'SERVICE' ,'06-DEC-1976');
insert into customer_master values('C00002', 'AVINASH', 'SUNDER', 'MINHA', 'DELHI', '9876532109' ,'SERVICE', '16-OCT-1974');
insert into customer_master values('C00003', 'RAHUL', 'NULL', 'RASTOGI', 'DELHI', '9765178901', 'STUDENT', '26-OCT-1981');
insert into customer_master values('C00004', 'PARUL', 'NULL', 'GANDHI', 'DELHI', '9876532109' ,'HOUSEWIFE','03-NOV-1976');
insert into customer_master values('C00005', 'NAVEEN' ,'CHANDRA', 'AEDEKAR', 'MUMBAI', '8976523190', 'SERVICE' ,'19-SEP-1976');
insert into customer_master values('C00006', 'CHITRESH', 'NULL', 'BARWE', 'MUMBAI', '7651298321', 'STUDENT' ,'06-NOV-1992');
insert into customer_master values('C00007', 'AMIT' ,'KUMAR', 'BORKAR', 'MUMBAI', '9875189761', 'STUDENT', '06-SEP-1981');
insert into customer_master values('C00008', 'NISHA', NULL, 'DAMLE', 'MUMBAI', '7954198761', 'SERVICE', '03-DEC-1975');
insert into customer_master values('C00009', 'ABHISHEK', NULL, 'DUTTA', 'KOLKATA' ,'9856198761', 'SERVICE' ,'22-MAY-1973');
insert into customer_master values('C00010','SHANKAR' ,NULL, 'NAIR', 'CHENNAI', '8765489076', 'SERVICE', '12-JUL-1976');
insert into branch_master values('B00001', 'ASAF ALI ROAD','DELHI');
insert into branch_master values('B00002','NEW DELHI MAIN BRANCH','DELHI');
insert into branch_master values('B00003' ,'DELHI CANTT', 'DELHI');
insert into branch_master values('B00004' ,'JASOLA', 'DELHI');
insert into branch_master values('B00005' ,'MAHIM' ,'MUMBAI');
insert into branch_master values('B00006' ,'VILE PARLE', 'MUMBAI');
insert into branch_master values('B00007', 'MANDVI' ,'MUMBAI');
insert into branch_master values('B00008' ,'JADAVPUR', 'KOLKATA');
insert into branch_master values('B00009' ,'KODAMBAKKAM', 'CHENNAI');
insert into account_master values('A00001' ,'C00001','B00001',1000 ,'15-DEC-2012', 'SAVING', 'ACTIVE');
insert into account_master values('A00002' ,'C00002','B00001',1000 ,'12-JUN-2012' ,'SAVING', 'ACTIVE');
insert into account_master values('A00003' ,'C00003', 'B00002', 1000 ,'17-MAY-2012','SAVING' ,'ACTIVE');
insert into account_master values('A00004','C00002','B00005',1000,'27-JAN-2013','SAVING','ACTIVE');
insert into account_master values('A00005' ,'C00006', 'B00006', 1000 ,'17-DEC-2012' ,'SAVING','ACTIVE');
insert into account_master values('A00006','C00007','B00007',1000,'12-AUG-2010','SAVING','SUSPENDED');
insert into account_master values('A00007','C00007','B00001',1000,'02-OCT-2012','SAVING','ACTIVE');
insert into account_master values('A00008','C00001','B00003',1000,'09-NOV-2009','SAVING','TERMINATED');
insert into account_master values('A00009' ,'C00003', 'B00007', 1000 ,'30-NOV-2008' ,'SAVING', 'TERMINATED');
insert into account_master values('A00010' ,'C00004', 'B00002', 1000 ,'01-MAR-2013' ,'SAVING', 'ACTIVE');
insert into transaction_details values('T00001','A00001','01-JAN-2013','CHEQUE','DEPOSIT',2000);
insert into transaction_details values('T00002','A00001','01-FEB-2013','CASH','WITHDRAWAL',1000);
insert into transaction_details values('T00003','A00002', '01-JAN-2013','CASH' ,'DEPOSIT',2000);
insert into transaction_details values('T00004','A00002','01-FEB-2013' , 'CASH' ,'DEPOSIT',3000);
insert into transaction_details values('T00005','A00007','11-JAN-2013','CASH','DEPOSIT',7000);
insert into transaction_details values('T00006','A00007','13-JAN-2013','CASH','DEPOSIT',9000);
insert into transaction_details values('T00007','A00001','13-MAR-2013','CASH','DEPOSIT',4000);
insert into transaction_details values('T00008','A00001','14-MAR-2013','CHEQUE','DEPOSIT',3000);
insert into transaction_details values('T00009','A00001','21-MAR-2013','CASH','WITHDRAWAL',9000);
insert into transaction_details values('T00010','A00001','22-MAR-2013','CASH','WITHDRAWAL',2000);
insert into transaction_details values('T00011','A00002','25-MAR-2013','CASH','WITHDRAWAL',7000);
insert into transaction_details values('T00012','A00007', '26-MAR-2013','CASH','WITHDRAWAL',2000);
insert into Loan_details values('C00001', 'B00001', 100000);
insert into Loan_details values('C00002', 'B00002', 200000);
insert into Loan_details values('C00009', 'B00008', 400000);
insert into Loan_details values('C00010', 'B00009', 500000);
insert into Loan_details values('C00001', 'B00003', 600000);
insert into Loan_details values('C00002', 'B00001', 600000);
select * customer_master;
select substr(account_number,1,6) as account_number,
substr(customer_number,1,6) as customer_number,
substr(branch_id,1,6) as branch_id,
opening_balance,
account_opening_date,
account_type,
account_status
from account_master;
select * from branch_master;
select * from transaction_master;
select substr(customer_number,1,6) as customer_number,
substr(branch_id,1,6) as branch_id,
loan_amount from loan_details;
Bank QUERIES
1. Write a query to display account number, customer’s number, customer’s firstname, lastname, account opening date. Display the records sorted in ascending order based on account number.
************
select substr(a.account_number,1,6) as ano, c.customer_number as cno, c.firstname as fname, c.lastname as lname,a.account_opening_date as a_op_date
from customer_master c
join account_master a
on (c.customer_number = a.customer_number)
order by 1
output -
ANO CNO FNAME LNAME A_OP_DATE
------------------------ ------ ------------------------------ ------------------------------ ---------
A00001 C00001 RAMESH SHARMA 15-DEC-12
A00002 C00002 AVINASH MINHA 12-JUN-12
A00003 C00003 RAHUL RASTOGI 17-MAY-12
A00004 C00002 AVINASH MINHA 27-JAN-13
A00005 C00006 CHITRESH BARWE 17-DEC-12
A00006 C00007 AMIT BORKAR 12-AUG-10
A00007 C00007 AMIT BORKAR 02-OCT-12
A00008 C00001 RAMESH SHARMA 09-NOV-09
A00009 C00003 RAHUL RASTOGI 30-NOV-08
A00010 C00004 PARUL GANDHI 01-MAR-13
************
2. Write a query to display the number of customer’s from Delhi. Give the count an alias name of Cust_Count.
************
select count(customer_number) as "Cust_Count", customer_city
from customer_master
where customer_city = 'DELHI'
group by customer_city
output -
Cust_Count CUSTOMER_CITY
---------- ---------------
4 DELHI
************
3. Write a query to display the customer number, customer firstname, account number for the customer’s whose accounts were created after 15th of any month. Display the records sorted in ascending order based on customer number and then by account number.
*************
select c.customer_number as cno, c.firstname as fname, c.lastname as lname, substr(a.account_number,1,6) as ano, a.account_opening_date as a_op_date
from customer_master c
join account_master a
on (c.customer_number = a.customer_number)
where substr(a.account_opening_date,1,2) >= 15
************
4. Write a query to display customer number, customer's first name, account number where the account status is terminated. Display the records sorted in ascending order based on customer number and then by account number.
5. Write a query to display the total number of withdrawals and total number of deposits being done by customer whose customer number ends with 001. The query should display transaction type and the number of transactions. Give an alias name as Trans_Count for number of transactions. Display the records sorted in ascending order based on transaction type.
6. Write a query to display the number of customers who have registration but no account in the bank. Give the alias name as Count_Customer for number of customers.
7. Write a query to display account number and total amount deposited by each account holder (Including the opening balance). Give the total amount deposited an alias name of Deposit_Amount. Display the records in sorted order based on account number.
8. Write a query to display the number of accounts opened in each city .The Query should display Branch City and number of accounts as No_of_Accounts.For the branch city where we don’t have any accounts opened display 0. Display the records in sorted order based on branch city.
9. Write a query to display the firstname of the customers who have more than 1 account. Display the records in sorted order based on firstname.
10. Write a query to display the customer number, customer firstname, customer lastname who has taken loan from more than 1 branch. Display the records sorted in order based on customer number.
11. Write a query to display the customer’s number, customer’s firstname, customer’s city and branch city where the city of the customer and city of the branch is different. Display the records sorted in ascending order based on customer number.
12. Write a query to display the number of clients who have asked for loans but they don’t have any account in the bank though they are registered customers. Give the count an alias name of Count.
13. Write a query to display the account number who has done the highest transaction. For example the account A00023 has done 5 transactions i.e. suppose 3 withdrawal and 2 deposits. Whereas the account A00024 has done 3 transactions i.e. suppose 2 withdrawals and 1 deposit. So account number of A00023 should be displayed. In case of multiple records, display the records sorted in ascending order based on account number.
14. Write a query to show the branch name,branch city where we have the maximum customers. For example the branch B00019 has 3 customers, B00020 has 7 and B00021 has 10. So branch id B00021 is having maximum customers. If B00021 is Koramangla branch Bangalore, Koramangla branch should be displayed along with city name Bangalore. In case of multiple records, display the records sorted in ascending order based on branch name.
15. Write a query to display all those account number, deposit, withdrawal where withdrawal is more than deposit amount. Hint: Deposit should include opening balance as well. For example A00011 account opened with Opening Balance 1000 and A00011 deposited 2000 rupees on 2012-12-01 and 3000 rupees on 2012-12-02. The same account i.e A00011 withdrawn 3000 rupees on 2013-01-01 and 7000 rupees on 2013-01-03. So the total deposited amount is 6000 and total withdrawal amount is 10000. So withdrawal amount is more than deposited amount for account number A00011. Display the records sorted in ascending order based on account number.
16. Write a query to show the balance amount for account number that ends with 001. Note: Balance amount includes account opening balance also. Give alias name as Balance_Amount. For example A00015 is having an opening balance of 1000. A00015 has deposited 2000 on 2012-06-12 and deposited 3000 on 2012-07-13. The same account has drawn money of 500 on 2012-08-12 , 500 on 2012-09-15, 1000 on 2012-12-17. So balance amount is 4000 i.e (1000 (opening balance)+2000+3000 ) – (500+500+1000).
17. Display the customer number, customer's first name, account number and number of transactions being made by the customers from each account. Give the alias name for number of transactions as Count_Trans. Display the records sorted in ascending order based on customer number and then by account number.
18. Write a query to display the customer’s firstname who have multiple accounts (atleast 2 accounts). Display the records sorted in ascending order based on customer's firstname.
19. Write a query to display the customer number, firstname, lastname for those client where total loan amount taken is maximum and at least taken from 2 branches. For example the customer C00012 took a loan of 100000 from bank branch with id B00009 and C00012 Took a loan of 500000 from bank branch with id B00010. So total loan amount for customer C00012 is 600000. C00013 took a loan of 100000 from bank branch B00009 and 200000 from bank branch B00011. So total loan taken is 300000. So loan taken by C00012 is more then C00013.
20. Write a query to display the customer’s number, customer’s firstname, branch id and loan amount for people who have taken loans. Display the records sorted in ascending order based on customer number and then by branch id and then by loan amount.
21. Write a query to display city name and count of branches in that city. Give the count of branches an alias name of Count_Branch. Display the records sorted in ascending order based on city name.
22. Write a query to display account id, customer’s firstname, customer’s lastname for the customer’s whose account is Active. Display the records sorted in ascending order based on account id /account number.
23. Write a query to display customer’s number, first name and middle name. For the customers who don’t have middle name, display their last name as middle name. Give the alias name as Middle_Name. Display the records sorted in ascending order based on customer number.
24. Write a query to display the customer number , firstname, customer’s date of birth . Display the records sorted in ascending order of date of birth year and within that sort by firstname in ascending order.
25. Write a query to display the customers firstname, city and account number whose occupation are not into Business, Service or Student. Display the records sorted in ascending order based on customer first name and then by account number.
Comments
Thank you.
I am your ex-student of CDAC Patna.