jtfb · jtfb · Sep 11, 2024 · Sep 12, 2024 · Sep 12, 2024 · Sep 12, 2024
diff --git a/02_activities/homework/homework_1.jpg b/02_activities/homework/homework_1.jpg
diff --git a/02_activities/homework/homework_2.sql b/02_activities/homework/homework_2.sql
@@ -1,42 +1,74 @@
 --SELECT
 /* 1. Write a query that returns everything in the customer table. */
-
-
+SELECT *
+FROM customer
 
 /* 2. Write a query that displays all of the columns and 10 rows from the cus- tomer table, 
 sorted by customer_last_name, then customer_first_ name. */
-
-
-
+SELECT *
+FROM customer
+ORDER BY customer_last_name, customer_first_name
+LIMIT 10
 
 --WHERE
 /* 1. Write a query that returns all customer purchases of product IDs 4 and 9. */
 -- option 1
-
+SELECT *
+FROM customer_purchases
+WHERE customer_id IN (4,9)
 -- option 2
+SELECT *
+FROM customer_purchases
+WHERE customer_id = 4 OR customer_id = 9
 
 /*2. Write a query that returns all customer purchases and a new calculated column 'price' (quantity * cost_to_customer_per_qty), 
 filtered by vendor IDs between 8 and 10 (inclusive) using either:
 	1.  two conditions using AND
 	2.  one condition using BETWEEN
 */
 -- option 1
-
+SELECT *, (quantity * cost_to_customer_per_qty) AS new_column
+FROM customer_purchases
+WHERE vendor_id >= 8
+   AND vendor_id <= 10
 
 -- option 2
+SELECT *, (quantity * cost_to_customer_per_qty) AS new_column
+FROM customer_purchases
+WHERE vendor_id BETWEEN 8 AND 10
 
 --CASE
 /* 1. Products can be sold by the individual unit or by bulk measures like lbs. or oz. 
 Using the product table, write a query that outputs the product_id and product_name
 columns and add a column called prod_qty_type_condensed that displays the word “unit” 
 if the product_qty_type is “unit,” and otherwise displays the word “bulk.” */
-
+SELECT *,
+CASE WHEN product_qty_type = 'unit'
+ THEN 'unit'
+ ELSE 'bulk'
+ END as prod_qty_type_condensed
+ FROM product
 
 /* 2. We want to flag all of the different types of pepper products that are sold at the market. 
 add a column to the previous query called pepper_flag that outputs a 1 if the product_name 
 contains the word “pepper” (regardless of capitalization), and otherwise outputs 0. */
-
+SELECT *,
+CASE WHEN product_qty_type = 'unit'
+ THEN 'unit'
+ ELSE 'bulk'
+ END as prod_qty_type_condensed
+
+,CASE WHEN product_name LIKE '%pepper%'
+  THEN 1
+  ELSE 0
+  END as pepper_flag
+FROM product
 
 --JOIN
 /* 1. Write a query that INNER JOINs the vendor table to the vendor_booth_assignments table on the 
 vendor_id field they both have in common, and sorts the result by vendor_name, then market_date. */
+SELECT DISTINCT *
+FROM vendor as v
+INNER JOIN vendor_booth_assignments as vba
+  ON v.vendor_id = vba.vendor_id
+ORDER BY vendor_name, market_date
diff --git a/02_activities/homework/homework_3.sql b/02_activities/homework/homework_3.sql
@@ -1,15 +1,26 @@
 -- AGGREGATE
 /* 1. Write a query that determines how many times each vendor has rented a booth 
 at the farmer’s market by counting the vendor booth assignments per vendor_id. */
-
+SELECT vendor_id, count(vendor_id)
+FROM vendor_booth_assignments
+GROUP BY vendor_id
 
 
 /* 2. The Farmer’s Market Customer Appreciation Committee wants to give a bumper 
 sticker to everyone who has ever spent more than $2000 at the market. Write a query that generates a list 
 of customers for them to give stickers to, sorted by last name, then first name. 
 
 HINT: This query requires you to join two tables, use an aggregate function, and use the HAVING keyword. */
+SELECT c.customer_last_name, c.customer_first_name, total_spent
+
+FROM customer as c 
+INNER JOIN (
+		SELECT customer_id, sum(quantity*cost_to_customer_per_qty) as total_spent
 
+		FROM customer_purchases as cp
+		GROUP by customer_id
+		HAVING total_spent > 2000
+) x ON c.customer_id = x.customer_id
 
 
 --Temp Table
@@ -23,18 +34,41 @@ When inserting the new vendor, you need to appropriately align the columns to be
 -> To insert the new row use VALUES, specifying the value you want for each column:
 VALUES(col1,col2,col3,col4,col5) 
 */
+DROP TABLE IF EXISTS new_vendor;
+
+CREATE TEMP TABLE new_vendor AS
+
+SELECT * FROM vendor;
 
+INSERT INTO new_vendor
+VALUES('10','Thomass Superfood Store', 'Fresh Focused', 'Thomas', 'Rosenthal');
+
+SELECT * FROM new_vendor;
 
 
 -- Date
 /*1. Get the customer_id, month, and year (in separate columns) of every purchase in the customer_purchases table.
 
 HINT: you might need to search for strfrtime modifers sqlite on the web to know what the modifers for month 
 and year are! */
+SELECT customer_id
+,strftime ('%m', market_date) as month
+,strftime ('%Y', market_date) as year
+FROM customer_purchases
+
 
-/* 2. Using the previous query as a base, determine how much money each customer spent in April 2019. 
+/* 2. Using the previous query as a base, determine how much money each customer spent in April 2022. 
 Remember that money spent is quantity*cost_to_customer_per_qty. 
 
 HINTS: you will need to AGGREGATE, GROUP BY, and filter...
 but remember, STRFTIME returns a STRING for your WHERE statement!! */
 
+SELECT customer_id
+,strftime ('%m', market_date) as month
+,strftime ('%Y', market_date) as year
+,SUM(quantity *cost_to_customer_per_qty) as money_spent
+FROM customer_purchases
+
+WHERE month='04'
+GROUP BY customer_id
+HAVING year='2022'
diff --git a/02_activities/homework/homework_6.md b/02_activities/homework/homework_6.md
@@ -6,3 +6,10 @@
 <br>
 
 **Write**: Reflect on your previous work and how you would adjust to include ethics and inequity components. Total length should be a few paragraphs, no more than one page.
+
+
+Reflecting on how to adjust to include ethics and inequality components, data is powerful and can shape models that influence society. When not handled carefully, these models can continue to support social inequalities, benefiting only those with more power or wealth. It’s essential to ensure transparency and involve people from diverse backgrounds such as different nationalities, social classes, genders, and religions. So we can create fairer systems. Privacy and transparency should also be a must when working with anything.
+
+I've worked many years in the film industry, and I saw something that highlights this need for inclusion. As a color specialist, I learned that early photography didn’t properly capture darker skin tones, focusing only on white subjects. Guess why? The technology improved over time, but not because of a moral obligation to represent everyone equally. It improved because companies wanted to display different shades of CHOCOLATE in advertisements. This example shows that unless fairness and inclusivity are considered from the start, some people or groups will be left out.
+
+We are all living in a information-driven world where data can be accessed and analyzed quickly, and from anywhere. We have the opportunity to use this on our advantage and bring diversity into every project and decision we make. It’s important to actively think about who might be affected or excluded by the data we work with, and to strive for fairness and privacy for everyone. By being conscious of these factors, we can work toward a more ethical and inclusive future for all of us. We won't be here for ever, so let's leave a better world than we found it.
diff --git a/05_src/sql/farmersmarket.db b/05_src/sql/farmersmarket.db