Oracle Oracle Certifications 1Z0-882 Questions & Answers

• Question 51:

  Consider the statements: Mysql> drop function foo; ERROR 1305 (420000): FUNCTION test, foo does not exist Mysql > show warnings;

  

  Mysql> get diagnostics condition 2 @msg=MESSAGE_TEXT; What is the result of the final statement?

  A. An empty result is returned.@msg is set to message of the warning.

  B. A warning message is generated that adds error 1758 (invalid condition number) to the diagnostics area.

  C. A line will be an output to the error log that contains the warning message details from the failed command.

  D. An error is generated as only one condition can exist in the diagnostics area.

  Correct Answer: C

• Question 52:

  You attempt to create a temporary table by using the following statement:

  CREATE TEMPORARY TABLE employeesMAIN SELECT * FROM employees1 UNION ALL SELECT * FROM employees2;

  What is the result?

  A. An error is produced because you cannot create a TEMPORARY TABLE with a UNION.

  B. The employees common to both tables exist in employees MAIN.

  C. A unique list of employees exist in employeesMAIN.

  D. All rows from both tables exist in employeesMAIN.

  Correct Answer: A

• Question 53:

  Consider the structures of the country and countrylanguage tables.

  mysql >DESCRIBE country;

  

  mysql> DESCRIBE countrylanguage;

  

  Which query will give you the list of all European countries where German is spoken?

  A. SELECT Code AS c, Name FROM Country WHERE Continent = `Europe' AND EXISTS ( SELECT * FROM CountryLanguage WHERE CountryCode = Code And Language= `German' )

  B. SELECT Code AS c, Name FROM Country WHERE Continent = `Europe' AND Name IN ( SELECT * FROM CountryLanguage WHERE CountryCode = Code AND Language ='German' )

  C. SELECT Code AS c, Name FROM Country WHERE Continent = ` Europe' AND EXIST ANY ( SELECT Language, CountryCode FROM CountryLanguage WHERE CountryCode =Code AND Language = `German' )

  D. SELECT Code AS c, Name FROM Country WHERE Continent = `Europe' AND ( SELECT * FROM CountryLanguage WHERE CountryCode =Code AND Language ='German' )

  Correct Answer: C

• Question 54:

  Consider the my_table table with two integer columns, a and b, and the contents as shown; Mysql > SELECT a, b FROM my_table;

  

  1 row in set result of this query?

  SELECT a--b

  FROM my_table;

  A. 0

  B. 2

  C. 4

  D. An error message

  Correct Answer: A

• Question 55:

  Examine the fruit and wine tables: Fruit

  

  You execute this query:

  SELECT fruited, fruitname FROM fruit

  UNION

  SELECT id, name, country FROM wine;

  What is the result?

  A. The query succeeds and returns five columns of data.

  B. The query succeeds and returns two columns of data.

  C. The query falls because UNION does not work on tables with different number of columns.

  D. The query falls because the number of columns in the SELECT in the SELECT clauses are not equal.

  Correct Answer: B

• Question 56:

  Inspect the query:

  Mysql>SELECT dept_no, emp_no FROM employees JOIN dept _manager USING(emp_no) WHERE

  dept_no'd004';

  

  4 rows in set (0.00 sec)

  Mysql>EXPLAIN SELECT dept_no, emp_no FROM employees JOIN dept_manager USING (emp_no) WHERE dept_no='d004';

  

  2 rows in set (0.00 sec)

  Which two statements are true about the EXPLAIN output?

  A. All data for the result is read from the indexex.

  B. The PRIMARY KEY is used for filtering in both tables.

  C. The minimal number of rows possible are read.

  D. The dept_manager table has 4 times as many rows than the employees table.

  Correct Answer: C

• Question 57:

  Given the data from table t1:

  

  This DELETE command is executed: DELETE FROM t1 ORDER BY b.a DESC LIMIT 2; Which set of rows will be deleted by the command?

  A. (7,1) and (1,4)

  B. (2,8) and (1,4)

  C. (7,1) and (10,8)

  D. (2,8) and (10,8)

  Correct Answer: B

• Question 58:

  Which two keywords cannot be used in multi-table deletes?

  A. USING

  B. ORDER BY

  C. LIMIT

  D. IGNORE

  E. JOIN

  Correct Answer: BC

• Question 59:

  Consider the query and its output:

  Mysql> SELECT Language As Lang FROM countrylanguage ->WHERE countrycode ='GBR';

  

  A user wants to have an output as shown:

  

  What query would achieve this?

  A. SELECT GROUP_IMPLODE (Language) As Lang FROM countrylanguage WHERE countrycode= `GBR';

  B. SELECT GROUP_CAT(Language) As Lang FROM countrylanguage WHERE countrycode='GBR';

  C. SELECT GROUP_CSV(Language) As Lang FROM countrylanguage WHERE countrycode='GBR';

  D. SELECT GROUP_CONCAT (Language) As Lang FROM countrylanguage WHERE countrycode='GBR';

  Correct Answer: D

• Question 60:

  Consider the CREATE FUNCTION statement:

  CREATE FUNCTION countrycount ()

  BEGIN

  DECLARE count INT;

  SELECT COUNT (*) INTO count FROM country;

  RETURN count ;

  END

  What is the outcome when you try to create the function?

  A. An error results as the SELECT must assign the return values to a user variable.

  B. An error results as the count variable is not initialized with a value.

  C. An error result as the function must be defined with the CONTAINS SQL clause.

  D. An error result as the variable type returned by the function must be defined with a RETURNS clause.

  Correct Answer: D