1Z0-874 Online Practice Questions and Answers

What are reasons to prefer using GRANT and REVOKE statements over editing the privilege tables directly?

A. Using GRANT and REVOKE allows the server to figure out the right tables and do all the appropriate work

B. All grant tables in memory are immediately updated on GRANT and REVOKE

C. Making changes directly to the grant tables, one must remember to execute flush privileges to make the changes take effect

D. GRANT and REVOKE statements allow you to do more fine-grained tuning of user privileges than does editing the grant tables directly.

E. None of the above

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 ALL ROUTINE PRIVILEGES ON world.* TO 'joe'@'example.com';

B. mysql> GRANT ALL PRIVILEGES ON world.* TO 'joe'@'example.com';

C. mysql> GRANT ALL PRIVILEGES ON PROCEDURE world.* TO 'joe'@'example.com';mysql> GRANT ALL PRIVILEGES ON FUNCTION world.* TO 'joe'@'example.com';

D. mysql> GRANT CREATE ROUTINE ON world.* TO 'joe'@'example.com';mysql> GRANT ALTER ROUTINE ON world.* TO 'joe'@'example.com';

E. 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

F. This cannot be done; permissions on tables and stored routines are always shared.

Which of the following are true regarding the usage of the IDENTIFIED BY clause in the GRANT statement used to create a new user account?

A. There is no IDENTIFIED BY clause for the GRANT statement

B. The IDENTIFIED BY clause is always optional

C. The IDENTIFIED BY clause is always mandatory

D. The IDENTIFIED BY clause may be mandatory depending on the current SQL mode

E. The IDENTIFIED BY clause specifies the password to be used with the account

F. The IDENTIFIED BY clause specifies an alternate user name for the account

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 | | shared_memory_base_name | MYSQL | | server_id | 2 |

| 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.

Granting access to the mysql database...

A. Should never be done for any users.

B. Should always be done for all users.

C. Should only be done on administrator accounts.

D. Should be done for anyone needing to create views

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 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.

Consider the following:

mysql> DESC customers;

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

| Field | Type | Null | Key | Default | Extra |

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

| id | int(11) unsigned | NO | PRI | NULL | auto_increment |

| name_1 | char(20) | YES | MUL | NULL | |

| name_2 | varchar(20) | YES | | NULL | |

| name_3 | varchar(20) | YES | | NULL | |

| email | varchar(50) | YES | | NULL | |

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

mysql> EXPLAIN SELECT * FROM customers WHERE name_1 = 'Johnny'\G

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

id: 1

select_type: SIMPLE

table: customers type:

ref

possible_keys: i_name_1_01,i_name_1_02

key: i_name_1_01

key_len: 21

ref: const

rows: 1

Extra: Using where

What can be assumed by looking at the key_len column?

A. No indexes are being used.

B. The full index is being used.

C. The partial index is being used.

D. The longest index is 21 characters long.

Which of the following statements are true?

A. A MERGE table cannot be used with compressed tables.

B. A MERGE table may span across compressed and uncompressed tables.

C. A MERGE table can only be a collection of either compressed or uncompressed tables.

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]

When choosing hardware for your MySQL server, is memory important to MySQL and why?

A. No, because MySQL makes use of virtual memory on disks.

B. No, because MySQL makes more use of disks and processors than memory.

C. Yes, because more memory allows for less swapping by the operating system.

D. Yes, because more memory allows for larger buffers improving caching and reducing disk usage.