Free 1Z0-909 sample questions

Correct answer: Change the isolation level to READ COMMITTED for the problematic transactions.

Changing the isolation level to READ COMMITTED for these specific transactions is the best solution. REPEATABLE READ uses gap locks, which can lock the space between index records, leading to a higher chance of deadlocks when inserting into a sequentially indexed column. READ COMMITTED does not use gap locks for ordinary statements, which significantly reduces the likelihood of this type of deadlock. Switching to SERIALIZABLE would worsen the problem by increasing locking. Using LOCK TABLES is too coarse and would serialize access, killing concurrency. Retrying transactions is a valid strategy but doesn't solve the root cause of the frequent deadlocks.

Correct answer: `JSON_MERGE_PATCH`

`JSON_MERGE_PATCH` is the correct function. It follows RFC 7396 semantics, where it recursively merges JSON documents. It updates existing key values and adds new key-value pairs. In this case, it would update `theme.color` and add `notifications.enabled`. `JSON_SET` can update existing values and add new ones, but requires specifying each path and value pair individually, making it less concise for merging objects. `JSON_REPLACE` only updates existing values and does not add new ones. `JSON_MERGE_PRESERVE` would create an array if keys conflict, which is not the desired behavior here.

Correct answer: SELECT c.category_name, SUM(oi.quantity * p.price) AS total_sales FROM categories c JOIN products p ON c.category_id = p.category_id JOIN order_items oi ON p.product_id = oi.product_id GROUP BY c.category_name HAVING SUM(oi.quantity * p.price) > 10000;

The `HAVING` clause is used to filter groups after aggregation has been performed, whereas the `WHERE` clause filters rows before aggregation. To filter based on the result of an aggregate function like `SUM()`, you must use `HAVING`. The alias `total_sales` cannot be used in the `WHERE` clause of the same query level where it is defined.

Correct answer: A function is applied to the indexed `email` column in the `WHERE` clause.

Applying a function like `SUBSTRING()` or `INSTR()` to an indexed column in the `WHERE` clause prevents the MySQL optimizer from using the index on that column. This makes the predicate non-SARGable (Search-Argument-able). To make the query use the index, the `WHERE` clause should be rewritten to compare the column directly, for example, `WHERE email LIKE '%@example.com'`. Note that even with `LIKE`, a leading wildcard (`%`) can also prevent index usage, but the function application is the definite cause here.

Correct answers: `OUT`; The procedure should return a result set containing the values.

The `OUT` parameter mode is used for parameters that the procedure will set and return to the caller. Since the procedure needs to return the department name and manager's name, these should be `OUT` parameters.

While using `OUT` parameters is one way to return values, a common and often preferred method for returning tabular data (even a single row with multiple columns) is to have the stored procedure execute a `SELECT` statement. This produces a result set that the calling application can then process.

Correct answer: True

This statement is true. The `JSON_TABLE()` function is a powerful feature in MySQL 8.0 that allows you to extract data from a JSON document and present it as a relational table with specified columns and data types. This resulting virtual table can be used in the `FROM` clause of a query and joined with other physical or virtual tables.

Correct answer: Use the MySQL Shell to convert the `products` table into a Document Store collection. Then, use the XDevAPI in the microservices to interact with the data and create indexes on the `details.price` and `details.brand_id` fields within the JSON documents.

This is the optimal solution. Using the MySQL Document Store and XDevAPI directly addresses all requirements. It maintains schema flexibility (NoSQL model), provides high-performance filtering by allowing indexes on nested JSON fields, and offers a modern, fluent API (XDevAPI) for microservice development. While adding generated columns (Option B) solves the performance issue, it doesn't address the need for a modern API and ties the schema more tightly to relational concepts. The other options are significantly less performant and scalable.

Correct answer: MySQL Connector/Python

MySQL Connector/Python is the official Oracle driver for Python. It is a pure Python implementation and provides support for both the classic MySQL protocol and the new X Protocol, which is required for interacting with the MySQL Document Store via the XDevAPI.

Correct answer: By using the `WITH CHECK OPTION` clause in the `CREATE VIEW` statement.

The `WITH CHECK OPTION` clause is used to enforce the conditions in the view's `WHERE` clause for any `INSERT` or `UPDATE` statements performed through the view. If the view is defined with `WHERE total_order_value >= 0`, adding `WITH CHECK OPTION` will cause any modification that violates this condition to fail. However, a view with aggregation (`SUM`) is not updatable, so this option would only work on an updatable view. In a non-updatable view scenario, a trigger would be the only way.

Correct answers: Break the import into smaller batches of rows (e.g., 10,000 rows).; Issue a `COMMIT` after each batch is processed.

Processing the import in smaller batches reduces the size of each individual transaction. This prevents the UNDO log from growing excessively and reduces memory consumption per transaction.

Committing after each smaller batch finalizes that part of the work, allowing MySQL to reclaim the UNDO log space and other resources used by that transaction. This combination of batching and committing is a standard pattern for large data loads.