4.SQL basics.sql

use ecom;

-- SELECT
-- -------
select * from dim_customer; -- Read all columns

-- LIMIT
-- ------
select 
	customer_id,email 
from 
	dim_customer  -- Read relevant columns
limit 20;		  -- Output 20 records

-- WHERE [CONDITION] Used when we want to filter out (prune) our data.
-- -----------------
-- Query 1
-- Show details of all female customers
select
	*
from 
	dim_customer
where
	gender = 'F';
	
-- We can only write one WHERE clause in whole SQL statement. For multiple conditions we do the following.

-- Query 2 (AND/OR)
-- Select female customers, from France or customers who joined after 2021.
select
	*
from 
	dim_customer
where
	(gender = 'F') and ((country = 'France') or (join_date > '2021-01-01'));

-- LIKE Operator
-- -------------
-- Query 3
-- Select all customers whose first name starts with S.
select
	*
from
	dim_customer
where
	first_name like 'S%';

-- Query 4
-- Select all customers whose first name starts with T, 4th letter F and ends with Y.
select 
	*
from
	dim_customer
where
	first_name like 'T__F%Y'; -- Case insensitive
    
-- ORDEDR BY [Sorting]
-- -------------------

select * from dim_product;

-- Query 5
-- Sort the dim_products table such that product with high unit price come at top and lowest unit price comes at bottom. And return top 3 products.

select 
	*
from 
	dim_product
order by
	unit_price desc -- Default behaviour - ascending (ASC)
limit 3;

-- AS [ALIAS]
-- ------------

select
	product_key,
    product_id,
    product_name AS 'Product Name',  -- Renames the column
    unit_price
from
	dim_product;

-- GROUP BY [Grouping the data]
-- ----------------------------

select * from dim_product;

-- Query 6
-- Find the averge and total price per category sorted along total price in descending order. (avg/sum/min/max)

select 
	category,
    avg(unit_price) as avg_price,
    sum(unit_price) as total_price
from
	dim_product
group by
	category
order by
	total_price desc;
 
 
 -- We use where clause only on columns which are already available in the table.
-- HAVING [condition on group by] 
-- -------------------------------

-- Query 7
-- Find those categories whose average price is greater than 500.

select
	category
from
	dim_product
group by
	category
having
	avg(unit_price) > 500;
    
    
-- FLOW OF EXCECUTION:
-- 1) FROM
-- 2) WHERE
-- 3) GROUP BY
-- 4) HAVING
-- 5) SELECT
-- 6) ORDER BY
-- 7) LIMIT