1Z0-909 study plan
1Z0-909 · Give each week a clear focus.
8 weeks · About 7 hours per week
MySQL 8.0 Database Developer
Install MySQL 8.0 locally and connect via both the mysql client and a programmatic connector
Create and alter schemas, tables, and columns using DDL, then inspect them with SHOW CREATE TABLE and INFORMATION_SCHEMA
Write SELECT statements exercising WHERE, ORDER BY, LIMIT, DISTINCT, and multi-table INNER/OUTER joins
Build a small practice schema of at least five related tables with realistic constraints
Your checkpoint
A version-controlled SQL script that creates a five-table practice schema with primary keys, foreign keys, and at least ten representative query statements that all run without error
MySQL 8.0 Database Developer
Apply numeric, string, date, and NULL-handling functions in queries and explain their return types
Use aggregate functions with GROUP BY and HAVING, and distinguish WHERE from HAVING filtering
Write subqueries in SELECT, FROM, and WHERE clauses, including correlated subqueries
Use common table expressions and window functions to solve ranking and running-total problems
Your checkpoint
A notebook or script of 15 solved query exercises covering joins, aggregation, subqueries, CTEs, and window functions, each with a one-line explanation of the approach
MySQL 8.0 Database Developer
Design normalized tables and justify each table against 1NF, 2NF, and 3NF
Define primary, unique, foreign key, and check constraints and observe how violations are rejected
Choose appropriate data types for columns and explain storage and range trade-offs
Create and use indexes for common query patterns and read EXPLAIN output to confirm index use
Your checkpoint
A written schema-design review of your practice database: normalization rationale per table, a data-type justification table, and before/after EXPLAIN plans for three indexed queries
MySQL 8.0 Database Developer
Write INSERT, UPDATE, DELETE, and INSERT ... ON DUPLICATE KEY statements safely using transactions
Demonstrate COMMIT, ROLLBACK, and SAVEPOINT behavior across two concurrent sessions
Use stored procedures with IN, OUT, and INOUT parameters and call them from a client
Create stored functions and triggers and explain when each is appropriate
Your checkpoint
A runnable script containing at least two stored procedures, one function, and one trigger, plus a transcript showing a successful transaction and a rolled-back transaction
MySQL 8.0 Database Developer
Use JSON column functions to store, query, and update semi-structured data
Apply generated columns and explain the difference between virtual and stored generated columns
Write queries using recursive CTEs to traverse hierarchical data
Use views and explain their role in simplifying and securing access
Your checkpoint
A working example that stores JSON documents, extracts values with JSON path expressions, indexes a generated column derived from JSON, and traverses a parent-child hierarchy with a recursive CTE
MySQL 8.0 Database Developer
Diagnose and resolve common error codes such as duplicate key, foreign key violation, and deadlock
Explain isolation levels and reproduce a non-repeatable read and a phantom read scenario
Use EXPLAIN ANALYZE to identify and fix a slow query
Manage user accounts and grant privileges at the appropriate scope
Your checkpoint
A troubleshooting log of at least five reproduced errors, each with the error code, root cause, and the exact fix, plus one query optimized with before/after timing
MySQL 8.0 Database Developer
Complete a full-length timed practice set and log every missed question by topic
Re-study the three weakest topics identified in the practice set
Memorize the syntax and behavior of the most frequently tested statements without reference material
Practice reading and predicting query output quickly under time pressure
Your checkpoint
A scored practice attempt with a topic-by-topic error breakdown and a prioritized remediation list of no more than five items
MySQL 8.0 Database Developer
Close every item on the remediation list from week 7
Re-run a second timed practice set and compare scores against week 7
Review all deliverables from weeks 1 through 6 as a consolidated reference
Confirm exam logistics and finalize a one-page syntax cheat sheet
Your checkpoint
A final readiness report showing both practice scores, the closed remediation items, and a one-page personal cheat sheet of syntax that caused repeated errors
Milestones
Week 2: You can write a multi-table join with aggregation and a correlated subquery from scratch without consulting notes
Week 4: You can create a stored procedure with OUT parameters and demonstrate transaction rollback in a live session
Week 6: You can read an EXPLAIN plan, identify a missing or unused index, and fix the query with measurable improvement
Week 8: Your second timed practice score exceeds the first, and no remediation item from week 7 remains open