Introduction:
Raw data is rarely enough—you often need summaries. How many orders were placed today? What’s the average
price of all products? Aggregate functions condense multiple rows into a single summary value. By pairing them with
GROUP BY, you can produce category-wise summaries, turning complex data sets into understandable
insights.
What You’ll Learn:
- Common Aggregates (COUNT, SUM, AVG, MIN, MAX): Quickly derive metrics or statistics from large datasets.
- GROUP BY: Categorize and break down data by groups (like categories, cities, or departments) before applying aggregates.
This step transforms raw numbers into meaningful narratives, helping you answer the “bigger picture” questions.
E-Commerce Dataset:
Welcome to our dataset, a comprehensive collection designed to simulate a rich, real-world environment for SQL practice.
Schema Information
| Column Name | Data Type | Description |
|---|---|---|
| customer_id | INTEGER | Unique identifier for each customer |
| first_name | TEXT | Customer’s first name |
| last_name | TEXT | Customer’s last name |
| TEXT | Customer’s email address | |
| city | TEXT | City of residence |
| join_date | DATE | Date the customer joined |
| phone | TEXT | Customer’s phone number |
| Column Name | Data Type | Description |
|---|---|---|
| city_id | INTEGER | Unique identifier for each city |
| city_name | TEXT | Name of the city |
| state | TEXT | State where the city is located |
| Column Name | Data Type | Description |
|---|---|---|
| order_id | INTEGER | Unique identifier for each order |
| customer_id | INTEGER | ID of the customer who placed the order |
| employee_id | INTEGER | ID of the employee who handled the order |
| order_date | DATE | Date when the order was placed |
| status | TEXT | Order status (e.g., Shipped, Cancelled, Pending) |
| total_amount | REAL | Total amount for the order |
| Column Name | Data Type | Description |
|---|---|---|
| order_id | INTEGER | Associated order identifier |
| product_id | INTEGER | Identifier for the product |
| quantity | INTEGER | Quantity of the product in the order |
| unit_price | REAL | Price per unit of the product |
| Column Name | Data Type | Description |
|---|---|---|
| product_id | INTEGER | Unique identifier for each product |
| product_name | TEXT | Name of the product |
| category_id | INTEGER | Category to which the product belongs |
| unit_price | REAL | Price per unit of the product |
| stock_quantity | INTEGER | Quantity of items available in stock |
| creation_date | DATE | Date the product was added to the system |
| Column Name | Data Type | Description |
|---|---|---|
| supplier_id | INTEGER | Unique identifier for each supplier |
| supplier_name | TEXT | Name of the supplier |
| city | TEXT | City where the supplier is located |
| products_supplied | TEXT | List of product IDs supplied (e.g., [90,91,33,121]) |
| Column Name | Data Type | Description |
|---|---|---|
| movement_id | INTEGER | Unique identifier for each movement record |
| product_id | INTEGER | Product being moved |
| change_quantity | INTEGER | Change in quantity (+/-) |
| movement_date | DATE | Date of the inventory movement |
| reason | TEXT | Explanation (Sale, Restock, Adjustment, etc.) |
| Column Name | Data Type | Description |
|---|---|---|
| employee_id | INTEGER | Unique identifier for each employee |
| first_name | TEXT | Employee’s first name |
| last_name | TEXT | Employee’s last name |
| department_id | INTEGER | Identifier of the department |
| hire_date | DATE | Date the employee was hired |
| salary | INTEGER | Employee’s salary |
| Column Name | Data Type | Description |
|---|---|---|
| department_id | INTEGER | Unique identifier for each department |
| department_name | TEXT | Name of the department |
| location | TEXT | Location of the department |
| Column Name | Data Type | Description |
|---|---|---|
| shipping_id | INTEGER | Unique identifier for each shipping record |
| order_id | INTEGER | Order to which the shipping address belongs |
| address | TEXT | Street address for shipping |
| city | TEXT | Shipping city |
| state | TEXT | Shipping state |
| postal_code | INTEGER | Shipping postal/ZIP code |
| Column Name | Data Type | Description |
|---|---|---|
| category_id | INTEGER | Unique identifier for each category |
| category_name | TEXT | Name of the category |
This wealth of interconnected data will allow you to work through exercises that involve aggregations, group bys, and more—building not just technical SQL proficiency, but also a deeper understanding of data relationships and reporting in a realistic business context.
