UofT-DSI | SQL - Assignment 1

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

This pull request adds my completed solutions for Assignment 1 in the SQL module.
Changes include:

• Implemented queries for basic SELECT, WHERE, and ORDER BY tasks.
• Implemented aggregate queries using COUNT, SUM, and AVG with GROUP BY.
• Used HAVING to filter grouped results based on aggregate conditions.
• Wrote join queries combining customer, customer_purchases, vendor, and vendor_booth_assignments.
• Created and populated a temporary table (new_vendor) based on the original vendor table, then inserted an additional vendor row.

What did you learn from the changes you have made?

From this assignment I:

• Solidified the difference between WHERE (filters rows) and HAVING (filters groups after aggregation).
• Practiced using GROUP BY correctly by ensuring all non-aggregated selected columns are included in the grouping.
• Got more comfortable writing INNER JOIN queries to combine information from multiple tables.
• Learned how to create a temporary table from an existing table and insert new rows into it.
• Improved my understanding of how to structure queries so they are readable and logically ordered (SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY).

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

In a few places I considered:

• Using subqueries or CTEs (WITH clauses) instead of writing everything in a single SELECT with joins, especially for the “big spenders” type queries.
• Using COUNT(*) versus COUNT(column) depending on whether I wanted to include potential NULL values.

For this assignment, I stayed close to the patterns shown in the course materials and used straightforward joins and aggregations.

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

Yes, a few:

• GROUP BY and aggregates:
  Initially I forgot to include all non-aggregated columns in the GROUP BY clause, which caused errors. I fixed this by systematically checking that every column in SELECT was either aggregated or listed in GROUP BY.

• Using HAVING correctly:
  I first tried to use aggregate functions in the WHERE clause. I resolved this by moving those conditions into HAVING and keeping WHERE only for row-level filters.

• Temporary table behavior:
  I was confused when creating and inserting into a temp table because nothing appeared to “show up” at first. I realized that:

  • CREATE and INSERT do not return result sets, so I needed an explicit SELECT * FROM new_vendor;
  • All related statements need to run in the same session/connection for the temp table to exist.

After those adjustments, all queries ran as expected.

How were these changes tested?

• Ran each query directly in the provided SQL environment against the assignment database.
• Verified:
  • Row counts and totals matched the expected logic (e.g., customers spending over a threshold, vendors with multiple booth assignments).
  • The temporary table contained all original vendors plus the newly inserted 10th vendor.
  • Sorting and filtering matched the requirements in the assignment prompt.

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

Not applicable for this assignment; this PR is only for completing Assignment 1 for the SQL module.

Checklist

I can confirm that my changes are working as intended