Oracle 1Z0-874 Online Practice Questions and Exam Preparation

Oracle 1Z0-874 Online Questions & Answers

• Question 101:

  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
  A. Table Countries: CountryCode, CountryNameTable Languages: LanguageID, LanguageTable CountryLanguage: CountryCode, LanguageID

• Question 102:

  Which are the effects of the following statement mysql> SET GLOBAL query_cache_limit = 2097152;

  A. Query results need to be at least 2MB in size to be cached in the Query Cache.
  B. Query results up to 2MB will be cached in the Query Cache.
  C. The total size of cached queries will be limited to 2MB.
  D. The statement will generate an error because query_cache_limit can only be set for a single connection.
  B. Query results up to 2MB will be cached in the Query Cache.

• Question 103:

  The stored function year_to_date is created by the 'root'@'localhost' account as follows:

  CREATE FUNCTION year_to_date ()

  RETURNS DECIMAL(10,2)

  SQL SECURITY DEFINER

  BEGIN

  ...

  END;

  Within the routine body, a number of calculations are made on data in the financials table and the calculated value is returned. The only account which can access the financials table is 'root'@'localhost'.

  If a client connects with the account 'joe'@'localhost' and calls the year_to_date function, what will happen?

  A. The function will always execute as if it was 'root'@'localhost' that invoked it since SQL SECURITY DEFINER has been specified.
  B. The function will not execute, as 'joe'@'localhost' does not have access to the financials table
  C. The function will not execute as SQL SECURITY DEFINER has been specified. It would execute if instead SQL SECURITY INVOKER had been specified
  D. If the account 'joe'@'localhost' has the EXECUTE privilege on year_to_date, the function will complete successfully
  D. If the account 'joe'@'localhost' has the EXECUTE privilege on year_to_date, the function will complete successfully

• Question 104:

  Can multi-column FULLTEXT indexes be used? If so, when?

  A. No, they can not be used.
  B. Yes, they should be used to search multiple columns simultaneously.
  C. Yes, they can be used to search either one or all of the indexed columns.
  B. Yes, they should be used to search multiple columns simultaneously.

• Question 105:

  When working with stored routines, which of the following are true in regards to the effect on the amount of data exchanged between client and server?

  A. They may increase the amount of data exchanged.
  B. They can help reduce the amount of data exchanged.
  C. They have no effect on the amount of data exchanged.
  B. They can help reduce the amount of data exchanged.

• Question 106:

  Is it a good idea to do periodic table rebuilds for InnoDB tables? Why?

  A. No, because the indexes have to be rebuilt.
  B. No, because there is a risk for data corruption.
  C. Yes, because it flushes the logs and frees memory.
  D. Yes, because the table may become fragmented after many INSERTs and DELETEs
  D. Yes, because the table may become fragmented after many INSERTs and DELETEs

• Question 107:

  Which of the following best describes how the binary log might be helpful in query optimization?

  A. By checking which queries it logs as slow.
  B. By checking the processor load it records when a query is executed.
  C. By identifying the WHERE clauses in queries for which indexes might be useful.
  C. By identifying the WHERE clauses in queries for which indexes might be useful.

• Question 108:

  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
  B. All grant tables in memory are immediately updated on GRANT and REVOKE

• Question 109:

  What will be the results of the following query?

  mysql> SELECT * FROM CountryLanguage PROCEDURE ANALYSE(10, 256)\G

  A. For each column of the CountryLanguage table, the minimum and maximum values will be listed.
  B. For each column of the CountryLanguage table, the average value or average length will be listed.
  C. For each column of the CountryLanguage table, a data type will be suggested that is just large enough to hold the data.
  D. For each column of the CountryLanguage table, the data type will be changed to one that is just large enough to hold the data.
  E. It will result in an error.
  C. For each column of the CountryLanguage table, a data type will be suggested that is just large enough to hold the data.
  D. For each column of the CountryLanguage table, the data type will be changed to one that is just large enough to hold the data.

• Question 110:

  Which of the following statements are required to create a key cache of 4 MB, assign the MyISAM table world. City to it and preload the index?

  A. mysql> SET GLOBAL city_cache.key_buffer_size = 4194304;mysql> CACHE INDEX world.City IN city_cache;mysql> LOAD INDEX INTO CACHE world.City;
  B. mysql> ALTER TABLE world.city KEY_CACHE = 4194304;
  C. mysql> CREATE CACHE FOR world.City SIZE = 4194304;
  D. It is not possible to create a key cache for a specific MyISAM table, only the global key cache can be used.
  A. mysql> SET GLOBAL city_cache.key_buffer_size = 4194304;mysql> CACHE INDEX world.City IN city_cache;mysql> LOAD INDEX INTO CACHE world.City;