samojoo · samojoo · Aug 13, 2025 · Aug 14, 2025 · Aug 14, 2025 · Aug 15, 2025
diff --git a/02_activities/assignments/assignment2.sql b/02_activities/assignments/assignment2.sql
@@ -59,9 +59,6 @@ Hint: you might need to use INSTR(product_name,'-') to find the hyphens. INSTR w
 
 
 
-/* 2. Filter the query to show any product_size value that contain a number with REGEXP. */
-
-
 
 -- UNION
 /* 1. Using a UNION, write a query that displays the market dates with the highest and lowest total sales.

diff --git a/04_this_cohort/live_code/module_3/DATES.sql b/04_this_cohort/live_code/module_3/DATES.sql
@@ -0,0 +1,27 @@
+-- dates
+
+-- now
+
+SELECT DISTINCT
+DATE('now') as [now]
+,DATETIME() as [right_now]
+
+--strftime
+,strftime('%Y/%m','now') as this_year_month
+,strftime('%Y-%m-%d', '2025-08-10', '+50 days') as the_future
+,market_date
+,strftime('%m-%d-%Y',market_date, '+50 days', '-1 year') as the_past
+
+--dateadd
+--last date of the month
+,DATE(market_date,'start of month','-1 day','start of month') as start_of_prev_month
+,DATE(market_date,'start of month','-1 day') as end_of_prev_month
+
+
+-- datediff "equiv"
+,market_date
+,julianday('now') - julianday(market_date) as now_md_dd-- number of days between now and each market_date
+,(julianday('now') - julianday(market_date)) / 365.25 as now_md_dd_yrs -- number of YEARS between now and market_date
+,(julianday('now') - julianday(market_date)) * 24 as now_md_dd_hours -- number of HOURS bewtween now and market_date
+
+FROM market_date_info
diff --git a/04_this_cohort/live_code/module_4/FULL_OUTER_JOIN_UNION.sql b/04_this_cohort/live_code/module_4/FULL_OUTER_JOIN_UNION.sql
@@ -0,0 +1,40 @@
+--FULL OUTER JOIN WITH A UNION
+-- two stores, determine which costumes they have in stock
+DROP TABLE IF EXISTS temp.store1; 
+CREATE TEMP TABLE IF NOT EXISTS temp.store1
+(
+costume TEXT,
+quantity INT
+);
+
+INSERT INTO temp.store1
+VALUES("tiger",6),
+        ("elephant",2),
+        ("princess", 4);
+
+
+DROP TABLE IF EXISTS temp.store2;
+CREATE TEMP TABLE IF NOT EXISTS temp.store2
+(
+costume TEXT,
+quantity INT
+);
+
+INSERT INTO temp.store2
+VALUES("tiger",2),
+	("dancer",7),
+	("superhero", 5);
+
+
+
+SELECT s1.costume, s1.quantity as store1_quantity, s2.quantity as store2_quantity, 'top query' as location
+FROM store1 s1
+LEFT JOIN store2 s2 on s1.costume = s2.costume 
+
+UNION ALL
+
+SELECT s2.costume, s1.quantity, s2.quantity, 'bottom query'
+FROM store2 as s2
+LEFT JOIN store1 s1 on s1.costume = s2.costume
+WHERE s1.costume IS NULL
+
diff --git a/04_this_cohort/live_code/module_4/IFNULL_NULLIF.sql b/04_this_cohort/live_code/module_4/IFNULL_NULLIF.sql
@@ -0,0 +1,24 @@
+--IFNULL and coalesce & NULLIF
+
+SELECT *
+,IFNULL(product_size,'Unknown') 
+
+--replace with another COLUMN
+,IFNULL(product_size, product_qty_type)
+,coalesce(product_size, product_qty_type)
+,coalesce(product_size,product_qty_type,'missing') -- if the first value is null, then the second value, if that is null, then the third value (missing) 
+
+,IFNULL(IFNULL(product_size, product_qty_type),'missing') -- same as above but with two ifnulls
+
+FROM product;
+
+SELECT *
+,coalesce(product_size,'Unknown') -- we aren't successfully handling the blank value
+--nullif
+,NULLIF(product_size,'') -- find the values in product_size that "blanks" and set them to null
+,coalesce(NULLIF(product_size,''),'Unknown')
+,coalesce(NULLIF(TRIM(product_size),''),'Unknown') -- a trimmed blank so all white space becomes blank '     ' = ''
+
+FROM product
+
+WHERE NULLIF(product_size,'') IS NULL -- capturing BOTH nulls and blanks at the same time!
diff --git a/04_this_cohort/live_code/module_4/INTERSECT_EXCEPT.sql b/04_this_cohort/live_code/module_4/INTERSECT_EXCEPT.sql
@@ -0,0 +1,29 @@
+-- INTERSECT / EXCEPT
+
+-- products that have been sold (e.g. are in the customer_purchases and product)
+
+SELECT product_id
+FROM customer_purchases
+INTERSECT
+SELECT product_id
+FROM product;
+
+-- products that have NOT been sold (e.g. are NOT in customer_purchases even though they are in product)
+SELECT product_name, x.product_id
+FROM 
+(
+	SELECT product_id
+	FROM product
+	EXCEPT
+	SELECT product_id
+	FROM customer_purchases
+) x
+JOIN product p on x.product_id = p.product_id;
+
+-- sold products that are not in the products table ... not possible
+-- NOTHING 
+SELECT product_id
+FROM customer_purchases
+EXCEPT
+SELECT product_id
+FROM product
diff --git a/04_this_cohort/live_code/module_4/NTILE.sql b/04_this_cohort/live_code/module_4/NTILE.sql
@@ -0,0 +1,28 @@
+--ntile 4, 5, 100
+
+--make quartiles, qunitiles, percentiles
+
+SELECT * 
+--,NTILE(4) OVER (PARTITION BY vendor_name ORDER BY sales) as quartiles
+--,NTILE(5) OVER (PARTITION BY vendor_name ORDER BY sales) as quantiles
+--,NTILE(100) OVER (PARTITION BY vendor_name ORDER BY sales) as percentile
+
+,NTILE(4) OVER (PARTITION BY vendor_name,product_id ORDER BY sales) 
+
+FROM (
+	SELECT 
+	md.market_date
+	,market_day
+	,market_week
+	,vendor_name
+	,product_id
+	,sum(quantity*cost_to_customer_per_qty) as sales
+
+	FROM market_date_info md
+	JOIN customer_purchases cp
+		ON md.market_date = cp.market_date
+	JOIN vendor v
+		ON cp.vendor_id = v.vendor_id
+
+	GROUP By md.market_date, v.vendor_id
+) x
diff --git a/04_this_cohort/live_code/module_4/ROW_NUMBER.sql b/04_this_cohort/live_code/module_4/ROW_NUMBER.sql
@@ -0,0 +1,39 @@
+--ROW_NUMBER 
+--what product is the highest price per vendor
+
+--outer QUERY
+
+SELECT x.*,product_name
+
+FROM (
+--inner QUERY
+	SELECT 
+	vendor_id,
+	market_date,
+	product_id,
+	original_price,
+	ROW_NUMBER() OVER(PARTITION BY vendor_id ORDER BY original_price DESC) as price_rank
+
+	FROM vendor_inventory
+) x
+INNER JOIN product p 
+	ON x.product_id = p.product_id
+
+WHERE price_rank = 1;
+
+--highest single purchase in a day PER customer 
+
+SELECT * 
+FROM (
+	SELECT 
+	customer_id
+	,product_id
+	,market_date
+	,quantity
+	,quantity*cost_to_customer_per_qty as cost
+	,ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY quantity*cost_to_customer_per_qty DESC) as sales_rank
+
+	FROM customer_purchases
+) x
+WHERE sales_rank = 1
+ORDER BY cost DESC
diff --git a/04_this_cohort/live_code/module_4/UNION_UNION_ALL.sql b/04_this_cohort/live_code/module_4/UNION_UNION_ALL.sql
@@ -0,0 +1,31 @@
+--UNION/UNION ALL
+
+--most and least expensive product per vendor with a UNION
+
+SELECT vendor_id, product_id, original_price, rn_max as [row_number]
+FROM 
+(
+	SELECT DISTINCT
+	vendor_id
+	,product_id
+	,original_price
+	,row_number() OVER(PARTITION BY vendor_id ORDER BY original_price DESC) as rn_max
+
+	FROM vendor_inventory
+) 
+where rn_max = 1
+
+UNION  -- union returned 5 rows...UNION all returned 6 rows (vendor #4 duplicated)
+
+SELECT *
+FROM 
+(
+	SELECT DISTINCT
+	vendor_id
+	,product_id
+	,original_price
+	,ROW_NUMBER() OVER(PARTITION BY vendor_id ORDER BY original_price ASC) as rn_min
+
+	FROM vendor_inventory
+) 
+where rn_min = 1
diff --git a/04_this_cohort/live_code/module_4/budget_coalesece_NULLIF.sql b/04_this_cohort/live_code/module_4/budget_coalesece_NULLIF.sql
@@ -0,0 +1,24 @@
+
+-- create a budge temp table
+DROP TABLE IF EXISTS temp.budgets; 
+
+-- here i am specifying the column types, this was asked, so budget is a string, current year is an integer, prev year also int
+CREATE TEMP TABLE IF NOT EXISTS temp.budgets (budget STRING, current_year INT, previous_year INT); 
+
+
+--nothing is yet in budget
+INSERT INTO temp.budgets
+
+-- so put as row 1
+VALUES ('software',1000,1000)
+--and row 2
+, ('candles',300,500);
+
+--show me the average difference in years
+--NULLIF, if the numbers are the same, then NULL
+--COALESCE, if the result is NULL then 0.00
+--average across the values = change in years
+SELECT  AVG(COALESCE(NULLIF(current_year, previous_year), 0.00)) 
+FROM budgets
+
+--result(300 [current year for candles] +0 / 2 [two rows] = 150.0)
diff --git a/04_this_cohort/live_code/module_4/row_rank_dense.sql b/04_this_cohort/live_code/module_4/row_rank_dense.sql
@@ -0,0 +1,28 @@
+-- dense_rank, rank, row_number
+
+DROP TABLE IF EXISTS temp.row_rank_dense;
+
+CREATE TEMP TABLE IF NOT EXISTS temp.row_rank_dense
+(
+emp_id INT,
+salary INT
+);
+
+INSERT INTO temp.row_rank_dense
+VALUES(1,200000),
+        (2,200000),
+        (3, 160000),
+	(4, 120000),
+	(5, 125000),
+	(6, 165000),
+	(7, 230000),
+	(8, 100000),
+	(9, 165000),
+	(10, 100000);
+
+SELECT *
+,row_number() OVER(ORDER BY salary desc) as [row_number]
+,rank() OVER(ORDER BY salary desc) as [rank]
+,dense_rank() OVER(ORDER BY salary desc) as [dense_rank]
+
+FROM row_rank_dense
diff --git a/04_this_cohort/live_code/module_4/string_manipulations.sql b/04_this_cohort/live_code/module_4/string_manipulations.sql
@@ -0,0 +1,35 @@
+--string manipulations
+
+SELECT  DISTINCT
+
+LTRIM('                   THOMAS ROSENTHAL            ') 
+,RTRIM('                   THOMAS ROSENTHAL            ')
+,TRIM('                   THOMAS ROSENTHAL            ') 
+
+,REPLACE('THOMAS ROSENTHAL', ' ', ' WILLIAM ') -- adds my middle name with spaces on both sides
+,REPLACE('THOMAS ROSENTHAL','A','') 
+,REPLACE('THOMAS ROSENTHAL','a','') 
+--,REPLACE(customer_first_name,'a','')
+
+,'THOMAS
+
+ROSENTHAL'
+
+,replace('THOMAS
+
+ROSENTHAL', char(10), ' ') -- removing all instances of line breaks (char(10)) from this string
+
+FROM customer;
+
+-- upper / lower
+
+SELECT DISTINCT
+UPPER(customer_first_name)
+,LOWER(customer_first_name)
+,customer_first_name || ' ' || customer_last_name as customer_name
+,UPPER(customer_first_name) || ' ' || UPPER(customer_last_name) as upper_full_name
+, '' || 'thomas'
+
+FROM customer
+
+WHERE customer_first_name REGEXP '(a)$' -- filtering to only ending in a has to be valid regex
diff --git a/04_this_cohort/live_code/module_5/CROSS_JOIN.sql b/04_this_cohort/live_code/module_5/CROSS_JOIN.sql
@@ -0,0 +1,14 @@
+-- CROSS JOIN
+DROP TABLE IF EXISTS temp.sizes;
+CREATE TEMP TABLE IF NOT EXISTS temp.sizes (size TEXT);
+
+INSERT INTO temp.sizes
+VALUES('small'),
+('medium'),
+('large');
+
+SELECT * FROM temp.sizes;
+
+SELECT product_name, size
+FROM product
+CROSS JOIN temp.sizes
diff --git a/04_this_cohort/live_code/module_5/INSERT_UPDATE_DELETE.sql b/04_this_cohort/live_code/module_5/INSERT_UPDATE_DELETE.sql
@@ -0,0 +1,29 @@
+-- INSERT UPDATE DELETE
+
+-- 1) add a product to the temp TABLE
+-- 2) change the product_size for THAT product
+-- 3) delete our product
+
+DROP TABLE IF EXISTS temp.product_expanded;
+CREATE TEMP TABLE product_expanded AS
+	SELECT * FROM product;
+
+--SELECT * FROM product_expanded
+
+--INSERT
+INSERT INTO product_expanded
+VALUES(24, 'Almonds', '1 lbs', 1, 'lbs');
+
+--UPDATE
+--change the product_size for almonds to 1/2 kg
+UPDATE product_expanded
+SET product_size = '1/2 kg', product_qty_type = 'kg'
+WHERE product_id = 24;
+
+--DELETE 
+DELETE FROM product_expanded
+--SELECT * FROM product_expanded -- can help you determine you are looking at the right rows before delete
+WHERE product_id = 24;
+
+
+SELECT * FROM product_expanded
Original file line number	Diff line number	Diff line change
Expand Up		@@ -59,9 +59,6 @@ Hint: you might need to use INSTR(product_name,'-') to find the hyphens. INSTR w



		/* 2. Filter the query to show any product_size value that contain a number with REGEXP. */



		-- UNION
		/* 1. Using a UNION, write a query that displays the market dates with the highest and lowest total sales.
Expand Down