London 9_Natalie Zablotska_SQL_week2 - #29
Conversation
|
Kudos, SonarCloud Quality Gate passed!
|
Alexander-Rennie
left a comment
There was a problem hiding this comment.
Great effort! There is nothing really wrong here, but some minor comments have been annotated.
| ```sql | ||
| select * from products where product_name ilike '%socks%'; | ||
| ``` | ||
|
|
There was a problem hiding this comment.
CORRECT (although do note your query will also return products containing 'SOCKS', or anything mixed case)
| where pa.unit_price > 100 | ||
| order by unit_price desc; | ||
| ``` | ||
|
|
| from products p INNER JOIN | ||
| product_availability pa on (p.id = pa.prod_id) | ||
| order by unit_price desc limit 5; | ||
| ``` |
| LEFT JOIN products p on (p.id = pa.prod_id) | ||
| where s.country = 'United Kingdom' | ||
| order by pa.unit_price desc; | ||
| ``` |
There was a problem hiding this comment.
Whilst this is essentially correct, be careful about the use of LEFT JOIN instead of INNER JOIN, depending upon the output you require. The question here suggests we are interested in products, whereas suppliers here who have no product_availability or product may still yield rows in this output. (See https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-joins/ for clarification.)
| order_items oi on (o.id = oi.order_id) | ||
| INNER JOIN customers c on (o.customer_id = c.id) | ||
| where c.name = 'Hope Crosby'; | ||
| ``` |
| INNER JOIN order_items oi on (oi.product_id = p.id and oi.supplier_id = pa.supp_id) | ||
| INNER JOIN orders o on (o.id = oi.order_id) | ||
| where o.order_reference = 'ORD006'; | ||
|
|
| INNER JOIN suppliers s on (s.id = oi.supplier_id) | ||
| INNER JOIN orders o on (oi.order_id = o.id) | ||
| inner join customers c on (c.id = o.customer_id); | ||
| ``` |
There was a problem hiding this comment.
This is essentially correct, although the earlier point about LEFT JOINs also applies here. If we are only interested in products from orders, we should be careful to avoid products which have not been ordered. Also, it is generally good practice to be consistent in the casing of SQL keywords, for purposes of readability and style rather than any impact on functionality. (Here, for example, 'select' is lower case, 'LEFT JOIN' is upper, and 'INNER JOIN' appears as both.)
Learners, PR Template
Self checklist
Changelist
Briefly explain your PR.
Questions
Ask any questions you have for your reviewer.