Scribd
Upload
22 views6 pages

3 Cases - Joins

The document contains SQL queries for a farmers market database, including adding columns to identify fresh vendors and categorize market days as weekend or weekday. It also includes queries to bucket purchase amounts into price ranges, list products with their categories, and find new customers who have not made purchases. Each query is designed to extract specific insights from the database.

Uploaded by

hariharanm22it01022005
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
22 views6 pages

3 Cases - Joins

The document contains SQL queries for a farmers market database, including adding columns to identify fresh vendors and categorize market days as weekend or weekday. It also includes queries to bucket purchase amounts into price ranges, list products with their categories, and find new customers who have not made purchases. Each query is designed to extract specific insights from the database.

Uploaded by

hariharanm22it01022005
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Cases & Joins

Add a column that identifies which vendors are selling fresh products and
which are not.

SELECT
vendor_id,
vendor_type,
CASE
WHEN LOWER(vendor_type) LIKE "%fresh%"
THEN 1
ELSE 0
END AS is_fresh
FROM `farmers_market.vendor`
Add a column to identify weekend or weekday using the market day
column in the market_date_info table.
SELECT
market_date,
Using IF()
market_day,
CASE SELECT
WHEN lower(market_day) IN ("saturday", market_date,
"sunday") market_day,
THEN "Weekend" IF(lower(market_day) IN ("saturday", "sunday"),
ELSE "Weekday" "Weekend", "Weekday") AS is_weekend
END AS is_weekend FROM `farmers_market.mdi`
FROM `farmers_market.mdi`
Put the total_amt in each purchase into following buckets:
1. Under $5.00
2. $5.00 - $9.99
3. $10.00 - $19.99
4. Over $20

SELECT
market_date,
customer_id,
ROUND(quantity * cost_to_customer_per_qty,2) AS total_price,
CASE
WHEN quantity * cost_to_customer_per_qty < 5
THEN "Under $5.00"
WHEN quantity * cost_to_customer_per_qty BETWEEN 5 AND 9.99
THEN "5 - 9.99"
WHEN quantity * cost_to_customer_per_qty BETWEEN 10 AND 19.99
THEN "10 - 19.99"
ELSE "Over $20"
END AS price_bins
FROM `farmers_market.customer_purchases`
List all the products along with their product category names.
SELECT
p.product_id,
p.product_category_id,
pc.product_category_name
FROM `farmers_market.product` AS p
LEFT JOIN `farmers_market.product_category` AS pc
ON p.product_category_id = pc.product_category_id
Find the customers which are new to the market.
SELECT
c.customer_id
FROM `farmers_market.customer` AS c
LEFT JOIN `farmers_market.customer_purchases` AS cp
ON c.customer_id = cp.customer_id
WHERE cp.customer_id IS NULL

You might also like