MYSQL MySQL 5.0 Database Administrator Certified Professional Exam, Part II Sample Questions:

1. With replication, what on the master is used to send commands to the slave?

A) The binary log.
B) The SQL Thread.
C) The relay log.

2. Which of the following optimizations could be made based on data collected in the slow query log? (Choose four)

A) Tune server parameters.
B) Rewrite queries to perform more efficiently.
C) Make changes to the network infrastructure.
D) Change the storage engines in use.
E) Normalize data or add indexes to the tables that the queries use.

3. You need to allow the user 'joe'@'example.com' the privilege to create and alter stored routines in the world database, but you do not want that user to be able to change any table definitions. The user already has the required permissions to access table data in the database. Which of the following will achieve that?

A) Mysql> GRANT CREATE ROUTINE ON world.* TO 'joe'@'example.com'; mysql> GRANT ALTER ROUTINE ON world.* TO 'joe'@'example.com';
B) Mysql> GRANT ALL ROUTINE PRIVILEGES ON world.* TO 'joe'@'example.com';
C) You must use GRANT ALL PRIVILEGES ON world.routine1_name, world.routine2_name... TO 'joe'@'example.com', replacing routine1_name etc. with the names of each of the stored routines the user may want to create or alter
D) This cannot be done; permissions on tables and stored routines are always shared.
E) Mysql> GRANT ALL PRIVILEGES ON world.* TO 'joe'@'example.com';
F) Mysql> GRANT ALL PRIVILEGES ON PROCEDURE world.* TO 'joe'@'example.com'; mysql> GRANT ALL PRIVILEGES ON FUNCTION world.* TO 'joe'@'example.com';

4. Assuming that the account 'joe'@'%' does not already exist on the server, executing the statement
mysql> CREATE USER 'joe'@'%' IDENTIFIED BY 'sakila' will have the following consequences:

A) The account 'joe'@'%' is created on the server. However, clients cannot connect using this account until further privileges have been assigned to the account.
B) The account 'joe'@'%' is created on the server. Clients may connect to the server using this account, but will not be able to access any databases, tables or stored routines
C) The account 'joe'@'%' is created on the server. Clients may connect to the server using this account and execute stored routines, but will not be able to access any databases or tables
D) Nothing; there is no such command as CREATE USER

5. Consider the following: mysql> EXPLAIN SELECT * FROM City WHERE CountryCode = 'USA' *************************** 1. row *************************** id: 1 select_type: SIMPLE table: City type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4079
Extra: Using where
What does it mean that the possible_keys column value is NULL?

A) No index is considered available for this query.
B) No index exists for the table you are querying.
C) You are not making use of any available indexes in your query.

Solutions: