Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 131:

  Examine the description of the EMPLOYEES table:

  

  Which two queries return rows for employees whose manager works in a different department?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  E. Option E
  B. Option B
  E. Option E

• Question 132:

  You execute these commands:

  

  Which two, used independently, can replace so the query returns 1?

  A. ROLLBACK;
  B. ROLLBACK TO SAVEPOINT post_insert;
  C. ROLLBACK TO post_insert;
  D. COMMIT;
  E. COMMIT TO SAVEPOINT post_insert;
  B. ROLLBACK TO SAVEPOINT post_insert;
  C. ROLLBACK TO post_insert;

• Question 133:

  Which statement executes successfully?

  A. SELECT TO_NUMBER(INTERVAL `800' SECOND, `HH24:MM') FROM DUAL;
  B. SELECT TO_CHAR(INTERVAL `800' SECOND, `HH24:MM') FROM DUAL;
  C. SELECT TO_NUMBER(TO_DATE(INTERVAL `800' SECOND)) FROM DUAL;
  D. SELECT TO_DATE(TO_NUMBER(INTERVAL `800' SECOND)) FROM DUAL;
  E. SELECT TO_DATE(INTERVAL `800' SECOND, `HH24:MM') FROM DUAL;
  B. SELECT TO_CHAR(INTERVAL `800' SECOND, `HH24:MM') FROM DUAL;

• Question 134:

  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
  B. CURRENT_ TIMESTAMP

  ---

  Explanation/Reference:

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

• Question 135:

  Examine the description of the BOOKS table:

  

  The table has 100 rows.

  Examine this sequence of statements issued in a new session:

  INSERT INTO books VALUES ('ADV112', 'Adventures of Tom Sawyer', NULL, NULL);

  SAVEPOINT a;

  DELETE FROM books;

  ROLLBACK TO SAVEPOINT a;

  ROLLBACK;

  Which two statements are true? (Choose two.)

  A. The second ROLLBACK command replays the delete.
  B. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row.
  C. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.
  D. The second ROLLBACK command undoes the insert.
  E. The second ROLLBACK command does nothing.
  C. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.
  D. The second ROLLBACK command undoes the insert.

• Question 136:

  Evaluate the following statement.

  

  Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

  A. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.
  B. They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
  C. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.
  D. The insert statement would give an error because the ELSE clause is not present for support in case none of WHEN clauses are true.
  A. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.

  ---

  Explanation/Reference:

  References: http://psoug.org/definition/WHEN.htm

• Question 137:

  Examine the data in the INVOICES table:

  

  Examine the data in the CURRENCIES table:

  

  Which query returns the currencies in CURRENCIES that are not present in INVOICES?

  A. SELECT currency_ code FROM currencies MINUS SELECT currency_ code FROM invoices;
  B. SELECT * FROM currencies WHERE NOT EXISTS ( SELECT NULL FROM invoices WHERE currency_ code = currency_ code);
  C. SELECT currency_ code FROM currencies INTERSECT SELECT currency_ code FROM invoices;
  D. SELECT * FROM currencies MINUS SELECT * FROM invoices;
  A. SELECT currency_ code FROM currencies MINUS SELECT currency_ code FROM invoices;

• Question 138:

  Which is the default column or columns for sorting output from compound queries using SET operators such as INTERSECT in a SQL statement?

  A. The first column in the last SELECT of the compound query
  B. The first NUMBER column in the first SELECT of the compound query
  C. The first VARCHAR2 column in the first SELECT of the compound query
  D. The first column in the first SELECT of the compound query
  E. The first NUMBER or VARCHAR2 column in the last SELECTof the compound query
  A. The first column in the last SELECT of the compound query

• Question 139:

  The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_DATE column of data type DATE NLS_DATE_FORMAT is set to DD-MON-RR.

  Which two are true about data type conversions involving these columns in query expressions?

  A. invoice_date> '01-02-2019': uses implicit conversion
  B. qty_sold ='05549821 ': requires explicit conversion
  C. CONCAT(qty_sold, invoice_date): requires explicit conversion
  D. qty_sold BETWEEN '101' AND '110': uses implicit conversion
  E. invoice_date = '15-march-2019': uses implicit conversion
  D. qty_sold BETWEEN '101' AND '110': uses implicit conversion
  E. invoice_date = '15-march-2019': uses implicit conversion

• Question 140:

  View the Exhibit and examine the structure of the ORDER_ITEMS table.

  

  Examine the following SQL statement:

  SELECT order_id, product_id, unit_price

  FROM order_items

  WHERE unit_price =

  (SELECT MAX(unit_price)

  FROM order_items

  GROUP BY order_id);

  You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per ORDER_ID. What correction should be made in the above SQL statement to achieve this?

  A. Remove the GROUP BY clause from the subquery and place it in the main query
  B. Replace = with the >ANY operator
  C. Replace = with the >ALL operator
  D. Replace = with the IN operator
  D. Replace = with the IN operator