Skip to content

London 9_Natalie Zablotska_SQL_week2 #29

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
nataliiazab wants to merge 2 commits into from
Closed

London 9_Natalie Zablotska_SQL_week2 #29

Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
0296474
completed E-Commerce
nataliiazab May 20, 2023
f16621c
fixed duplicates
nataliiazab May 20, 2023
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
60 changes: 60 additions & 0 deletions E-Commerce/readme.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -46,13 +46,73 @@ erDiagram
Write SQL queries to complete the following tasks:

- [ ] List all the products whose name contains the word "socks"

```sql
select * from products where product_name ilike '%socks%';
```

@Alexander-Rennie Alexander-Rennie Jun 26, 2023 •
edited
Loading

Copy link
Copy Markdown

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

CORRECT (although do note your query will also return products containing 'SOCKS', or anything mixed case)

- [ ] List all the products which cost more than 100 showing product id, name, unit price, and supplier id

```sql
select p.id, p.product_name, pa.unit_price, pa.supp_id
from products p INNER JOIN
product_availability pa on (p.id = pa.prod_id)
where pa.unit_price > 100
order by unit_price desc;
```

@Alexander-Rennie Alexander-Rennie Jun 26, 2023

Copy link
Copy Markdown

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

CORRECT

- [ ] List the 5 most expensive products

```sql
select p.id, p.product_name, pa.unit_price
from products p INNER JOIN
product_availability pa on (p.id = pa.prod_id)
order by unit_price desc limit 5;
```

@Alexander-Rennie Alexander-Rennie Jun 26, 2023

Copy link
Copy Markdown

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

CORRECT


- [ ] List all the products sold by suppliers based in the United Kingdom. The result should only contain the columns product_name and supplier_name


```sql
select p.product_name, s.supplier_name
from suppliers s LEFT JOIN
product_availability pa on (s.id = pa.supp_id)
LEFT JOIN products p on (p.id = pa.prod_id)
where s.country = 'United Kingdom'
order by pa.unit_price desc;
```

@Alexander-Rennie Alexander-Rennie Jun 26, 2023

Copy link
Copy Markdown

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

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.)



- [ ] List all orders, including order items, from customer named Hope Crosby
```sql
select * from orders o INNER JOIN
order_items oi on (o.id = oi.order_id)
INNER JOIN customers c on (o.customer_id = c.id)
where c.name = 'Hope Crosby';
```

@Alexander-Rennie Alexander-Rennie Jun 26, 2023

Copy link
Copy Markdown

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

CORRECT

- [ ] List all the products in the order ORD006. The result should only contain the columns product_name, unit_price, and quantity

```sql
select p.product_name, pa.unit_price, oi.quantity
from products p INNER JOIN
product_availability pa on (p.id = pa.prod_id)
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';

@Alexander-Rennie Alexander-Rennie Jun 26, 2023

Copy link
Copy Markdown

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

CORRECT

```

- [ ] 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

```sql
select c.name, o.order_reference, o.order_date, p.product_name, s.supplier_name, oi.quantity
from products p
LEFT JOIN order_items oi on (oi.product_id = p.id)
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);
```

@Alexander-Rennie Alexander-Rennie Jun 26, 2023

Copy link
Copy Markdown

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

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.)


## Acceptance Criteria

- [ ] The `cyf_ecommerce` database is imported and set up correctly
Expand Down