Database Query Examples: E-commerce Product Catalog

This document provides SQL query examples for an e-commerce product catalog scenario, ranging from beginner to advanced levels. These queries are designed to be executed against a MySQL database, such as the one provisioned by the accompanying Terraform configuration.

Scenario: E-commerce Product Catalog

Imagine you are building an e-commerce platform. You need to manage products, categories, customers, and orders. Here's a simplified schema we'll use for our examples:

Tables:

• products:
  • product_id (INT, PK)
  • name (VARCHAR)
  • description (TEXT)
  • price (DECIMAL)
  • stock_quantity (INT)
  • category_id (INT, FK to categories)
  • created_at (DATETIME)
• categories:
  • category_id (INT, PK)
  • name (VARCHAR)
  • description (TEXT)
• customers:
  • customer_id (INT, PK)
  • first_name (VARCHAR)
  • last_name (VARCHAR)
  • email (VARCHAR)
  • registered_at (DATETIME)
• orders:
  • order_id (INT, PK)
  • customer_id (INT, FK to customers)
  • order_date (DATETIME)
  • total_amount (DECIMAL)
  • status (VARCHAR)
• order_items:
  • order_item_id (INT, PK)
  • order_id (INT, FK to orders)
  • product_id (INT, FK to products)
  • quantity (INT)
  • price_at_order (DECIMAL)

---

Beginner Queries

These queries cover basic data retrieval and manipulation.

1. Select all products

Retrieve all columns and rows from the products table.

SELECT * FROM products;

2. Select specific product details

Retrieve only the name and price for all products.

SELECT name, price FROM products;

3. Select products with a condition

Find all products with a price greater than 50.00.

SELECT name, price FROM products WHERE price > 50.00;

4. Insert a new product

Add a new product to the products table.

INSERT INTO products (name, description, price, stock_quantity, category_id, created_at)
VALUES ('Laptop Pro', 'Powerful laptop for professionals', 1200.00, 10, 1, NOW());

5. Update product stock

Update the stock_quantity for a specific product.

UPDATE products
SET stock_quantity = 15
WHERE product_id = 1;

6. Delete a product

Remove a product from the products table.

DELETE FROM products
WHERE product_id = 3;

---

Intermediate Queries

These queries involve joining tables, aggregation, and more complex filtering.

1. List products with their category names

Join products and categories tables to show product names alongside their category names.

SELECT p.name AS product_name, c.name AS category_name
FROM products p
JOIN categories c ON p.category_id = c.category_id;

2. Count products per category

Group products by category and count how many products are in each category.

SELECT c.name AS category_name, COUNT(p.product_id) AS total_products
FROM categories c
LEFT JOIN products p ON c.category_id = p.category_id
GROUP BY c.name;

3. Find customers who placed orders in the last 30 days

Use JOIN and date functions to find customers with recent orders.

SELECT DISTINCT cu.first_name, cu.last_name, cu.email
FROM customers cu
JOIN orders o ON cu.customer_id = o.customer_id
WHERE o.order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY);

4. Calculate total sales for each product

Sum the quantity * price_at_order from order_items for each product.

SELECT p.name AS product_name, SUM(oi.quantity * oi.price_at_order) AS total_sales
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.name
ORDER BY total_sales DESC;

5. Products with low stock (less than 5) and their category

Combine filtering and joining to identify products that need restocking.

SELECT p.name AS product_name, p.stock_quantity, c.name AS category_name
FROM products p
JOIN categories c ON p.category_id = c.category_id
WHERE p.stock_quantity < 5
ORDER BY p.stock_quantity ASC;

---

Advanced Queries

These queries involve subqueries, window functions, and more complex analytical operations.

1. Find the top 3 best-selling products by total quantity

Use a subquery or LIMIT with ORDER BY and GROUP BY.

SELECT p.name AS product_name, SUM(oi.quantity) AS total_quantity_sold
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.name
ORDER BY total_quantity_sold DESC
LIMIT 3;

2. Customers who have placed more than 5 orders

Use GROUP BY and HAVING to filter aggregated results.

SELECT cu.first_name, cu.last_name, COUNT(o.order_id) AS number_of_orders
FROM customers cu
JOIN orders o ON cu.customer_id = o.customer_id
GROUP BY cu.customer_id, cu.first_name, cu.last_name
HAVING COUNT(o.order_id) > 5;

3. Products that have never been ordered

Use a LEFT JOIN with a WHERE clause to find unmatched records.

SELECT p.name AS product_name
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL;

4. Calculate a running total of order amounts per customer

Use a window function (SUM() OVER()) to get a running total.

SELECT
    o.customer_id,
    o.order_id,
    o.order_date,
    o.total_amount,
    SUM(o.total_amount) OVER (PARTITION BY o.customer_id ORDER BY o.order_date) AS running_total
FROM orders o
ORDER BY o.customer_id, o.order_date;

5. Find the Nth highest priced product (e.g., 2nd highest)

Use DENSE_RANK() window function.

WITH RankedProducts AS (
    SELECT
        name,
        price,
        DENSE_RANK() OVER (ORDER BY price DESC) as price_rank
    FROM products
)
SELECT name, price
FROM RankedProducts
WHERE price_rank = 2;

6. Identify customers who bought all products from a specific category

This is a classic relational division problem.

SELECT c.first_name, c.last_name
FROM customers c
WHERE NOT EXISTS (
    SELECT p.product_id
    FROM products p
    WHERE p.category_id = (SELECT category_id FROM categories WHERE name = 'Electronics') -- Specify category
    AND NOT EXISTS (
        SELECT oi.product_id
        FROM orders o
        JOIN order_items oi ON o.order_id = oi.order_id
        WHERE o.customer_id = c.customer_id AND oi.product_id = p.product_id
    )
);

---

Explanation of Concepts

• SELECT: Used to retrieve data from one or more tables.
• FROM: Specifies the table(s) from which to retrieve data.
• WHERE: Filters records based on a specified condition.
• INSERT INTO: Adds new rows of data to a table.
• UPDATE: Modifies existing data in a table.
• DELETE FROM: Removes rows from a table.
• JOIN: Combines rows from two or more tables based on a related column between them.
  • INNER JOIN: Returns rows when there is a match in both tables.
  • LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table. If there is no match, the right side will have NULLs.
• GROUP BY: Groups rows that have the same values in specified columns into summary rows.
• HAVING: Filters groups based on a specified condition (used with GROUP BY).
• ORDER BY: Sorts the result set of a query.
• LIMIT: Restricts the number of rows returned by a query.
• DISTINCT: Returns only unique values.
• COUNT(), SUM(): Aggregate functions that perform calculations on a set of rows.
• NOW(), DATE_SUB(): Date and time functions.
• Subquery: A query nested inside another query.
• Window Functions (OVER (PARTITION BY ... ORDER BY ...)): Perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, window functions do not collapse the rows into a single output row.
  • SUM() OVER(): Calculates a running total.
  • DENSE_RANK(): Assigns a rank to each row within its partition based on the ordering, with no gaps in the ranking sequence.
• Relational Division: A complex operation to find entities that are related to all entities in another set. Often solved using NOT EXISTS twice.

This setup provides a solid foundation for managing your e-commerce database and interacting with it using various SQL query techniques.