01. You want to run one parameterized query many times within a single session using a server-side prepared statement in plain SQL.
Which sequence prepares and runs it correctly?
a) PREPARE s FROM 'SELECT * FROM t WHERE id = ?'; EXECUTE s USING @v;
b) PREPARE s AS 'SELECT * FROM t WHERE id = ?'; RUN s USING @v;
c) SET @v = 7; EXECUTE 'SELECT * FROM t WHERE id = ?' USING @v;
d) DECLARE s STATEMENT FROM 'SELECT * FROM t WHERE id = ?'; CALL s(@v);
02. Consider how MySQL stores and compares character data.
Which two statements are accurate?
(Choose two.)
a) VARCHAR(n) always reserves exactly n characters of storage regardless of content.
b) CHAR(n) is fixed-length and right-pads stored values with spaces.
c) A _ci collation treats 'A' and 'a' as different values.
d) A _bin collation compares by byte value, so a _bin collation is case-sensitive.
e) ENUM lets a column hold several members of its list at once.
03. Table t holds three rows:
dept A, emp a1, salary 100
dept A, emp a2, salary 200
dept B, emp b1, salary 300
This query runs:
SELECT emp, SUM(salary) OVER (PARTITION BY dept ORDER BY emp) AS s FROM t ORDER BY dept, emp;
What are the s values for a1, a2, and b1?
a) 100, 300, 600
b) 100, 300, 300
c) 300, 300, 300
d) 100, 200, 300
04. A connector must require an encrypted connection and confirm it is talking to the intended server by validating the server certificate's hostname against a trusted CA.
Which TLS/SSL mode meets both requirements?
a) PREFERRED
b) REQUIRED
c) DISABLED
d) VERIFY_IDENTITY
05. Which two statements about InnoDB isolation levels are correct?
(Choose two.)
a) REPEATABLE READ is InnoDB's default isolation level.
b) READ UNCOMMITTED prevents non-repeatable reads but allows dirty ones.
c) READ COMMITTED prevents dirty reads but allows non-repeatable reads.
d) REPEATABLE READ allows dirty reads to occur.
e) SERIALIZABLE still permits phantom reads at its range.
06. STRICT_TRANS_TABLES is enabled. Table t is an InnoDB table with column q TINYINT. You run:
INSERT INTO t (q) VALUES (500);
500 exceeds the TINYINT range. What is the result?
a) The value is clipped to the TINYINT maximum and stored with a warning.
b) The INSERT fails with an out-of-range error and no row is stored.
c) The value wraps around to a negative TINYINT value and is stored.
d) The column is widened to SMALLINT automatically to hold the value.
07. Both columns below are separately indexed, yet the plan for this query shows type: ALL:
SELECT * FROM sales WHERE region_id = 3 OR salesperson_id = 55;
Which rewrite most reliably lets both indexes contribute?
a) Replace the OR with a filter that requires both conditions.
b) Add ORDER BY region_id to guide the optimizer.
c) Replace OR with a CONCAT() comparison of the columns.
d) Split it into two single-predicate SELECTs combined with UNION.
08. The sales table holds three rows: region East with amount 100, region East with amount 200, and region West with amount 300.
This query runs:
SELECT region, SUM(amount) FROM sales GROUP BY region WITH ROLLUP;
Besides the per-region rows, what does WITH ROLLUP add to the result?
a) A final row with NULL region and 600
b) A row with region 'TOTAL' and 600
c) One extra row for each base row in a group
d) Nothing; only East 300 and West 300 are returned
09. Consider where a window function is syntactically allowed within a single SELECT statement in MySQL 8.0.
Which two statements are true?
(Choose two.)
a) A window function may appear in the SELECT list.
b) A window function may appear in the ORDER BY clause.
c) A window function may appear directly in the WHERE clause.
d) A window function may appear directly in the HAVING clause.
e) A window function may appear inside the GROUP BY clause.
f) A bare RANK() without OVER acts as an ordinary aggregate.
10. Which two statements about InnoDB gap locks and next-key locks under REPEATABLE READ are true?
(Choose two.)
a) A next-key lock combines a record lock with a gap lock before the record.
b) Gap locks are placed on individual existing rows to block their update.
c) Next-key locking is disabled by default under the REPEATABLE READ isolation level.
d) Gap locks help prevent phantom rows in a scanned range.
e) Gap locks are taken only under the READ COMMITTED isolation level.
The Oracle MySQL Database Developer (1Z0-909) Sample Question Set is designed to help you prepare for the MySQL 8.0 Database Developer Oracle Certified Professional certification exam. To become familiar with the actual Oracle Certification exam environment, we suggest you try our Sample Oracle 1Z0-909 Certification Practice Exam.
