pala2003 · pala2003 · Apr 19, 2025 · Apr 16, 2025 · Apr 17, 2025 · Apr 17, 2025
diff --git a/02_activities/assignments/Assignment1.md b/02_activities/assignments/Assignment1.md
@@ -61,7 +61,7 @@ There are 10 tables in the Main Window:
 7) vendor
 8) vendor_booth_assignments
 9) vendor_inventory
-10) zip_data
+10) postal_data
 
 Switch to the Browse Data tab, booth is selected by default
 

diff --git a/...es/rsconnect/documents/self_2.Rmd/shinyapps.io/mrpotatocode/Self-Assessment-Session-2.dcf b/...es/rsconnect/documents/self_2.Rmd/shinyapps.io/mrpotatocode/Self-Assessment-Session-2.dcf
@@ -5,7 +5,7 @@ account: mrpotatocode
 server: shinyapps.io
 hostUrl: https://api.shinyapps.io/v1
 appId: 14519703
-bundleId: 10114269
+bundleId: 10132601
 url: https://mrpotatocode.shinyapps.io/Self-Assessment-Session-2/
 version: 1
 asMultiple: FALSE

diff --git a/03_instructional_team/interactive_quizzes/self_2.Rmd b/03_instructional_team/interactive_quizzes/self_2.Rmd
@@ -75,7 +75,7 @@ quiz(
   question("Which of the following WHERE clauses correctly filters results to show employees with a salary between $92,000 and $117,500?",
   answer("`...WHERE Salary BETWEEN 92000 AND 117500`", correct = TRUE),
   answer("`...WHERE Salary > 92000 AND Salary < 117500`"),
-  answer("`...WHERE Salary >= 92000 AND Salary =< 117500 `", correct = TRUE),
+  answer("`...WHERE Salary >= 92000 AND Salary <= 117500`", correct = TRUE),
   answer("`...WHERE Salary IN (92000, 117500)`")
   ,allow_retry = TRUE
   ,random_answer_order = TRUE

diff --git a/03_instructional_team/interactive_quizzes/self_2.html b/03_instructional_team/interactive_quizzes/self_2.html
diff --git a/04_this_cohort/custom_slides/markdown/slides_01.Rmd b/04_this_cohort/custom_slides/markdown/slides_01.Rmd
@@ -837,7 +837,7 @@ class: top, left, inverse
       - *orders* refer to *products* that exist
   - Deleted records don't make data elsewhere meaningless
       - *customers* who have had *orders* can't be deleted without deleting the orders first
-      - *orders* referring to *products* can't be deleted without deleting the products first
+      - *products* associated with *orders* can't be deleted without deleting the orders first
 - The PK-FK relationship can sometimes feel a bit backwards: by establishing an FK we are determining what is allowed to happen!
 
 .pull-left.w33[
@@ -1331,7 +1331,7 @@ Please do not pick the exact same tables that I have already diagrammed. For exa
 
 *HINTS*:
 - You will need to use the Browse Data tab in the main window to figure out the relationship types.
-- You can't diagram tables that don't share a common column (see )
+- You can't diagram tables that don't share a common column (see conceptual model)
 - The column names can be found in a few spots (DB Schema window in the bottom right, the Database Structure tab in the main window by expanding each table entry, at the top of the Browse Data tab in the main window)
 ---
 

diff --git a/04_this_cohort/custom_slides/markdown/slides_01.html b/04_this_cohort/custom_slides/markdown/slides_01.html
@@ -825,7 +825,7 @@
       - *orders* refer to *products* that exist
   - Deleted records don't make data elsewhere meaningless
       - *customers* who have had *orders* can't be deleted without deleting the orders first
-      - *orders* referring to *products* can't be deleted without deleting the products first
+      - *products* associated with *orders* can't be deleted without deleting the orders first
 - The PK-FK relationship can sometimes feel a bit backwards: by establishing an FK we are determining what is allowed to happen!
 
 .pull-left.w33[
@@ -1319,7 +1319,7 @@
 
 *HINTS*:
 - You will need to use the Browse Data tab in the main window to figure out the relationship types.
-- You can't diagram tables that don't share a common column (see )
+- You can't diagram tables that don't share a common column (see conceptual model)
 - The column names can be found in a few spots (DB Schema window in the bottom right, the Database Structure tab in the main window by expanding each table entry, at the top of the Browse Data tab in the main window)
 ---
 

diff --git a/04_this_cohort/custom_slides/pdf/slides_01.pdf b/04_this_cohort/custom_slides/pdf/slides_01.pdf
diff --git a/04_this_cohort/live_code/module_2/CASE.sql b/04_this_cohort/live_code/module_2/CASE.sql
@@ -0,0 +1,16 @@
+--CASE
+
+SELECT * 
+,CASE WHEN vendor_type = 'Fresh Focused' THEN 'Wednesday'
+WHEN vendor_type = 'Prepared Foods'
+	THEN 'Thursday'
+ELSE 'Saturday'
+END as day_of_specialty
+--pie day, otherwise nothing
+,CASE WHEN vendor_name LIKE '%pie%'
+	THEN 'Wednesday'
+	END as only_pie_day
+,CASE WHEN vendor_name = "Annie's Pies"  -- double quote work, but not for all editors!
+	THEN 'annie is best'
+	END as annie_is_king
+FROM vendor
diff --git a/04_this_cohort/live_code/module_2/DISTINCT.sql b/04_this_cohort/live_code/module_2/DISTINCT.sql
@@ -0,0 +1,31 @@
+--DISTINCT
+
+-- without distinct, 4221 rows, of various customer_ids 
+SELECT customer_id FROM customer_purchases;
+
+-- with DISTINCT, one row per customer, 26 rows total
+SELECT DISTINCT customer_id FROM customer_purchases;
+
+-- without distinct, only wed/sat 150 times over
+SELECT market_day
+FROM market_date_info;
+
+-- market is open sat/wed!
+SELECT DISTINCT market_day
+FROM market_date_info;
+
+/* which vendor has sold products to a customer */
+SELECT DISTINCT vendor_id
+FROM customer_purchases;  -- 3 rows, vendor_id 7,8,4
+
+
+
+/* which vendor has sold products to a customer AND which product was it... */
+SELECT DISTINCT vendor_id, product_id
+FROM customer_purchases; -- 8 rows
+
+/* which vendor has sold products to a customer AND which product was it AND to whom was it sold ? */
+SELECT DISTINCT vendor_id, product_id, customer_id -- this is dissatisfying...let's have a name!
+FROM customer_purchases -- 200 rows
+ORDER by product_id ASC, customer_id DESC
+
diff --git a/04_this_cohort/live_code/module_2/DSI, supplemental joins.xlsx b/04_this_cohort/live_code/module_2/DSI, supplemental joins.xlsx
diff --git a/04_this_cohort/live_code/module_2/INNER_JOIN.sql b/04_this_cohort/live_code/module_2/INNER_JOIN.sql
@@ -0,0 +1,25 @@
+-- INNER JOIN
+-- no alias INNER JOIN
+
+/* get product names alongside customer_purchases...only products that a customer has purchased will be present */
+SELECT 
+product_name, -- coming from product
+vendor_id, -- coming from cp...below
+market_date,
+customer_id,
+customer_purchases.product_id
+
+FROM customer_purchases
+INNER JOIN product
+	ON customer_purchases.product_id = product.product_id;
+
+/* which vendor has sold products to a customer AND which product was it AND to whom was it sold ? */
+SELECT DISTINCT vendor_id, 
+product_id, 
+--customer_id -- this is dissatisfying...let's have a name!
+customer_first_name,
+customer_last_name
+
+FROM customer_purchases as cp
+INNER JOIN customer as c
+	ON cp.customer_id = c.customer_id
diff --git a/04_this_cohort/live_code/module_2/LEFT_JOIN.sql b/04_this_cohort/live_code/module_2/LEFT_JOIN.sql
@@ -0,0 +1,32 @@
+--LEFT JOIN
+
+-- there are products that have been bought, but are there products that have not?
+
+SELECT 
+p.product_id, cp.product_id,
+product_name -- all columns in cp table
+
+FROM product p
+LEFT JOIN customer_purchases cp
+	ON p.product_id = cp.product_id;
+
+SELECT DISTINCT
+p.product_id, product_name, cp.product_id
+
+FROM product p
+LEFT JOIN customer_purchases cp
+	ON p.product_id = cp.product_id
+--WHERE cp.product_id IS NULL -- what products have been NOT been sold 
+
+-- directions matter!
+-- only products that have been sold...because there are no product ids in cp that arent in p
+SELECT DISTINCT
+p.product_id, product_name, cp.product_id
+
+FROM customer_purchases cp
+LEFT JOIN product p
+	ON p.product_id = cp.product_id
+
+
+
+
diff --git a/04_this_cohort/live_code/module_2/SELECT.sql b/04_this_cohort/live_code/module_2/SELECT.sql
@@ -0,0 +1,15 @@
+-- SELECT 
+
+--selecting everything from customer
+SELECT * 
+FROM customer;
+
+-- add a static value
+SELECT 2025 as this_year, 'April' as this_month, customer_id
+FROM customer;
+
+-- add an order by + limit
+SELECT * 
+FROM customer
+ORDER BY customer_last_name
+LIMIT 10
diff --git a/04_this_cohort/live_code/module_2/WHERE.sql b/04_this_cohort/live_code/module_2/WHERE.sql
@@ -0,0 +1,30 @@
+-- WHERE
+
+SELECT * FROM customer
+WHERE customer_id = 1
+OR customer_id = 2; -- 1 or 2
+--AND customer_id = 2 -- returns nothing
+
+--IN
+SELECT * FROM customer_purchases
+WHERE customer_id IN (3,4,5) -- only customers 3,4,5 
+AND vendor_id IN (3,4,5);
+
+--LIKE
+-- all of the peppers
+SELECT * FROM product
+WHERE product_name LIKE '%pepper%';
+
+-- customers with a last name starting with a
+SELECT * FROM customer
+WHERE customer_last_name LIKE 'a%';
+
+--NULLs and Blanks
+SELECT * FROM product
+WHERE product_size IS NULL
+OR product_size = '' -- two single quotes, "blank" -- different than null
+
+
+
+
+
diff --git a/04_this_cohort/live_code/module_2/multiple_table_joins.sql b/04_this_cohort/live_code/module_2/multiple_table_joins.sql
@@ -0,0 +1,41 @@
+--multiple table JOINs 
+
+/* which vendor has sold products to a customer AND which product was it AND to whom was it sold ? */
+SELECT DISTINCT 
+--vendor_id, 
+vendor_name,
+--product_id, 
+product_name,
+--customer_id -- this is dissatisfying...let's have a name!
+customer_first_name,
+customer_last_name
+
+FROM customer_purchases as cp
+INNER JOIN customer as c
+	ON cp.customer_id = c.customer_id
+INNER JOIN vendor as v
+	ON cp.vendor_id = v.vendor_id
+INNER JOIN product as p
+	ON cp.product_id = p.product_id;
+
+/* what if we add the dates they were purchased
+which vendor has sold products to a customer AND which product was it AND to whom was it sold ? */
+SELECT DISTINCT 
+market_date,
+--vendor_id, 
+vendor_name,
+--product_id, 
+product_name,
+--customer_id -- this is dissatisfying...let's have a name!
+customer_first_name,
+customer_last_name
+
+FROM customer_purchases as cp
+INNER JOIN customer as c
+	ON cp.customer_id = c.customer_id
+INNER JOIN vendor as v
+	ON cp.vendor_id = v.vendor_id
+INNER JOIN product as p
+	ON cp.product_id = p.product_id
+
+ORDER BY customer_first_name, customer_last_name, product_name, market_date
diff --git a/04_this_cohort/live_code/module_3/COUNT.sql b/04_this_cohort/live_code/module_3/COUNT.sql
@@ -0,0 +1,25 @@
+-- COUNT
+
+--count the number of products
+
+SELECT COUNT(product_id) as num_of_prod
+FROM product;
+
+-- how many products PER product_qty_type
+SELECT product_qty_type, COUNT(product_id) as num_of_prod
+FROM product
+GROUP BY product_qty_type;
+
+--how many products PER product_qty_type and PER their product_size
+SELECT product_size, product_qty_type, COUNT(product_id) as num_of_prod
+FROM product
+GROUP BY product_size, product_qty_type;
+
+--count DISTINCT
+--how many UNIQUE products were bought
+
+SELECT count(DISTINCT  product_id) as bought_prods
+FROM customer_purchases;
+
+
+
diff --git a/04_this_cohort/live_code/module_3/CTE.sql b/04_this_cohort/live_code/module_3/CTE.sql
@@ -0,0 +1,31 @@
+-- CTE
+
+-- calculate sales per vendor per day
+WITH vendor_daily_sales AS (
+	SELECT 
+	md.market_date,
+	market_day,
+	market_week,
+	market_year,
+	vendor_name,
+	SUM(quantity*cost_to_customer_per_qty) as sales
+
+	FROM customer_purchases cp
+	INNER JOIN vendor v -- we want the vendor_name 
+		ON v.vendor_id = cp.vendor_id
+	INNER JOIN market_date_info md -- we want the market_day, market_week, market_year
+		ON cp.market_date = md.market_date
+
+	GROUP by md.market_date, v.vendor_id
+)
+
+-- re-aggregate the daily sales for each WEEK instead
+
+SELECT 
+market_year,
+market_week,
+vendor_name,
+sum(sales)
+
+FROM vendor_daily_sales
+GROUP BY market_year, market_week, vendor_name
diff --git a/04_this_cohort/live_code/module_3/HAVING.sql b/04_this_cohort/live_code/module_3/HAVING.sql
@@ -0,0 +1,23 @@
+-- HAVING
+
+-- how much did a customer spend on each day
+SELECT 
+market_date,
+customer_id,
+SUM(quantity*cost_to_customer_per_qty) as total_cost
+
+FROM customer_purchases
+WHERE customer_id BETWEEN 1 AND 5 -- filtering the non-aggregated values
+OR customer_id = 10
+GROUP BY market_date, customer_id
+HAVING total_cost > 50; -- filtering the aggregated values
+
+-- how many products were bought
+
+SELECT count(product_id) as num_of_prod, product_id
+
+FROM customer_purchases
+WHERE product_id <= 8
+GROUP BY product_id
+HAVING count(product_id) BETWEEN 300 AND 500
+
diff --git a/04_this_cohort/live_code/module_3/MIN_MAX.sql b/04_this_cohort/live_code/module_3/MIN_MAX.sql
@@ -0,0 +1,38 @@
+-- MIN & MAX 
+
+-- what is the most expensive product
+
+SELECT 
+product_name, max(original_price) as most_expensive -- doesnt handle ties well
+
+FROM product p 
+INNER JOIN vendor_inventory vi
+	ON p.product_id = vi.product_id;
+
+-- minimum price per each product_qty_type
+SELECT DISTINCT
+product_name, product_qty_type,
+--original_price
+min(original_price)
+
+FROM product p 
+INNER JOIN vendor_inventory vi
+	ON p.product_id = vi.product_id
+GROUP BY product_qty_type	
+
+order by product_qty_type, original_price;
+
+
+--PROVE IT!
+
+-- minimum price per each product_qty_type
+SELECT DISTINCT
+product_name, product_qty_type,
+original_price
+
+
+FROM product p 
+INNER JOIN vendor_inventory vi
+	ON p.product_id = vi.product_id	
+
+order by product_qty_type, original_price