I85PREP
⌘ K
1Z0-909 study materials
1Z0-909 study resources

1Z0-909 practice questions

One question at a time. Build your 1Z0-909 understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1MySQL 8.0 Database Developer

In MySQL 8.0, which statement correctly describes the difference between the utf8mb3 and utf8mb4 character sets?

  1. utf8mb3 and utf8mb4 are identical in storage but differ only in collation naming conventions.
  2. utf8mb3 stores up to three bytes per character, while utf8mb4 stores up to four bytes and supports supplementary characters such as emoji. Correct answer.
  3. utf8mb3 is a fixed-width 4-byte encoding, while utf8mb4 uses variable width up to 3 bytes.
  4. utf8mb4 can only be used with InnoDB tables, while utf8mb3 works with every storage engine.

Explanation

utf8mb3 is the legacy three-byte UTF-8 encoding and cannot represent characters outside the Basic Multilingual Plane. utf8mb4 is full UTF-8 with up to four bytes per character, so it stores emoji and other supplementary characters correctly.

Question 2MySQL 8.0 Database Developer

Which data type should you choose to store a value that must be restricted to one of the strings 'small', 'medium', or 'large'?

  1. VARCHAR(10)
  2. SET('small','medium','large')
  3. ENUM('small','medium','large') Correct answer.
  4. TEXT

Explanation

ENUM restricts a column to exactly one value drawn from the declared list, and invalid values are rejected in strict mode. SET allows combinations of multiple listed values, which is not the requirement here.

Question 3MySQL 8.0 Database Developer

You run CREATE TABLE t (id INT, name VARCHAR(50)) without specifying an engine. What determines the storage engine used?

  1. The default_storage_engine system variable in effect for the session. Correct answer.
  2. Tables without an explicit ENGINE clause are created as MEMORY tables.
  3. The engine of the most recently created table in the schema.
  4. The storage engine is chosen automatically per column based on data type.

Explanation

When ENGINE is omitted, MySQL uses the server's default_storage_engine setting, which is InnoDB by default in 8.0. The choice is not inherited from other tables or decided per column.

Question 4MySQL 8.0 Database Developer

Which statement about a PRIMARY KEY in MySQL 8.0 InnoDB tables is correct?

  1. A primary key column implicitly allows NULL values.
  2. A table may define multiple primary keys as long as their columns differ.
  3. A primary key must always be an AUTO_INCREMENT column.
  4. A primary key enforces uniqueness and implicitly has a NOT NULL constraint. Correct answer.

Explanation

A primary key uniquely identifies each row, and its columns are implicitly declared NOT NULL. Only one primary key can exist per table, and AUTO_INCREMENT is optional.

Question 5MySQL 8.0 Database Developer

Which function returns the current date and time as a DATETIME value at the moment the statement begins executing?

  1. UNIX_TIMESTAMP()
  2. NOW() Correct answer.
  3. CURTIME()
  4. SYSDATE()

Explanation

NOW() (and its synonym CURRENT_TIMESTAMP) returns the statement start time, so repeated calls within one statement return the same value. SYSDATE() returns the exact moment of the call, which can differ within a statement.

Question 6MySQL 8.0 Database Developer

What is the effect of the DISTINCT keyword in SELECT DISTINCT department FROM employees?

  1. It sorts the result rows in ascending department order.
  2. It excludes rows where department is NULL.
  3. It removes duplicate department values from the result set. Correct answer.
  4. It returns only the first row of each department group with all other columns.

Explanation

DISTINCT eliminates duplicate rows from the output, so each distinct department value appears once. It does not impose ordering or filter out NULL values.

Question 7MySQL 8.0 Database Developer

Which statement about the AUTO_INCREMENT attribute is correct?

  1. Multiple AUTO_INCREMENT columns are allowed in a single table.
  2. AUTO_INCREMENT can be applied to a VARCHAR column.
  3. AUTO_INCREMENT values are always reused after a row is deleted.
  4. An AUTO_INCREMENT column must be part of an index, typically the primary key. Correct answer.

Explanation

An AUTO_INCREMENT column must be indexed, and it is commonly the primary key. InnoDB does not reuse a previously generated value after deletes unless the table is recreated or the counter is reset.

Question 8MySQL 8.0 Database Developer

Which privilege is required for a user to create a new table inside an existing schema?

  1. INSERT on the schema
  2. SELECT on the schema
  3. EXECUTE on the schema
  4. CREATE on the schema Correct answer.

Explanation

The CREATE privilege at the schema level allows a user to create tables and other objects within that database. SELECT, INSERT, and EXECUTE govern data access and routine execution, not object creation.

Question 9MySQL 8.0 Database Developer

You need to return each customer along with the total number of orders they placed, including customers with zero orders. Which join type and clause accomplish this?

  1. RIGHT JOIN with a WHERE filter on order_id IS NOT NULL
  2. INNER JOIN with GROUP BY customer
  3. LEFT JOIN with GROUP BY customer and COUNT(order_id) Correct answer.
  4. CROSS JOIN with COUNT(*) and no GROUP BY

Explanation

A LEFT JOIN keeps every customer row even when no matching order exists, and COUNT(order_id) ignores NULLs so zero-order customers report 0. Grouping by customer aggregates the order count per customer.

Question 10MySQL 8.0 Database Developer

Given a table sales(region, amount), which query returns only regions whose total amount exceeds 1000?

  1. SELECT region FROM sales WHERE SUM(amount) > 1000 GROUP BY region
  2. SELECT region, SUM(amount) FROM sales GROUP BY region HAVING SUM(amount) > 1000 Correct answer.
  3. SELECT region FROM sales HAVING SUM(amount) > 1000
  4. SELECT region FROM sales GROUP BY region WHERE SUM(amount) > 1000

Explanation

Aggregate filters belong in HAVING, which is evaluated after GROUP BY, so SUM(amount) can be tested there. WHERE cannot reference aggregate functions, and HAVING without GROUP BY does not produce per-region totals reliably.

Question 11MySQL 8.0 Database Developer

Which subquery form returns rows from employees whose salary is greater than the average salary of their own department?

  1. A scalar subquery in the SELECT list with no correlation.
  2. A correlated subquery referencing the outer department in the WHERE clause. Correct answer.
  3. An uncorrelated IN subquery comparing salary to a single global average.
  4. A derived table joined with a CROSS JOIN.

Explanation

A correlated subquery re-evaluates for each outer row and can reference the outer department, so the comparison uses that department's average. An uncorrelated subquery computes one value for the whole statement.

Question 12MySQL 8.0 Database Developer

Which statement about window functions such as ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) is correct?

  1. They collapse rows into groups like GROUP BY does.
  2. They require the DISTINCT keyword in the SELECT list.
  3. They compute a value for each row while retaining all rows in the result. Correct answer.
  4. They can only be used in the HAVING clause.

Explanation

Window functions evaluate over a partition of rows but return one value per row without reducing the row count. This distinguishes them from aggregate functions used with GROUP BY.

Question 13MySQL 8.0 Database Developer

A query uses WHERE last_name LIKE 'Sm%'. Which index can MySQL use to satisfy this predicate efficiently?

  1. A B-tree index on last_name Correct answer.
  2. A hash index on last_name
  3. A full-text index on last_name
  4. No index can ever be used with LIKE

Explanation

A left-anchored pattern with a literal prefix allows a range scan on a B-tree index on last_name. Leading-wildcard patterns such as '%ith' generally cannot use that index.

Question 14MySQL 8.0 Database Developer

Which statement about the InnoDB clustered index is correct?

  1. Secondary indexes store full row data instead of the primary key.
  2. Table rows are stored in the leaf nodes of the primary key's B-tree. Correct answer.
  3. The clustered index is always built on the first column of the table.
  4. InnoDB tables have no primary key index by default.

Explanation

InnoDB organizes table data within the clustered index, whose leaf pages contain the actual rows ordered by the primary key. Secondary indexes store the primary key value and look up the row through the clustered index.

Question 15MySQL 8.0 Database Developer

You want to insert a row and, if a duplicate key exists, update specified columns instead. Which statement accomplishes this?

  1. INSERT ... ON DUPLICATE KEY UPDATE col = value Correct answer.
  2. INSERT IGNORE ... VALUES (...)
  3. REPLACE INTO ... VALUES (...)
  4. UPDATE ... WHERE ... with no matching rows

Explanation

INSERT ... ON DUPLICATE KEY UPDATE performs an insert, and on a unique or primary key conflict it applies the specified column updates. INSERT IGNORE silently skips the row, and REPLACE deletes and reinserts.

Question 16MySQL 8.0 Database Developer

Which statement correctly describes the behavior of a transaction with the default REPEATABLE READ isolation level in InnoDB?

  1. Uncommitted changes from other sessions become visible after a short delay.
  2. The transaction cannot perform any writes.
  3. Every read sees the latest committed data from other transactions.
  4. Each consistent read within the transaction sees the snapshot established by the first read. Correct answer.

Explanation

Under REPEATABLE READ, InnoDB uses a consistent snapshot so repeated plain SELECTs in the same transaction return the same data. Locking reads and writes may still see or affect newer committed versions.

Question 17MySQL 8.0 Database Developer

Which statement about foreign key constraints in InnoDB is correct?

  1. A foreign key column must have the same name as the referenced column.
  2. The referenced columns must be indexed, and the child table columns are automatically indexed. Correct answer.
  3. Foreign keys do not require the referenced table to use a storage engine that supports them.
  4. ON DELETE CASCADE prevents deletion of the parent row.

Explanation

InnoDB requires an index on the referenced columns and creates an index on the child columns if one does not exist. Column names need not match, and both tables must use a foreign-key-capable engine such as InnoDB.

Question 18MySQL 8.0 Database Developer

What does the following statement do? CREATE VIEW high_earners AS SELECT id, salary FROM employees WHERE salary > 100000;

  1. It copies the matching rows into a new physical table.
  2. It creates a stored query that returns matching rows when referenced. Correct answer.
  3. It adds a CHECK constraint to the employees table.
  4. It creates a trigger that fires on salary updates.

Explanation

A view is a named stored query; selecting from it executes the underlying SELECT and returns matching rows. It does not duplicate data into a table or enforce constraints.

Question 19MySQL 8.0 Database Developer

Which statement about stored procedure parameters in MySQL 8.0 is correct?

  1. OUT parameters must be declared with a DEFAULT value.
  2. A procedure can return a result set only through an OUT parameter.
  3. All parameters are OUT parameters by default.
  4. INOUT parameters can both receive an input value and return a modified value. Correct answer.

Explanation

INOUT parameters pass a value in and can be changed to return a value to the caller. IN is the default mode, and result sets are returned with plain SELECT statements inside the routine.

Question 20MySQL 8.0 Database Developer

A developer runs EXPLAIN on a query and sees type = ALL with a large rows estimate. What does this indicate?

  1. The query uses a covering index for all columns.
  2. The query uses an efficient index range scan.
  3. The query performs a full table scan with no usable index. Correct answer.
  4. The query returns no rows because of a filter.

Explanation

In EXPLAIN output, type ALL means a full table scan, which is expensive for large tables. Adding or adjusting an index on the filtered or joined columns can reduce the rows examined.

Question 21MySQL 8.0 Database Developer

Which statement about the JSON data type in MySQL 8.0 is correct?

  1. JSON columns store a binary format that is validated on insert and can be queried with functions such as JSON_EXTRACT. Correct answer.
  2. JSON columns cannot be indexed in any way.
  3. JSON columns store text that is validated only when read.
  4. JSON columns automatically convert all numbers to strings.

Explanation

The JSON type validates documents on insert and stores them in an optimized binary format. Functions like JSON_EXTRACT and the -> operator query values, and generated columns can be indexed.

Question 22MySQL 8.0 Database Developer

A table has a composite index on (a, b, c). Which WHERE clause can use this index most effectively for a range on b?

  1. WHERE b = 5
  2. WHERE c = 3
  3. WHERE a = 1 AND b > 10 Correct answer.
  4. WHERE b > 10 AND c = 2

Explanation

A composite B-tree index is used left-to-right; an equality on the leading column a followed by a range on b allows an efficient range scan. Skipping the leading column generally prevents effective use of the index.

Question 23MySQL 8.0 Database Developer

Consider a transaction that updates rows in table A and then table B, while a concurrent transaction updates B first and then A. What is the most likely outcome under InnoDB?

  1. A deadlock may occur, and InnoDB rolls back one transaction with an error. Correct answer.
  2. The second transaction silently blocks forever with no error.
  3. Both transactions always complete because InnoDB serializes them automatically.
  4. InnoDB merges the conflicting updates into a single statement.

Explanation

Opposing lock acquisition orders can create a deadlock, which InnoDB detects and resolves by rolling back one transaction and returning an error. Applications should order lock acquisition consistently or retry on deadlock.

Question 24MySQL 8.0 Database Developer

Which statement about generated columns in MySQL 8.0 is correct?

  1. VIRTUAL generated columns are stored on disk and updated on every write.
  2. Both VIRTUAL and STORED generated columns can be indexed, and VIRTUAL ones are computed on read. Correct answer.
  3. STORED generated columns are computed on read and occupy no storage.
  4. Indexes can be created on STORED generated columns but not on VIRTUAL ones.

Explanation

VIRTUAL generated columns are evaluated when read and are not stored, while STORED ones are materialized on write. Secondary indexes can be defined on both types, which makes virtual columns useful for indexing JSON expressions.

Question 25MySQL 8.0 Database Developer

A developer wants to insert rows into a table and capture the auto-generated primary key values for later use within the same session. Which approach is correct?

  1. Rely on the AUTO_INCREMENT counter value stored in information_schema.
  2. Use SELECT * FROM table ORDER BY id DESC LIMIT 1 in another session.
  3. Read the value of LAST_INSERT_ID() immediately after the INSERT statement. Correct answer.
  4. Query MAX(id) from the table after the insert.

Explanation

LAST_INSERT_ID() returns the first automatically generated value produced by the most recent statement that generated one, and it is maintained per connection. Using MAX(id) or reading from another session is unreliable under concurrency.

Question 26MySQL 8.0 Database Developer

Which MySQL 8.0 string function returns the number of characters in a string, counting a multi-byte character such as 'é' as a single character?

  1. OCTET_LENGTH()
  2. CHAR_LENGTH() Correct answer.
  3. LENGTH()
  4. BIT_LENGTH()

Explanation

CHAR_LENGTH() counts characters, so a multi-byte character in a utf8mb4 column contributes 1. LENGTH() is a synonym for OCTET_LENGTH() and returns the byte count, which differs whenever characters occupy more than one byte.

Question 27MySQL 8.0 Database Developer

A developer writes SELECT IFNULL(NULL, 'x'), COALESCE(NULL, NULL, 'y'). What does the statement return?

  1. 'x' and 'y' Correct answer.
  2. NULL and 'y'
  3. 'x' and NULL
  4. NULL and NULL

Explanation

IFNULL() returns its second argument when the first is NULL, so it yields 'x'. COALESCE() returns the first non-NULL argument in its list, so it skips both NULLs and yields 'y'.

Question 28MySQL 8.0 Database Developer

Which data type should be used for a column that stores monetary amounts where exact decimal arithmetic is required, such as 1234.56?

  1. INT
  2. FLOAT
  3. DOUBLE
  4. DECIMAL(10,2) Correct answer.

Explanation

DECIMAL stores exact fixed-point values with a declared precision and scale, so 1234.56 is represented exactly. FLOAT and DOUBLE are approximate binary floating-point types and can introduce rounding error in monetary calculations.

Question 29MySQL 8.0 Database Developer

Which statement about the ENUM data type in MySQL 8.0 is correct?

  1. An ENUM column rejects NULL values even when the column is declared without NOT NULL
  2. An ENUM column stores one value chosen from a declared list and is internally stored as an integer index Correct answer.
  3. An ENUM column stores its values as variable-length strings and ignores the declared order
  4. An ENUM column can store any number of values from the declared list simultaneously

Explanation

ENUM restricts a column to a single value from the declared list and stores it internally as a numeric index into that list. Sorting and comparison therefore follow the declared order rather than alphabetical order.

Question 30MySQL 8.0 Database Developer

Which statement removes all rows from table log_entries while keeping the table definition intact and resetting the AUTO_INCREMENT counter?

  1. DROP TABLE log_entries
  2. TRUNCATE TABLE log_entries Correct answer.
  3. DELETE FROM log_entries
  4. ALTER TABLE log_entries DROP COLUMN id

Explanation

TRUNCATE TABLE removes every row and resets the AUTO_INCREMENT counter to its start value while leaving the table structure in place. It is a DDL operation and cannot be filtered with a WHERE clause.

Question 31MySQL 8.0 Database Developer

Which SQL clause filters rows produced by GROUP BY based on an aggregate value?

  1. HAVING Correct answer.
  2. ORDER BY
  3. LIMIT
  4. WHERE

Explanation

HAVING is evaluated after grouping and can reference aggregate expressions such as SUM(amount) or COUNT(*). WHERE is applied to individual rows before grouping and cannot filter on aggregate results.

Question 32MySQL 8.0 Database Developer

Which statement about the CAST() function in MySQL 8.0 is correct?

  1. CAST() is a synonym for CONVERT() only when the USING clause is specified
  2. CAST() can only convert numeric values to strings and cannot convert strings to dates
  3. CAST(expr AS type) converts the expression to the specified type, for example CAST('2024-01-15' AS DATE) Correct answer.
  4. CAST() modifies the stored data type of the underlying column permanently

Explanation

CAST(expr AS type) converts a value to the named type for the duration of the expression, such as turning a string literal into a DATE. It affects only the result, never the column definition in the table.

Question 33MySQL 8.0 Database Developer

Which statement about the ORDER BY clause in MySQL 8.0 is correct?

  1. It must always be followed by the ASC keyword
  2. It can sort by a column alias defined in the select list Correct answer.
  3. It can only reference columns that appear in the select list
  4. It is evaluated before the WHERE clause

Explanation

ORDER BY is evaluated after the select list is formed, so it may reference column aliases and even expressions not returned to the client. The WHERE clause is evaluated earlier, during row retrieval.

Question 34MySQL 8.0 Database Developer

A table orders has a DATE column order_date. Which query returns the number of orders placed in each calendar month of 2024?

  1. SELECT MONTH(order_date), COUNT(*) FROM orders GROUP BY order_date
  2. SELECT COUNT(*) FROM orders WHERE YEAR(order_date) = 2024 GROUP BY order_date
  3. SELECT MONTH(order_date) AS m, COUNT(*) FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01' GROUP BY m Correct answer.
  4. SELECT COUNT(*) FROM orders WHERE order_date = 2024

Explanation

The WHERE clause restricts rows to 2024 using a range that still allows an index on order_date, and GROUP BY on the month expression collapses rows into one count per month. The alias m can be used in GROUP BY in MySQL.

Question 35MySQL 8.0 Database Developer

Which query returns employees whose salary is above 5000 together with a computed column that labels them 'Senior' when salary exceeds 10000 and 'Mid' otherwise?

  1. SELECT name, CASE salary WHEN 10000 THEN 'Senior' ELSE 'Mid' END AS band FROM employees WHERE salary > 5000
  2. SELECT name FROM employees WHERE salary > 5000 AND salary > 10000
  3. SELECT name, IF(salary > 5000, 'Senior', 'Mid') AS band FROM employees
  4. SELECT name, CASE WHEN salary > 10000 THEN 'Senior' ELSE 'Mid' END AS band FROM employees WHERE salary > 5000 Correct answer.

Explanation

A searched CASE expression with WHEN salary > 10000 tests the intended threshold and an ELSE handles all remaining rows, while the WHERE clause enforces the salary floor. This yields both the filter and the derived label.

Question 36MySQL 8.0 Database Developer

A developer needs the average salary per department but only for departments having at least three employees. Which clause placement achieves this?

  1. Group by department, then filter with HAVING COUNT(*) >= 3 Correct answer.
  2. Filter rows with WHERE COUNT(*) >= 3, then group by department
  3. Group by department, then filter with WHERE COUNT(*) >= 3
  4. Order by department, then apply LIMIT 3

Explanation

Aggregate conditions are evaluated after grouping, so HAVING COUNT(*) >= 3 removes departments with fewer than three employees. WHERE cannot reference aggregate functions because it runs before grouping.

Question 37MySQL 8.0 Database Developer

Which statement about UNION versus UNION ALL in MySQL 8.0 is correct?

  1. UNION ALL can combine result sets with different numbers of columns, while UNION cannot
  2. UNION removes duplicate rows from the combined result, while UNION ALL keeps all rows and avoids the duplicate-removal step Correct answer.
  3. Both forms behave identically except that UNION ALL requires matching column names
  4. UNION ALL removes duplicates, while UNION keeps all rows

Explanation

UNION performs an implicit duplicate elimination over the combined result, which requires extra work. UNION ALL concatenates the result sets without deduplication and is therefore generally faster when duplicates are acceptable.

Question 38MySQL 8.0 Database Developer

A developer wants each department's employees ranked by salary, with ties receiving the same rank and no gaps in the ranking sequence. Which window function should be used?

  1. NTILE(4) OVER (PARTITION BY dept ORDER BY salary DESC)
  2. ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)
  3. RANK() OVER (PARTITION BY dept ORDER BY salary DESC)
  4. DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) Correct answer.

Explanation

DENSE_RANK() assigns equal values the same rank and continues with the next consecutive integer, so no gaps appear after a tie. RANK() also ties values but leaves gaps, and ROW_NUMBER() never produces ties.

Question 39MySQL 8.0 Database Developer

Which statement about a LEFT JOIN between customers and orders, joined on customer_id, is correct?

  1. Only orders that have a matching customer are returned
  2. Only customers that have at least one order are returned
  3. Every customer row is preserved, and order columns are NULL for customers with no matching order Correct answer.
  4. Rows from orders with no matching customer are preserved with NULL customer columns

Explanation

LEFT JOIN keeps all rows from the left table and fills columns from the right table with NULL when no match exists. Rows from the right table without a match are discarded.

Question 40MySQL 8.0 Database Developer

A developer must add a NOT NULL column named status to an existing table that already contains rows. Which approach succeeds without error?

  1. Add the column with a DEFAULT value so existing rows receive a valid non-NULL value Correct answer.
  2. Add the column as NOT NULL with no default, since MySQL fills existing rows with empty strings
  3. Add the column as NOT NULL only if the table is empty, otherwise the operation is impossible
  4. Add the column and then run UPDATE to set values before adding the NOT NULL constraint in a separate step

Explanation

When a NOT NULL column is added to a populated table, MySQL needs a value for existing rows, which a DEFAULT supplies. Without a default, strict mode rejects the statement because NULL cannot be assigned.

Question 41MySQL 8.0 Database Developer

Which statement about the CHECK constraint in MySQL 8.0 is correct?

  1. CHECK constraints are enforced for both inserts and updates on InnoDB tables Correct answer.
  2. CHECK constraints are enforced only on INSERT statements, not on UPDATE
  3. CHECK constraints are parsed but ignored, as in earlier MySQL releases
  4. CHECK constraints can reference columns from other tables in the same schema

Explanation

MySQL 8.0.16 and later enforces CHECK constraints on InnoDB tables for both INSERT and UPDATE operations. A row violating the predicate is rejected.

Question 42MySQL 8.0 Database Developer

A developer needs to exchange a partition of a partitioned table with a standalone table quickly. Which statement accomplishes this?

  1. ALTER TABLE orders EXCHANGE PARTITION p2023 WITH TABLE orders_2023 Correct answer.
  2. ALTER TABLE orders REORGANIZE PARTITION p2023 INTO (PARTITION p2024 VALUES LESS THAN (2025))
  3. ALTER TABLE orders REMOVE PARTITIONING
  4. ALTER TABLE orders TRUNCATE PARTITION p2023

Explanation

EXCHANGE PARTITION swaps a partition with a non-partitioned table that has a matching structure, moving data by metadata operation rather than row-by-row copying. It requires the standalone table to be empty or to match compatibility rules and to have no foreign keys.

Question 43MySQL 8.0 Database Developer

Which statement about the CTE defined by WITH recent AS (SELECT ...) SELECT * FROM recent is correct?

  1. The CTE is stored in the schema and remains available to later sessions
  2. The CTE cannot be joined to other tables in the same statement
  3. The CTE exists only for the duration of the single statement that follows it Correct answer.
  4. The CTE must be referenced at least twice or the statement fails

Explanation

A common table expression defined with WITH is a temporary named result set scoped to the statement in which it appears. It is not persisted in the data dictionary and disappears when the statement completes.

Question 44MySQL 8.0 Database Developer

A table has a column doc of type JSON. Which expression extracts the scalar string value of the key 'name' without JSON quotes?

  1. doc.name
  2. doc->'$.name'
  3. doc->>'$.name' Correct answer.
  4. JSON_EXTRACT(doc, 'name')

Explanation

The ->> operator is shorthand for JSON_UNQUOTE(JSON_EXTRACT(doc, '$.name')), returning the value as an unquoted string. The -> operator returns a JSON value, which retains the surrounding quotes for strings.

Question 45MySQL 8.0 Database Developer

Which statement about the SIGNAL statement in a stored program is correct?

  1. SIGNAL raises a user-defined error with a specified SQLSTATE and message, terminating the current statement Correct answer.
  2. SIGNAL writes a message to the MySQL error log without affecting execution
  3. SIGNAL can only be used inside triggers, not stored procedures
  4. SIGNAL converts a warning into a normal result set

Explanation

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...' raises a user-defined exception that aborts the current statement unless a handler traps it. The SQLSTATE value determines the error class and the message text is returned to the client.

Question 46MySQL 8.0 Database Developer

A developer wants to lock the rows read by a SELECT inside a transaction so that another transaction cannot modify them until commit. Which clause should be added?

  1. FOR SHARE
  2. SQL_NO_CACHE
  3. LOCK IN SHARE MODE with READ UNCOMMITTED
  4. FOR UPDATE Correct answer.

Explanation

SELECT ... FOR UPDATE takes exclusive row locks on the rows read, preventing other transactions from updating them until the current transaction commits or rolls back. FOR SHARE takes shared locks that still permit other shared reads but block writes.

Question 47MySQL 8.0 Database Developer

A developer runs a query with a WHERE clause on a column of type VARCHAR that is compared to an integer literal, and the index on that column is not used. What is the most likely cause?

  1. The query must include an ORDER BY clause for the optimizer to consider the index
  2. The column lacks a UNIQUE constraint, which is required for index use
  3. VARCHAR columns can never be indexed in InnoDB
  4. The type mismatch forces an implicit conversion of the column, preventing an index range scan Correct answer.

Explanation

Comparing a string column to a numeric literal causes MySQL to convert the column values to numbers for comparison, which makes the index on the column unusable for a range scan. Matching the literal type to the column type restores index usage.

Question 48MySQL 8.0 Database Developer

A table t has an index on (a, b). Which predicate set allows the optimizer to use the index for both columns as an equality range?

  1. WHERE a = 1 OR b = 2
  2. WHERE b = 2
  3. WHERE a = 1 AND b = 2 Correct answer.
  4. WHERE a > 1 AND b = 2

Explanation

An index on (a, b) supports equality searches on the leading column a, and with a fixed value for a it can also use the second column b for equality. That combination forms a single composite key lookup.

Question 49MySQL 8.0 Database Developer

Two sessions execute the same SELECT inside REPEATABLE READ transactions. Session 1 begins, reads a row, and then Session 2 commits an update to that row. Session 1 reads the row again. What does Session 1 see?

  1. The updated values only if Session 1 issued a locking read the first time
  2. The updated row values, because committed changes are always visible
  3. The original row values, because the consistent snapshot was established at its first read Correct answer.
  4. An error, because the row was modified after it was read

Explanation

Under REPEATABLE READ, InnoDB establishes a consistent read view at the first read in the transaction, so later plain SELECTs see the same snapshot even after another transaction commits. Locking reads such as FOR UPDATE read the latest committed version instead.

Question 50MySQL 8.0 Database Developer

A stored procedure receives an IN parameter p_id and must return the matching row count to the caller through an output parameter. Which parameter mode is required for the count?

  1. INOUT only, because IN parameters are read-only copies
  2. OUT Correct answer.
  3. No parameter is needed; stored procedures cannot return scalar values
  4. IN

Explanation

An OUT parameter is assigned inside the procedure and its value is passed back to the caller when the procedure returns. INOUT would also work but is not required, and IN parameters are input-only copies.