Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 321:

  Examine this constraint information:

  

  Which three statements are true?

  A. The SALARY column must have a value.

  B. The DEPTNO column in the EMP table can contain NULLS.

  C. The COMMISION column can contain negative values.

  D. The DEPTNO column in the EMP table can contain the value 1.

  E. The MANAGER column is a foreign key referencing the EMPNO column.

  F. The DNAME column has a unique constraint.

  G. An index is created automatically in the MANAGER column.

  Correct Answer: BCE

• Question 322:

  Examine this SQL statement:

  

  Which two are true? (Choose two.)

  A. The subquery is executed before the UPDATE statement is executed.

  B. All existing rows in the ORDERS table are updated.

  C. The subquery is executed for every updated row in the ORDERS table.

  D. The UPDATE statement executes successfully even if the subquery selects multiple rows.

  E. The subquery is not a correlated subquery.

  Correct Answer: BC

• Question 323:

  Which three statements are true about indexes and their administration in an Oracle database?

  A. An INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its underlying table.

  B. An index can be created as part of a CREATE TABLE statement.

  C. A DROP INDEX statement always prevents updates to the table during the drop operation

  D. A UNIQUE and non-unique index can be created on the same table column

  E. A descending index is a type of function-based index

  F. If a query filters on an indexed column then it will always be used during execution of the query

  Correct Answer: BCE

• Question 324:

  Which statement will execute successfully?

  A. SELECT 1, 2 FROM DUAL UNION SELECT 3, 4 FROM DUAL ORDER BY 1, 2;

  B. SELECT 3 FROM DUAL UNION SELECT 4 FROM DUAL ORDER BY 3 ;

  C. SELECT 1, 2 FROM DUAL UNION SELECT 3, 4 FROM DUAL ORDER BY 3, 4;

  D. SELECT 1 FROM DUAL UNION SELECT 2 FROM DUAL ORDER BY 1, 2;

  Correct Answer: A

• Question 325:

  Examine this description of the PRODUCTS table:

  

  Rows exist in this table with data in all the columns. You put the PRODUCTS table in read- only mode. Which three commands execute successfully on PRODUCTS?

  A. ALTER TAELE products DROP COLUMN expiry_date;

  B. CREATE INDEX price_idx on products (price);

  C. ALTER TABLE products SET UNUSED(expiry_date);

  D. TRUNCATE TABLE products;

  E. ALTER TABLE products DROP UNUSED COLUMNS

  F. DROP TABLE products

  Correct Answer: BEF

• Question 326:

  Examine the description of the BOOKS table: Examine these requirements:

  

  1.

  Display book titles for books purchased before January 17, 2007 costing less than 500 or more than 1000.

  2.

  Sort the titles by date of purchase, starting with the most recently purchased book. Which two queries can be used? (Choose two.)

  

  A. Opion A

  B. Opion B

  C. Opion C

  D. Opion D

  E. Opion E

  Correct Answer: CE

• Question 327:

  Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?

  A. SELECT deptno,LISTAGG(ename, ' , ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno;

  B. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno ORDER BY ename;

  C. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (GROUP BY deptno) AS employee_list FROM emp ORDER BY ename;

  D. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;

  Correct Answer: D

• Question 328:

  Which three statements are true about Oracle synonyms? (Choose three.)

  A. A synonym cannot be created for a PL/SQL package.

  B. A synonym can be available to all users.

  C. A SEQUENCE can have a synonym.

  D. Any user can drop a PUBLIC synonym.

  E. A synonym created by one user can refer to an object belonging to another user.

  Correct Answer: BCE

• Question 329:

  Examine these SQL statements Which execute successfully:

  

  Which two statements are true after execution?

  A. The primary key constraint will be enabled and DEFERRED.

  B. The primary key constraint will be enabled and IMMEDIATE.

  C. The foreign key constraint will be disabled.

  D. The foreign key constraint will be enabled and DEFERRED.

  E. The foreign key constraint will be enabled and IMMEDIATE.

  Correct Answer: BC

• Question 330:

  Examine the data in the COLORS table:

  

  Examine the data in the BRICKS table:

  

  Which two queries return all the rows from COLORS?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  E. Option E

  Correct Answer: AB