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

• Question 21:

  Which statement describes the process of normalizing databases?

  A. All text is trimmed to fit into the appropriate fields. Capitalization and spelling errors are corrected.

  B. Redundant tables are combined into one larger table to simplify the schema design.

  C. Numeric values are checked against upper and lower accepted bounds. All text is purged of illegal characters.

  D. Columns that contain repeating data values are split into separate tables to reduce item duplication.

  E. Indexes are created to improve query performance. The data of types of columns are adjusted to use the smallest allocation.

  Correct Answer: D

• Question 22:

  The people table contains the data as shown:

  

  Which two statements return two rows each?

  A. SELECT DISTINCT last_name, first_name FROM people

  B. SELECT 1,2 FROM people GROUP BY last_name

  C. SELECT first_name, last _name FROM people WHERE age LIKE `2'

  D. SELECT 1, 2 FROM people WHERE last _name ='smith'

  E. SELECT first _name, last_name FROM people LIMIT 1, 2

  Correct Answer: CE

• Question 23:

  You started a MySQL command line session with sq1_ mode (empty), and created the person table with

  the structure:

  Mysql> DESC person;

  

  You issue:

  INSERT INTO person VALUES (`casper', `undefined')

  What is the effect?

  A. `Casper' and ` undefined values are inserted into the `name' and gender' column.

  B. The server returns an error indicating that `undefined' cannot be inserted into a column of ENUM type

  C. The server returns a warning and the empty string is inserted to the `gender' column.

  D. The server returns a warning and the first specified value ,"male" is inserted to the gender column.

  Correct Answer: B

• Question 24:

  You create a new,empty database called `test'. You want to change the database `s CHARACTER SET to "latin1" and the database `sCOLLATION to `latin_german_ci'.

  Which statement is true?

  A. You can do this one command: ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_german_ci

  B. You can only do this with two separate commands: ALTER DATABASE `test' CHARACTER SET latin1 ALTER DATABASE `test' COLLATE latin_german1_ci

  C. You cannot change the CHARACTER set or COLLATION value on an existing database.

  D. Databases do not have CHARACTER SET or COLLATION attributes.

  Correct Answer: A

• Question 25:

  You wish to create a trigger on the country table. It will populate two session variables based on the row that is deleted: @old _countryNames with the value of the Name field @old _countryName with the value of the code field

  You may assume that only one row is ever deleted at a time. CREATE TRIGGER Country_ad AFTER DELETE ON Country FOR EACH ROW SET

  @old _CountryName= NEW.Name,

  @

  old _CountryCode=NEW.Code;

  What is the outcome of the CREATE TRIGGER statement?

  A.

  The trigger will be created successfully.

  B.

  An error results because the NEW keyword cannot be used in a DELETE trigger.

  C.

  An error results because FOR EACH ROW is invalid syntax.

  D.

  An error results because a BEGIN. . .END block is required.

  Correct Answer: C

• Question 26:

  Consider the content of the class and student tables: Class

  

  Which three queries produce the same result?

  A. SELECT * FROM class INNER JOIN student ON class.class_id=student.class_id

  B. SELECT * FROM JOIN student LEFT JOIN student ON class. Class.class_id=student.class_id

  C. SELECT * FROM class INNER JOIN student WHERE NOT ISNULL (student.class_id)

  D. SELECT * FROM JOIN student On class .class_id=student.class_id WHERE NOT ISNULL (student.class_id)

  E. SELECT * FROM student RIGHT JOIN class ON class.class_id=student.class_id

  Correct Answer: D

• Question 27:

  Which statement correctly demonstrates using a subquery as a scalar expression?

  A. SELECT (

  SELECT SUM (population)

  FROM Country

  SELECT SUM (population)

  FROM Country

  WHERE Code ='CAN'

  )

  B. SELECT SUM (population) FROM Country WHERE Code ="USA"

  + (SELECT SUM (population)

  FROM Country

  WHERE Code =.'CAN'

  )

  C. SELECT (SELECT SUM (population) FROM Country WHERE Code ='USA' FROM country WHERE Code= `CAN' )

  D. (SELECT SUM (population) FROM Country WHERE Code ="USA'

  Correct Answer: D

• Question 28:

  Given the data:

  

  Expected output: Which query produces the expected output?

  

  A. SELECT colors2.name, colors1.name FROM colors2 OPTIONAL JOIN colors1 ON colors2.name, colors1.name

  B. SELECT colors2.name, colors1.name FROM colors2 NATURAL JOIN colors1 ON colors2.name=colors1.name

  C. SELECT colors2.name, colors1.name FROM colors2 STRAIGHT JOIN colors1 ON colors2.name, =colors1.name

  D. SELECT colors2.name,colors1.name FROM colors2 LEFT JOIN colors1 ON colors2.name=colors1.name

  E. SELECT colors2.name,colors1.name FROM colors2 RIGHT JOIN colors1 ON colors2.name=colors1.name

  Correct Answer: D

• Question 29:

  As a developer, you inherit this table as part of a project:

  CREATE TABLE exam (

  Exam_id INTEGER UNSIGNED NOT NULL PRIMARY KEY,

  Examinee_id INTEGER UNSIGNED UNIQUE,

  Score INTEGER UNSIGNED

  )

  What change should you make to ensure that examinee_id is an integer value throughout the table?

  A. The examinee_id column should be designated as PRIMARY KEY.

  B. A NOT NULL qualifier should be moved from exam-id to examinee-id.

  C. The PRIMARY KEY should be dropped and re-created as PRIMARY KEY (examinee-id, exam_id).

  D. A NOT NULL qualifier should be added to examinee_id.

  Correct Answer: A

• Question 30:

  You have access to a MySQL 5.6 database with the SELECT ,INSERT, and DELETE privileges on all

  tables in the mydb database.

  The mydb database.

  The mydb t1 table has five rows of data.

  You use the statement below to remove all rows in the t1 table:

  Mysql> TRUNCATE TABLE mysql.t1;

  What is the result?

  A. The command succeeds and o rows are affected.

  B. The command succeeds and five rows are affected.

  C. The command fails owing to syntax error.

  D. The command fails owing to permissions error.

  Correct Answer: A