task.md

E-Commerce Database

In this homework, you are going to work with an ecommerce database. In this database, you have products that consumers can buy from different suppliers. Customers can create an order and several products can be added in one order.

Submission

Below you will find a set of tasks for you to complete to set up a databases of students and mentors.

To submit this homework write the correct commands for each question here:

1. Retrieve all the customers names and addresses who lives in United States SELECT name, address FROM customers WHERE country='United States';

2. Retrieve all the customers ordered by ascending name SELECT * FROM customers ORDER BY name;

3. Retrieve all the products which cost more than 100 SELECT * FROM products WHERE unit_price > 100;

4. Retrieve all the products whose name contains the word socks SELECT * FROM products WHERE product_name LIKE '%socks%';

5. Retrieve the 5 most expensive products SELECT * FROM products ORDER BY unit_price DESC LIMIT 5;

6. Retrieve all the products with their corresponding suppliers. The result should only contain the columns product_name, unit_price and supplier_name SELECT p.product_name, p.unit_price, s.supplier_name FROM products p INNER JOIN suppliers s ON s.id = p.supplier_id;

7. Retrieve all the products sold by suppliers based in the . The result should only contain the columns product_name and supplier_name. SELECT p.product_name, s.supplier_name FROM products p INNER JOIN suppliers s ON s.id = p.supplier_id WHERE s.country = 'United Kingdom';

8. Retrieve all orders from customer ID 1 SELECT * FROM orders WHERE customer_id = 1;

9. Retrieve all orders from customer named Hope Crosby SELECT o.* FROM orders o INNER JOIN customers c ON c.id = o.customer_id WHERE c.name = 'Hope Crosby';

10. Retrieve all the products in the order ORD006. The result should only contain the columns product_name, unit_price and quantity. SELECT p.product_name, p.unit_price, oi.quantity FROM order_items oi INNER JOIN products p ON p.id = oi.product_id INNER JOIN orders o ON o.id = oi.order_id WHERE o.order_reference = 'ORD006';

11. Retrieve 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. SELECT c.name, o.order_reference, o.order_date, p.product_name, s.supplier_name, oi.quantity FROM customers c INNER JOIN orders o ON c.id = o.customer_id INNER JOIN order_items oi ON oi.order_id = o.id INNER JOIN products p ON oi.product_id = p.id INNER JOIN suppliers s ON s.id = p.supplier_id;

12. Retrieve the names of all customers who bought a product from a supplier from China. SELECT DISTINCT name FROM customers c INNER JOIN orders o ON c.id = o.customer_id INNER JOIN order_items oi ON oi.order_id = o.id INNER JOIN products p ON oi.product_id = p.id INNER JOIN suppliers s ON p.supplier_id = s.id WHERE s.country = 'China';

When you have finished all of the questions - open a pull request with your answers to the Databases-Homework repository.

Setup

To prepare your environment for this homework, open a terminal and create a new database called cyf_ecommerce:

createdb cyf_ecommerce

Import the file cyf_ecommerce.sql in your newly created database:

psql -d cyf_ecommerce -f cyf_ecommerce.sql

Open the file cyf_ecommerce.sql in VSCode and make sure you understand all the SQL code. Take a piece of paper and draw the database with the different relations between tables. Identify the foreign keys and make sure you understand the full database schema.

Task

Once you understand the database that you are going to work with, solve the following challenge by writing SQL queries using everything you learned about SQL:

1. Retrieve all the customers names and addresses who lives in United States
2. Retrieve all the customers ordered by ascending name
3. Retrieve all the products which cost more than 100
4. Retrieve all the products whose name contains the word socks
5. Retrieve the 5 most expensive products
6. Retrieve all the products with their corresponding suppliers. The result should only contain the columns product_name, unit_price and supplier_name
7. Retrieve all the products sold by suppliers based in the United Kingdom. The result should only contain the columns product_name and supplier_name.
8. Retrieve all orders from customer ID 1
9. Retrieve all orders from customer named Hope Crosby
10. Retrieve all the products in the order ORD006. The result should only contain the columns product_name, unit_price and quantity.
11. Retrieve 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.
12. Retrieve the names of all customers who bought a product from a supplier from China.