Oracle Oracle Database Application Development 1Z0-882 Questions & Answers

• Question 41:

  Consider the table structure shown by this output: Mysql> desc city:

  

  5 rows in set (0.00 sec)

  You execute this statement:

  SELECT -,-, city. * FROM city LIMIT 1

  What is returned?

  A. An error message

  B. One row with 5 columns

  C. One row with 10 columns

  D. One row with 15 columns

  Correct Answer: A

• Question 42:

  An application packs several fields of information into the details column of the table sensors. The first six characters of that data represent a location code.

  Example: "ABCDEFOO ooozzz comments will be here FIELDS----FIELD64"

  Given the query pattern:

  SELECT----FROM sensors WHERE details LIKE `ABCDEF

  Which three ALTER TABLE commands enable the optimizer to user an index for this WHERE patterns?

  A. ALTER TABLE sensors ADD KEY (details ) USING BTREE

  B. ALTER TABLE sensors ADD KEY (details) USING HASH

  C. ALTER TABLE sensors ADD KEY (details) USING BTREE

  D. ALTER TABLE sensors ADD KEY (details )USING HASH

  E. ALTER TABLE sensors ADD FULLTEX (details)

  Correct Answer: AB

• Question 43:

  Examine the structure and content of the MemberLocation table:

  

  You want to have the field location returned in all letters (example: BERLIN). Which query would you use?

  A. SELECT UPPER (Location) as location FROM MemberLocation

  B. SELECT UPPER (BINARY location) as location FROM MemberLocation

  C. SELECT UPPER (location AS UPPER ) as location FROM Memberlocation

  D. SELECT CONVERT (Location AS UPPER ) as location FROM memberlocation

  Correct Answer: C

• Question 44:

  What is true about the contents of the INFORMATION_SCHEMATA table?

  A. It contains information about the table structure for all databases.

  B. It contains information about all the tables, triggers, and views for all databases.

  C. It contains information such as name, character set, and collation for all the databases on the server.

  D. It contains information including tables, trigger, stored routines, and views for all databases

  Correct Answer: B

• Question 45:

  You have two tables:

  CREATE TABLE department (

  Department_ID int unsigned NOT NULL auto_increment PRIMARY KEY, Department _Name varchar(12)

  NOT NULL

  ) ENGINE=InnoDB

  CREATE TABLE employee (

  Employee_Number int unsigned NOT NULL PRIMARY KEY, Employee_Name varchar(10) NOT NULL,

  Department_ID int unsigned DEFAULT NULL,

  FOREIGN KEY (Department ID) REFERENCES Department (Department_ID) ON UPDATE SET NULL

  ON DELETE CASCADE

  ) ENGINE= InnoDB

  The tables have the data:

  Department

  

  You execute the statement:

  REPLACE INTO department (Department_ID, Department_Name) VALUES (1, `Admin');

  What data is in the employee table after the statement?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  Correct Answer: C

• Question 46:

  Consider the statement:

  CREATE TABLE t1 (a INT) PARTITION BY KEY

  /*150611 ALGORITHM = 1*/

  What does this statement do?

  A. Create the t1 table partitioned by KEY with the default algorithm in all versions.

  B. Create the t1 table partitioned by KEY using algorithm 1 only in MYSQL version 5.6.11 and the default algorithm in other versions.

  C. Create the t1 table partitioned by KEY using algorithm 1 only in MYSQL versions 5.6.11 or newer and the default algorithm in older versions.

  D. Create the t1 table partitioned by KEY using algorithm 1 only if the preceding statement returned error condition 50611.

  Correct Answer: C

• Question 47:

  Consider the my_table table with these contents:

  

  You execute: SELECT 1 FROM my_table; What is the result?

  A. A single value: 1

  B. Two values: 1, 1

  C. Two values: 11, 12

  D. Two values: 11, 13

  E. An error message

  Correct Answer: A

• Question 48:

  Given the table City: SELECT Name FROM City WHERE CountryCode = `USA" OR WHERE CountryCode= `JPN'

  What does this statement procedure?

  A. A single result set with one column that contains the names of cities from country codes USA and JPN.

  B. Two result sets each containing a single column with the names of cities from country codes USA and JPN.

  C. A single result set with two columns containing the names from country codes USA and JPN.

  D. No result set is returned and an error message is given.

  Correct Answer: A

• Question 49:

  An application tracks usage of educational courses in a company. Many people can take one course. Each person can take multiple courses. The data has been stored in one table but it is growing too large. You decide to normalize the table.

  What would a normalized data model contain?

  A. Two tables: employee and course, with foreign keys on employee ID and course ID.

  B. Three tables: employee, course, and a table cross-referencing employee IDs and course IDs

  C. Two tables: an employee table with multiple course IDs , and a course table

  D. Four tables: employee, course, courses by employee, and employees by course

  Correct Answer: A

• Question 50:

  A table country exists with a column Name. A user variable @ limitcount contains the value 20. Which two statements are valid uses of the LIMIT clause?

  A. SELECT Name FROM country LIMIT 100-50

  B. SELECT Name FROM country LIMIT 100,50

  C. SELECT Name FROM country LIMIT 35

  D. SELECT Name FROM country LIMIT @limitcount

  E. SELECT Name FROM country LIMIT RAND ()

  Correct Answer: BC