SELECT
product_name || ', ' || product_size|| ' (' || product_qty_type || ')'
FROM product,

Results:
| product_name || ', ' || product_size|| ' (' || product_qty_type || ')' |
| --- |
| Habanero Peppers - Organic, medium (lbs) |
| Jalapeno Peppers - Organic, small (lbs) |
| Poblano Peppers - Organic, large (unit) |

✅ Query 2:

SELECT
product_name || ', ' || COALESCE(product_size, '') || ' (' || COALESCE(product_qty_type, 'unit') || ')'
FROM product,

Results:
| product_name || ', ' || COALESCE(product_size, '') || ' (' || COALESCE(product_qty_type, 'unit') || ')' |
| --- |
| Habanero Peppers - Organic, medium (lbs) |
| Jalapeno Peppers - Organic, small (lbs) |
| Poblano Peppers - Organic, large (unit) |

✅ Query 3:

SELECT cp.,
DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY market_date) AS visit_number

FROM customer_purchases AS cp
WHERE market_date < '2022-04-29'

ORDER BY customer_id, market_date*,

Results:

✅ Query 4:

*SELECT customer_id, market_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY market_date) AS visit_number

FROM customer_purchases
WHERE market_date < '2022-04-29'

GROUP BY customer_id, market_date
ORDER BY customer_id, market_date*,

Results:

✅ Query 5:

*SELECT *,
CASE WHEN INSTR(product_name,'-') > 0
THEN LTRIM(RTRIM(SUBSTR(product_name,INSTR(product_name,'-')+1)))
ELSE NULL END AS description

FROM product*,

Results:

✅ Query 7:

*WITH sales_per_market AS (
SELECT
market_date,
ROUND(SUM(quantity * cost_to_customer_per_qty),2) AS sales

FROM customer_purchases
GROUP BY market_date
)

,market_dates_ranked_by_sales AS (
SELECT
market_date,
sales,
RANK() OVER (ORDER BY sales) AS sales_rank_asc,
RANK() OVER (ORDER BY sales DESC) AS sales_rank_desc

FROM sales_per_market
)

SELECT market_date, sales, sales_rank_desc AS sales_rank
FROM market_dates_ranked_by_sales
WHERE sales_rank_asc = 1

UNION

SELECT market_date, sales, sales_rank_desc AS sales_rank
FROM market_dates_ranked_by_sales
WHERE sales_rank_desc = 1*,

Results:

✅ Query 8:

*SELECT vendor_name, product_name, sum(price)
FROM (
SELECT DISTINCT
vendor_name,
product_name,
5 * original_price as price

FROM vendor_inventory vi
JOIN vendor v on v.vendor_id = vi.vendor_id
JOIN product p on p.product_id = vi.product_id
) x
CROSS JOIN
(SELECT DISTINCT
customer_id
FROM customer)
GROUP BY vendor_name, product_name*,

Results:

✅ Query 9:

*CREATE TABLE product_units AS

SELECT p.,
CURRENT_TIMESTAMP AS snapshot_timestamp
FROM product AS p
WHERE product_qty_type = "unit",

Results:
No data

✅ Query 10:

INSERT INTO product_units (product_id, product_name,
product_size, product_category_id, product_qty_type, snapshot_timestamp)
SELECT
product_id,
product_name,
product_size,
product_category_id,
product_qty_type,
CURRENT_TIMESTAMP
FROM product AS p
WHERE product_id = 7,

Results:
No data

✅ Query 11:

*WITH older_record AS
(SELECT product_id
,MIN(snapshot_timestamp) AS snapshot_timestamp
FROM product_units
WHERE product_id = 7)

DELETE FROM product_units
WHERE (product_id =
(SELECT product_id
FROM older_record
)
AND snapshot_timestamp =
(SELECT snapshot_timestamp
FROM older_record
))*,

Results:
No data

✅ Query 1:

SELECT
product_name || ', ' || product_size|| ' (' || product_qty_type || ')'
FROM product,

Results:
| product_name || ', ' || product_size|| ' (' || product_qty_type || ')' |
| --- |
| Habanero Peppers - Organic, medium (lbs) |
| Jalapeno Peppers - Organic, small (lbs) |
| Poblano Peppers - Organic, large (unit) |

✅ Query 2:

SELECT
product_name || ', ' || COALESCE(product_size, '') || ' (' || COALESCE(product_qty_type, 'unit') || ')'
FROM product,

Results:
| product_name || ', ' || COALESCE(product_size, '') || ' (' || COALESCE(product_qty_type, 'unit') || ')' |
| --- |
| Habanero Peppers - Organic, medium (lbs) |
| Jalapeno Peppers - Organic, small (lbs) |
| Poblano Peppers - Organic, large (unit) |

✅ Query 3:

SELECT cp.,
DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY market_date) AS visit_number

FROM customer_purchases AS cp
WHERE market_date < '2022-04-29'

ORDER BY customer_id, market_date*,

Results:

✅ Query 4:

*SELECT customer_id, market_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY market_date) AS visit_number

FROM customer_purchases
WHERE market_date < '2022-04-29'

GROUP BY customer_id, market_date
ORDER BY customer_id, market_date*,

Results:

✅ Query 5:

*SELECT *,
CASE WHEN INSTR(product_name,'-') > 0
THEN LTRIM(RTRIM(SUBSTR(product_name,INSTR(product_name,'-')+1)))
ELSE NULL END AS description

FROM product*,

Results:

✅ Query 7:

*WITH sales_per_market AS (
SELECT
market_date,
ROUND(SUM(quantity * cost_to_customer_per_qty),2) AS sales

FROM customer_purchases
GROUP BY market_date
)

,market_dates_ranked_by_sales AS (
SELECT
market_date,
sales,
RANK() OVER (ORDER BY sales) AS sales_rank_asc,
RANK() OVER (ORDER BY sales DESC) AS sales_rank_desc

FROM sales_per_market
)

SELECT market_date, sales, sales_rank_desc AS sales_rank
FROM market_dates_ranked_by_sales
WHERE sales_rank_asc = 1

UNION

SELECT market_date, sales, sales_rank_desc AS sales_rank
FROM market_dates_ranked_by_sales
WHERE sales_rank_desc = 1*,

Results:

✅ Query 8:

*SELECT vendor_name, product_name, sum(price)
FROM (
SELECT DISTINCT
vendor_name,
product_name,
5 * original_price as price

FROM vendor_inventory vi
JOIN vendor v on v.vendor_id = vi.vendor_id
JOIN product p on p.product_id = vi.product_id
) x
CROSS JOIN
(SELECT DISTINCT
customer_id
FROM customer)
GROUP BY vendor_name, product_name*,

Results:

⚪ Query 9:

*CREATE TABLE product_units AS

SELECT p.,
CURRENT_TIMESTAMP AS snapshot_timestamp
FROM product AS p
WHERE product_qty_type = "unit",

No rows returned

⚪ Query 10:

INSERT INTO product_units (product_id, product_name,
product_size, product_category_id, product_qty_type, snapshot_timestamp)
SELECT
product_id,
product_name,
product_size,
product_category_id,
product_qty_type,
CURRENT_TIMESTAMP
FROM product AS p
WHERE product_id = 7,

No rows returned

⚪ Query 11:

*WITH older_record AS
(SELECT product_id
,MIN(snapshot_timestamp) AS snapshot_timestamp
FROM product_units
WHERE product_id = 7)

DELETE FROM product_units
WHERE (product_id =
(SELECT product_id
FROM older_record
)
AND snapshot_timestamp =
(SELECT snapshot_timestamp
FROM older_record
))*,

No rows returned