Priyank315 · Priyank315 · Sep 16, 2024 · Sep 16, 2024
diff --git a/Homework2_SQLfile.sql b/Homework2_SQLfile.sql
@@ -0,0 +1,44 @@
+-- HOMEWORK 2
+
+--1 SELECT FUNCTION
+
+-- SELECT *
+-- FROM customer
+
+--SELECT *
+-- FROM customer
+-- ORDER BY customer_last_name, customer_first_name
+-- LIMIT 10;
+
+--2 WHERE FUNCTION
+
+-- SELECT *
+-- FROM customer_purchases
+-- WHERE product_id IN (4,9)
+
+-- SELECT *,(quantity*cost_to_customer_per_qty)as [price]
+-- FROM customer_purchases
+-- WHERE vendor_id BETWEEN 8 AND 10
+-- AND vendor_id IN (8,10)
+
+-- 3 CASE FUNCTION
+
+-- SELECT product_id, product_name
+-- ,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
+
+
+-- 4 JOIN FUNCTION
+
+-- 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;
diff --git a/SQL classwork files/CASE.sql b/SQL classwork files/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_speciality
+,CASE WHEN vendor_type = 'Fresh Focused' 
+	THEN 'Wednesday'
+	WHEN vendor_name LIKE '%pie%'
+	THEN 'Thursday'
+	ELSE 'Saturday'
+	END as day_of_speciality2
+FROM vendor
diff --git a/SQL classwork files/DISTINCT.sql b/SQL classwork files/DISTINCT.sql
@@ -0,0 +1,11 @@
+--DISTINCT
+
+-- SELECT DISTINCT booth_type from booth
+
+-- SELECT DISTINCT booth_price_level, booth_type
+-- FROM booth
+
+-- which vendor has sold products to a customer and which product was it?
+
+SELECT DISTINCT vendor_id, product_id, customer_id
+FROM customer_purchases
diff --git a/SQL classwork files/HAVING_function.sql b/SQL classwork files/HAVING_function.sql
@@ -0,0 +1,14 @@
+--HAVING
+
+-- how much did a customer spend on each day
+SELECT
+customer_id
+,market_date
+,sum(quantity*cost_to_customer_per_qty) as cost 
+
+FROM customer_purchases as cp
+WHERE customer_id BETWEEN 1 AND 3 -- filters BEFORE the aggregation, only customers 1 2 3 are being aggregated
+--AND cost>50 -- Not allowed! as the Cost is not yet defined which will happen after the grouping!
+GROUP BY customer_id
+,market_date
+HAVING cost>50 -- filters AFTER based on the aggregationORDER BY product_qty_type, original_price ASC
diff --git a/SQL classwork files/Initial query.sql b/SQL classwork files/Initial query.sql
@@ -0,0 +1,15 @@
+--SELECT *
+--FROM customer
+
+--SELECT customer_first_name, customer_last_name, customer_id
+--FROM customer
+
+--SELECT 10*5 AS [math_product], customer_id, customer_id + 10 as [addition]
+
+--FROM customer
+
+SELECT 'Thomas' FROM customer;
+
+SELECT 10*5 FROM customer;
+
+SELECT[customer_first_name] AS name FROM customer
diff --git a/SQL classwork files/Inner JOIN.sql b/SQL classwork files/Inner JOIN.sql
@@ -0,0 +1,17 @@
+-- Inner JOIN
+
+SELECT *
+FROM customer_purchases
+INNER JOIN product
+on customer_purchases.product_id= product.product_id
+
+-- SELECT product.product_id, product.product_id, product_name, vendor_id, market_date, customer_id
+-- 
+-- FROM customer_purchases
+-- INNER JOIN product 
+-- 	ON customer_purchases.product_id = product.product_id
+
+SELECT DISTINCT p.product_id, product_name
+FROM product as p
+INNER JOIN customer_purchases as cp
+	ON p.product_id = cp.product_id
diff --git a/SQL classwork files/Left JOIN.sql b/SQL classwork files/Left JOIN.sql
@@ -0,0 +1,6 @@
+-- LEFT JOIN
+
+SELECT DISTINCT p.product_id, product_name
+FROM product as p
+LEFT JOIN customer_purchases as cp
+	ON p.product_id = cp.product_id
diff --git a/SQL classwork files/MIN&MAX_function.sql b/SQL classwork files/MIN&MAX_function.sql
@@ -0,0 +1,29 @@
+-- Min and max
+
+-- What is the most expensive product?
+
+-- SELECT product_name, max(original_price)
+-- 
+-- FROM product AS p
+-- INNER JOIN vendor_inventory AS vi -- You can keep AS or you can keep space as well.
+-- ON p.product_id = vi.product_id
+
+--minimum price per each type of the unit/lbs
+SELECT product_name, product_qty_type
+, min(original_price)
+FROM product as p
+INNER JOIN vendor_inventory AS vi
+	ON p.product_id = vi.product_id
+
+	group by product_qty_type
+
+
+-- PROVE IT
+SELECT product_name, product_qty_type, original_price
+--, min(original_price)
+FROM product as p
+INNER JOIN vendor_inventory AS vi
+	ON p.product_id = vi.product_id
+
+	--group by product_name, product_qty_type
+	ORDER BY product_qty_type, original_price ASC
diff --git a/SQL classwork files/SQL_Homework1.jpg b/SQL classwork files/SQL_Homework1.jpg
diff --git a/SQL classwork files/SUMnAVG_fucntion.sql b/SQL classwork files/SUMnAVG_fucntion.sql
@@ -0,0 +1,18 @@
+-- SUM and avg
+
+-- How much did a customer spend on each day?
+-- SELECT *
+-- ,sum(quantity*cost_to_customer_per_qty) as total_cost
+-- 
+-- FROM customer_purchases
+-- GROUP by market_date , customer_id
+
+--how much does each customer spend on average
+SELECT customer_first_name,customer_last_name
+,AVG(quantity*cost_to_customer_per_qty) as avg_cost
+
+FROM customer_purchases as cp
+INNER JOIN customer as c
+	ON c.customer_id = cp.customer_id
+GROUP BY cp.customer_id
+ORDER BY avg_cost DESC
diff --git a/SQL classwork files/Steps to follow group by and having.png b/SQL classwork files/Steps to follow group by and having.png
diff --git a/SQL classwork files/WHERE.sql b/SQL classwork files/WHERE.sql
@@ -0,0 +1,15 @@
+-- WHERE
+
+-- SELECT * FROM customer
+-- WHERE customer_id = 1
+-- or customer_id =2
+
+-- SELECT *
+-- FROM customer_purchases
+-- WHERE customer_id IN (3,4,5)
+-- AND vendor_id= 20
+
+SELECT * FROM product
+-- WHERE product_name like '%pepper%organic'
+WHERE product_id IN (1,2)
+OR product_size = 'medium'
diff --git a/SQL classwork files/count_function.sql b/SQL classwork files/count_function.sql
@@ -0,0 +1,18 @@
+-- count
+
+-- count the number of products in a product TABLE
+-- SELECT count(product_id)
+-- FROM product
+
+-- SELECT product_size, count(product_id)
+-- FROM product
+-- GROUP by product_size
+
+-- SELECT product_qty_type, count(product_id)
+-- FROM product
+-- WHERE product_qty_type IS NOT NULL
+-- GROUP BY product_qty_type;
+
+-- SELECT product_size, count(product_id)
+-- FROM product
+-- GROUP BY product_size
diff --git a/SQL classwork files/subqueries_JOIN_function.sql b/SQL classwork files/subqueries_JOIN_function.sql
@@ -0,0 +1,26 @@
+--subqueries: JOIN
+
+-- "Whats the single item that has been bought in the greatest quantity"
+
+
+SELECT product_name, max(max_quantity)
+
+FROM product p 
+INNER JOIN (
+		SELECT product_id, max(quantity) as max_quantity
+
+		FROM customer_purchases
+		GROUP by product_id
+) x ON p.product_id = x.product_id;
+
+
+--simple subquery in a FROM statement
+SELECT DISTINCT inflation, product_id
+
+FROM (
+	SELECT product_id, cost_to_customer_per_qty
+	,CASE WHEN cost_to_customer_per_qty < '1.00' THEN cost_to_customer_per_qty*5
+		ELSE cost_to_customer_per_qty END as inflation
+
+	FROM customer_purchases
+) 
diff --git a/SQL classwork files/subquery_WHERE_function.sql b/SQL classwork files/subquery_WHERE_function.sql
@@ -0,0 +1,27 @@
+-- subquery: WHERE
+
+-- what postal code have had at least one customer spend 3000 at the farmers market
+SELECT DISTINCT customer_postal_code
+
+FROM customer
+
+WHERE customer_id IN (
+	SELECT customer_id -- all time spending for each customer
+
+	FROM customer_purchases
+	group by customer_id
+	HAVING sum(quantity*cost_to_customer_per_qty) > 3000
+)
+
+-- whats the name of the vendor who sell pie
+
+SELECT DISTINCT vendor_name
+FROM vendor as v
+JOIN vendor_inventory as vi
+	ON v.vendor_id = vi.vendor_id
+
+WHERE product_id IN (
+	SELECT product_id
+	FROM product
+	WHERE product_name LIKE '%pie%'
+)
diff --git a/sql b/sql