OCP MySQL 8.0 Database Developer Free Sample Questions

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.