Use of SQL Inner Join to combine customer, account and transaction data in the banking sector

Note:  All SQL queries used in the Case study is tested on Oracle,SQLServer,MySQL and PostgreSQL.

1.List all customers along with their accounts and balance details.
SELECT c.CustomerID, c.Name, a.AccountID, a.AccountType, a.Balance
FROM Customers c INNER JOIN Accounts a ON c.CustomerID = a.CustomerID;
Explanation
This question uses the INNER JOIN keyword to indicate that only the customers with matching records in the Accounts table are included in the result set. The INNER JOIN keyword is used along with the JOIN keyword. In SQL, JOIN without a kind of JOIN (INNER, LEFT, RIGHT, FULL, CROSS) makes all inner join; all of these can also be implicit by simply using JOIN. This query returns CustomerID, Name from the Customers table and AccountID, AccountType, Balance from the Accounts table. The SQL join is determined based on the CustomerID. It ensures that only customers with accounts only are returned in the result set. If a customer has several accounts, then this customer will appear once for every account.

2.Retrieve all customers and their transaction history, including transaction amounts and dates.
SELECT c.CustomerID, c.Name, t.TransactionID, t.TransactionType, t.Amount, t.tDate
FROM Customers c INNER JOIN Accounts a ON c.CustomerID = a.CustomerID
INNER JOIN Transactions t ON a.AccountID = t.AccountID;
Explanation
This SQL query retrieves customer details along with the transaction history of that particular customer. This is done through two INNER JOIN operations. In the first INNER JOIN, the Customers table has been merged with the Accounts table such that the CustomerID in the Customers table finds only those customers who have accounts and subsequently the Accounts table has been merged with the Transactions table using AccountID so that every account's transaction details including TransactionID, TransactionType, Amount and tDate can be brought in.
This would display each customer's ID and name along with their transaction history. In such a situation where a customer had more than one transaction, each will be displayed as a single row. A query like this is excellent for analyzing customer activity by account transactions since INNER JOIN filters out customers in such a case where for instance only one of the accounts is available and/or a customer lacks transactions.

3. Get details of customers and accounts with accounts having balances above 1000 average balance.
SELECT c.CustomerID, c.Name, a.AccountID, a.AccountType, a.Balance
FROM Customers c
INNER JOIN Accounts a ON c.CustomerID = a.CustomerID
WHERE a.Balance > (
SELECT AVG(Balance)
FROM Accounts
WHERE Balance > 1000
);
Explanation
The above query returns the customer information and information from accounts whose balances are greater than 1000 and whose balances are higher than the average of balances that are greater than 1000.
This is done using INNER JOIN, applied on the customers table and accounts table by the common column CustomerID, and therefore the result has returned only customers with an account.
Now, the WHERE clause filters the account balance based on the result of another query. It locates the average balance of accounts whose balance is higher than 1000 by using a subquery and fetches all the customers whose balances are higher than that average and continues printing out their customer ID, name, account ID, type, and balance. This technique discovers the customers who have relatively more or very high account balances.