JavaScript Project – Sales Data Analysis

Program 1

const db = require('./db');
const readline = require('readline');

const rl = readline.createInterface({
  input: process.stdin,
  output: process.stdout
});

function showMenu() {
  console.log("\n ------- Sales Data Analysis --------");
  console.log("1. Add Customer");
  console.log("2. Add Product");
  console.log("3. Add Sales Transaction");
  console.log("4. Total Sales by Product");
  console.log("5. Total Sales by Customer");
  console.log("6. Monthly Sales Summary");
  console.log("7. Top Selling Products");
  console.log("8. Sales by Region");
  console.log("9. Average Sale per Transaction");
  console.log("0. Exit");
  console.log("------------------------------------------")
  rl.question("Choose an option: ", handleChoice);
}

async function handleChoice(choice) {
  switch (choice.trim()) {
    case '1':
      rl.question("Customer ID: ", id => {
        rl.question("Name: ", name => {
          rl.question("Region: ", async region => {
            await db.execute("INSERT INTO customers VALUES (?, ?, ?)", [id,name,region]);
            console.log("Customer added.");
            showMenu();
          });
        });
      });
      break;

    case '2':
      rl.question("Product ID: ", id => {
        rl.question("Name: ", name => {
          rl.question("Price: ", async price => {
            await db.execute("INSERT INTO products VALUES (?, ?, ?)", [id, name, price]);
            console.log("Product added.");
            showMenu();
          });
        });
      });
      break;

    case '3':
      rl.question("Transaction ID: ", id => {
        rl.question("Customer ID: ", customerId => {
          rl.question("Product ID: ", productId => {
            rl.question("Quantity: ", quantity => {
              rl.question("Date (YYYY-MM-DD): ", async date => {
                await db.execute("INSERT INTO sales VALUES (?, ?, ?, ?, ?)", [id, customerId, productId, quantity, date]);
                console.log("Transaction added.");
                showMenu();
              });
            });
          });
        });
      });
      break;
    //Total Sales by Product
    case '4': {
      const [rows] = await db.query(`
        SELECT p.product_name, SUM(s.quantity * p.price) AS total_sales
        FROM sales s
        JOIN products p ON s.product_id = p.product_id
        GROUP BY s.product_id
        ORDER BY total_sales DESC
      `);
      console.log("\n Total Sales by Product:");
      rows.forEach(r => console.log(`${r.product_name} - ₹${Number(r.total_sales).toFixed(2)}`));
      showMenu();
      break;
    }
  //Total Sales by Customer
    case '5': {
      const [rows] = await db.query(`
        SELECT c.customer_name, SUM(s.quantity * p.price) AS total_spent
        FROM sales s
        JOIN products p ON s.product_id = p.product_id
        JOIN customers c ON s.customer_id = c.customer_id
        GROUP BY s.customer_id
        ORDER BY total_spent DESC
      `);
      console.log("\n Total Sales by Customer:");
      rows.forEach(r => console.log(`${r.customer_name} - ₹${Number(r.total_spent).toFixed(2)}`));
      showMenu();
      break;
    }
  //Monthly Sales Summary
    case '6': {
      const [rows] = await db.query(`
        SELECT DATE_FORMAT(s.sale_date, '%Y-%m') as month, SUM(s.quantity * p.price) as total
        FROM sales s
        JOIN products p ON s.product_id = p.product_id
        GROUP BY month
        ORDER BY month
      `);
      console.log("\n Monthly Sales Summary:");
      rows.forEach(r => console.log(`${r.month} - ₹${Number(r.total).toFixed(2)}`));
      showMenu();
      break;
    }
  //Top Selling Products
    case '7': {
      const [rows] = await db.query(`
        SELECT p.product_name, SUM(s.quantity) as total_units
        FROM sales s
        JOIN products p ON s.product_id = p.product_id
        GROUP BY p.product_id
        ORDER BY total_units DESC
        LIMIT 5
      `);
      console.log("\n Top Selling Products:");
      rows.forEach(r => console.log(`${r.product_name} - ${r.total_units} units`));
      showMenu();
      break;
    }
//Sales by Region
    case '8': {
      const [rows] = await db.query(`
        SELECT c.region, SUM(s.quantity * p.price) AS total_sales
        FROM sales s
        JOIN customers c ON s.customer_id = c.customer_id
        JOIN products p ON s.product_id = p.product_id
        GROUP BY c.region
        ORDER BY total_sales DESC
      `);
      console.log("\n Sales by Region:");
      rows.forEach(r => console.log(`${r.region} - ₹${Number(r.total_sales).toFixed(2)}`));
      showMenu();
      break;
    }
//Average Sale per Transaction
    case '9': {
      const [rows] = await db.query(`
        SELECT AVG(s.quantity * p.price) AS avg_sale
        FROM sales s
        JOIN products p ON s.product_id = p.product_id
      `);
      console.log(`\n Average Sale per Transaction: ₹${Number(rows[0].avg_sale).toFixed(2)}`);
      showMenu();
      break;
    }

    case '0':
      console.log("Exiting...");
      rl.close();
      break;

    default:
      console.log("Invalid option!");
      showMenu();
  }
}

showMenu();

Program 2

const mysql = require('mysql2');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'root',
  database: 'sales_db'
});

module.exports = pool.promise();

Program 3

***************Sales Data Analysis Console Application using JavaScript (Node.js) with MySQL*************



Features Included:

1. Insert Options:

3. Add Customer

3. Add Product

Add Sales Transaction

Data Analysis Options:

1. Total Sales by Product

2. Total Sales by Customer

3. Monthly Sales Summary

4. Top Selling Products

5. Sales by Region

6. Average Sale per Transaction

CREATE DATABASE sales_db;
USE sales_db;

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(100),
  region VARCHAR(100)
);

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(100),
  price DECIMAL(10, 2)
);

CREATE TABLE sales (
  transaction_id INT PRIMARY KEY,
  customer_id INT,
  product_id INT,
  quantity INT,
  sale_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);




INSERT INTO customers (customer_id, customer_name, region) VALUES
(1, 'Amit Sharma', 'North'),
(2, 'Priya Verma', 'South'),
(3, 'Rohit Singh', 'East'),
(4, 'Neha Kapoor', 'West'),
(5, 'Vikas Mehta', 'North');
----------------------------------------------------------------------------------------

INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Laptop', 55000.00),
(2, 'Smartphone', 20000.00),
(3, 'Printer', 8000.00),
(4, 'Tablet', 15000.00),
(5, 'Monitor', 12000.00);
--------------------------------------------------------------------------------------

INSERT INTO sales (transaction_id, customer_id, product_id, quantity, sale_date) VALUES
(101, 1, 1, 1, '2024-01-10'),
(102, 2, 2, 2, '2024-01-12'),
(103, 3, 3, 1, '2024-02-05'),
(104, 1, 2, 1, '2024-02-15'),
(105, 4, 5, 3, '2024-03-01'),
(106, 5, 4, 2, '2024-03-12'),
(107, 3, 1, 1, '2024-04-18'),
(108, 2, 3, 2, '2024-04-25'),
(109, 1, 5, 1, '2024-04-27'),
(110, 5, 2, 1, '2024-05-01');