Mastering Subqueries in SQL: Effective Subqueries Solutions for Bank Account Management Systems

Note: All SQL queries used in the case studies have been tested on Oracle, SQLServer, MySQL and PostgreSQL.

1. Write a subquery to find the customer name with the highest account balance.
Oracle
SELECT Name FROM Customers WHERE CustomerID =
( SELECT CustomerID FROM Accounts ORDER BY Balance DESC FETCH FIRST 1 ROWS ONLY );
Explanation
The SQL query will return the name of the customer who has the highest account balance. This will select first the customerid from the Accounts table and the results are ordered by Balance in descending order bringing to the top the highest balance.
Again using the FETCH FIRST 1 ROWS ONLY clause, the top result belonging to the account with the highest balance is fetched and taken for CustomerID to retrieve the Name from the Customers table for display. As such this will ensure that the correct identification of the customer with the highest amount of balance is to be displayed.

MySQL and PostgreSQL
SELECT Name
FROM Customers
WHERE CustomerID = (
SELECT CustomerID
FROM Accounts
ORDER BY Balance DESC
LIMIT 1
);
The requirement of this SQL query is to retrieve the name of the customer who has the maximum account balance. Now, the subquery does the following: It selects the CustomerID with the highest balance by ordering the accounts in descending order, based on the balance and selecting the top record. Once this CustomerID is determined, the outer query uses it to look up the Customers table for the name of the customer who holds this account: SELECT Name FROM Customers WHERE CustomerID = (.).

SQL Server
SELECT Name
FROM Customers
WHERE CustomerID = (
SELECT TOP 1 CustomerID
FROM Accounts
ORDER BY Balance DESC
);
This SQL query is designed to find out the name of the customer with the highest account balance. Here, the subquery SELECT TOP 1 CustomerID FROM Accounts ORDER BY Balance DESC is used for retrieving CustomerID of the account having the highest balance by sorting all accounts in a descending order based on their balance and selecting the topmost result. It returns a CustomerID, which is then used by the outer query SELECT Name FROM Customers WHERE CustomerID = (.) to find and return the name of the customer who owns this account. This method efficiently links the highest account balance to its owner in the database.

2. Write a query to find the average balance of all accounts for a customer named "John Doe."
Oracle, MySQL, SQL Server and PostgreSQL
SELECT AVG(Balance) AS AverageBalance FROM Accounts
WHERE CustomerID = ( SELECT CustomerID FROM Customers WHERE Name = 'John Doe' );
Explanation
This SQL will determine the average account balance for all customer accounts named "John Doe". The inner subquery SELECT customerID FROM Customers WHERE Name = 'John Doe' retrieves the customer ID of the customer named 'John Doe'. This customer ID is used to find all accounts owned by "John Doe" and then calculate the average balance of these accounts in an outer query, where customerID = (.) . The result is displayed as Average Balance, which represents the average balance of all John Doe accounts.

3. Write a query to find the most recent transaction date for a specific account ID.
Oracle, MySQL, SQL Server and PostgreSQL
SELECT MAX(tDate) AS MostRecentTransactionDate FROM Transactions
WHERE AccountID = your_account_id;
Explanation
This SQL query will return the most recent transaction date, given an account. It utilizes the MAX() function to find the latest (tDate) in table Transactions for the given AccountID. You need to replace your_account_id in the following with the exact Account ID you are interested in.

4.Write a query to retrieve the name of the customer with the second-highest total balance across all their accounts.
SELECT Name FROM Customers WHERE CustomerID =
( SELECT CustomerID FROM Accounts GROUP BY CustomerID HAVING SUM(Balance) = ( SELECT MAX(TotalBalance) FROM ( SELECT SUM(Balance) AS TotalBalance FROM Accounts GROUP BY CustomerID HAVING SUM(Balance) <ALL( SELECT MAX(SUM(Balance)) FROM Accounts GROUP BY CustomerID ) ) AS SecondHighest ) );
Explanation
This SELECT query will return the customer with the second highest balance in all of his accounts. What this does, it cycles through all the customers, figuring out how much each of them has by adding up all the balances in all of their accounts, and then at the end, we see which one's got the most and put it off to one side. Again, it checks for who has the highest total, but this time it does not consider the top customer. This is in order to retrieve the customer who has the second highest amount of money. Finally, take the ID of this customer and use this ID to find his name, which is the result of the query.

5.Write a query to list account IDs with no transactions in the last 6 months.
Oracle
SELECT AccountID FROM Accounts
WHERE AccountID NOT IN
( SELECT DISTINCT AccountID FROM Transactions WHERE tDate >= ADD_MONTHS(SYSDATE, -6) );
Explanation
The given query selects all the ids of account from the table named Accounts where there has no transaction taken place since past last six months. It identifies active accounts by looking up all unique account IDs from the transactions table where the transaction history is within the last six months. This is done by checking tDate>=ADD_MONTHS(SYSDATE, -6) minus six months from today's date.

A sample SQL subquery to solve an account management case study problem

6.Write a query to calculate the total balance for all savings accounts.
SELECT SUM(Balance) AS TotalSavingsBalance FROM ( SELECT Balance FROM Accounts WHERE AccountType = 'Savings' ) AS SavingsAccounts;
Explanation
The above query was supposed to get the sum of the total balance of all savings accounts. It talks about the accounting record and it first filters the lines in which the account type is "savings." It uses a subquery that determines those balances related to the stated savings accounts. Then you would take the available balance and add to figure out the sum of the balances for all accounts. This is being returned as the savings total balance. By dividing the process into the steps presented, this question delivers a clear-cut and straightforward total of the account balances that are professionally classified as savings accounts, and for those accounts, respectively.

7.Write a query to find the total number of transactions for accounts with a balance less than 500.
SELECT COUNT(*) AS TotalTransactions FROM Transactions
WHERE AccountID IN ( SELECT AccountID FROM Accounts WHERE Balance < 500 );
Explanation
This query will count all transactions from accounts with a balance of less than 500. The query will focus on the transaction table where all the records related to any kind of transaction are stored. However, it takes into account only a few transactions and filters these entries by considering transactions related to accounts with a balance of less than 500.
This is achieved by a sub query that first considers a table of accounts for IDs with less than 500 balances, then the main query executes all transactions for these accounts and returns the count as the total number of transactions.

8.Write a query to retrieve the oldest transaction date for all accounts held by a customer named "Alice".
SELECT MIN(tDate) AS OldestTransactionDate FROM Transactions WHERE AccountID IN ( SELECT AccountID FROM Accounts WHERE CustomerID = ( SELECT CustomerID FROM Customers WHERE Name = 'Alice' ) );
Explanation
This query is used to find out the oldest date of transaction of all the accounts of a customer named Alice. It begins by identifying Alice's court number on the court table. After she finds her client ID it asks to bring all the account IDs associated with Alice's accounts to the table of accounts. With these account identifiers the query then goes to the transaction table to find the closest date to any transaction on their accounts. The query identifies the oldest transaction by selecting the MIN transaction date (tdate). This history tells us when Alice's first deal was made. The inquiry involved recording the date of the transaction as soon as possible for any account named Alice. This process works in the same way in different database systems like Oracle, MySQL, SQL Server and PostgreSQL.

9.Write a query to find the customer with the smallest single transaction amount..
MySQL and PostgreSQL
SELECT Name
FROM Customers
WHERE CustomerID = (
SELECT CustomerID
FROM Transactions
ORDER BY Amount ASC
LIMIT 1
);
This SQL query will be used to find the customer who made the single smallest transaction. This starts with a subquery that selects a CustomerID associated with the smallest transaction amount from the Transactions table. It performs this by ordering the transaction amounts in an ascending manner and then picking out the top result, which corresponds to the smallest amount. This subquery will return the CustomerID, and the ID is given to the outer query to find and return the name of the customer from the Customers table. In other words, this query is actually joining the smallest transaction with a certain customer who made this transaction and returning the name as the output of this query. Common for both MySQL and PostgreSQL databases this can be achieved through the using by using the approach.

Oracle
SELECT Name
FROM Customers
WHERE CustomerID = (
SELECT CustomerID
FROM Transactions
ORDER BY Amount ASC
FETCH FIRST 1 ROWS ONLY
);
This SQL query is planned to retrieve the name of the customer who made the smallest transaction. The procedure begins with a subquery that selects the customer ID from the transaction table, where the transactions are sorted in ascending order based on the specified amount column.
Using the FETCH FIRST 1 ROWS ONLY statement the subquery fetches only the first customer ID in this ordered list that matches the transaction with the smallest amount. The outer query then takes this customer ID and retrieves the matching customer name from the Customers table. This method successfully identifies the customer associated with the smallest transaction.

SQL Server
SELECT Name
FROM Customers
WHERE CustomerID = (
SELECT TOP 1 CustomerID
FROM Transactions
ORDER BY Amount ASC
);
Here is a SQL query that finds a name of a customer with the smallest transaction. A subquery is used here. It selects customer IDs sorting the transactions in ascending order by amount then they are fetched in the main SELECT.
Top 1 returns only the customer Id where the minimum transaction is applied. The outer query would be to return the identification number of the customer and then it will return the matching name in the customer table. In this way you can determine the minimum transactions of a database as SQL Server does for you by using Top 1 syntax.

10.Write a query to count the number of unique customers who have made at least one transaction.
SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomerCount
FROM Accounts WHERE AccountID IN ( SELECT DISTINCT AccountID FROM Transactions );
The question returns the count of unique customers that have processed at least one transaction. This it does by first identifying all distinct account IDs from the Transactions table using a subquery. Subquery finds all the accounts that recorded transactions, and then from the Accounts table, main query counts how many of the distinct customer IDs are on those found in the subquery. It actually filters out customers who have no transaction history at all. Through these steps combined, the query returns the total count of unique customers involved in transactions-a reflection of engagement with accounts they hold.

11.Write a query to find the customer name associated with the account with the highest number of transactions.
Oracle
SELECT C.Name
FROM Customers C
WHERE C.CustomerID = (
SELECT A.CustomerID
FROM Accounts A
WHERE A.AccountID = (
SELECT T.AccountID
FROM Transactions T
GROUP BY T.AccountID
ORDER BY COUNT(T.TransactionID) DESC
FETCH FIRST 1 ROWS ONLY
)
);
This query is made to show the name of that customer who has accounted with the most number of transactions. It selects AccountID from the table Transactions, groups by AccountID, counts the number of transactions per account, orders the resultset in the descending order by transaction count, and fetches the AccountID with the most number of transactions using FETCH FIRST 1 ROWS ONLY. First it checks which one is the top account, and then searches for the corresponding CustomerID in the Accounts table. Lastly, it uses the CustomerID to select the Customers table, asking for and returning the name of the customer who owns the most active account.

MySQL and PostgreSQL
SELECT C.Name
FROM Customers C
WHERE C.CustomerID = (
SELECT A.CustomerID
FROM Accounts A
WHERE A.AccountID = (
SELECT T.AccountID
FROM Transactions T
GROUP BY T.AccountID
ORDER BY COUNT(T.TransactionID) DESC
LIMIT 1
)
);
This query fetches the name of a customer with the maximum number of transactions for an account, and it starts off by selecting the AccountID from the Transactions table and then grouping the transactions by AccountID counting how many transactions are associated with each account:. This query orders the accounts by the count of transactions in descending order, then LIMIT 1 specifies it will return only that account with the highest transaction count. The AccountID is then found, used to query the Accounts table for CustomerID associated with it, then that CustomerID is used to look up and return the customer's name from the Customers table.

SQL Server
SELECT C.Name
FROM Customers C
WHERE C.CustomerID = (
SELECT A.CustomerID
FROM Accounts A
WHERE A.AccountID = (
SELECT TOP 1 T.AccountID
FROM Transactions T
GROUP BY T.AccountID
ORDER BY COUNT(T.TransactionID) DESC
)
);
This is a query to search for the name of a customer who has an account with most transactions. This is accomplished in the initial step in the view by only looking at the Transactions table since it aggregates the transactions by AccountID, counts the number of transactions per account, orders the results in descending order by count, and then selects the AccountID with most transactions using TOP 1. Then, after this identification of the account, it pulls out the actual CustomerID for accounts based on an account match to the Accounts table. It then takes up and displays the name of that customer as contained in the Customers table holding that account.

12. Write a query to calculate the average transaction amount for accounts with a balance greater than 5000.
Oracle,Mysql,SQLServer and PostgreSQL

SELECT AVG(Amount) AS AverageTransactionAmount
FROM Transactions
WHERE AccountID IN (
SELECT AccountID
FROM Accounts
WHERE Balance > 5000
);
The query computes average transaction amount from the Transactions table by accounts whose balances exceed 5000. It begins with select statement of the average of the Amount column as aliased AverageTransactionAmount. From the primary query, it filters transactions based on the AccountID. Only the transactions associated with qualifying accounts are taken into account at the filtration level.
To get the list of qualifying accounts, it takes the help of a subquery. It uses AccountID from Accounts table whose balance is greater than 5000. The inner query employs the IN keyword of the outer query to check whether the AccountID of each transaction that it accesses falls into the list that the inner query had constructed.
In other words, the returned output shows the average transaction amount only as it occurred in accounts with larger balances, thereby helping in financial analysis that is particularly applicable to an account.

13.Write a query to find the name of the customer who made the largest single transaction.

MySQL, PostgreSQL

SELECT C.Name FROM Customers C WHERE C.CustomerID = (
SELECT A.CustomerID FROM Accounts A WHERE A.AccountID = (
SELECT T.AccountID FROM Transactions T ORDER BY T.Amount DESC LIMIT 1 ) );

This query returns the name of a customer that ever did one transaction which is the largest single one. How does it do it? This essentially means it looks at the Transactions table first, sorts transactions in descending order by Amount, then selects from that list the associated AccountID with that transaction, that has the highest amount using LIMIT 1 to return only the top result. Once having selected AccountID, it will then search the Accounts table for the CustomerID associated with the account. It then uses this CustomerID to query and return that customer's Name out of the Customers table. More wordily, it finds who the customer is who made the highest amount transaction by tracing the transaction back to their name by first determining which account was used to make the transaction.

SQL Server

SELECT C.Name FROM Customers C WHERE C.CustomerID = (
SELECT A.CustomerID FROM Accounts A WHERE A.AccountID = (
SELECT TOP 1 T.AccountID FROM Transactions T ORDER BY T.Amount DESC ) );

The query is supposed to return the name of that customer who made the biggest single transaction, i.e.: It will look first in the Transactions table and order them by Amount in descending order and use the TOP 1 to select the AccountID corresponding to the highest amount of transaction, hence with the biggest amount. It now seeks out the AccountID of the largest transaction which it then looks up in the Accounts table to determine if that CustomerID corresponds to that account. It finally searches based on the CustomerID found there and returns the Name of that customer in the Customers table. In short, this query traces the biggest transaction to a customer who owns an account involved in that big transaction and then, in conclusion, returns his name. It is the version for SQL Server, where we will use TOP 1 to limit the result.

Oracle

SELECT C.Name
FROM Customers C
WHERE C.CustomerID = (
SELECT A.CustomerID
FROM Accounts A
WHERE A.AccountID = (
SELECT T.AccountID
FROM Transactions T
ORDER BY T.Amount DESC
FETCH FIRST 1 ROWS ONLY
)
);

This would return the customer's name for the largest transaction made. It first orders all transactions in the table by Amount in descending order this is equivalent to doing from largest to smallest. The query then fetches the AccountID of the row having the maximum amount .
Then, it refers to the related Customer ID of that account from the Accounts table through this Account ID of maximum transaction and traces back that brings its name from the Customers table in other words, traces its way back to ascertain who that owner is whose ID is linked with that highest transaction. This is Oracle, limiting the result of the preceding query to the top transaction by using FETCH FIRST 1 ROWS ONLY. Compute the total balance of all checking accounts.

14.Write a query to list customers who have never made a transaction.
SELECT C.Name FROM Customers C WHERE C.CustomerID NOT IN (
SELECT A.CustomerID FROM Accounts A WHERE A.AccountID IN (
SELECT T.AccountID FROM Transactions T ) );
Explanation
The query show the customers who never had a transaction. First, it will start with all customer names from the Customers table. A subquery is used in establishing the customers who had transactions by checking on their associated accounts in the Accounts table. In that subquery, it checks if the AccountID for each customer appears in the Transactions table. That means that accounts have made transactions. Then this feeds into the outer query by using the NOT IN to eliminate any customer whose account has been involved in a transaction. Which means that this query will return only those customers whose accounts are not there in the Transactions table, thus listing the customers who have never made a transaction.
This query structure works for all MySQL, PostgreSQL, SQL Server, and Oracle.

15.Write a query to find the total number of transactions for a specific customer ID.
MySQL, PostgreSQL, SQL Server and Oracle.
SELECT COUNT(T.TransactionID) AS TotalTransactions
FROM Transactions T
WHERE T.AccountID IN (
SELECT A.AccountID
FROM Accounts A
WHERE A.CustomerID = 4
);
Explanation
This query calculates the total number of transactions made by a particular customer, identified by their CustomerID. It does this by first using COUNT() to count up how many rows in the Transactions table exist having that specified CustomerID. So as to limit the count to just the transactions for the named customer it makes a subquery, first by getting the values of AccountID values belonging to the named customer, by reaching inside the Accounts table. Now, the inner query filters Accounts with CustomerID. In my example, I have a customer ID = 4. You can substitute that for whatever customer ID you wish to get an account count for. Then, from those accounts, the outer query counts how many transactions are actually tied to them by matching AccountID from the Transactions table. It will count the total transactions for that customer.

16.Write a query to retrieve the account ID with the lowest balance.
SELECT A.AccountID FROM Accounts A WHERE A.Balance = (
SELECT MIN(A.Balance) FROM Accounts A );

This subquery is intended to return the AccountID of that account which holds the smallest balance. To accomplish this, it declares the smallest balance in the Accounts table to be the return value of a subquery which invokes the MIN() function: That subquery returns the least value of a balance of all accounts. Then, in the outer query, it returns AccountID from the table Accounts where the balance of an account equals the smallest account balance found out by a subquery. Thus, the query merely compares all balances between accounts and returns an ID of the account with the smallest balance. Such a way, this method is robust on various DBMS: MySQL, PostgreSQL, SQL Server, Oracle.

17.Write an SQL statement that will find the average transaction amount of all the transactions made by customers named "John.".
SELECT AVG(T.Amount) AS AvgTransactionAmount FROM Transactions T WHERE T.AccountID IN (
SELECT A.AccountID FROM Accounts A WHERE A.CustomerID IN (
SELECT C.CustomerID FROM Customers C WHERE C.Name = 'John' ) );
Explanation
This query finds the average transactional amount for all accounts held by customers with names "John" It makes a subquery of the Customers table to select which of their numbers match "John" and uses that number as CustomerID in a second subquery of the Accounts table to pick up the related AccountIDs from those customers. Now the outer query is making a query on the Transactions table using the valid AccountID values and it is averaging the amount of all of those transactions associated with the accounts into the AVG(T.Amount) function. The result is the average transaction amount for all the transactions related to accounts whose owners names are "John".
This query formulated only with subqueries and works in all above mentioned databases like MySQL, PostgreSQL, SQL Server and Oracle.

18.Write a query to identify the customer with the most recent transaction.
MySQL and PostgreSQL
SELECT C.Name FROM Customers C WHERE C.CustomerID = (
SELECT A.CustomerID FROM Accounts A WHERE A.AccountID = (
SELECT T.AccountID FROM Transactions T ORDER BY T.tDate DESC LIMIT 1 ) );
Explanation
This SQL query is to retrieve a customer's name from a database. It first looks for the most recent transaction in the "Transactions" table, sorting transactions by date (Date), and utilizes LIMIT 1 to retrieve the last occurrence of this result; this returns the AccountID linked to that most recent transaction. Then it pulls out the CustomerID corresponding to the AccountID generated in the earlier step from the "Accounts" table. Lastaly using that CustomerID it fetches the name of that customer from the "Customers" table. Which means it fetches the customer who made the most recent transaction.

SQL Server
SELECT C.Name FROM Customers C WHERE C.CustomerID = (
SELECT A.CustomerID FROM Accounts A WHERE A.AccountID = (
SELECT TOP 1 T.AccountID FROM Transactions T ORDER BY T.tDate DESC ) );
This SQL statement retrieves the name of a customer from the "Customers" table. He first searches for a specific customer who has the same ID as the one that represents the most recent transaction in the "transactions" table. This is achieved with some sub-queries the innermost query tries to fetch the latest executed transaction, sorting rows in the "transactions" table by tDate in descending order and returning the first one; then uses that AccountID to fetch CustomerID for this account; eventually finds name of the customer using CustomerID to "Customers" table. General ability: This question does a good job of relating three tables. It is able to find the customer name based on the most recent activity appearing within the transactions.

Oracle
SELECT C.Name FROM Customers C WHERE C.CustomerID = (
SELECT A.CustomerID FROM Accounts A WHERE A.AccountID = (
SELECT T.AccountID FROM Transactions T ORDER BY T.tDate DESC FETCH FIRST 1 ROWS ONLY ) );
Retrieves the name of a customer from the "Customers" table. It especially retrieves the name of the customer based on the match between the "CustomerID" in the query and the ID retrieved from another table called "Accounts." The "CustomerID" that should be retrieved is first found by querying the "Transactions" table to retrieve the latest transaction by ordering the transactions by date ("tDate") in descending order and selecting the first row of the results. This "AccountID" is then used to look up the corresponding "CustomerID" in the table "Accounts," and the query finally returns the name of the customer associated with that account.

19.Write a query to calculate the total balance of all accounts held by customers who made a transaction in the last week.
MySQL and PostgreSQL
SELECT SUM(A.Balance) AS TotalBalance FROM Accounts A WHERE A.CustomerID IN (
SELECT DISTINCT A.CustomerID FROM Accounts A WHERE A.AccountID IN (
SELECT T.AccountID FROM Transactions T WHERE T.tDate >= CURDATE() - INTERVAL 7 DAY ) );
Explanation
The SQL query will calculate the total balance of the accounts of customers who had a transaction in the last seven days. First, the sum of the balances of the "accounts" is selected, taking into account only those accounts where the customer identifier appears at least once in the list of customers who have made at least one recent transaction. This is done through a subquery that searches for the unique identifiers of the clients for the accounts so that on a specific date in the "transactions" table, these accounts had transactions within the last seven days, using CURDATE() - INTERVAL 7 DAY. Thus, in reality the query will gather the balances of all accounts belonging to active clients that is to those who have made transactions recently.

SQL Server
SELECT SUM(A.Balance) AS TotalBalance
FROM Accounts A
WHERE A.CustomerID IN (
SELECT DISTINCT A.CustomerID
FROM Accounts A
WHERE A.AccountID IN (
SELECT T.AccountID
FROM Transactions T
WHERE T.tDate >= DATEADD(DAY, -7, GETDATE())
)
);
It returns the sum of accounts for those customers who traded during the past week. Here is the beginning of the construction of the query it selects the sum of balances from the "Accounts" table aliasing this total to "TotalBalance." To determine whose accounts balances should be summed, it filters for the accounts based on customer IDs returned in a nested subquery:. The subquery will return a unique list of customer IDs in "Accounts" where accounts are tied to records in "Transaction." It will return account IDs in "Transactions" where tDate has been within the last seven days-as calculated by DATEADD relative to the date the query is executed on, GETDATE(). So, the overall objective of the question will be to provide a sum total of the balances of customers who have recently conducted transactions in it. It will reflect active customer finances for the last week.

Oracle
SELECT SUM(A.Balance) AS TotalBalance
FROM Accounts A
WHERE A.CustomerID IN (
SELECT DISTINCT A.CustomerID
FROM Accounts A
WHERE A.AccountID IN (
SELECT T.AccountID
FROM Transactions T
WHERE T.tDate >= SYSDATE - 7
)
);
This SQL query is intended to find the total balance of accounts whose owners made transactions during the past week. Broken down, it first gets a sum of balances from the Accounts table, assigning this sum the name TotalBalance. Then, it searches for records that contain accounts with CustomerIDs to be found in another query.
This subquery first pulls unique values of CustomerIDs from the Accounts table, though only where accounts meet a condition stipulated in another subquery. The innermost subquery pulls accountids from the Transactions table where the transaction date, tDate, falls within the last seven days- really determined by SYSDATE - 7, capturing the records that are more recent than a week ago). Summarized, the entire question is how to determine all customers' account balances who have, at some point in time, done something during the last week, filter accounts by a nested process of subqueries.

20.Write a query to find the name of the customer who has the highest number of accounts.

MySQL, PostgreSQL
SELECT C.Name
FROM Customers C
WHERE C.CustomerID = (
SELECT A.CustomerID
FROM Accounts A
GROUP BY A.CustomerID
ORDER BY COUNT(A.AccountID) DESC
LIMIT 1
);
This query will return the name of the customer who has maximum numbers of accounts in the database. It starts off with selecting the column Name from Customers table which is temporarily aliased as C then goes on to execute a subquery that will find the customer with maximum numbers of accounts.This inner query returns the CustomerID from the Accounts table which is named as A. There is the expression GROUP BY CustomerID so that there will actually be a count of how many accounts for each customer. Then the results are ordered in descending order based on the count of AccountID so that you would see which of the customers have the highest number of accounts linked to them.It only returns one result limiting it just to the customer ID with the highest account count. Then the main query compares that customer ID from the customers table with that one thus the return of the name of the customer associated with that highest account count.
SQL Server
SELECT C.Name
FROM Customers C
WHERE C.CustomerID = (
SELECT TOP 1 A.CustomerID
FROM Accounts A
GROUP BY A.CustomerID
ORDER BY COUNT(A.AccountID) DESC
);

Oracle
SELECT C.Name
FROM Customers C
WHERE C.CustomerID = (
SELECT A.CustomerID
FROM Accounts A
GROUP BY A.CustomerID
ORDER BY COUNT(A.AccountID) DESC
FETCH FIRST 1 ROWS ONLY
);
The SQL statement selects a name of a customer, which is found in the Customers table to most accounts are assigned. This is achieved through selecting the column Name in the table Customers aliased as C where CustomerID will match the result of a subquery.
The subquery now calculates its value for the table Accounts A groups the results by CustomerID counts the number of entries for AccountID for each customer and returns those results ordered by descending count of accounts. Returns only the top row through the use of FETCH FIRST 1 ROWS ONLY, which moves the customer with the most accounts to the top of the result set. It prints the name of the customer having maximum account holdings.

21.Write a query to retrieve the account ID and account type with the most deposits.
MySQL and PostgreSQL
SELECT A.AccountID,A.AccountType
FROM Accounts A
WHERE A.AccountID = (
SELECT T.AccountID
FROM Transactions T
WHERE T.TransactionType = 'Deposit'
GROUP BY T.AccountID
ORDER BY COUNT(T.TransactionID) DESC
LIMIT 1
);
This SQL statement retrieves AccountID and AccountType from the Accounts table for that account which has provided a depositor with the most transaction made on the account. The method here is a subquery, where it counts the number of depositor's transactions on each of the AccountID in the Transactions table. The subquery is constructed so that to take results by their AccountID in descending order with respect to the count of the transactions. The LIMIT 1 clause ensures that only the highest count of deposits AccountID is selected. Then the outer query filters the Accounts table in order to get details from the account that matches that AccountID, so effectively it identifies an account with the greatest number of deposit transactions and fetches its details.
SQL Server

SELECT A.AccountID
FROM Accounts A
WHERE A.AccountID = (
SELECT TOP 1 T.AccountID
FROM Transactions T
WHERE T.TransactionType = 'Deposit'
GROUP BY T.AccountID
ORDER BY COUNT(T.TransactionID) DESC
);
This query will return the AccountID from the Accounts table where the AccountID is that of the account to have the most deposit transactions recorded in the Transactions table because of the subquery that has selected the top account to have the most deposits. a subquery only filters transactions of type 'Deposit' The following query groups the result by AccountID and then counts the number of transactions for each account in descending order to select only the first record that is the account with the most deposits. Therefore the main query returns AccountID of that account which have maximum number of deposits under the existence of aggreagation and ordering techniques.
Oracle

SELECT A.AccountID
FROM Accounts A
WHERE A.AccountID = (
SELECT T.AccountID
FROM Transactions T
WHERE T.TransactionType = 'Deposit'
GROUP BY T.AccountID
ORDER BY COUNT(T.TransactionID) DESC
FETCH FIRST 1 ROWS ONLY
);
The given query searches for the AccountID in the Accounts table with the most deposit transactions. It does so using a subquery which first selects the AccountID from the Transactions table where the type of transaction is classified as 'Deposit'. This subquery groups the returned result by AccountID and then continues on to count the number of transactions that occur for each account ordered such that the results come back ordered by that count descending. It limits results to only the first account with the most deposit transactions, using the FETCH FIRST 1 ROWS ONLY clause.Then the outer query matches up this AccountID with the Accounts table to get its final selection. In effect it determines with a coherent sequence of selections and grouping, the account having the most deposit transactions.

22.Write a query to find the total number of transactions made by customers with a balance greater than 10000.
Oracle, MySQL, SQL Server and PostgreSQL

SELECT COUNT(*) AS TotalTransactions
FROM Transactions
WHERE AccountID IN (
SELECT AccountID
FROM Accounts
WHERE Balance > 10000);
This query is written to return the count of the total number of transactions based on the Transactions table for accounts holding a balance of over 10,000.
It does this by first pulling back the AccountIDs from the Accounts table where the balance is over 10,000.Then it brings those AccountIDs to filter through the Transactions table and counts how many transactions correspond with those accounts.
The result of the count is named Total Transactions.

SQL FAQ1:-->>SQL Interview Questions List 1. || SQL FAQ2:-->>SQL Interview Questions List 2.