

Sometime I need to execute more that a single query in my SQL
I need to compute a results, then use that result in my main query

for example:

please show me all the cities in Japan and Denmark, but do not use a join

so I code:

select name, country_code
from world.city
where country_code in (select code from world.country where name in ('Japan', 'Denmark'))
                         step #1 find the code for Japan and Denmark
      step #2 use those code in the main query

------------------------------------
can you show me the orders where the order total is greater than the 
average orders for the customer

please show me these by customer

select outside.customer_id, outside.total_amount
 from customer.orders outside
 where outside.total_amount > 
  (select avg(inside.total_amount) from customer.orders inside
    where inside.customer_id = outside.customer_id)
order by outside.customer_id

I need to do 2 steps
#1 find the average order per customer but I need the customer Id that is being used
        outside this query
      
#2 use that value in the main query to limit the output



------------------------------------

I can use a subquery as a column in the SELECT
as a source in the FROM
as a group in the GROUP BY
as filter in the WHERE or HAVING

for example

select order_number, total_amount,
  (select avg(total_amount) from customer.orders) as "Average Order Amount"
from customer.orders
where total_amount >
  (select avg(total_amount) from customer.orders)

