Free 1Z0-874 Exam Dumps PDF Preview

Question 1

Single choice

When performing diagnostic functions on a server should excessive start and stop messages be looked for in the error log, and why?

A
No, start and stop messages are of no relevance to diagnostics.
B
Yes, because there is a bug in mysqld that can cause multiple start and stop messages to be inserted in the log.
C
No, because they will not provide any information as to what is wrong.
D
Yes, because they can indicate that there is something causing the server to restart unexpectedly and not due to normal startup and shutdown.

Reveal answer details Close answer details

Correct answerD

Question 2

Single choice

Consider the following:

mysql> SELECT * FROM CountryLanguages;

+----+---------------+-------------+----------+

+----+---------------+-------------+----------+

| 1 | United States | USA | English |

| 2 | United States | USA | Spanish |

| 3 | Mexico | MEX | Spanish |

| 4 | Canada | CAN | English |

| 5 | Canada | CAN | French |

+----+---------------+-------------+----------+

Which of the following describe how this table would look correctly normalized?

A
Table Countries: CountryCode, CountryNameTable Languages: LanguageID, LanguageTable
CountryLanguage: CountryCode, LanguageID
B
Table Countries: CountryCode, CountryNameTable Languages: LanguageID, CountryCode, Language
C
Table Countries: CountryCode, CountryName, LanguageIDTable Languages: LanguageID, Language
D
Table Countries: CountryCode, CountryNameTable Languages: LanguageID, CountryCodeTable
CountryLanguage: LanguageID, CountryCode, Language

Reveal answer details Close answer details

Correct answerA

Question 3

Single choice

Which key buffers are available for MyISAM tables?

A
A global key buffer that keeps index information for all MyISAM tables.
B
An optional table-specific key buffer that can be assigned to single MyISAM tables.
C
An optional column-specific key buffer that can be assigned to a single column of a MyISAM table.

Reveal answer details Close answer details

Correct answerA

Question 4

Single choice

Which factors can be considered in a stored routine to allow or disallow access or modification of data?

A
The identity of the client user
B
The current date and time
C
The CPU load of the system
D
The number of current connections

Reveal answer details Close answer details

Correct answerB

Question 5

Single choice

When working with replication, why might you want to stop the SQL thread on the slave while keeping the I/O thread running?

A
So that no changes are made while making backups.
B
So that events for the slave do not get backlogged on the master.
C
So that remaining events can be processed while not receiving new events from the master.

Reveal answer details Close answer details

Correct answerC

Question 6

Single choice

Which of the following best describe how access control occurs in MySQL?

A
On login, the username, hostname and password are checked to allow access. On every query execution, the username, hostname and current object access permissions are checked.
B
On login, the username, hostname and password are checked to allow access. On every query execution, username, hostname, password and current object access permissions are checked.
C
On login, the username, hostname and password are checked to allow access and the object access permissions at login time are used for all subsequent operations for the lifetime of the connection.

Reveal answer details Close answer details

Correct answerA

Question 7

Single choice

As an administrator, you attempt to create the account 'joe'@'example.com' with the following statement:

GRANT SELECT, UPDATE, DELETE ON world.

* TO 'joe'@'example.com' Which of the following best describe the effects of issuing this statement?

A
The account is not created, as you must always specify a password for new users
B
Depending on active SQL modes, you may be prevented from creating the account because no password is specified. Otherwise, an account with no password is created.
C
An account with no password is created, regardless of SQL modes. The password will not need to be set at a later time.
D
Since no password is specified, the account is created with an empty password that must be changed by the user on first login
E
There is not enough information provided to determine the outcome of executing that statement.

Reveal answer details Close answer details

Correct answerA

Question 8

Single choice

How can the SHOW PROCESSLIST command be helpful when optimizing queries?

A
It shows if a query is using an index or not.
B
It shows how the server processes a query.
C
If checked periodically, it can reveal queries that cause other queries to hang.
D
It shows the percentage of processing power that each query is using on a server.

Reveal answer details Close answer details

Correct answerC

Question 9

Single choice

Which of the following statements best describe the indexes supported by the MEMORY storage engine, and their general characteristics?

A
MEMORY tables only support HASH indexes which provide fast lookups that use a unique index.
B
MEMORY tables only support BTREE which provide better performance with non-unique index values and other comparison operators.
C
MEMORY tables support HASH indexes which provide fast lookups that use a unique index, and BTREE indexes which provide better performance with non-unique index values and other comparison operators.
D
MEMORY tables support HASH indexes which provide better performance with non-unique index values and other comparison operators, and BTREE indexes which provide fast lookups that use a unique index.

Reveal answer details Close answer details

Correct answerC

Question 10

Single choice

Which of the following best describes the purpose of the InnoDB buffer pool?

A
It caches data and index information for InnoDB tables.
B
It is the amount of buffers available during a transaction.
C
It holds changes made during a transaction before they are written to the log.

Reveal answer details Close answer details

Correct answerA

Question 11

Single choice

Which of the following best describe the general steps involved in normalization?

A
Change the character set of the tables to normalize.
B
Remove repeating groups within rows.
C
Remove duplicate data within columns.

Reveal answer details Close answer details

Correct answerA

Question 12

Single choice

Which of the following statements are true regarding the structure of grant tables in new distributions?

A
They change with every upgrade.
B
They are often changed.
C
They remain the same for backwards compatibility.

Reveal answer details Close answer details

Correct answerB

Question 13

Single choice

Consider the following:

mysql> EXPLAIN SELECT * FROM City WHERE CountryCode = 'USA'\G *************************** 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 the possible_keys column in this output denote?

A
Whether there are any indexes on the table(s) you are querying.
B
If there are any indexes you may be able to use.
C
Whether you are using any indexes in your query.
D
If it is possible for you to include any indexes in your query.

Reveal answer details Close answer details

Correct answerB

Question 14

Single choice

Consider the following two partial outputs of the SHOW VARIABLES command on a master and slave server:

Master:

+--------------------------+---------------------------+

| Variable_name | Value |

+--------------------------+---------------------------+

| connect_timeout | 5 |

| log_bin | ON |

| max_connections | 100 |

| shared_memory_base_name | MYSQL |

| server_id | 2 |

| tmp_table_size | 5242880 |

| version | 5.0.13-rc-nt |

Slave:

+--------------------------+---------------------------+

| Variable_name | Value |

---------------------------+---------------------------+

| connect_timeout | 5 |

| log_bin | ON |

| max_connections | 10 |

| tmp_table_size | 4266336 |

| version | 5.0.16-rc-nt |

There is apparently a problem with the slave replicating from the master.

Which of the following best describes the most likely problem?

A
The server_id is not unique.
B
The version of the slave is newer than the version of the master.
C
The connect_timeout variable needs to be increased.
D
The max_connections variable needs to be increased.

Reveal answer details Close answer details

Correct answerA

Question 15

Single choice

Consider the following:

mysql> EXPLAIN SELECT Name FROM Country WHERE Population BETWEEN 1 AND 10000\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: Country type:

range possible_keys:

i_pop key: i_pop

key_len: 4

ref: NULL

rows: 10

Extra: Using where

What is the meaning of the range value of the type column?

A
There is a range of indexes that can be used.
B
The type of index uses the range algorithm.
C
The index is used to select rows that fall between a range of index values.
D
The optimizer has determined that only data from the very beginning or the very end of the table is
needed

Reveal answer details Close answer details

Correct answerC

Question 16

Single choice

In which ways can you set the query_cache_type option?

A
You can set it in the configuration file and restart the server.
B
You can set it at runtime for the entire server with the following statement: SET GLOBAL query_cache_type=[0|1|2]
C
You can set it at runtime for the current connection with the following statement: SET SESSION query_cache_type=[0|1|2]

Reveal answer details Close answer details

Correct answerA

Question 17

Single choice

Which of the following statements about MEMORY tables are true?

A
Creating many MEMORY tables should be avoided.
B
It is OK to store large amounts of data in MEMORY tables.
C
MEMORY tables should not be used to store large amounts of data.
D
The number of MEMORY tables on a server will not cause any performance problems.

Reveal answer details Close answer details

Correct answerD

Question 18

Single choice

Which of the following optimizations could be made based on data collected in the slow query log?

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

Reveal answer details Close answer details

Correct answerC

Question 19

Single choice

Consider the following EXPLAIN of a simple Join:
mysql> EXPLAIN SELECT CountryList.Name, CityList.Name, CityList.Population

-> FROM CountryList JOIN CityList ON CountryList.Code = CityList.Country

-> WHERE CityList.Population > 8000000\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: CountryList

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 239

Extra:

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: CityList type:

ALL possible_keys:

NULL key: NULL

key_len: NULL

ref: NULL

rows: 4079

Extra: Using where

How many rows need to be examined to perform the join?

A
239, the number of rows in the first table
B
4079, the number of rows in the second table
C
2159, the average of both numbers
D
4318, the sum of both numbers
E
974881, the product of both numbers

Reveal answer details Close answer details

Correct answerE

Question 20

Single choice

Why is --bind-address an important security option?

A
On a server with multiple network interfaces, you can have mysqld accept connections only on the given interface.
B
mysqld will only accept connections from the given address.
C
mysqld will turn off networking and will only accept local connections
D
mysqld won't accept any connections at all.

Reveal answer details Close answer details

Correct answerA

Question 21

Single choice

Which of the following best describes the purpose of the max_allowed_packet parmeter, and when it is set?

A
The max_allowed_packet parameter is set per-connection.
B
The max_allowed_packet parameter is global and is only set by shutting down the server.
C
The max_allowed_packet parameter is used to control the buffer that is used for communication between the server and the client.
D
The max_allowed_packet parameter is used to control the size of the dataset that is sent to the client.

Reveal answer details Close answer details

Correct answerA

Free 1Z0-874 Exam Dumps Preview

MySQL 5.0 Database Administrator Certified Professional Exam, Part II

PREVIEW CONTENTS · 1 VOLUME

QUESTION 1

QUESTION 2

QUESTION 3

QUESTION 4

QUESTION 5

QUESTION 6

QUESTION 7

QUESTION 8

QUESTION 9

QUESTION 10

QUESTION 11

QUESTION 12

QUESTION 13

QUESTION 14

QUESTION 15

QUESTION 16

QUESTION 17

QUESTION 18

QUESTION 19

QUESTION 20

QUESTION 21