**# EXAMPLE SQL Sub-Queries:**

SELECT
     *
FROM bank.loan
WHERE amount > (SELECT avg(amount) FROM bank.loan);
               -- subquery is a query that appears inside another query statement.

SELECT
     *
FROM (SELECT 
            account_id, 
            bank_to, 
            account_to, 
            sum(amount) as Total 
FROM bank.order 
GROUP BY 1,2,3) sub1
WHERE Total > 10000;

**# EXAMPLE SQL Sub-Queries:**

SELECT 
      account_id, 
      ifnull(num_trans,0) as num_trans 
FROM bank.account a
LEFT JOIN (
            SELECT 
                  account_id, 
                  count(*) num_trans
			FROM bank.trans 
            GROUP BY 1
            HAVING count(*) > (SELECT 
                                    avg(num_trans) 
                               FROM (SELECT account_id, count(*) num_trans
							         FROM bank.trans 
                                     GROUP BY account_id) t)
            ) b USING (account_id);

-- # Sub-Queries Tips to use:
-- Use subqueries when the result that you want requires more than one query and 
-- each subquery provides a subset of the table involved in the query.