What changes are you trying to make? (e.g. Adding or removing code, refactoring existing code, adding reports)

My submission for SQL Homework 5.

What did you learn from the changes you have made?

I practiced:

1. Using JOIN and CROSS JOIN in a same query. I learnt that if we put CROSS JOIN before the JOIN clauses, the "customer_count" alias will be treated as a table, and I would have to do "customer_count.customer_count", which can look confusing to viewer. So I put CROSS JOIN after JOIN clauses, and it'll be treated like a single value (a scalar), simplifying the code.
2. Even though I just need unique combinations (ie. SELECT DISTINCT) of 2 variables (vendor_id and product_id) from "vendor_inventory" table, if I'm using it as a subquery and use the subquery's results to work with an AGGREGATE clause (like SUM()) that uses "price" in that "vendor_inventory" table: then including "price" in the SELECT DISTINCT will remove a lot of the headache later on (otherwise, I'd need to code INNER JOIN to join the "prices" to the topic table.
3. DROP TABLE IF EXISTS is a crucial line.
4. The INSERT clause will not display the table. Need a SELECT * FROM to display.
5. Add a new column to an existing table with "ALTER TABLE
ADD " clause.
6. Various ways to UPDATE values in a column (with much tears and sweat to find out which clauses work with UPDATE).
7. MAX() on date will give the most recent date.
   8 ) UPDATE in and off itself will act as INNER JOIN for the table we're at with the table in the subquery.
8. SQL processes the UPDATE statement 1 row at a time. Together with the WHERE clause where the "product_id" in both tables must match, and the LIMIT 1 clause: it only looks for the most recent quantity update that matches the product_id, to update the value "current_quantity" in-row for that particular product_id. This means the GROUP BY clause is not needed.
9. The UPDATE clause will not display the table. Need a SELECT * FROM
to display.
10. The " ALTER TABLE product_units ADD current_quantity INT;" has to be muted once run, for subsequent syntaxes after it to work.

Was there another approach you were thinking about making? If so, what approach(es) were you thinking of?

Yes - particularly for the UPDATE question, there were various ways to approach it, including: using MAX() to get the most recent date; creating CET to use with WINDOWED function (like RANK()). Please refer to my comments in the homework for more details.

Were there any challenges? If so, what issue(s) did you face? How did you overcome it?

I encountered a lot of errors, and learnt the following:

1. Clauses that work directly with UPDATE statement: SET, WHERE.
2. Clauses that work in subquery but NOT directly with UPDATE : ORDER BY, LIMIT, GROUP BY, HAVING.
3. Clauses that neither work in subquery nor directly with UPDATE : JOIN, MERGE.

How were these changes tested?

They were tested in DB SQLite.

A reference to a related issue in your repository (if applicable)

N/A

Checklist

• I can confirm that my changes are working as intended