Oracle 1Z1-888 Exam : MySQL 5.7 Database Administrator

The following grants were executed:
GRANT CREATE ROUTING ON sales.* TO `webadmin'@'%';
GRANT ALTER ON PROCEDURE sales.myproc TO `webadmin'@'%';
A user successfully connects to the database as webadmin and created a stored procedure named get_reports. The next day, the user logs in again as webadmin and wants to delete the stored procedure named get_reports, and therefore, issues the following statement:
USE sales;
DROP PROCEDURE IF EXISTS get_reports;
What is the result of executing the statement?

• A. The user will get an error because he or she did not use the ALTER statement to drop the stored procedure.
• B. The user will get an error because he or she does not have the permission to drop stored procedures.
• C. The stored procedure named get_reports will be dropped.
• D. The user will get an error because he or she did not put the database name in front of the stored procedure name.

How does the InnoDB storage engine handle deadlocks when they are detected?

• A. The innodb_locks_unsafe_for_binlog setting determines which transaction is rolled back.
• B. One of the affected transactions will be rolled back, the other is allowed to proceed.
• C. Both the affected transactions will be rolled back.
• D. The transaction isolation level determines which transaction is rolled back.
• E. The affected transactions wait for innodb_lock_wait_timeout seconds, and then roll back.

Consider the ANALYZE TABLE command.
In which two situations should this command be used?

• A. after large amounts of table data have changed
• B. when you want to check a tables structure to see if it may have been damaged and needs repair
• C. when you want to update index distribution statistics
• D. when you need to find out why a query is taking a long time to execute

old_alter_table is disabled as shown.
mysql> SELECT @@old_alter_table;

Consider this statement on a RANGE-partitioned table:
mysql> ALTER TABLE orders DROP PARTITION p1, p3;
What is the outcome of executing this statement?

• A. Only the first partition (p1) will be dropped because only one partition can be dropped at any time.
• B. It results in a syntax error because you cannot specify more than one partition in the same statement.
• C. All data in p1 and p3 partitions is removed, but the table definition remains unchanged.
• D. All data in p1 and p3 partitions is removed and the table definition is changed.

In which order does MySQL process an incoming INSERT statement?

• A. It checks whether the user is authorized to perform the query, writes to the binary log, and then optimizes it.
• B. It optimizes the query, checks whether the user is authorized to perform it, and then writes to the binary log.
• C. It writes the query to the binary log, optimizes it, and then checks whether the user is authorized to perform the query.
• D. It checks whether the user is authorized to perform the query, optimizes it, and then writes to the binary log.