Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 361:

  Examine this Statement Which returns the name of each employee and their manager,

  

  You want to extend the query to include employees with no manager.

  What must you add before JOIN to do this?

  A. CROSS

  B. FULL OUTER

  C. LEFT OUTER

  D. RIGHT OUTER

  Correct Answer: C

• Question 362:

  Which three actions can you perform on an existing table containing date?

  A. Add a new column as the table's first column.

  B. Define a default value that is automatically inserted into a column containing nulls.

  C. Add a new NOT NULL Column with a DEFAULT value.

  D. Change a DATE Column containing data to a NUMBER data type.

  E. Increase the width of a numeric column.

  F. Change the default value of a column.

  Correct Answer: CEF

• Question 363:

  Which three queries execute successfully?

  A. SELECT 1 - SYSDATE - DATE '2019-01-01' FROM DUAL;

  B. SELECT SYSDATE - DATE '2019-01-01' - 1 FROM DUAL;

  C. SELECT SYSDATE / DATE '2019-01-01' - 1 FROM DUAL;

  D. SELECT SYSDATE - 1 - DATE '2019-01-01' FROM DUAL;

  E. SELECT (SYSDATE - DATE '2019-01-01') / 1 FROM DUAL;

  F. SELECT 1 / SYSDATE - DATE '2019-01-01' FROM DUAL;

  Correct Answer: BDE

• Question 364:

  Which two statements are true about the order by clause when used with a sql statement containing a set operator such as union?

  A. column positions must be used in the order by clause.

  B. The first column in the first select of the compound query with the union operator is used by default to sort output in the absence of an order by clause.

  C. Each select statement in the compound query must have its own order by clause.

  D. only column names from the first select statement in the compound query are recognized.

  E. Each select statement in the compound query can have its own order by clause.

  Correct Answer: BD

• Question 365:

  The STORES table has a column START_ DATE of data type DATE, containing the date the row was inserted.

  You only want to display details of rows where START_ DATE is within the last 25 months.

  Which WHERE clause can be used?

  A. WHERE MONTHS_ BETWEEN (SYSDATE, start_ date) <= 25

  B. WHERE MONTHS_ BETWEEN (start_ date, SYSDATE) <= 25

  C. WHERE TO_ NUMBER (start_ date - SYSDATE) <= 25

  D. WHERE ADD_ MONTHS (start_ date, 25) <= SYSDATE

  Correct Answer: A

• Question 366:

  Which two statements are true about Oracle databases and SQL? (Choose two.)

  A. Updates performed by a database user can be rolled back by another user by using the ROLLBACK command.

  B. The database guarantees read consistency at select level on user-created tablers.

  C. When you execute an UPDATE statement, the database instance locks each updated row.

  D. A query can access only tables within the same schema.

  E. A user can be the owner of multiple schemas In the same database.

  Correct Answer: BC

• Question 367:

  Examine this query:

  

  Which two methods should yours to prevent prompting for hire date value when this queries executed?

  A. Execute the SET VERIFY ON command before executing the query.

  B. Execute the SET VERIFY OFF command before executing the query.

  C. Store the query in a script and pass the substitution value to the script when executing it.

  D. Replace 's1' with and1'in the query:

  E. Use the UNDEFINE command before executing the query.

  F. Use the DEFINE command before executing the query

  Correct Answer: CF

• Question 368:

  Examine the description of the BOOKS_TRANSACTIONS table:

  

  Examine this partial SQL statement:

  SELECT * FROM books_transactions

  Which two WHERE conditions give the same result?

  A. WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND member_id = 'A101' OR member_id = 'A102');

  B. WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND (member_id = 'A101' OR member_id = 'A102'));

  C. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN ('A101', 'A102');

  D. WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' OR member_id IN ('A101', 'A102');

  E. WHERE (borrowed_date = SYSDATE AND transaction_type = 'RM') OR member_id IN ('A101', 'A102');

  Correct Answer: CE

• Question 369:

  Examine this statement:

  

  What is returned upon execution?

  A. 2 rows

  B. 0 rows

  C. An error

  D. 1 ROW

  Correct Answer: D

• Question 370:

  Examine the description of the CUSTOMERS table CUSTNO is the PRIMARY KEY.

  

  You must derermine if any customers'derails have entered more than once using a different costno,by listing duplicate name

  Which two methode can you use to get the requlred resuit?

  A. RIGHT OUTER JOIN with seif join

  B. FULL OUTER JOIN with seif join

  C. SUBQUERY

  D. seif join

  E. LEFT OUTER JOIN with seif join

  Correct Answer: CD