Implementation

Usage documentation

An online version of the documentation is available on this site at the docs page. It is also available in the offline copy.

SQL statements

1. Get all Stores

Gets all store locations from the system.

SELECT location_name, address FROM Store;

2. New Customer

Registers a new customer in the system. Could be used in a scenario where a new customer is being signed up.

INSERT INTO Customer (
    first_name, last_name,
    email,
    phone_number
)
VALUES (
    'John', 'Doe',
    'john.doe@gmail.com',
    '0123456789'
);

3. New Product

Creates a new product in the system. Useful when a new product is introduced in stores.

INSERT INTO Product (
    name, description
)
VALUES (
    'USB-C to HDMI Adapter',
    'Adapter to connect USB-C devices to a TV or monitor'
);

4. Store’s available Inventory

Gets the stock available per store for a given product, in this case, product id 1. Returns the store name and the quantity of stock.

SELECT
    Store.location_name,
    Inventory.quantity
FROM ((Inventory
    INNER JOIN Product ON Inventory.product_id=Product.id)
    INNER JOIN Store ON Inventory.store_id=Store.id)
    
WHERE Product.id=1;

5. Get Employee’s store

Get the name and address of the store which an employee works at.

SELECT
    Employee.first_name, Employee.last_name,
    Store.location_name, Store.address
FROM Employee
    INNER JOIN Store ON Employee.store_id = Store.id;

6. Employees paid $100,000+

Gets all employee’s whose salary is higher than $100,000.

SELECT * FROM Employee WHERE salary>=100000;

7. New Sale

Creates a new sale within a store, with a customer and the employee who served them.

INSERT INTO Sale (
    store_id,
    customer_id,
    employee_id
)
VALUES (
    1, -- store_id
    18, -- customer_id
    1 -- employee_id
);

8. Add products to Sale

(After a sale has been created/initiated) Adds two products to the sale with id: 1. Use this query to get all products within the sale.

INSERT INTO SaleProduct (
    product_id,
    sale_id,
    quantity
)
VALUES (
    1, -- product_id
    1, -- sale_id
    1 -- quantity of this product
), (
    2, -- product_id
    1, -- sale_id
    3 -- quantity of this product
);

9. Get Sale products

Gets the products that were involved with a given sale, in this case, sale id 1.

SELECT
    Product.name, Product.description,
    SaleProduct.quantity
FROM SaleProduct
    INNER JOIN Product ON SaleProduct.product_id=Product.id

WHERE SaleProduct.sale_id=1; 

10. Get Sale info

Retrieves the store name, employee name, and customer name of a given sale, in this case, sale id 1.

SELECT
    Store.location_name AS store_name,
    Employee.first_name AS employee_first_name,
    Employee.last_name AS employee_last_name,
    Customer.first_name AS customer_first_name,
    Customer.last_name AS customer_last_name
FROM (((Sale
    INNER JOIN Store ON Sale.store_id=Store.id)
    INNER JOIN Employee ON Sale.employee_id=Employee.id)
    INNER JOIN Customer ON Sale.customer_id=Customer.id)

WHERE Sale.id=1;