UUS Practice Tests⌕ Search
IT Certifications · 1Z0-909

Oracle MySQL 8.0 Database Developer Exam Practice Test

The Oracle MySQL 8.0 Database Developer Exam (1Z0-909) centers on SQL-based application development and relational data handling. Practice topics include defining indexes and constraints, choosing column types, writing joins and filters, and interpreting aggregate expressions. These skills help developers shape queries and schema elements for application features while reasoning about how MySQL returns and groups data.

Exam overview

Exam providerOracle
Exam code1Z0-909
CategoryIT Certifications

About the Oracle MySQL 8.0 Database Developer Exam

Effective MySQL development requires matching query structure to the result an application needs. Distinguish row filtering with WHERE from group filtering with HAVING, and use joins to preserve or exclude unmatched records as required. Aggregate functions such as SUM and GROUP_CONCAT summarize grouped data, while COALESCE provides a fallback for NULL values. Schema decisions also matter: a DECIMAL definition controls precision and scale, an ENUM represents a constrained set of string values, and a CHECK constraint can express a value condition. Index selection and creation support lookup patterns, including exact matches on character columns. In development work, these concepts help translate requirements into readable SQL, validate data constraints, and anticipate result shape before integrating queries into application code.

Sample Practice Questions

Review a selection from this exam's published US Practice Tests practice bank.

Question 1

Which index type is generally suitable for an exact-match lookup on a VARCHAR column, such as WHERE code = 'X17'?

  1. AFULLTEXT index
  2. BSPATIAL index
  3. CB-tree index
  4. DNo index type can support VARCHAR lookups
Show answerCorrect answer: C

MySQL's standard B-tree indexes support equality lookups on VARCHAR values and are commonly used for such predicates. FULLTEXT and SPATIAL indexes serve different kinds of searches.

Question 2

Which MySQL statement creates a secondary index named `idx_created` on the `created_at` column of an existing `events` table?

  1. ACREATE INDEX idx_created ON events (created_at);
  2. BADD INDEX idx_created TO events (created_at);
  3. CCREATE KEY idx_created FOR events (created_at);
  4. DINDEX events.idx_created USING created_at;
Show answerCorrect answer: A

The `CREATE INDEX index_name ON table_name (column_name)` syntax creates an index on an existing table. The other options do not use valid MySQL index-creation syntax.

Question 3

Which MySQL constraint can restrict a column's values to satisfy a Boolean condition, such as requiring `quantity >= 0`?

  1. ACHECK
  2. BDEFAULT
  3. CINDEX
  4. DAUTO_INCREMENT
Show answerCorrect answer: A

A `CHECK` constraint evaluates a condition for inserted or updated rows and rejects values that do not satisfy it. MySQL 8.0 enforces `CHECK` constraints.

Question 4

A MySQL table has an `ENUM('draft','published')` column named `state`. Which expression returns the string value stored in that column?

  1. A`CAST(state AS CHAR)`
  2. B`ENUM_VALUE(state)`
  3. C`state + 0`
  4. D`LENGTH(state)`
Show answerCorrect answer: A

Casting an `ENUM` value to `CHAR` produces its string label. Numeric contexts such as `state + 0` use the enum's internal numeric index instead.

Question 5

When a query groups rows by `region`, which clause can filter the resulting groups based on an aggregate such as `SUM(revenue)`?

  1. AWHERE SUM(revenue) > 1000
  2. BHAVING SUM(revenue) > 1000
  3. CORDER BY SUM(revenue) > 1000
  4. DLIMIT SUM(revenue) > 1000
Show answerCorrect answer: B

`HAVING` filters groups after grouping and can use aggregate expressions. `WHERE` filters input rows before aggregation and does not serve to filter groups by an aggregate result.

Question 6

What does `GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ', ')` produce for a group in MySQL?

  1. AA comma-separated string of the group's product names, ordered by name.
  2. BA JSON array of product names, ordered by name.
  3. CThe number of distinct product names in the group.
  4. DA separate result row for each product name.
Show answerCorrect answer: A

`GROUP_CONCAT()` combines non-NULL expression values from a group into a string. Its optional `ORDER BY` controls the order within that string, and `SEPARATOR` sets the delimiter.

Question 7

Which JOIN returns every row from the left table and matching rows from the right table, with NULLs for right-side columns when there is no match?

  1. AINNER JOIN
  2. BLEFT JOIN
  3. CCROSS JOIN
  4. DNATURAL JOIN
Show answerCorrect answer: B

A LEFT JOIN preserves all rows from its left input. When no matching right-side row exists, the right-side columns in the result are NULL.

How to use this practice test

The free practice session draws up to 20 random questions from the 50-question bank and displays results when you finish. Use the questions to check your SQL reasoning across indexes, constraints, data types, joins, filtering, and aggregation; review any missed items by tracing the query or definition that determines the result.

Preparation tips

  • Write paired queries using WHERE and HAVING, then identify whether each condition applies before or after grouping.
  • Trace LEFT JOIN results with matched and unmatched keys, noting where NULL values appear in the right-side columns.
  • Practice creating a secondary index and explain how its indexed column relates to an exact-match predicate.
  • Compare DECIMAL precision and scale with sample values, and test how ENUM, CHECK, and COALESCE affect stored or returned values.
  • Predict the output of aggregate expressions such as GROUP_CONCAT, including ordering and separator behavior.
Official sourceOfficial exam page ↗