📊 Sales Report (SQL Preparation)

This note explains how to set up your database for the Sales Report template.

erDiagram
    Customers ||--o{ Sales : has
    Products ||--o{ Sales : has

    Customers {
        int customer_id
        string customer_name
        string region
    }
    
    Products {
        int product_id
        string product_name
        string category
    }
    
    Sales {
        int sales_id
        int customer_id
        int product_id
        int quantity
        decimal price
        date sale_date
        string channel
   }

-- Mysql schema
CREATE TABLE Customers (
    customer_id int NOT NULL AUTO_INCREMENT,
    customer_name varchar(255),
    region varchar(255),
    PRIMARY KEY (customer_id)
);
CREATE TABLE Products (
    product_id int NOT NULL AUTO_INCREMENT,
    product_name varchar(255),
    category varchar(255),
    PRIMARY KEY (product_id)
);
CREATE TABLE Sales (
    sales_id int NOT NULL AUTO_INCREMENT,
    customer_id int,
    product_id int,
    quantity int,
    price DECIMAL(10,2),
    sale_date DATE,
    channel varchar(255),
    PRIMARY KEY (sales_id)
);

Populate data

INSERT INTO Customers (customer_name, region)
VALUES
('Charlie Chaplin', 'North'),
('Beyonce Beatle', 'South'),
('Mick Mercury', 'East'),
('Diana Drake', 'West'),
('Freddie Fitzgerald', 'North'),
('Amy Adams', 'South'),
('Jennifer Joplin', 'East'),
('Elvis Evans', 'West');

INSERT INTO Products (product_name, category)
VALUES
('Falcon Flux', 'Hypercar'),
('Nebula Navigator', 'Spacecraft'),
('Quantum Quattro', 'Electric Car'),
('Galaxy Glider', 'Airbike'),
('Orion Overdrive', 'Hypercar'),
('Zephyr Zest', 'Spacecraft'),
('Comet Cruiser', 'Electric Car'),
('Astro Antelope', 'Airbike');

INSERT INTO Sales (customer_id, product_id, quantity, price, sale_date, channel)
VALUES
(1, 1, 5, 150000.00, '2023-01-15', 'Facebook'),
(1, 1, 5, 50000.00, '2023-01-15', 'Google'),
(1, 1, 5, 25000.00, '2023-01-15', 'Linkedin'),
(1, 2, 10, 200000.00, '2023-01-20', 'Facebook'),
(2, 1, 3, 150000.00, '2023-02-05', 'Google'),
(3, 3, 8, 60000.00, '2023-02-10', 'Linkedin'),
(4, 4, 2, 50000.00, '2023-02-15', 'Google'),
(2, 2, 5, 200000.00, '2023-02-20', 'Facebook'),
(3, 1, 7, 150000.00, '2023-03-05', 'Referral'),
(4, 3, 4, 60000.00, '2023-03-10', 'Google'),
(1, 4, 6, 50000.00, '2023-03-15', 'Referral'),
(3, 2, 9, 200000.00, '2023-03-20', 'Facebook'),
(5, 5, 7, 180000.00, '2023-04-05', 'Facebook'),
(6, 6, 4, 220000.00, '2023-04-10', 'Facebook'),
(7, 7, 5, 65000.00, '2023-04-15', 'Linkedin'),
(8, 8, 6, 52000.00, '2023-04-20', 'Referral');

---

• ✅ Run these SQL scripts in your MySQL database.
• ✅ Use the Sales Report template to analyze the data visually.