This repository contains the solution for Task 4 of the Elevate Labs Data Analyst Internship. The objective of this task was to use SQL queries to extract, manipulate, and analyze structured data from an e-commerce database.
Tools Used Database: MySQL
IDE: MySQL Workbench
Project Steps Database Creation: Established a new database (schema) named Ecommerce_SQL_Database in MySQL Workbench.
Table Creation: Defined and created four tables to model a simple e-commerce system:
Customers: Stores customer information.
Products: Stores product details and prices.
Orders: Stores order-level information, linking to customers.
Order_Items: A junction table linking orders to products and storing quantities.
Data Insertion: Populated the tables with sample data to simulate real-world transactions.
SQL Analysis: Wrote and executed a series of SQL queries to analyze the data, following the task hints.
Deliverables: Saved the analysis queries in the ecommerce_analysis.sql file and captured screenshots of the query outputs, as required.
SQL Queries Overview The ecommerce_analysis.sql file contains queries that demonstrate the following concepts as required by the task:
Basic Filtering (SELECT, WHERE, ORDER BY): A query to find products above a certain price, ordered from highest to lowest.
Aggregates & Grouping (SUM, GROUP BY): A query to calculate the total revenue generated by each product.
Joins (LEFT JOIN): A query to list all customers and the total number of orders they have placed, including customers with zero orders.
Subqueries: A query to find the names of customers who purchased a specific product.
Views (CREATE VIEW): Created a view named v_OrderRevenue to simplify future queries for total revenue per order.
Optimization (CREATE INDEX): Added an index to the email column in the Customers table to speed up lookups.