Shopping Tracker Database

The Problem

Comparing pet product prices was tedious and manual.

I regularly shop for pet products across multiple retailers, including Walmart, Petco, Chewy, and Amazon, and price, delivery time, and availability all vary between them. Keeping track of the best option for each product by hand didn't scale, and it also seemed like a strong opportunity to build something real with SQL rather than working from a generic tutorial dataset.

I wanted a project that would do two things at once: actually help me make more informed purchasing decisions, and demonstrate practical database design and query-writing skills to employers.

At a glance

3 normalized tables
9 retailers tracked
19 products, 100+ price records
Primary & foreign key relationships
Queries from basic filtering to window functions

How It Was Solved

Database design, then queries: basic to advanced.

I designed a normalized schema in MySQL Workbench, populated it with realistic product and pricing data, and then wrote a progression of queries to answer real purchasing questions.

Stores

Holds each retailer's name and website: the source of the pricing being compared.

Products

Product name, brand, manufacturer, category, and food type (wet/dry), independent of any one store.

StorePrices

The linking table: price, date checked, and estimated delivery days for each product at each store, tied together with foreign keys back to Stores and Products.

Query Examples

From filtering to window functions.

Basic

Filtering, sorting, and searching: the everyday shape of "what do I have and how do I find it."

SELECT *
FROM Products
WHERE category = 'Cat Food'
AND food_type = 'Wet';

Intermediate

Joining all three tables together, then aggregating to see patterns across stores.

SELECT
    s.store_name,
    ROUND(AVG(sp.estimated_delivery_days), 1) AS average_delivery_days
FROM StorePrices sp
JOIN Stores s
    ON sp.store_id = s.store_id
GROUP BY s.store_name
HAVING AVG(sp.estimated_delivery_days) <= 2;

Advanced

A CTE with ROW_NUMBER() to find the single cheapest store for every product in one pass:

WITH RankedPrices AS (
    SELECT
        p.product_name,
        s.store_name,
        sp.price,
        ROW_NUMBER() OVER (
            PARTITION BY p.product_id
            ORDER BY sp.price
        ) AS price_rank
    FROM StorePrices sp
    JOIN Products p ON sp.product_id = p.product_id
    JOIN Stores s ON sp.store_id = s.store_id
)
SELECT product_name, store_name, price
FROM RankedPrices
WHERE price_rank = 1
ORDER BY product_name;

MySQL Workbench showing the RankedPrices CTE query and its result grid, listing the cheapest store for each product.

CRUD Operations

Create and Read are covered above through inserts, joins, and views. This rounds those out with Update and Delete, wrapped in a transaction so changes can be reviewed before they're made permanent:

UPDATE StorePrices
SET price = 1.09,
    date_checked = CURDATE()
WHERE store_id = (SELECT store_id FROM Stores WHERE store_name = 'Petco')
  AND product_id = (
      SELECT product_id FROM Products
      WHERE product_name LIKE 'Pumpkin Patch Up!%'
  );

START TRANSACTION;
DELETE FROM StorePrices
WHERE date_checked < '2026-01-01';
COMMIT;

Project Files

What's in the repo.

File	Description
Create_Database.sql	Creates the database
Create_Tables.sql	Creates the Stores, Products, and StorePrices tables
Insert_Stores.sql	Inserts retailer data
Insert_Products.sql	Inserts product data
Insert_StorePrices.sql	Inserts pricing and delivery data
Basic_Queries.sql	SELECT, WHERE, ORDER BY, LIMIT
Intermediate_Queries.sql	Joins, aggregates, GROUP BY, HAVING
Advanced_Queries.sql	Subqueries, CASE, CTEs, window functions, views
CRUD_Operations.sql	UPDATE, DELETE, and transactions (COMMIT/ROLLBACK)

What I Learned

Beyond just writing SQL.

Designing the schema first forced me to think about normalization before a single row of data went in, deciding what belonged in Products versus StorePrices, and why pricing needed its own linking table rather than living on the product itself. Writing the query set in stages, from basic filtering up through window functions and CTEs, also made it clear how much easier complex reporting becomes once the underlying joins and relationships are solid. It's the same instinct I bring to QA work: get the structure right before you start testing against it.

View full code on GitHub ↗ View full code on GitLab ↗ ← Back to SQL Portfolio

Shopping Tracker Database

Comparing pet product prices was tedious and manual.

At a glance

Database design, then queries: basic to advanced.

Stores

Products

StorePrices

From filtering to window functions.

Basic

Intermediate

Advanced

CRUD Operations

What the database can actually tell you.

Which store has the lowest price for a product?

Which products are available for same-day pickup?

What is the average price of dog food?

Which stores offer the fastest delivery?

What is the cheapest retailer for each product?

Which products are priced above or below average?

What's in the repo.

Beyond just writing SQL.