Conversation
amanda-ng518
left a comment
There was a problem hiding this comment.
HW1: good diagram, perhaps you can add 1 to "infinity sign" to the diagram, there should be an option for that when you code it out.
There was a problem hiding this comment.
HW5: 8/8, good
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 pretty close
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)
Create a relationship diagram for the customers and customer purchases tables.
What did you learn from the changes you have made?
There are many different kinds of relationships, one-to-one, one-to-many and many-to-many.
Was there another approach you were thinking about making? If so, what approach(es) were you thinking of?
No
Were there any challenges? If so, what issue(s) did you face? How did you overcome it?
No
How were these changes tested?
I verified by checking the database tables on sql lite.
A reference to a related issue in your repository (if applicable)
Checklist