iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

Certificate

By · Reviewed & last updated · Part of MySQL Tutorial

MySQL track certificate: pass criteria, project brief, and the rubric for a real-world MySQL portfolio piece.

MySQL — certificate

EXAMPLE
# ===== Award criteria =====
# Pass: >= 70%; Distinction >= 85%.
#
# 1. Schema design          (20)
# 2. Indexing               (15)
# 3. Queries                (15)
# 4. Performance + EXPLAIN  (15)
# 5. Backups + replication  (10)
# 6. Security               (10)
# 7. Operational scripts    (10)
# 8. Communication          (5)

# ===== Project brief =====
# Design + ship a small MySQL database supporting an application of your choice:
# - 4+ tables with relationships + constraints
# - Realistic dataset (10k+ rows) seeded via SQL or scripts
# - 5+ queries showing JOIN, GROUP BY, window function, JSON path query
# - EXPLAIN output for each query + index decisions
# - Replication set up between primary + replica (or sketch via docker-compose)
# - Backup + restore runbook
# - Roles per workload (writer, reader, backup)
# - Brief migration story (Liquibase / Flyway / golang-migrate)

# Suggested ideas:
# - Bookstore: books, authors, sales, inventory
# - Ride sharing: trips, drivers, riders, payments
# - Forum: posts, comments, votes, users
# - Inventory: items, locations, movements, audit

# ===== Sample structure =====
# myapp-db/
#   schema/
#     001_init.sql
#     002_orders.sql
#   seed/
#     users.sql (10k rows)
#     orders.sql (50k rows)
#   queries/
#     by-customer.sql      (with EXPLAIN)
#     monthly-revenue.sql
#   ops/
#     backup.sh
#     restore.sh
#     replication.yml      (docker-compose)
#   roles.sql              (writer, reader, backup)
#   README.md

# ===== Marking sheet (example) =====
# 1. Schema             18/20  utf8mb4, DECIMAL for money, FK constraints, indexes planned
# 2. Indexing           14/15  composite where right; covering indexes for hot paths
# 3. Queries            14/15  JOIN + window function + JSON path
# 4. Performance        13/15  EXPLAIN + cost commentary
# 5. Backup/replication  9/10  docker-compose with primary + replica + back-up dump
# 6. Security            9/10  least-privilege roles, secure_file_priv
# 7. Ops scripts         9/10  backup/restore shell scripts; test recovery
# 8. Communication       4/5   README, ER diagram
# Total: 90/100 -> Distinction

# ===== Patterns to internalise =====
# - InnoDB + utf8mb4_0900_ai_ci as defaults
# - Composite indexes for left-anchored multi-column queries
# - JSON for sparse / variable shapes; generated columns + indexes for hot paths
# - Distinct roles per service responsibility; least privilege
# - Daily logical backups + replica; test the restore

# ===== Pitfalls =====
# - FLOAT for money
# - VARCHAR(255) on utf8mb4 + InnoDB row format -> index key length
# - Generic 'app' user with ALL PRIVILEGES
# - Backups that never tested

Why it matters

The MySQL certificate proves you can design and operate a real database: schema, indexes, queries, EXPLAIN, replication, backups, roles. Ship a working project with a clear README and a tested restore script, and the cert is a credible portfolio piece.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
-- /certificate/mysql
Try it Yourself »

Discussion

Loading…