Skip to content

Update Assignment1.md #1

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
Hanstke wants to merge 3 commits into
base: main
Choose a base branch
from
Open

Update Assignment1.md #1

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
3 commits
Select commit Hold shift + click to select a range
bd1ee92
Update Assignment1.md
Hanstke Apr 6, 2026
295a039
Final draft of assignment 1
Hanstke Apr 7, 2026
a00f004
Merge branch 'assignment-one' of https://github.com/Hanstke/sql into …
Hanstke Apr 7, 2026
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
15 changes: 15 additions & 0 deletions 02_activities/assignments/DC_Cohort/Assignment1.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -210,4 +210,19 @@ Consider, for example, concepts of fariness, inequality, social structures, marg

```
Your thoughts...
Reading the linked article reminded me of many times that I have run up against similar issues even in supposedly less rigid data systems employed in North America. For example,
in accessing medical services, there have been a number of times that I have been required to provide an emergency contact; despite the fact that I have not always had an appropriate
person to designate as this contact, the system nonetheless forces me to provide the name and phone number of someone, often ending up being a person living in another province or
otherwise unable to intervene if I ever experienced a real medical emergency. In this way, the requirement for an input value overrides its actual intended functionality, and thereby
is indicative of an incorrect assumption about people being logged in these medical data systems.

This has also made me think about current debates around required age-verification systems for social media platforms. On the one hand, such systems are theorized to help protect
children from the increasingly evident developmental harms of childhood social media usage; yet, on the other hand, these systems also have the potential to store sensitive information
about 'verified' users that could be used unethically in the future. For example, I recall a recent news story about a women-only app that was designed to allow women to share
stories about abusive ex-partners and protect other women from being harmed by the same people. Use of the app required verification that the users were female, including photos
of sensitive information like government IDs. There was little transparency about how this information was being stored. The app was ultimately hacked, and this sensitive
information about many of the apps' users was made widely available online, making the users potential targets for abuse by those who had vehemently criticized the ethics of the app itself.
Regardless of what one thinks about the design and function of that particular app, the event of it being hacked demonstrates the importance of ethics in the storing and handling of
sensitive data, and the importance for users to be conscious of the increasing proliferation of data systems in our daily lives, as well as what the implications are for each one that is in
possession of our sensitive data.
```
Binary file added 02_activities/assignments/DC_Cohort/Entity-Relationship Diagram.png
View file
Open in desktop
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
96 changes: 55 additions & 41 deletions 02_activities/assignments/DC_Cohort/assignment1.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -7,7 +7,7 @@
/* 1. Write a query that returns everything in the customer table. */
--QUERY 1


SELECT * FROM customer;


--END QUERY
Expand All @@ -17,8 +17,10 @@
sorted by customer_last_name, then customer_first_ name. */
--QUERY 2



SELECT *
FROM customer
ORDER BY customer_last_name,customer_first_name
LIMIT 10;

--END QUERY

Expand All @@ -28,23 +30,29 @@ sorted by customer_last_name, then customer_first_ name. */
Limit to 25 rows of output. */
--QUERY 3


SELECT *
FROM customer_purchases
WHERE product_id IN (4,9)
LIMIT 25;


--END QUERY



/*2. Write a query that returns all customer purchases and a new calculated column 'price' (quantity * cost_to_customer_per_qty),
/* 2. Write a query that returns all customer purchases and a new calculated column 'price' (quantity * cost_to_customer_per_qty),
filtered by customer IDs between 8 and 10 (inclusive) using either:
1. two conditions using AND
2. one condition using BETWEEN
Limit to 25 rows of output.
*/
--QUERY 4



SELECT *
,CASE WHEN quantity > 0.00
THEN quantity*cost_to_customer_per_qty
END as price
FROM customer_purchases
WHERE customer_id BETWEEN 8 AND 10
LIMIT 25;

--END QUERY

Expand All @@ -56,6 +64,10 @@ columns and add a column called prod_qty_type_condensed that displays the word
if the product_qty_type is “unit,” and otherwise displays the word “bulk.” */
--QUERY 5

SELECT product_id,product_name
,CASE WHEN product_qty_type = 'unit' THEN 'unit'
ELSE 'bulk'
END as prod_qty_type_condensed



Expand All @@ -67,8 +79,12 @@ add a column to the previous query called pepper_flag that outputs a 1 if the pr
contains the word “pepper” (regardless of capitalization), and otherwise outputs 0. */
--QUERY 6

,CASE WHEN product_name LIKE '%pepper%'
THEN '1'
ELSE '0'
END as pepper_flag


FROM product;

--END QUERY

Expand All @@ -79,7 +95,13 @@ vendor_id field they both have in common, and sorts the result by market_date, t
Limit to 24 rows of output. */
--QUERY 7

SELECT *

FROM vendor
INNER JOIN vendor_booth_assignments
ON vendor_booth_assignments.vendor_id = vendor.vendor_id
ORDER BY market_date,vendor_name
LIMIT 24;


--END QUERY
Expand All @@ -93,8 +115,9 @@ Limit to 24 rows of output. */
at the farmer’s market by counting the vendor booth assignments per vendor_id. */
--QUERY 8



SELECT vendor_id, COUNT(market_date) as num_of_rentals
FROM vendor_booth_assignments
GROUP BY vendor_id;

--END QUERY

Expand All @@ -106,8 +129,17 @@ 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. */
--QUERY 9

SELECT
customer_last_name
,customer_first_name
,SUM(quantity*cost_to_customer_per_qty) as total_spend

FROM customer_purchases as cp
INNER JOIN customer as c
ON c.customer_id = cp.customer_id

GROUP BY c.customer_last_name, customer_first_name
HAVING total_spend > 2000;

--END QUERY

Expand All @@ -125,35 +157,17 @@ VALUES(col1,col2,col3,col4,col5)
*/
--QUERY 10

DROP TABLE IF EXISTS temp.new_vendor;

CREATE TEMPORARY TABLE temp.new_vendor AS

SELECT
vendor_id
,vendor_name
,vendor_type
,vendor_owner_first_name
,vendor_owner_last_name
FROM vendor;

--END QUERY


-- 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!
Limit to 25 rows of output. */
--QUERY 11




--END QUERY


/* 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...
AND be sure you remove the LIMIT from the previous query before aggregating!! */
--QUERY 12




--END QUERY
INSERT INTO temp.new_vendor
VALUES ('10', 'Thomas Superfood Store', 'Fresh Focused', 'Thomas', 'Rosenthal');
Loading