Conversation
There was a problem hiding this comment.
HW5: 8/8
INSERT Q2
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
DELETE Q1
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
))
UPDATE Q1
ALTER TABLE product_units
ADD current_quantity INT;
UPDATE product_units
SET current_quantity = (
SELECT current_quantity
FROM (
SELECT p.product_id, COALESCE(quantity,0) as current_quantity
FROM product_units p
LEFT JOIN (
SELECT *
,ROW_NUMBER() OVER( PARTITION BY vi.product_id ORDER BY market_date DESC) AS rn
FROM vendor_inventory vi
) vi ON p.product_id = vi.product_id
WHERE rn = 1
OR rn IS NULL
) p
WHERE product_units.product_id = p.product_id);
What changes are you trying to make? (e.g. Adding or removing code, refactoring existing code, adding reports)
Homework 5 submission
Homework 6 submission
What did you learn from the changes you have made?
SQLite is very limited vs SQL Server
Was there another approach you were thinking about making? If so, what approach(es) were you thinking of?
Using OUTER APPLY would have save a lot of time on the "UPDATE" challenge
Were there any challenges? If so, what issue(s) did you face? How did you overcome it?
How were these changes tested?
Tested against sample DB to check for expected results
A reference to a related issue in your repository (if applicable)
Checklist