In my previous article I have given the different SQL interview questions for data validation and its examples. In this article I would like to give the SQL queries for banking domain. There are so many interview questions and if you are facing the any banking related interviews you will be asked some SQL queries related to banking. We need to check database design at first stage and then we will write queries accordingly.
Database Design for this Assignment :
We are using the above design diagram for multiple bank related questions. These are very basic tables which we can use and we can see that there are more relational tables which has primary key and foreign key. The account number is primary key and unique identifier for most of the given tables.
SQL Queries for Banking Domain :
Query 1 : We need to count number of accounts opened in USA Bank Irvine branch.
We need to use the count function to calculate the number of account from USA bank. If you see the Account_master and Branch_master table. We require to use the count function.
Select count(a.account_number) from Account_master a,Branch_master b where a.branch_id= b.branch_id and upper(Branch_name) = ‘USA BANK IRVINE’;
Query 2 : We require customer KYC details where customer date of birth is 11-OCT.
These kind of queries we require to fire frequently. You can use following query,
Select * from Customer_Master where Customer_date_of_birth like ’11-OCT%’;
Query 3 : What is query to fetch the customer records who took loan.
Select c.* from Customer_Master c,Loan_details d where c.customer_number=d.customer_number;
Query 4 : We require to fetch all records of branch.
Select * from Branch_Master;
Query 5 : What are different account_holders who completed the transactions on 12th August 2021.
Select a.* from Account_master a,Transaction_details b where a.account_number=b.account_number and b.date_of_transaction = ‘ 12-08-2021’;
Query 6 : What are different account_holder details who did the card transaction.
Select a.* from Account_master a,Transaction_details b where a.account_number=b.account_number and b.transaction_type = ‘ Card’;
Query 7 : We need to find account details who done the transaction with Card and Net banking in last hour.
Select a.* from Account_master a,Transaction_details b where a.account_number=b.account_number and b.transaction_type in(‘Card’ , ‘Net Banking’ and b.date_of_transaction=TO_CHAR(SYSDATE -1, ‘dd-mm-yy hh24:mi:ss’) ;
Query 8 : We require to take Customers whose occupation is Service and Opening balance is more than 5000.
Select a.First_name,b.Occupation from Customer_master a,Account_master b where a.customer_number=b.customer_number and b.Opening_balance > 5000;
Question 9 : What are multiple types of transactions . Please list those.
Select distinct transaction_type from Transaction_details;
Question 10 : List the accounts which are in closed status.
Select * from Account_Master where account_status = ‘Closed’;
These are top 10 examples for banking domain in detail. If you like this article or if you have any issues with the same kindly comment in comments section.