Oracle Oracle Certifications 1Z0-874 Questions & Answers

• Question 51:

  Consider the following:

  ALTER TABLE City ROW_FORMAT = FIXED

  What would be the effect of this statement?

  A. Nothing as this is not a valid statement.

  B. The table City is changed to a fixed row format.

  C. The columns in the table City are changed from VARCHAR to CHAR.

  Correct Answer: B

• Question 52:

  Which of the following are reasons you would want to normalize your data?

  A. It removes redundant data.

  B. It is the only way to make your data internationalized.

  C. It makes data access more flexible.

  D. It helps eliminate possible data inconsistencies.

  E. It makes backups more efficient.

  Correct Answer: B

• Question 53:

  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.

  Correct Answer: A

• Question 54:

  Consider the following:

  mysql> SELECT * FROM CountryLanguages;

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

  | ID | CountryName | CountryCode | Language |

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

  | 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

  Correct Answer: A

• Question 55:

  Consider the following: mysql> EXPLAIN SELECT * FROM City WHERE Name = 'Jacksonville' AND CountryCode = 'USA'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: City type: ref possible_keys: name_country_index key: name_country_index key_len: 13 ref: const,const rows: 1 Extra: Using where Which of the following best describes the meaning of the value for the key_len column?

  A. It shows how big the indexes are.

  B. It shows how many columns are examined.

  C. It shows how many results will be returned.

  D. It shows how many bytes will be used from index rows.

  Correct Answer: D

• Question 56:

  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.

  Correct Answer: B

• Question 57:

  Consider the following: mysql> EXPLAIN SELECT DISTINCT City.id,City.name -> FROM City,Country

  -> WHERE Country.Name IN ('United States','Canada','Mexico')

  -> AND City.CountryCode=Country.Code -> ORDER BY name\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 temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: Country type: eq_ref possible_keys: PRIMARY key: PRIMARY

  key_len: 3 ref: world.City.CountryCode rows: 1 Extra: Using where; Distinct Which of the following best describes the meaning of the values in the ref columns?

  A. City.CountryCode is used to sort what rows are returned from the City table.

  B. No indexed columns are used to choose rows from City and the City.CountryCode column is used to reference rows in the Country table.

  C. No index columns are used to choose rows from Country and the City.CountryCode column is used to reference rows in the City table.

  Correct Answer: B

• Question 58:

  In the following query, the Population column is indexed: mysql> EXPLAIN SELECT Name -> FROM Country -> WHERE Code LIKE '%B%' AND Population > 10000 \G

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

  id: 1

  select_type: SIMPLE

  table: Country type:

  ALL possible_keys:

  i_pop key: NULL

  key_len: NULL

  ref: NULL

  rows: 239

  Extra: Using where

  Which of the following best describes how to deal with the key value of the EXPLAIN output?

  A. Use FORCE KEY.

  B. Use USE INDEX.

  C. Use FORCE INDEX.

  D. Use USE POSSIBLE_KEY.

  Correct Answer: D

• Question 59:

  Is it possible to force the join order in a query, and if so how?

  A. No, it is not possible.

  B. Yes it is possible, use SELECT STRAIGHT_JOIN rather than just SELECT, the list the tables in the desired order.

  C. Yes it is possible use SELECT FORCE_JOIN rather than just SELECT, then list the tables in the desired order.

  D. Yes it is possible simply list the tables in the desired order in your SELECT.

  Correct Answer: B

• Question 60:

  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.

  Correct Answer: A