Design and build a complete, normalized e-commerce database from scratch. Create 7 tables with full FK constraints, load 50+ rows of realistic sample data, write complex reporting queries, and add performance indexes.
docker exec mysql-lab mysql -uroot -prootpass << 'EOF'
USE ecommerce;
INSERT INTO reviews (product_id, user_id, rating, title, body, is_verified, helpful_count) VALUES
(1, 1, 5, 'Best laptop I have ever owned', 'The M2 chip is incredibly fast. Battery life is amazing. Worth every penny.', TRUE, 24),
(1, 2, 4, 'Great performance, minor issues', 'Excellent speed and display. Runs a bit hot under load. Fan noise occasional.', TRUE, 8),
(3, 3, 5, 'Gaming beast!', 'Runs every game at max settings. The 165Hz display is buttery smooth.', TRUE, 15),
(5, 1, 4, 'Comfortable for long sessions', 'The ergonomic design really helps with wrist fatigue. Battery lasts 2 months.', TRUE, 12),
(5, 5, 5, 'Perfect travel mouse', 'Switched from wired to this and never going back. Pairs instantly.', TRUE, 7),
(8, 6, 5, 'Typing feels premium', 'Cherry MX Browns are perfect for typing and coding. RGB is a nice touch.', TRUE, 19),
(10, 8, 5, 'Stunning 4K display', 'Colors are incredibly accurate. USB-C charging is a game changer.', TRUE, 31),
(10, 2, 4, 'Great monitor with minor cons', 'Image quality is superb. The stand is a bit wobbly at max height.', TRUE, 5),
(12, 1, 5, 'Comprehensive SQL guide', 'Best SQL book I have read. Examples are practical and well-explained.', TRUE, 43),
(12, 6, 4, 'Good but slightly outdated', 'Great fundamentals but some PostgreSQL examples are for older versions.', TRUE, 11),
(12, 9, 5, 'Essential for any developer', 'Bought this for a junior dev on my team. They learned SQL in 2 weeks.', FALSE, 28),
(17, 9, 5, 'Transformed my work setup', 'My back no longer hurts after 8-hour work days. Lumbar support is perfect.', TRUE, 37),
(17, 3, 4, 'Good chair, assembly instructions poor', 'Chair is comfortable but assembly instructions were confusing.', TRUE, 9),
(19, 9, 5, 'Worth every penny', 'The electric motor is whisper quiet. Memory presets are super convenient.', TRUE, 22),
(4, 9, 5, 'Powerhouse workstation', 'Handles 4K video editing without breaking a sweat. Upgradeable too.', TRUE, 18),
(2, 5, 4, 'Great ultrabook for travel', 'Light, fast, and battery lasts all day. Screen could be brighter outdoors.', TRUE, 6),
(11, 3, 5, 'Best budget monitor', 'For the price, image quality is incredible. Zero backlight bleed.', TRUE, 14),
(13, 8, 5, 'Excellent PostgreSQL resource', 'Goes deep into internals. Not for beginners but invaluable for DBAs.', TRUE, 25),
(20, 8, 4, 'Solid dual monitor arm', 'Solid construction. Easy to adjust. Instructions could be clearer.', TRUE, 3),
(16, 10,5, 'Great science fiction read', 'Thought-provoking story about data privacy and AI. Highly recommended.', FALSE, 7);
EOF
docker exec mysql-lab mysql -uroot -prootpass << 'EOF'
USE ecommerce;
-- ============================================================
-- REPORT 1: Top Products by Revenue
-- ============================================================
SELECT
p.sku,
p.name AS product_name,
c.name AS category,
SUM(oi.quantity) AS units_sold,
ROUND(SUM(oi.line_total), 2) AS total_revenue,
ROUND(AVG(oi.unit_price), 2) AS avg_unit_price,
ROUND(SUM(oi.line_total) / SUM(oi.quantity), 2) AS avg_revenue_per_unit
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.cat_id = c.cat_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.status NOT IN ('cancelled', 'refunded')
GROUP BY p.product_id, p.sku, p.name, c.name
ORDER BY total_revenue DESC
LIMIT 10;
EOF
docker exec mysql-lab mysql -uroot -prootpass << 'EOF'
USE ecommerce;
-- ============================================================
-- REPORT 2: User Order History with Lifetime Value
-- ============================================================
SELECT
u.user_id,
CONCAT(u.first_name, ' ', u.last_name) AS customer_name,
u.email,
COUNT(DISTINCT o.order_id) AS total_orders,
COUNT(DISTINCT CASE WHEN o.status NOT IN ('cancelled','refunded') THEN o.order_id END) AS completed_orders,
ROUND(SUM(CASE WHEN o.status NOT IN ('cancelled','refunded') THEN o.total ELSE 0 END), 2) AS lifetime_value,
ROUND(AVG(CASE WHEN o.status NOT IN ('cancelled','refunded') THEN o.total END), 2) AS avg_order_value,
MAX(o.placed_at) AS last_order_date,
MIN(o.placed_at) AS first_order_date
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.is_active = TRUE
GROUP BY u.user_id, u.first_name, u.last_name, u.email
ORDER BY lifetime_value DESC;
EOF
docker exec mysql-lab mysql -uroot -prootpass << 'EOF'
USE ecommerce;
-- ============================================================
-- REPORT 3: Inventory Report (Stock + Revenue Potential)
-- ============================================================
SELECT
c.name AS category,
p.sku,
p.name AS product,
p.price,
p.cost,
ROUND(p.price - p.cost, 2) AS margin,
ROUND((p.price - p.cost) / p.price * 100, 1) AS margin_pct,
p.stock_qty,
ROUND(p.price * p.stock_qty, 2) AS stock_value,
COALESCE(r.avg_rating, 'No reviews') AS avg_rating,
COALESCE(r.review_count, 0) AS reviews,
CASE
WHEN p.stock_qty = 0 THEN 'OUT OF STOCK'
WHEN p.stock_qty < 20 THEN 'LOW STOCK'
WHEN p.stock_qty < 50 THEN 'MODERATE'
ELSE 'IN STOCK'
END AS stock_status
FROM products p
JOIN categories c ON p.cat_id = c.cat_id
LEFT JOIN (
SELECT product_id,
ROUND(AVG(rating), 1) AS avg_rating,
COUNT(*) AS review_count
FROM reviews
GROUP BY product_id
) r ON p.product_id = r.product_id
WHERE p.is_active = TRUE
ORDER BY category, margin_pct DESC;
EOF
docker exec mysql-lab mysql -uroot -prootpass << 'EOF'
USE ecommerce;
-- Row counts per table
SELECT 'categories' AS tbl, COUNT(*) AS rows FROM categories UNION ALL
SELECT 'products', COUNT(*) FROM products UNION ALL
SELECT 'users', COUNT(*) FROM users UNION ALL
SELECT 'addresses', COUNT(*) FROM addresses UNION ALL
SELECT 'orders', COUNT(*) FROM orders UNION ALL
SELECT 'order_items', COUNT(*) FROM order_items UNION ALL
SELECT 'reviews', COUNT(*) FROM reviews;
-- Total rows
SELECT SUM(cnt) AS total_rows FROM (
SELECT COUNT(*) AS cnt FROM categories UNION ALL
SELECT COUNT(*) FROM products UNION ALL
SELECT COUNT(*) FROM users UNION ALL
SELECT COUNT(*) FROM addresses UNION ALL
SELECT COUNT(*) FROM orders UNION ALL
SELECT COUNT(*) FROM order_items UNION ALL
SELECT COUNT(*) FROM reviews
) t;
-- Order total consistency check
SELECT
order_id,
total AS stored_total,
ROUND(subtotal - discount_amt + shipping_cost + tax_amt, 2) AS calculated_total,
ROUND(total - (subtotal - discount_amt + shipping_cost + tax_amt), 2) AS discrepancy
FROM orders
HAVING ABS(discrepancy) > 0.01;
-- Revenue summary by month
SELECT
DATE_FORMAT(o.placed_at, '%Y-%m') AS month,
COUNT(DISTINCT o.order_id) AS order_count,
COUNT(DISTINCT o.user_id) AS unique_customers,
ROUND(SUM(o.total), 2) AS gross_revenue,
ROUND(SUM(oi.line_total), 2) AS net_item_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status NOT IN ('cancelled', 'refunded')
GROUP BY DATE_FORMAT(o.placed_at, '%Y-%m')
ORDER BY month;
EOF
