Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 201:

  Examine this partial statement: SELECT ename, sal,comm FROM emp Now examine this output:

  

  WHICH ORDER BY clause will generate the displayed output?

  A. ORDER BY NVL(enam,0) DESC, ename

  B. ORDER BY NVL(comm,0) ASC NULLS FIRST, ename

  C. ORDER BY NVL(comm,0) ASC NULLS LAST, ename

  D. ORDER BY comm DESC NULLS LAST, ename

  Correct Answer: D

• Question 202:

  Which three statements are true about inner and outer joins? (Choose three.)

  A. A full outer join returns matched and unmatched rows.

  B. A full outer join must use Oracle syntax.

  C. Outer joins can be used when there are multiple join conditions on two tables.

  D. Outer joins can only be used between two tables per query.

  E. An inner join returns matched rows.

  F. A left or right outer join returns only unmatched rows.

  Correct Answer: ACE

• Question 203:

  SELECT *

  FROM bricks,colors;

  Which two statements are true?

  A. You can add an ON clause with a join condition.

  B. You can add a WHERE clause with filtering criteria.

  C. It returns the number of rows in BRICKS plus the number of rows in COLORS.

  D. You can add a USING clause with a join condition.

  E. It returnsthe same rows as SELECT * FROM bricks CROSS JOIN colors.

  Correct Answer: BE

• Question 204:

  Which two statements are true about conditional INSERT ALL?

  A. Each row returned by the subquery can be inserted into only a single target table.

  B. It cannot have an ELSE clause.

  C. The total number of rows inserted is always equal to the number of rows returned by the subquery

  D. A single WHEN condition can be used for multiple INTO clauses.

  E. Each WHEN condition is tested for each row returned by the subquery.

  Correct Answer: DE

• Question 205:

  Which two statements are true about a full outer join?

  A. It includes rows that are returned by an inner join.

  B. The Oracle join operator (+) must be used on both sides of the join condition in the WHERE clause.

  C. It includes rows that are returned by a Cartesian product.

  D. It returns matched and unmatched rows from both tables being joined.

  E. It returns only unmatched rows from both tables being joined.

  Correct Answer: AD

• Question 206:

  Which three are true about privileges and roles?

  A. A role is owned by the user who created it.

  B. System privileges always set privileges for an entire database.

  C. All roles are owned by the SYS schema.

  D. A role can contain a combination of several privileges and roles.

  E. A user has all object privileges for every object in their schema by default.

  F. PUBLIC can be revoked from a user.

  G. PUBLIC acts as a default role granted to every user in a database

  Correct Answer: DEG

• Question 207:

  Which is true about the round,truncate and mod functions>?

  A. ROUND(MOD(25,3),-1) IS INVALID

  B. ROUND(MOD(25,3),-1) AND TRUNC(MOD(25,3),-1) ARE BOTH VALID AND GIVE THE SAME RESULT.

  C. ROUND(MOD(25,3),-1) AND TRUNC(MOD(25,3),-1) ARE BOTH VALID AND GIVE THE DIFFERENT RESULTS.

  D. TRUNC(MOD(25,3),-1) IS INVALID.

  Correct Answer: B

• Question 208:

  Which three are true about the CREATE TABLE command?

  A. It can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint.

  B. The owner of the table should have space quota available on the tablespace where the table is defined.

  C. It implicitly executes a commit.

  D. It implicitly rolls back any pending transactions.

  E. A user must have the CREATE ANY TABLE privilege to create tables.

  F. The owner of the table must have the UNLIMITED TABLESPACE system privilege.

  Correct Answer: ABC

• Question 209:

  You want to return the current date and time from the user session, with a data type of TIMESTAMP WITH TIME ZONE.

  Which function will do this?

  A. CURRENT DATE

  B. CURRENT_ TIMESTAMP

  C. SYSDATE

  D. LOCALTIMESTAMP

  Correct Answer: B

  Reference: https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG004

• Question 210:

  Which two are true about using constraints? (Choose two.)

  A. A FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the parenttable must have the same names.

  B. A table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints.

  C. A table can have only one PRIMARY KEY and one FOREIGN KEY constraint.

  D. PRIMARY KEY and FOREIGN KEY constraints can be specified at the column and at the table level.

  E. A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.

  F. NOT NULL can be specified at the column and at the table level.

  Correct Answer: DE