-- option 2
-- QUERY 5**,
Reults: ****,
✅ Query number: 6, Query: **-- END QUERY

/* 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. /
-- QUERY 7*,
Reults: ****,
✅ Query number: 8, Query: **-- END QUERY

/* SECTION 3 */

-- 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. /
-- QUERY 9*,
Reults: ****,
✅ Query number: 10, Query: **-- END QUERY

--Temp Table
/* 1. Insert the original vendor table into a temp.new_vendor and then add a 10th vendor:
Thomass Superfood Store, a Fresh Focused store, owned by Thomas Rosenthal

HINT: This is two total queries -- first create the table from the original, then insert the new 10th vendor.
When inserting the new vendor, you need to appropriately align the columns to be inserted
(there are five columns to be inserted, I've given you the details, but not the syntax)

-> To insert the new row use VALUES, specifying the value you want for each column:
VALUES(col1,col2,col3,col4,col5)
/
-- QUERY 11*,
Reults: ****,
✅ Query number: 12, Query: **-- 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!! */

-- QUERY 12**,
Reults: ****,

-- option 2
-- QUERY 5**,
Results: ****,
✅ Query 6: -- END QUERY

/* 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. /
-- QUERY 7*,
Results: ****,
✅ Query 8: -- END QUERY

/* SECTION 3 */

-- 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. /
-- QUERY 9*,
Results: ****,
✅ Query 10: -- END QUERY

--Temp Table
/* 1. Insert the original vendor table into a temp.new_vendor and then add a 10th vendor:
Thomass Superfood Store, a Fresh Focused store, owned by Thomas Rosenthal

HINT: This is two total queries -- first create the table from the original, then insert the new 10th vendor.
When inserting the new vendor, you need to appropriately align the columns to be inserted
(there are five columns to be inserted, I've given you the details, but not the syntax)

-> To insert the new row use VALUES, specifying the value you want for each column:
VALUES(col1,col2,col3,col4,col5)
/
-- QUERY 11*,
Results: ****,
✅ Query 12: -- 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!! */

-- QUERY 12**,
Results: ****,

Results: 1,Alejandro,Costa,M8Y;

✅ Query 2: -- END QUERY
--WHERE
/* 1. Write a query that returns all customer purchases of product IDs 4 and 9. /
-- QUERY 3*,

Results: ****;

✅ Query 4: -- END QUERY

-- option 2
-- QUERY 5**,

Results: ****;

✅ Query 6: -- END QUERY

/* 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. /
-- QUERY 7*,

Results: ****;

✅ Query 8: -- END QUERY

/* SECTION 3 */

-- 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. /
-- QUERY 9*,

Results: ****;

✅ Query 10: -- END QUERY

--Temp Table
/* 1. Insert the original vendor table into a temp.new_vendor and then add a 10th vendor:
Thomass Superfood Store, a Fresh Focused store, owned by Thomas Rosenthal

HINT: This is two total queries -- first create the table from the original, then insert the new 10th vendor.
When inserting the new vendor, you need to appropriately align the columns to be inserted
(there are five columns to be inserted, I've given you the details, but not the syntax)

-> To insert the new row use VALUES, specifying the value you want for each column:
VALUES(col1,col2,col3,col4,col5)
/
-- QUERY 11*,

Results: ****;

✅ Query 12: -- 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!! */

-- QUERY 12**,

Results: ****;

Results: 1,Alejandro,Costa,M8Y;

✅ Query 2: **,

Results: ****;

✅ Query 3: **,

Results: ****;

✅ Query 4: **,

Results: ****;

✅ Query 5: **,

Results: ****;

✅ Query 6: **,

Results: ****;

✅ Query 7: **,

Results: ****;

✅ Query 8: **,

Results: ****;

✅ Query 9: **,

Results: ****;

✅ Query 10: **,

Results: ****;

✅ Query 11: **,

Results: ****;

✅ Query 12: **,

Results: ****;

✅ Query 13: **,

Results: ****;

Results: 1,Alejandro,Costa,M8Y;

✅ Query 2: SELECT *
FROM customer
ORDER BY customer_last_name, customer_first_name
LIMIT 10**,

Results: 4,Leyla,Abadi,M1L,7,Anwar,Ali,M6C,12,Jalen,Carter,M1L;

✅ Query 3: SELECT
market_date,
customer_id,
vendor_id,
product_id,
quantity,
quantity * cost_to_customer_per_qty AS price
FROM customer_purchases
WHERE product_id IN (4, 9)**,

Results: 2022-04-05,3,7,4,1,4,2022-04-05,4,7,4,1,4,2022-04-05,5,7,4,3,12;

✅ Query 4: SELECT
market_date,
customer_id,
vendor_id,
product_id,
quantity,
quantity * cost_to_customer_per_qty AS price
FROM customer_purchases
WHERE customer_id >= 8
AND customer_id <= 10**,

Results: 2022-07-12,8,7,1,2.71,18.9429,2022-07-15,10,7,1,2.34,16.3566,2022-07-15,10,7,1,2.28,15.937199999999999;

✅ Query 5: SELECT
market_date,
customer_id,
vendor_id,
product_id,
quantity,
quantity * cost_to_customer_per_qty AS price
FROM customer_purchases
WHERE customer_id BETWEEN 8 AND 10
ORDER BY market_date, vendor_id, product_id**,

Results: 2022-04-05,9,8,5,1,6.5,2022-04-05,9,8,5,1,6.5,2022-04-05,9,8,7,1,18;

✅ Query 6: SELECT
product_id,
product_name,
CASE WHEN product_qty_type = "unit" THEN "unit"
ELSE "bulk" END AS prod_qty_type_condensed
FROM product**,

Results: 1,Habanero Peppers - Organic,bulk,2,Jalapeno Peppers - Organic,bulk,3,Poblano Peppers - Organic,unit;

✅ Query 7: SELECT
product_id,
product_name,
CASE WHEN product_name LIKE '%epper%'
THEN 1 ELSE 0 END AS pepper_flag
FROM product**,

Results: 1,Habanero Peppers - Organic,1,2,Jalapeno Peppers - Organic,1,3,Poblano Peppers - Organic,1;

✅ Query 8: SELECT *
FROM vendor AS v
INNER JOIN vendor_booth_assignments AS vba
ON v.vendor_id = vba.vendor_id
ORDER BY v.vendor_name, vba.market_date**,

Results: 8,Annie's Pies,Prepared Foods,Annie,Aquinas,8,6,2022-04-05,8,Annie's Pies,Prepared Foods,Annie,Aquinas,8,6,2022-04-08,8,Annie's Pies,Prepared Foods,Annie,Aquinas,8,6,2022-04-12;

✅ Query 9: SELECT
vendor_id,
count(*) AS count_of_booth_assignments
FROM vendor_booth_assignments
GROUP BY vendor_id**,

Results: 1,142,3,142,4,211;

✅ Query 10: SELECT
cp.customer_id,
c.customer_first_name,
c.customer_last_name,
SUM(quantity * cost_to_customer_per_qty) AS total_spent

FROM customer c
LEFT JOIN customer_purchases cp
ON c.customer_id = cp.customer_id

GROUP BY
cp.customer_id,
c.customer_first_name,
c.customer_last_name

HAVING total_spent > 2000

ORDER BY c.customer_last_name, c.customer_first_name**,

Results: 4,Leyla,Abadi,3561.6286,7,Anwar,Ali,2921.1743,12,Jalen,Carter,3290.0783;

✅ Query 12: SELECT customer_id,
STRFTIME('%m',market_date) AS purchase_month,
STRFTIME('%Y', market_date) AS purchase_year

FROM customer_purchases**,

Results: 14,07,2022,14,07,2022,15,07,2022;

✅ Query 13: SELECT customer_id,
STRFTIME('%m',market_date) AS purchase_month,
STRFTIME('%Y', market_date) AS purchase_year,
SUM(quantity*cost_to_customer_per_qty)

FROM customer_purchases

WHERE STRFTIME('%Y', market_date) = '2023'
AND STRFTIME('%m',market_date) = '04'

GROUP BY STRFTIME('%Y', market_date) ,STRFTIME('%m', market_date), customer_id**,

Results: 1,04,2023,129.5,2,04,2023,200,3,04,2023,352.5;