Free 1Z0-909 sample questions

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.

Correct answer: ?

The question mark (`?`) is the standard positional placeholder used in prepared statements with MySQL connectors like Connector/J for Java. The values are then bound to these placeholders in the order they appear.

Correct answer: `ST_Distance_Sphere()`

`ST_Distance_Sphere()` is the appropriate function for calculating the distance between two points on a spherical model of the Earth, which is ideal for geographic coordinates. It returns the distance in meters, so the result can be compared to 10,000 (10km) to find all stores within the radius. `ST_Distance` calculates Cartesian distance, which is incorrect for lat/lon. `ST_Contains` and `ST_Within` are for checking if one geometry is inside another, not for radius searches.