Skip to content

adding assignment 1 files #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
ecervinka wants to merge 1 commit into
base: main
Choose a base branch
from
Open

adding assignment 1 files #1

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
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
13 changes: 11 additions & 2 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 @@ -106,6 +106,8 @@ Please do not pick the exact same tables that I have already diagrammed. For exa
- <img src="./images/01_farmers_market_conceptual_model.png" width="600">
- 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)

![My Image](images/Cervinka_FarmersMarket_Diagram_SQL.jpg)

***

## Section 2:
Expand Down Expand Up @@ -175,7 +177,7 @@ To insert the new row use VALUES, specifying the value you want for each column:

<div align="center">-</div>

#### Date
#### Date - not required for submission
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!
Expand Down Expand Up @@ -209,5 +211,12 @@ Consider, for example, concepts of fariness, inequality, social structures, marg


```
Your thoughts...
Reading this article prompted me to think about a recent experience I had flying back from Columbus, Ohio after a conference, which solidified some of the concerns I have about the ethics and deployment of new digital systems. Due to the TSA shutdown, among other operational issues, security lines were massive, and CLEAR employees were circulating through the crowd offering travellers a way to bypass the queue. Their pitch implied that providing some personal information and a facial scan, for free, would ease the delay, however, at the final stage of sign-up, the employees revealed that the service would cost $200. This encounter reminded me of how popular these sorts of convenience bypasses have become, where individuals can pay to skip a line, receive priority service (e.g., food delivery), or gain access denied to others.

Though these systems and programs are framed as user-friendly and efficiency-enhancing, they are fundamentally biased toward those of higher socioeconomic status. The ability to pay becomes a prerequisite for access, time savings, and sometimes even user dignity, reshaping public services into tiered experiences dictated by purchasing power. This embedded bias extends beyond these convenience services into state and institutional infrastructures, as shown by the unrolling of Real IDs, or Pakistan’s digital ID system, as discussed in the article.

The article also brought up my broader concerns about surveillance capitalism and data commodification. There are constant news articles and lawsuits about how consumer data is continuously collected, analyzed, and sold to influence behaviour, without individuals understanding the scope of their consent. This is of special concern to me as a geographer who uses digital technologies and GPS tracking for her research. Through conversations with a friend working in marketing and data collection, I have become very aware of how much personal information is harvested by tech companies, and subsequently used for behavioural influence, yet marketed as convenience.

The proliferation of AI technologies further complicates things, as AI datasets, particularly those of LLMS are trained on datasets that reflect existing social inequalities and assumptions, similar to Pakistan’s system that assumes a nuclear family structure. As the implementation and integration of AI technologies grow, they continue to reproduce these biases, which disproportionately impacts marginalized groups, especially women, as discussed in other books I’ve read such as Searches: Selfhood in the Digital Age by Vauhini Vara and Invisible Women by Caroline Criado-Perez. As shown in the article with the phrase “Sorry sir, the computer doesn’t allow it,” these programs can culminate in a troubling abdication of human judgement to automated systems. While the Pakistan ID system may not implement AI, there is a broader pattern of technology being treated as an all-knowing authority rather than a tool designed for human needs. Anyone who has worked with technology knows this all too well. Poorly designed systems (i.e., the cash system at a retail job, or an online form) can sometimes leave customer service workers unable to assist the human being standing directly in front of them due to software constraints. As digital systems grow more complex, and integrated with everyday life, they produce the aforementioned redundancies and exclusion, instead of efficiency, and often fail to address the complexity and nuances of human life.

```
142 changes: 131 additions & 11 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,8 +7,8 @@
/* 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,13 +30,23 @@ sorted by customer_last_name, then customer_first_ name. */
Limit to 25 rows of output. */
--QUERY 3

SELECT *
FROM customer_purchases
WHERE product_id = 4
OR product_id = 9;

-- product ID 9 does not exist? --
--adding limit of 25 rows--

SELECT *
FROM customer_purchases
WHERE product_id = 4
OR product_id = 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),
filtered by customer IDs between 8 and 10 (inclusive) using either:
1. two conditions using AND
Expand All @@ -43,8 +55,20 @@ Limit to 25 rows of output.
*/
--QUERY 4

SELECT *,
(quantity * cost_to_customer_per_qty) AS price
FROM customer_purchases
WHERE customer_id BETWEEN 8 AND 10
LIMIT 25;

--or, using the >=< signs--

SELECT *,
(quantity * cost_to_customer_per_qty) AS price
FROM customer_purchases
WHERE customer_id >=8
AND customer_id <=10
LIMIT 25;

--END QUERY

Expand All @@ -56,8 +80,14 @@ 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
Copy link
Copy Markdown

@anjali-deshpande-hub anjali-deshpande-hub Apr 2, 2026

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Column name should be prod_qty_type_condensed



FROM product;

--END QUERY

Expand All @@ -67,8 +97,15 @@ 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

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


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

--END QUERY

Expand All @@ -79,8 +116,12 @@ 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.vendor_id = vendor_booth_assignments.vendor_id
ORDER BY market_date, vendor_name
LIMIT 24;

--END QUERY

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

SELECT COUNT(vendor_id) as num_of_rentals1
FROM vendor_booth_assignments;

--not grouped by vendor id--

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

--vendor_id of 2,5,6 do not exist in vendor_booth_assignments so the final table makes sense--

--END QUERY

Expand All @@ -106,8 +156,58 @@ 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



-- joining the tables with customer_id--
SELECT *
FROM customer
INNER JOIN customer_purchases
ON customer.customer_id = customer_purchases.customer_id
ORDER BY customer_last_name, customer_first_name;


--selecting the relevant columns and using table aliases --
SELECT
c.customer_first_name,
c.customer_last_name,
c.customer_id,
cp.quantity,
cp.cost_to_customer_per_qty
FROM customer AS c
INNER JOIN customer_purchases AS cp
ON c.customer_id = cp.customer_id
ORDER BY c.customer_last_name, c.customer_first_name;

-- now adding the sum to determine the purchase totals
--,SUM(quantity*cost_to_customer_per_qty) as total_spend
--HAVING total_spend > 2000

-- final query
SELECT
c.customer_first_name,
c.customer_last_name,
c.customer_id,
cp.quantity,
cp.cost_to_customer_per_qty,
SUM (cp.quantity * cp.cost_to_customer_per_qty) AS total_spend
FROM customer AS c
INNER JOIN customer_purchases AS cp
ON c.customer_id = cp.customer_id
GROUP BY c.customer_last_name, c.customer_first_name
HAVING total_spend > 2000
ORDER BY c.customer_id, c.customer_last_name, c.customer_first_name;


--checking results without >2000 and order by--
SELECT
c.customer_first_name,
c.customer_last_name,
c.customer_id,
cp.quantity,
cp.cost_to_customer_per_qty,
SUM (cp.quantity * cp.cost_to_customer_per_qty) AS total_spend
FROM customer AS c
INNER JOIN customer_purchases AS cp
ON c.customer_id = cp.customer_id
GROUP BY c.customer_last_name, c.customer_first_name

--END QUERY

Expand All @@ -125,6 +225,26 @@ VALUES(col1,col2,col3,col4,col5)
*/
--QUERY 10

-- vendor_id, vendor_name, vendor_type, vendor_owner_first_name, vendor_owner_last_name - from vendor table
-- will be 10, Thomass Superfood Store, a Fresh Focused store, Thomas, Rosenthal

DROP TABLE IF EXISTS temp.new_vendor;

--creating the new table
CREATE TABLE temp.new_vendor AS
SELECT *
FROM vendor;

--inserting the values in
INSERT INTO temp.new_vendor
VALUES(10,
'Thomass Superfood Store',
'a Fresh Focused store',
'Thomas',
'Rosenthal');

--checking work - yay it shows up!!!!
SELECT * FROM temp.new_vendor;



Expand Down
Binary file added 02_activities/assignments/DC_Cohort/images/Cervinka_FarmersMarket_Diagram_SQL.jpg
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.