Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 401:

  Which set of commands will prompt only once for the name of the table to use in the query?

  A. PROMPT Enter table name andx SELECT employee_id FROM andx WHERE last_name = `King';

  B. DEFINE x = `employees' PROMPT Enter table name andx SELECT employee_id FROM andx WHERE last_name = `King';

  C. PROMPT Enter table name andx SELECT employee_id FROM andandx WHERE last_name = `King';

  D. PROMPT Enter table name andandx SELECT employee_id FROM andx WHERE last_name = `King';

  Correct Answer: D

• Question 402:

  The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.

  The table has two rows whose CUST_LAST_NAME values are Anderson and Ausson.

  Which query produces output for CUST_LAST_NAME containing Oder for the first row and Aus for the second?

  A. SELECT REPLACE(REPLACE(cust_last_name, `son', `'), `An', `O') FROM customers;

  B. SELECT REPLACE(TRIM(TRAILING `son' FROM cust_last_name), `An', `O') FROM customers;

  C. SELECT REPLACE(SUBSTR(cust_last_name, -3), `An', `O') FROM customers;

  D. SELECT INITCAP(REPLACE(TRIM(`son' FROM cust_last_name), `An', `O')) FROM customers;

  Correct Answer: A

• Question 403:

  Which two statements are true about the data dictionary?

  A. The data dictionary is accessible when the database is closed.

  B. The data dictionary does not store metadata in tables.

  C. Views with the prefix ALL_, DBA_ and USER_ are not all available for every type of metadata.

  D. Views with the prefix DBA_ display only metadata for objects in the SYS schema.

  E. Views with the prefix ALL_ display metadata for objects to which the current user has access.

  Correct Answer: CE

• Question 404:

  Which three statements are true about external tables? (Choose three.)

  A. They can be temporary tables.

  B. DML statements can modify them.

  C. They can be used in queries containing joins.

  D. They can be used in queries containing sorts.

  E. They can be indexed.

  F. Their metadata is stored in the database.

  Correct Answer: CDF

• Question 405:

  Table HR.EMPLOYEES contains a row where the EMPLOYEE_ID is 109.

  User ALICE has no privileges to access HR.EMPLOYEES.

  User ALICE starts a session.

  User HR starts a session and successfully executes these statements:

  GRANT DELETE ON employees TO alice;

  UPDATE employees SET salary = 24000 WHERE employee_id = 109;

  In her existing session ALICE then executes:

  DELETE FROM hr.employees WHERE employee_id = 109;

  What is the result?

  A. The DELETE command will wait for HR's transaction to end then return an error.

  B. The DELETE command will immediately delete the row.

  C. The DELETE command will wait for HR's transaction to end then delete the row.

  D. The DELETE command will immediately return an error.

  Correct Answer: D

• Question 406:

  Examine this partial statement:

  

  Which is true?

  A. Both the query and the subquery can select only zero rows or one row.

  B. Both the query and the subquery can select any number of rows.

  C. The query can select only zero rows or one row, but the subquery can select any number of rows.

  D. The query can select any number of rows, but the subquery can select only zero rows or one row.

  Correct Answer: D

  Reference: https://community.oracle.com/tech/developers/discussion/3684885/statements-about-subqueries

• Question 407:

  Examine this statement:

  

  Which two things must be changed for it to execute successfully? (Choose two.)

  A. The foreign key constraint on DEPT_ID must be defined at the table level instead of the column level.

  B. The NOT NULL constraint on ENAME must be defined at the column level instead of the table level.

  C. The primary key constraint on EMP_ID must have a name.

  D. One of the LONG columns must be changed to a VARCHAR2 or CLOB.

  E. The word CONSTRAINT in the foreign key constraint on DEPT_ID must be changed to FOREIGN KEY.

  Correct Answer: BD

• Question 408:

  Examine this query and its output:

  

  Examine this query with an incomplete WHERE clause:

  

  Which two are true about operators that can be used in the WHERE clause? (Choose two.)

  A. Using <> ANY will display all the product names except the product named Fork.

  B. Using IN will display all the product names.

  C. Using NOT IN or <> ANY will give the same result.

  D. Using <> ANY will display all the product names.

  E. Using NOT IN or <> ANY will give the same result.

  Correct Answer: AE

• Question 409:

  Examine the description of the COUNTRIES table:

  

  Examine the description of the DEPARTMENTS table:

  

  Examine the description of the LOCATIONS table:

  

  Which two queries will return a list of countries with no departments? (Choose two.)

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  Correct Answer: BD

• Question 410:

  User HR has CREATE SESSION, CREATE ANY TABLE and UNLIMITED TABLESPACE privileges. User SCOTT has CREATE SESSION, CREATE TABLE and UNLIMITED TABLESPACE privileges. HR successfully executes this statement:

  

  HR attempts to execute:

  1.

  INSERT INTO scott.products VALUES (1, 'LAPTOP'); SCOTT attempts to execute:

  2.

  SELECT * FROM products;

  3.

  INSERT INTO scott.products VALUES (2, 'HDD');

  4.

  CREATE SYNONYM prod FOR products; Which will execute successfully?

  A. 1, 2 and 3

  B. 2, 3 and 4

  C. 1 only

  D. 2 and 3 only

  Correct Answer: D