In this project, you will work with an e-commerce database. The database has products that consumers can buy from different suppliers. Customers can create an order and add several products in one order.

• Use SQL queries to retrieve specific data from a database
• Draw a database schema to visualize relationships between tables
• Label database relationships defined by the REFERENCES keyword in CREATE TABLE commands

To prepare your environment, 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

psql big-spender

Open the file cyf_ecommerce.sql in VSCode and examine the SQL code. Take a piece of paper and draw the database with the different relationships between tables (as defined by the REFERENCES keyword in the CREATE TABLE commands). Identify the foreign keys and make sure you understand the full database schema.

Don't skip this step. You may one day be asked at interview to draw a database schema. Sketching systems is a valuable skill for back end developers and worth practising. If you're interested in systems design, you may also want to take a course on Udemy.

You can even draw relationship diagrams on GitHub:

erDiagram
    customers {
        id INT PK
        name VARCHAR(50) NOT NULL
    }
    customers ||--o{ orders : places

/ Write SQL queries to complete the following tasks:

• List all the products whose name contains the word "socks" SELECT * FROM products WHERE product_name LIKE '%socks%';

  Diagram link to query 1: https://drive.google.com/file/d/1-01XnzYTWFBFMyA_kKhA6UvrJ-anLiXN/view?usp=sharing
  

• List all the products which cost more than 100 showing product id, name, unit price, and supplier id SELECT id AS product_id, product_name, unit_price, supp_id AS supplier_id FROM products JOIN product_availability ON id = prod_id WHERE unit_price > 100;

  Diagram link to query 2: https://drive.google.com/file/d/1hVbsw6rQ0ywqX3uw2hdtQMtB-QSCFRcs/view?usp=sharing
  

• List the 5 most expensive products select unit_price from product_availability order by unit_price DESC limit 5;

  Diagram link to query 3: https://drive.google.com/file/d/1Rt4WaQnZV1y9rZ01Zv66pO1OY1yElj2n/view?usp=sharing
  

• List all the products sold by suppliers based in the United Kingdom. The result should only contain the columns product_name and supplier_name. In this answer i used aliases. SELECT product_name, supplier_name FROM products JOIN product_availability ON id = prod_id JOIN suppliers s ON supp_id = s.id WHERE country = 'United Kingdom';

Diagram link on query 4: https://drive.google.com/file/d/1sDqZSZ8znMNKihS71hFBBj98CCYXwUN8/view?usp=sharing

• List all orders, including order items, from customer named Hope Crosby. In this answer I use aliases. SELECT name AS customer_name, p.product_name, oi.quantity FROM orders o JOIN customers c ON o.customer_id = c.id JOIN order_items oi ON o.id = oi.order_id JOIN products p ON oi.product_id = p.id WHERE name = 'Hope Crosby';

Diagram link on query 5; https://drive.google.com/file/d/1FS9kUc2DJ93l1RG7ORC_1XUS_ld7DSkV/view?usp=sharing

• List all the products in the order ORD006. The result should only contain the columns product_name, unit_price, and quantity. In this answer I used aliases. SELECT p.product_name, pa.unit_price, oi.quantity FROM order_items oi JOIN products p ON oi.product_id = p.id JOIN product_availability pa ON oi.product_id = pa.prod_id AND oi.supplier_id = pa.supp_id JOIN orders o ON oi.order_id = o.id WHERE o.order_reference = 'ORD006';

  Diagram on query 6 link: https://drive.google.com/file/d/1dFeyYFILuXlN773QgKy23fweOaQZS0ip/view?usp=sharing
  

• 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. In this answer I use aliases. SELECT c.name, o.order_reference, o.order_date, p.product_name, s.supplier_name, oi.quantity FROM customers c JOIN orders o ON c.id = o.customer_id JOIN order_items oi ON o.id = oi.order_id JOIN products p ON oi.product_id = p.id JOIN product_availability pa ON oi.product_id = pa.prod_id AND oi.supplier_id = pa.supp_id JOIN suppliers s ON pa.supp_id = s.id;

Diagram on query 7: https://drive.google.com/file/d/1WFaeFO7YThEUzYIowneJrcJqvac1f_bV/view?usp=sharing

The diagram represents the relationships between the tables involved in the SQL code. Here is the explanation of the diagram:

The "customers" table has a one-to-many relationship with the "orders" table. Each customer can have multiple orders, but each order belongs to a single customer. The "orders" table has a one-to-many relationship with the "order_items" table. Each order can have multiple order items, but each order item belongs to a single order. The "order_items" table has a many-to-one relationship with the "products" table. Each order item is associated with a single product, but each product can be associated with multiple order items. The "order_items" table has a many-to-one relationship with the "suppliers" table. Each order item is associated with a single supplier, but each supplier can be associated with multiple order items. The "order_items" table has a many-to-one relationship with the "product_availability" table. Each order item is associated with a single product availability, but each product availability can be associated with multiple order items.

• The cyf_ecommerce database is imported and set up correctly
• The database schema is drawn correctly to visualize relationships between tables
• The SQL queries retrieve the correct data according to the tasks listed above
• The pull request with the answers to the tasks is opened on the main branch of the E-Commerce repository