Browse Oracle Certification Guides

MySQL 1Z0-909 Sample Questions with Explanations

April 25, 2026

MySQL 1Z0-909 sample questions with explanations, traps, topic labels, and IT Mastery route links.

On this page

These original sample questions are designed to help you check how the exam topics appear in decision-style prompts. They are not taken from the live exam.

Use these sample questions as a guided self-assessment for Oracle MySQL 8.0 Database Developer (1Z0-909) topics such as SQL correctness, joins, grouping, indexes, transactions, stored programs, JSON usage, and schema-design tradeoffs. The strongest answers usually come from tracing rows and constraints before choosing the query that looks familiar.

Where these questions fit in the 1Z0-909 guide

The sample set below is part of the Oracle MySQL 1Z0-909 guide path:

1Z0-909 MySQL developer sample questions

Work through each prompt before opening the explanation. For MySQL developer questions, check SQL legality, row shape, and transaction behavior before trusting an answer that sounds convenient.

Question 1

Topic: Preserving unmatched parent rows

A report must list every customer, including customers who have not placed an order. For customers with orders, it should show the order count. Which query shape best preserves customers with zero orders?

  • A. Use an INNER JOIN from customers to orders and group by customer.
  • B. Use a LEFT JOIN from customers to orders and count a non-null order column.
  • C. Query only the orders table and group by customer_id.
  • D. Use a RIGHT JOIN from customers to orders and filter out null customer names.

Best answer: B

Explanation: The preserved table is customers, so the query should start from customers and left join matching orders. Counting a non-null order column returns zero for customers with no matching order rows.

Why the other choices are weaker:

  • A removes customers without matching orders.
  • C cannot list customers who have no rows in the orders table.
  • D preserves the wrong side and the filter risks removing unmatched rows.

What this tests: Join direction, null-extended rows, grouping, and aggregate-count behavior.

Related topics: LEFT JOIN; Aggregation; COUNT; Report queries

Question 2

Topic: Keeping a predicate sargable

A table has an index on created_at. A query must return rows from April 2026. Which predicate is most likely to let MySQL use the index efficiently?

  • A. WHERE DATE_FORMAT(created_at, '%Y-%m') = '2026-04'
  • B. WHERE MONTH(created_at) = 4 AND YEAR(created_at) = 2026
  • C. WHERE created_at LIKE '2026-04%'
  • D. WHERE created_at >= '2026-04-01' AND created_at < '2026-05-01'

Best answer: D

Explanation: A half-open range keeps the indexed column unwrapped and expresses the exact time window. That gives the optimizer a clean range predicate on created_at.

Why the other choices are weaker:

  • A applies a function to the indexed column, which commonly prevents direct index range use.
  • B also wraps the column in functions.
  • C treats a datetime value like text and is less precise than a typed range predicate.

What this tests: Sargable predicates, date filtering, and index-aware query design.

Related topics: Indexes; Sargability; Date ranges; Optimizer

Question 3

Topic: Choosing the right transaction boundary

An application transfers funds between two accounts by subtracting from one row and adding to another. The operation must never leave only one side of the transfer committed. Which approach is strongest?

  • A. Run both updates inside one transaction and commit only after both succeed.
  • B. Run each update in autocommit mode so each statement is saved immediately.
  • C. Update the destination account first and trust the application log for recovery.
  • D. Disable constraints during the transfer to avoid lock waits.

Best answer: A

Explanation: The two updates form one logical unit of work. A transaction gives the application all-or-nothing behavior, so a failure can roll back both changes instead of committing a partial transfer.

Why the other choices are weaker:

  • B can commit the first update before the second one fails.
  • C relies on external cleanup instead of database transaction guarantees.
  • D weakens integrity and does not solve atomicity.

What this tests: Transaction atomicity and choosing commit boundaries for multi-step changes.

Related topics: Transactions; Atomicity; COMMIT; ROLLBACK

Question 4

Topic: JSON value extraction

A table stores product attributes in a JSON column named details. A query needs the scalar color value at $.color as text so it can be displayed without JSON quotes. Which expression is the cleanest fit?

  • A. JSON_OBJECT(details, '$.color')
  • B. JSON_ARRAY(details->'$.color')
  • C. details->'$.color'
  • D. details->>'$.color'

Best answer: D

Explanation: In MySQL, -> returns a JSON value, while ->> returns an unquoted scalar text value. The prompt explicitly asks for display text without JSON quotes.

Why the other choices are weaker:

  • A constructs a JSON object rather than extracting the scalar value.
  • B wraps the extracted value in a JSON array.
  • C extracts JSON, but it does not unquote the scalar for display.

What this tests: MySQL JSON path extraction and the difference between JSON and unquoted scalar output.

Related topics: JSON; Path extraction; Scalar values; SQL expressions

Independent study note

Tech Exam Lexicon and IT Mastery are independent study tools. They are not affiliated with, endorsed by, or sponsored by Oracle or any certification body.

Revised on Sunday, May 10, 2026
Cheat Sheet
Glossary