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

• Question 31:

  Which two queries return a value of NULL?

  A. SELECT NULL =NULL

  B. SELECT NULL is NULL

  C. SELECT NULL <= > NULL

  D. SELECT 1 > NULL

  E. SELECT COUNT (NULL);

  Correct Answer: AB

• Question 32:

  The city table has the following structure:

  

  Consider the statement with an incorrect field name:

  PREPARE countryBYID FROM `SELECT country FROM city WHERE ID=?, What happens if a prepared

  statement named countryByID already exists when the above statement is executed?

  A. A duplicate name error will result because a prepared statement with the same name already exists.

  B. An unknown column error will result and the old prepared statement definition will remain in effect.

  C. An unknown column error will result and no prepared statement named countryByID will exist.

  D. A warning will result and the old prepared statement definition will remain in effect.

  Correct Answer: A

• Question 33:

  Which statement is true when working with PHP and prepared statement?

  A. The mysql extension supports only client-side emulation.

  B. The mysql1 extension support client-side emulation.

  C. The PDO_MySQL extension supports both client side and server side emulation.

  D. The PDO_MySQL extension supports only server side emulation.

  Correct Answer: C

• Question 34:

  Assume that none of the databases exist.

  A. CREATE DATABASE $test

  B. CREATE DATABASE 1$

  C. CREATE DATABASE $

  D. CREATE DATABASE _

  E. CREATE DATABASE 12

  Correct Answer: E

• Question 35:

  You attempt to create two new tables:

  CREATE TABLE `warehouse' (

  `id' int (11) NOT NULL AUTO_INCREMENT,

  `name' varchar (20) NOT NULL,

  `phone' varchar (20) NOT NULL,

  PRIMARY KEY (` id)

  ) ENGINE=MyISAM

  CREATE TABLE `warehouseitem' (

  `warehouse_id' bigint (11) NOT NULL,

  `item_id' int (11) NOT NULL,

  `count' int(11) NOT NULL DEFAULT `0',

  KEY "warehouse_id' (`warehouse-id) ,

  FOREIGN KEY (warehouse_id) REFFERENCES warehouse (id) ) ENGINE= InnoDB

  You get this error :

  ERROR 1215 ( HYooo): cannot add foreign key constraint Which two changes are required to permit these

  statements to execute without any error?

  A. The `warehouseitem' table must be managed by the MySAm storage engine.

  B. The `warehouse-table must be managed by the InnoDB storage engine.

  C. The foreign key clause must be reversed: FOREIGN KEY warehouse(1)REFERENCES (warehouseid).

  D. The data types of the `warehouse'.'id' and ` warehouseitem.warehouse_is columns must match.

  E. The warehouse_id' column must be renamed `id' to match the definition on the `warehouse' table.

  F. A UNIQUE key must be defined for the columns (`item_id','warehouse_id').

  Correct Answer: CD

• Question 36:

  You have been tasked to create a database that will store a list of all managers and the employees who report directly to them. The following is stipulated:

  No manage is managing more than three people.

  No employee can work for more than one manage.

  Which of these designs represents a normalized schema that meets the project requirements?

  A. CREATE TABLE `manager' `manager' varchar (50) DEFAULT NULL, `employee2' varchar (50) DEFAULT NULL, `employee' varchar (50) DEFAULT NULL, UNIQUE ( `manager `, `employee1', `employee2, `employee3') )

  B. CREATE TABLE `managers' ( "id' int(11) NOT NULL AUTO_INCREMENT, `manager' varchar (50) DEFAULT NULL , PRIMARY KEY (`id') ) CREATE TABLE "employees' ( `id' int(11) NOT NULL AUTO _INCREMENT, `manager_id' int(11) DEFAULT NULL, `employee varchar (25) DEFAULT NULL, PRIMARY KEY (`id') )

  C. CREATE TABLE `manager' ( `manager' varchar (50) DEFAULT NULL, `employee_list'varchar (150) DEFAULT NULL, )

  D. CREATE TABLE `message' ( `id' int(11) NOT NULL AUTO_INCREMENT, `manager' varchar(50) DEFAULT NULL, PRIMARY KEY ("id') ) CREATE TABLE `employees' ( `id int (11) NOT NULL AUTO _INCREMENT, ` employees' varchar(25) DEFAULT NULL, )

  Correct Answer: A

• Question 37:

  You have a database `dev' that contains 15 tables, all of which use the CHARACTER SET `utfs' and the COLLATION `utfs_general_ci'.

  You perform the command:

  ALTER DATABASE `dev' CHARACTER SET ='latin' COLLATION='latin1'_swedish_ci'

  What is the result?

  A. You get an error because database are not allowed to have CHARACTER SET or COLLATION attributes.

  B. You get an error because the settings for CHARACTER SET and COLLATION attributes do not match the settings for the tables inside the database.

  C. You get an error while trying to change from a more inclusive CHARACTER SET like `utfs to a less' inclusive CHARACTER SET like `latin'.

  D. You get an error because changes to the CHARACTER SET or COLLATION attribute can happen only for empty databases.

  E. The statement succeeds and new tables created in this database use the new settings as their default values.

  F. The statement succeeds and all of the tables inside the database are converted to user the new settings.

  Correct Answer: E

• Question 38:

  You want to load data directly from a file into MYSQL by using the SOURCE command. Which types of data can the file contains to perform this?

  A. SQL commands

  B. Comma-delimited data

  C. Tab-delimited data

  D. MyISAM or InnoDB data files

  Correct Answer: B

• Question 39:

  Which two statements provide the definition for a view named view1 in the test database?

  A. SHOW CREATE VIEW view1 FROM test

  B. SHOW CREATE VIEW test-view1

  C. SELECT VIEW_DEFINITION_SCHEMA.VIEWS FROM INFORMATION _SCHEMA.VIEWS WHERE TABLE_NAME="view1" AND TABLE_SCHEMA = "test"

  D. SELECT DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE NAME = "test"

  E. SHOW DEFINITION FOR test.view

  Correct Answer: AC

• Question 40:

  When working with stored routines, these details are available:

  The affected rows count

  The number of conditions that occurred

  The condition information, such as the error code and message

  Where can you find these default?

  A. In the Handler area, defined in the DECLARE handler_action HANDLER block in a stored routine

  B. In the Signal area, which is set with the help of the SIGNAL statement in a stored routine

  C. In the Diagnostics area, part, of which can be stored in user-defined or routine variables

  D. In the Error area, which can be accessed with the help of the SHOW ERRORS statement

  Correct Answer: C