E-commerce Project

📖 Overview

This project simulates a small e-commerce platform using SQLite. It demonstrates relational database design, data insertion, and SQL querying to extract meaningful insights. The project is designed to showcase practical skills in SQL, database modeling, and data analysis.

🗂 Database Structure:

Tables & Relationships

1. Users

• Stores customer information.
• Columns: user_id (PK), first_name, last_name,email, signup_date

2. Products

• Stores product details.
• Columns: product_id (PK), name, category, price, stock

3. Orders

• Represents orders placed by users.
• Columns: order_id (PK), user_id (FK), order_date, total_amount

4. Order_Items

• Details products in each order.
• Columns: order_item_id (PK), order_id (FK), product_id (FK), quantity, price

5. Reviews

• Stores product reviews by users.
• Columns: review_id (PK), product_id (FK), user_id (FK), rating, comment, review_date

🧩 Diagram:**

💡 The project demonstrates:

• Creation of relational tables with primary and foreign keys.

• Inserting and managing sample data.

• Writing SQL queries for:

  - Basic data retrieval (SELECT, WHERE)

  • List all users:

    

  • List all products in Electronics:

    

  • Get all orders of 1st user:

    

  - Joins across multiple tables

  • Get orders with product details:

    

  • Get average rating per product:

    

  - Aggregations (SUM, COUNT, AVG)

  • Total sales per product:

    

  • Number of orders per user:

    

  - Advanced queries with subqueries and HAVING

  • Products never ordered:

    

  • Users who ordered more than once: