🌿 Lucky Shrub — SQL Database Analysis

A MySQL project simulating business analytics for a home & garden retailer 🏡🪴. Covers schema design, stored routines, triggers, JSON handling, and query optimization — plus an extended section on indexing strategy, window functions, and rewriting inefficient queries into faster ones. 🚀

---

📖 Overview

Lucky Shrub is a fictional retailer whose data lives across eight related tables: Clients, Products, Addresses, Employees, Orders, Activity, Audit, and Notifications. This project answers a set of realistic business questions against that schema using a range of core and advanced SQL techniques. 📊

🎬 Demo

💡 Drop your own screen recording here! ScreenToGif (Windows, free) or Kap (Mac, free) let you record a MySQL Workbench query run and export straight to .gif in under a minute. Save it as demo.gif in this repo and it'll show up right here:

![demo](./demo.gif)

🗺️ Entity-Relationship Diagram

erDiagram
    CLIENTS ||--o{ ORDERS : places
    PRODUCTS ||--o{ ORDERS : "ordered in"
    ADDRESSES ||--o{ CLIENTS : "lives at"
    ADDRESSES ||--o{ EMPLOYEES : "lives at"
    ORDERS ||--o{ AUDIT : triggers

    CLIENTS {
        varchar ClientID PK
        varchar FullName
        int ContactNumber
        int AddressID FK
    }
    PRODUCTS {
        varchar ProductID PK
        varchar ProductName
        decimal BuyPrice
        decimal SellPrice
        int NumberOfItems
    }
    ADDRESSES {
        int AddressID PK
        varchar Street
        varchar County
    }
    EMPLOYEES {
        int EmployeeID PK
        varchar FullName
        varchar JobTitle
        varchar Department
        int AddressID FK
    }
    ORDERS {
        int OrderID PK
        varchar ClientID FK
        varchar ProductID FK
        int Quantity
        decimal Cost
        date Date
    }
    ACTIVITY {
        int ActivityID PK
        json Properties
    }
    AUDIT {
        int AuditID PK
        timestamp OrderDateTime
    }

Loading

🛠️ Tech Stack

• 🐬 MySQL 8.0 — functions, procedures, triggers, views, CTEs, window functions, JSON
• 🖥️ Tested in MySQL Workbench

⚡ Setup

mysql -u root -p < 01_schema_and_data.sql
mysql -u root -p < 02_core_tasks.sql
mysql -u root -p < 03_advanced_analysis.sql

Or open each file in MySQL Workbench and hit execute (⚡) in order.

✅ Core Tasks

#	Business question	SQL concept
1️⃣	Average order cost in a given year	User-defined FUNCTION
2️⃣	Units sold per year for a product	PROCEDURE with OUT parameters
3️⃣	Auto-log every new order	AFTER INSERT TRIGGER
4️⃣	Combined client + employee address list	UNION ALL, sort optimization
5️⃣	Multi-year product sales summary	Common Table Expression (CTE)
6️⃣	Client activity from JSON logs	JSON path extraction (->>)
7️⃣	Profit by product and year	PROCEDURE with derived calculations
8️⃣	Client/order/product summary report	VIEW joining 4 tables

📄 Full implementations: 02_core_tasks.sql

🚀 Advanced Analysis

Beyond the core business requests, 03_advanced_analysis.sql demonstrates:

• 🗂️ Indexing strategy — composite and single-column indexes targeting the actual filter/join patterns used above (ProductID + Date, ClientID, address foreign keys)
• 🎯 Sargability — showing how wrapping a column in YEAR(Date) blocks index range scans, and the BETWEEN-based rewrite that fixes it
• 📈 Window functions — RANK() for a client spend leaderboard, running totals with SUM() OVER (... ROWS BETWEEN ...), and ROW_NUMBER() to find the top-selling product per year
• ♻️ Query rewriting — a correlated subquery vs. an equivalent LEFT JOIN + GROUP BY, with notes on why the join scales better
• 🛡️ Defensive procedures — DECLARE ... HANDLER for graceful error handling instead of raw MySQL exceptions

📸 Sample Output

CALL EvaluateProduct('P1', @a, @b, @c); SELECT @a, @b, @c;

@sold_items_2020	@sold_items_2021	@sold_items_2022
35	10	65

SELECT * FROM DataSummary LIMIT 5;

FullName	ContactNumber	County	ProductName	ProductID	Cost	Date
Takashi Ito	351786345	Graham County	Sycamore trees	P4	1200.00	2022-09-10
Jane Murphy	351567243	Pinal County	Artificial grass bags	P1	1000.00	2022-09-01
Laurina Delgado	351342597	Santa Cruz County	Patio slates	P3	800.00	2022-09-03

📁 File Structure

lucky-shrub-sql/
├── 01_schema_and_data.sql       # 🏗️  Database, tables, seed data
├── 02_core_tasks.sql            # ✅  8 core business-question solutions
├── 03_advanced_analysis.sql     # 🚀  Indexing, window functions, optimization
├── banner.svg                   # 🎨  README banner graphic
└── README.md

🙋 Why this project

Built to practice translating real business questions into production-style SQL — not just "does it return the right number" but "would this hold up at scale," which is why the advanced-analysis file exists alongside the core tasks. 🌱

---

Made with 🐬 and way too much coffee ☕