E-commerce Database Management System

Project Overview

This project demonstrates an advanced e-commerce database system using SQL. It includes tables for managing customers, products, orders, suppliers, categories, and order details. The project showcases various SQL queries to retrieve useful insights and reports from the database.

Table of Contents

• Project Overview
• Database Schema
  • Categories
  • Suppliers
  • Products
  • Customers
  • Orders
  • OrderDetails
• Sample Data
  • Categories
  • Suppliers
  • Products
  • Customers
  • Orders
  • OrderDetails
• Project Structure
• How to Use
• Examples of Queries
• Contributing
• License

Database Schema

Categories

• CategoryID: INT, Primary Key
• CategoryName: VARCHAR(50)
• Descriptions: VARCHAR(255)

Suppliers

• SupplierID: INT, Primary Key
• SupplierName: VARCHAR(100)
• ContactName: VARCHAR(50)
• StreetAddress: VARCHAR(255)
• City: VARCHAR(50)
• Country: VARCHAR(50)
• Phone: VARCHAR(20)

Products

• ProductID: INT, Primary Key
• ProductName: VARCHAR(100)
• CategoryID: INT, Foreign Key
• SupplierID: INT, Foreign Key
• Price: DECIMAL(10, 2)
• StockQuantity: INT

Customers

• CustomerID: INT, Primary Key
• FirstName: VARCHAR(50)
• LastName: VARCHAR(50)
• Email: VARCHAR(100)
• Phone: VARCHAR(20)
• StreetAddress: VARCHAR(255)
• City: VARCHAR(50)
• Country: VARCHAR(50)

Orders

• OrderID: INT, Primary Key
• OrderDate: DATE
• CustomerID: INT, Foreign Key
• TotalAmount: DECIMAL(10, 2)

OrderDetails

• OrderDetailID: INT, Primary Key
• OrderID: INT, Foreign Key
• ProductID: INT, Foreign Key
• Quantity: INT
• UnitPrice: DECIMAL(10, 2)

Sample Data

Categories

• Electronics
• Clothing
• Home & Kitchen
• Books
• Sports

Suppliers

• TechSupplier
• FashionSupplier
• HomeSupplier
• BookSupplier
• SportsSupplier

Products

• Laptop, Smartphone, Jeans, T-Shirt, Blender, Toaster, Fiction Book, Science Book, Football, Tennis Racket

Customers

• Alice Thomas, Jane Smith, Emily Clark, Mark Taylor, Laura Wilson, Hollie Barker

Orders

• 5 orders placed with varying amounts

OrderDetails

• Details of products ordered with quantities and prices

Project Structure

The project repository is structured as follows:

    sql-ecommerce-database/
    │
    ├── create_tables.sql       # SQL script to create tables
    ├── insert_data.sql         # SQL script to insert sample data
    ├── queries.sql             # SQL script with all the advanced queries
    ├── README.md               # Project overview and documentation
    └── screenshots/            # Folder containing screenshots of query results
        ├── query1_result.png
        ├── query2_result.png
        └── ...

How to Use

To set up the database:

1. Execute create_tables.sql to create the database schema.
2. Execute insert_data.sql to populate the tables with sample data.

To run queries:

1. Execute the queries in queries.sql to analyze different aspects of the eCommerce database.

Examples of Queries

Here are some examples of the SQL queries included:

• Retrieve all customers who have placed orders.
• Listdown all products with their category and supplier information.
• Calculate total sales per product category.
• Listdown the Product Popularity (by no. of Quantity sold).
• Calculate average order value per customer.
• Listdown Orders with Total Quantity and Total Price of Products in Each Order.
• Listdown All Suppliers with Their Products and the Total Stock Quantity.
• Get Total Revenue Generated by Each Supplier.
• Find Customers Who Have Placed Orders Exceeding 100 GBP.
• Listdown Products with Less Than 50 Units in Stock.
• Calculate Average Order Value per Month.
• Listdown All Products Sold in a Specific Month.
• Calculate the Total Number of Orders Placed by Each Customer.

Contributing

Feel free to contribute to this project by suggesting improvements, optimizing queries, or adding new features. Fork the repository, make your changes, and submit a pull request.

License

This project is licensed under the MIT License - see the LICENSE file for details.