UofT-DSI | SQL - Assignment 2

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

I have added 2 ERDs containing logical data models for a bookstore. I have also added SQL codes to the assignment2.sql file in response to the prompts and have updated the assignment2.md file with written responses for section 1 and section 4.

What did you learn from the changes you have made?

I have learned to work with window functions, and have practiced manipulating data by using INSERT, UPDATE, and DELETE statements. Also, I have learned to handle NULL values by using functions like IFNULL and NULLIF, as well as COALESCE. Finally, I have practiced string manipulation commands and have learned to use CROSS JOINS to combine tables.

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

I have mostly followed the hints provided for each question, except for under the prompt on UNION, where I couldn't figure out why to do a windowed function on the second CTE... I'm still not sure I did this correctly, but the results looked like they were right. I also alternated between CTEs and temp tables in this assignment, just to get practice in both.

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

This assignment was very difficult since we learned a lot of commands in a short period of time and some of the questions even required us to nest commands within others. I reviewed the video of our live lessons many times, esp. when it came to windowed functions to try to get a better grasp of how they worked.

Nested queries just took extra time, and I eventually learned to approach them by starting from the innermost query and building outwards from there. Since I nested an INSTR statement inside a SUBSTR statement in query 5, doing something similar in query 12 was less confusing than expected.

How were these changes tested?

I ran each of the queries to make sure it generated the results I wanted.

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

I want to flag that I'm much less certain with this assignment than I have been with previous assignments, esp. when it comes to the questions on windowed functions. I tested my solutions and they all look like they work, but when it came to query 5, I couldn't grasp why a rank windowed function was needed. Does it add anything that my solution does not provide?

Checklist

• I can confirm that my changes are working as intended