Open
Conversation
Added response to section 4
added diagram for Section 1 of Assignment 1
Updated with answers to assignment 1 sections 2-3
kelichiu
reviewed
Jan 27, 2025
| /* 1. Write a query that INNER JOINs the vendor table to the vendor_booth_assignments table on the | ||
| vendor_id field they both have in common, and sorts the result by vendor_name, then market_date. */ | ||
|
|
||
| SELECT vendor.vendor_id, vendor.vendor_name, vendor_booth_assignments.booth_number, vendor_booth_assignments.market_date |
There was a problem hiding this comment.
Consider using table alias to shorten the queries and improve readability.
kelichiu
reviewed
Jan 27, 2025
| INNER JOIN customer_purchases | ||
| ON customer.customer_id = customer_purchases.customer_id | ||
| GROUP BY customer.customer_id, customer.customer_first_name, customer.customer_last_name | ||
| HAVING SUM(customer_purchases.quantity * customer_purchases.cost_to_customer_per_qty) > 2000 |
There was a problem hiding this comment.
Missing the sorting part of the requirement.
kelichiu
suggested changes
Jan 27, 2025
There was a problem hiding this comment.
Point: 27/30
Section 1: ☑️ Partial
The relationship between market_date_info and customer_purchases is indeed one-to-many but from the opposite direction. At a specific day, there can be many customer purchases. On the other hand, a customer purchase (a transaction for a specific item) can only happen in one specific day. A same item can't be purchased more than once.
Todo:
- Revisit the relationship direction between customer_purchases and market_date_info
Section 2: ✅ Complete
Section 3: ☑️ Partial
Aggregate Question 2
Todo:
- Sort the result by the requirement stated in the question
Section 4: ✅ Complete
Thank you for the thoughtful reflection, I enjoyed reading it.
Added code to sort for AGGREGATE q2
New logical model reflects correct relationship between market_date_info and customer_purchases (one to many, in that direction).
kelichiu
approved these changes
Jan 28, 2025
kelichiu
left a comment
There was a problem hiding this comment.
2nd Assessment Point: 30/30
Section 1: ✅ Complete
Section 2: ✅ Complete
Section 3: ✅ Complete
Section 4: ✅ Complete
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
2 participants
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
What changes are you trying to make? (e.g. Adding or removing code, refactoring existing code, adding reports)
I added code to the assignment 1 sql file, uploaded an image for section 1, as well as my written response for section 4.
What did you learn from the changes you have made?
I learnt how to tinker with SQL code!! it was a challenge since i'm new to this, but I learnt new commands.
Was there another approach you were thinking about making? If so, what approach(es) were you thinking of?
I wonder whether there are more efficient/shorter ways of coding for some of the questions.
Were there any challenges? If so, what issue(s) did you face? How did you overcome it?
I struggled with the syntax a little bit, and more of the time I was either missing or added a ;/, somewhere.
Challenge this week was also mostly time-related (sorry for the late submission!)
How were these changes tested?
I coded in DB SQlite and ran each code each time to make sure that they worked.
A reference to a related issue in your repository (if applicable)
Checklist