Free MySQL 8.0 Database Developer Practice Questions

Sample Practice Questions

Try these 10 sample questions from the MySQL 8.0 Database Developer practice bank. Each includes the answer and explanation. Continue with the interactive practice above to review the full question set.

1. 1A Java application uses the JDBC API to access MySQL. Which connector implements that API?

   1. A.MySQL Connector/JCorrect answer: A
   2. B.MySQL Connector/NET
   3. C.MySQL Connector/ODBC
   4. D.MySQL Connector/Python

   Explanation: The manual states: "Connector/J implements the Java Database Connectivity (JDBC) API". Connector/J implements JDBC for Java; the other connectors target .NET, ODBC, or Python interfaces.

   Reference: MySQL Connector/J Developer Guide: Overview — Chapter 1, Overview of MySQL Connector/J

2. 2In MySQL SQL prepared statements, what can a ? parameter marker represent?

   1. A.A table identifier
   2. B.A SQL keyword
   3. C.A bound data valueCorrect answer: C
   4. D.A column identifier

   Explanation: The manual states: "Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth." A marker substitutes a value, not SQL syntax or identifiers.

   Reference: MySQL 8.0 Reference Manual: PREPARE Statement — PREPARE Statement, parameter markers

3. 3A view is created as SELECT * from a table. A column is later added to the table. What happens to the view's columns?

   1. A.The new column becomes visible automatically
   2. B.The new column is not added to the existing viewCorrect answer: B
   3. C.The view is automatically dropped and recreated
   4. D.The view switches to a temporary table

   Explanation: The manual states: "if a view is defined as SELECT * on a table, new columns added to the table later do not become part of the view" The view definition is frozen at creation, so the new column is not added automatically.

   Reference: MySQL 8.0 Reference Manual: CREATE VIEW Statement — CREATE VIEW, frozen definitions

4. 4A session executes START TRANSACTION while autocommit is enabled, then commits. What happens to autocommit after the transaction ends?

   1. A.It reverts to its previous stateCorrect answer: A
   2. B.It remains disabled for the session
   3. C.It is disabled for every session
   4. D.It switches to the global setting only after reconnect

   Explanation: The manual states: "The autocommit mode then reverts to its previous state." START TRANSACTION suspends autocommit until the explicit transaction ends.

   Reference: MySQL 8.0 Reference Manual: START TRANSACTION, COMMIT, and ROLLBACK — START TRANSACTION, autocommit behavior

5. 5A composite index is defined on (col1, col2, col3). Which column set is a leftmost prefix for indexed lookup?

   1. A.(col2)
   2. B.(col2, col3)
   3. C.(col1, col2)Correct answer: C
   4. D.(col3)

   Explanation: The manual states: "if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3)." The index begins with col1, so (col1, col2) is a leftmost prefix.

   Reference: MySQL 8.0 Reference Manual: Multiple-Column Indexes — Multiple-Column Indexes, leftmost prefixes

6. 6A procedure parameter is declared INOUT. How does its value pass between caller and procedure?

   1. A.It passes in but changes are not returned
   2. B.It starts as NULL and only passes out
   3. C.It passes in and changes are returnedCorrect answer: C
   4. D.It starts as NULL and changes are not returned

   Explanation: The manual states: "An INOUT parameter is initialized by the caller, can be modified by the procedure, and any change made by the procedure is visible to the caller when the procedure returns." INOUT carries a value in and returns modifications; OUT starts as NULL in the procedure.

   Reference: MySQL 8.0 Reference Manual: CREATE PROCEDURE and CREATE FUNCTION — Stored Routine Parameters

7. 7What happens when an invalid JSON document is inserted into a MySQL JSON column?

   1. A.It is stored unchanged as text
   2. B.The server raises an errorCorrect answer: B
   3. C.It is silently converted to an empty object
   4. D.The server stores it as SQL NULL

   Explanation: The manual states: "Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error." The JSON type validates inserted documents rather than storing invalid JSON unchanged.

   Reference: MySQL 8.0 Reference Manual: The JSON Data Type — The JSON Data Type, validation

8. 8A C# application needs a managed ADO.NET data provider for MySQL. Which connector supplies it?

   1. A.MySQL Connector/J
   2. B.MySQL Connector/NETCorrect answer: B
   3. C.MySQL Connector/C++
   4. D.MySQL Connector/Python

   Explanation: The manual states: "Connector/NET is a fully managed ADO.NET data provider written in 100% pure C#". Connector/NET supplies the requested managed ADO.NET provider; the other connectors target Java, C++, or Python.

   Reference: MySQL Connector/NET Developer Guide: Introduction — Chapter 1, Introduction to MySQL Connector/NET

9. 9A client prepares a SQL statement in one session. What happens to that statement when the session ends?

   1. A.It remains available to all sessions
   2. B.It remains available only to the next session
   3. C.It is automatically deallocated by the serverCorrect answer: C
   4. D.It is committed into the database schema

   Explanation: The manual states: "If you terminate a session without deallocating a previously prepared statement, the server deallocates it automatically." Prepared statements are session-specific and are cleaned up at session end.

   Reference: MySQL 8.0 Reference Manual: Prepared Statements — Prepared Statements, session scope

10. 10For an updatable view, what does WITH CHECK OPTION prevent?

    1. A.Updates or inserts that fail the view's WHERE conditionCorrect answer: A
    2. B.All updates and inserts through the view
    3. C.All SELECT statements that use the view
    4. D.Changes to the underlying table made outside the view

    Explanation: The manual states: "The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts or updates to rows except those for which the WHERE clause in the select_statement is true." The option restricts writes through the view to rows satisfying its condition.

    Reference: MySQL 8.0 Reference Manual: CREATE VIEW Statement — CREATE VIEW, WITH CHECK OPTION