London 9 _Sana Asaf _Module-Databases_Week 2 - #32
Closed
SanaAsaf wants to merge 1 commit into
Closed
Conversation
|
Kudos, SonarCloud Quality Gate passed!
|
Alexander-Rennie
left a comment
There was a problem hiding this comment.
Great answers (some minor observations included below)
| - [ ] List all the products in the order ORD006. The result should only contain the columns product_name, unit_price, and quantity | ||
| - [ ] List all the products with their supplier for all orders of all customers. The result should only contain the columns name (from customer), order_reference, order_date, product_name, supplier_name, and quantity | ||
| - [*] List all the products whose name contains the word "socks" | ||
| SELECT * FROM products WHERE product_name LIKE ‘%socks%’; |
| SELECT prod_id, product_name, unit_price, supp_id | ||
| FROM product_availability pa INNER JOIN products p on (pa.prod_id=p.id) | ||
| INNER JOIN suppliers s on (pa.supp_id=s.id) | ||
| WHERE unit_price > 100; |
There was a problem hiding this comment.
CORRECT - Whilst this is not wrong, there is no need to join in the suppliers table, as you are only returning no details from it (only the supp_id which is in product_availability)
| WHERE unit_price > 100; | ||
|
|
||
| - [*] List the 5 most expensive products | ||
| select product_name , unit_price FROM product_availability pa INNER JOIN products p on (pa.prod_id=p.id) by unit_price desc Limit 5; |
| - [*] List all the products sold by suppliers based in the United Kingdom. The result should only contain the columns product_name and supplier_name | ||
| SELECT product_name , supplier_name | ||
| FROM product_availability pa INNER JOIN products p on (pa.prod_id=p.id) | ||
| INNER JOIN suppliers s on (pa.supp_id=s.id) where s.country='United Kingdom'; |
| SELECT order_id,product_id,supplier_id,quantity | ||
| FROM order_items oi INNER JOIN orders o ON (oi.order_id=o.id) | ||
| INNER JOIN customers c ON (o.customer_id=c.id) | ||
| where c.name = 'Hope Crosby'; |
| FROM order_items oi INNER JOIN orders o ON (oi.order_id=o.id) | ||
| INNER JOIN products p ON (p.id=oi.product_id) | ||
| INNER JOIN product_availability pa ON (p.id=pa.prod_id) | ||
| WHERE o.order_reference = 'ORD006'; |
| INNER JOIN order_items oi ON (oi.order_id=o.id) | ||
| INNER JOIN products p ON (p.id=oi.product_id) | ||
| INNER JOIN product_availability pa ON (p.id=pa.prod_id) | ||
| INNER JOIN suppliers s ON (pa.supp_id=s.id); |
There was a problem hiding this comment.
CORRECT - Whilst this is not wrong, there is no need to join in the product_availability table, as you are returning no details from it, and could link to suppliers via oi.supplier_id
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
3 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.
ER- Diagram
(https://github.com/CodeYourFuture/Module-Databases/assets/85436780/5d1025c6-450e-4168-91c6-36cce474c210)
Learners, PR Template
Self checklist
Changelist
Briefly explain your PR.
Questions
Ask any questions you have for your reviewer.