13. Subqueries.sql

use ecom;
select * from dim_product;

-- SUBQUERY : Query within query
-- Sub queries are queries written inside another query. They're used when we need to get a result from one query and use it inside another.
-- Example, in dim_product table, filter out those records which are greater than the average price.

select 
	*
from 
	dim_product
where
	unit_price > (select					
						avg(unit_price)
				  from
						dim_product); 	-- inner query will be excecuted first
                        
-- We can also use above subquery as a table

select
	*
from (select 
			*
		from 
			dim_product
		where
			unit_price > (select					
								avg(unit_price)
						  from
								dim_product)) as subquery_table -- Treated as in memory table
where
	product_name = 'Figure Method'; 
    
-- We use subqueries when we want to use multi step transformation.