Browse Exams — Mock Exams & Practice Tests

1Z0-909 Syllabus — Learning Objectives by Topic

Learning objectives for MySQL 8.0 Database Developer (1Z0-909), organized by topic with quick links to targeted practice.

Use this syllabus as your checklist for 1Z0‑909.

What’s covered

Topic 1: SQL Foundations and Data Types

Practice this topic →

1.1 Data types, literals, and conversions

  • Choose appropriate MySQL data types for common use cases (numeric, text, date/time, JSON) and explain trade-offs (concept-level).
  • Write safe literals and understand when implicit conversions can change results.
  • Given a query, predict how NULLs propagate through expressions and comparisons.
  • Recognize common data type pitfalls: truncation, collation/charset issues (concept-level), and time zone assumptions (concept-level).

1.2 SELECT basics and expressions

  • Write SELECT statements with aliases, expressions, and deterministic output columns.
  • Use DISTINCT correctly and predict when it changes row counts.
  • Given a query, predict output ordering and explain why ORDER BY is required for stable ordering.
  • Use LIMIT/OFFSET correctly and recognize when ordering must be specified to avoid nondeterminism.

1.3 Core built-in functions (strings and dates)

  • Use common string functions to normalize and parse text (case, trim, substring).
  • Use date/time functions to compute boundaries and intervals and predict results for edge cases (month end, leap years).
  • Given a scenario, choose formatting vs parsing functions and avoid implicit conversion surprises.
  • Recognize when function use in WHERE predicates can prevent index usage (non-sargable) at a conceptual level.

Topic 2: Filtering, Joins, and Subqueries

Practice this topic →

2.1 WHERE clause, predicates, and NULL behavior

  • Filter rows using comparison operators, IN, BETWEEN, LIKE, and IS NULL/IS NOT NULL.
  • Apply boolean logic with AND/OR and parentheses and predict which rows match.
  • Given a scenario, choose between NOT IN vs NOT EXISTS and explain the effect of NULLs.
  • Recognize collation and case-sensitivity considerations in text filtering at a conceptual level.

2.2 JOIN types and common traps

  • Write INNER JOIN and OUTER JOIN queries with correct join predicates.
  • Differentiate join predicates from filters and place predicates to preserve LEFT JOIN semantics.
  • Given a scenario, identify accidental row multiplication caused by one-to-many joins.
  • Recognize when to use EXISTS/anti-joins to express “has related rows” vs “missing related rows”.

2.3 Subqueries, derived tables, and CTE basics

  • Write single-row and multi-row subqueries and avoid errors caused by unexpected row counts.
  • Use correlated subqueries and EXISTS correctly and identify the correlation predicate.
  • Use derived tables (subquery in FROM) to structure multi-step logic for readability and correctness.
  • Use common table expressions (WITH) at a conceptual level and recognize scoping rules.

Topic 3: Aggregation, Grouping, and Set Operations

Practice this topic →

3.1 GROUP BY, HAVING, and aggregate functions

  • Use COUNT, SUM, AVG, MIN, and MAX correctly and explain NULL handling in aggregates.
  • Group results with GROUP BY and identify which columns must be grouped vs aggregated.
  • Filter aggregated results with HAVING and explain why WHERE cannot filter aggregates directly.
  • Given a scenario, choose grouping keys that match reporting requirements and avoid unintended granularity.

3.2 Window functions (MySQL 8.0) awareness

  • Differentiate GROUP BY aggregates from window functions at a conceptual level.
  • Use ranking windows (ROW_NUMBER, RANK, DENSE_RANK) at a conceptual level and interpret results.
  • Given a scenario, choose window functions for running totals, moving averages, and top-N-per-group patterns (concept-level).
  • Recognize common window pitfalls: ordering, partitioning, and how windows affect row counts.

3.3 UNION and grouping extensions

  • Use UNION vs UNION ALL and explain duplicate handling and performance implications.
  • Ensure set-operator queries are type-compatible and apply ORDER BY to the final combined result.
  • Use ROLLUP at a conceptual level and interpret subtotal rows when present.
  • Given a scenario, choose a set operator vs join/subquery approach based on correctness and readability.

Topic 4: Schema Design and DDL

Practice this topic →

4.1 CREATE TABLE, keys, and constraints

  • Create tables with correct column definitions and NOT NULL/default constraints.
  • Define PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints and explain what each enforces.
  • Given a scenario, choose ON DELETE behavior and reason about integrity implications (concept-level).
  • Recognize why constraint choices influence both correctness and query performance.

4.2 ALTER TABLE and schema evolution

  • Alter tables safely (add/modify columns, add indexes) and recognize operational impacts (concept-level).
  • Given a scenario, choose a schema change approach that meets downtime constraints (concept-level).
  • Use generated columns at a conceptual level and identify when they enable indexing computed values.
  • Recognize risks of large schema changes: table rebuilds, locks, and temporary disk usage (concept-level).

4.3 Modeling and normalization (concept-level)

  • Explain basic normalization goals (reduce redundancy, preserve integrity) at a conceptual level.
  • Given a scenario, choose between normalization and denormalization based on read patterns and constraints (concept-level).
  • Choose surrogate vs natural keys and explain trade-offs for stability and indexing.
  • Recognize when many-to-many relationships require a junction table and how to enforce integrity.

Topic 5: Indexing Fundamentals

Practice this topic →

5.1 Index types and selection

  • Differentiate common index types (BTREE) and specialized indexes (FULLTEXT/SPATIAL) at a conceptual level.
  • Given a scenario, choose index columns based on selectivity, join keys, and ordering needs.
  • Explain why composite index column order matters and how it relates to query predicates (concept-level).
  • Recognize when indexes hurt (write overhead, low selectivity) and how to validate with evidence.

5.2 Covering indexes and query shapes

  • Explain what a covering index is at a conceptual level and why it can reduce I/O.
  • Given a query, identify whether an index can satisfy both filtering and ordering (concept-level).
  • Recognize how functions, implicit conversions, and leading wildcards can prevent index use (concept-level).
  • Choose when to refactor a query vs add an index to improve performance while preserving correctness.

5.3 Index maintenance and statistics awareness

  • Inspect existing indexes and constraints and identify redundant or missing indexes.
  • Given a scenario, identify when index bloat or fragmentation could affect performance (concept-level).
  • Recognize the role of statistics/cardinality estimates in plan selection at a conceptual level.
  • Identify safe practices for adding/dropping indexes in production (backups, staging tests, rollback plans).

Topic 6: Query Optimization and EXPLAIN

Practice this topic →

6.1 Reading execution plans (concept-level)

  • Use EXPLAIN to inspect access paths and identify full table scans vs index lookups (concept-level).
  • Given an EXPLAIN output, identify likely causes of poor plans: missing predicates, missing indexes, or non-sargable expressions.
  • Recognize the impact of join order and join conditions on plan quality (concept-level).
  • Identify when data distribution changes can cause plan regressions (concept-level).

6.2 Common performance pitfalls and fixes

  • Identify N+1 query patterns and explain why they cause excessive database load.
  • Given a scenario, refactor queries to reduce repeated lookups (joins, batching, pre-aggregation).
  • Recognize when LIMIT without ORDER BY produces unstable results and how it can mask performance issues.
  • Choose safe optimizations that preserve correctness (avoid rewriting logic that changes semantics).

6.3 Optimizer and hints awareness (concept-level)

  • Explain why optimizer choices depend on statistics and query shape (concept-level).
  • Given a scenario, decide whether to add an index, rewrite a query, or adjust schema to fix performance.
  • Recognize why hints should be used cautiously and can cause brittleness across data changes (concept-level).
  • Identify a workflow for performance testing changes: baseline, change one variable, measure, and roll back if needed.

Topic 7: Transactions, Isolation, and Concurrency

Practice this topic →

7.1 Transaction control and ACID basics

  • Use START TRANSACTION/COMMIT/ROLLBACK correctly to group changes safely.
  • Explain why long transactions increase contention and reduce throughput (concept-level).
  • Given a scenario, choose transaction boundaries that preserve consistency for multi-step updates.
  • Recognize implicit commit behavior for DDL (concept-level) and how it affects application logic.

7.2 Isolation levels and visibility (concept-level)

  • Differentiate isolation levels at a conceptual level and relate them to visibility anomalies.
  • Given a scenario, choose an isolation level consistent with correctness requirements and performance constraints (concept-level).
  • Recognize the impact of consistent reads and how MVCC affects query results under concurrency (concept-level).
  • Identify when to use explicit locking reads (concept-level) and the risks of over-locking.

7.3 Locks, deadlocks, and mitigation strategies

  • Explain why proper indexing reduces lock scope and contention for UPDATE/DELETE operations.
  • Given a scenario, recognize a deadlock vs a simple lock wait and choose the next best action.
  • Identify design patterns that reduce deadlocks: consistent lock ordering and short transactions.
  • Recognize when to retry transactions safely and how to ensure idempotent writes (concept-level).

Topic 8: Views and Stored Programs

Practice this topic →

8.1 Views and access semantics

  • Create views to encapsulate query logic and simplify consumer access.
  • Differentiate view usage from derived tables and choose the right tool for a scenario.
  • Recognize definers/invokers and privilege implications for views at a conceptual level.
  • Given a scenario, use views to restrict columns/rows exposed to an application or analyst.

8.2 Stored procedures and functions

  • Create stored procedures/functions and pass parameters with correct direction and types (concept-level).
  • Use basic control flow in stored programs (IF/CASE/LOOP) at a conceptual level.
  • Given a scenario, decide when stored programs improve maintainability vs when they increase operational risk.
  • Recognize privilege and security implications of stored programs (least privilege and ownership) at a conceptual level.

8.3 Cursors, handlers, and error management (concept-level)

  • Explain cursor use in stored programs at a conceptual level and identify when set-based SQL is preferable.
  • Use handlers conceptually to manage expected conditions and avoid uncontrolled failures in stored programs.
  • Given a scenario, design stored program logic that is testable and fails safely.
  • Recognize common stored-program pitfalls: hidden side effects, poor performance, and debugging complexity.

Topic 9: Triggers, Events, and JSON

Practice this topic →

9.1 Triggers and data integrity patterns (concept-level)

  • Create BEFORE/AFTER triggers at a conceptual level and recognize row-level execution behavior.
  • Given a scenario, decide whether a trigger is appropriate vs enforcing integrity in application logic or constraints.
  • Recognize performance and debugging risks of triggers and how to mitigate them (concept-level).
  • Identify common trigger pitfalls: recursion assumptions, hidden writes, and unexpected locking.

9.2 Event scheduler awareness (concept-level)

  • Explain what the event scheduler is used for at a conceptual level (scheduled tasks inside MySQL).
  • Given a scenario, choose events vs external scheduling (cron/workflow) based on operational constraints.
  • Recognize security and performance risks of scheduled jobs running inside the database (concept-level).
  • Design an event that is idempotent and safe to retry conceptually (avoid double-processing).

9.3 JSON data type and querying

  • Use JSON functions to extract and filter data and predict results for missing paths.
  • Given a scenario, choose JSON storage vs normalized relational schema and explain trade-offs (concept-level).
  • Recognize indexing strategies for JSON queries (generated columns) at a conceptual level.
  • Identify common JSON pitfalls: inconsistent schemas, heavy scanning, and lack of constraints (concept-level).

Topic 10: Security and Application Integration

Practice this topic →

10.1 Prepared statements and safe SQL usage

  • Explain why parameterized queries reduce SQL injection risk and improve plan reuse (concept-level).
  • Given a scenario, identify unsafe string concatenation patterns and choose a safe alternative.
  • Recognize how to handle dynamic filtering safely (whitelists and validated identifiers) at a conceptual level.
  • Identify common pitfalls with implicit conversions that can change query results or performance (concept-level).

10.2 Privilege design for applications

  • Design least-privilege roles for applications (read-only vs read-write vs admin tasks).
  • Given a scenario, separate privileges for schema migrations from privileges for runtime application access.
  • Recognize risks of using a single shared superuser account in applications and choose safer patterns.
  • Identify how to audit and rotate application credentials safely (concept-level).

10.3 SQL modes, compatibility, and error handling

  • Explain the purpose of SQL modes (concept-level) and how strict modes affect data correctness.
  • Given a scenario, identify bugs caused by permissive defaults (silent truncation) and choose stricter settings.
  • Recognize how collation/charset choices affect application behavior and comparisons (concept-level).
  • Design error handling and retry behavior that is safe under concurrency (idempotent operations) at a conceptual level.
Study Plan
Cheat Sheet