Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 371:

  Which two queries return the string Hello! We're ready? (Choose two.)

  A. SELECT "Hello! We're ready" FROM DUAL;
  B. SELECT 'Hello! We're ready' FROM DUAL;
  C. SELECT q'!Hello! We're ready!' FROM DUAL;
  D. SELECT q'[Hello! We're ready]' FROM DUAL;
  E. SELECT 'Hello! We\'re ready' ESCAPE '\' FROM DUAL;
  C. SELECT q'!Hello! We're ready!' FROM DUAL;
  D. SELECT q'[Hello! We're ready]' FROM DUAL;

• Question 372:

  You need to display the date 11-oct-2007 in words as `Eleventh of October, Two Thousand Seven'.

  Which SQL statement would give the required result?

  A. SELECT TO_CHAR (TO_DATE ('11-oct-2007'), `fmDdthsp "of" Month, Year') FROM DUAL
  B. SELECT TO_CHAR (`11-oct-2007', `fmDdspth "of" Month, Year') FROM DUAL
  C. SELECT TO_CHAR (TO_DATE (`11-oct-2007'), `fmDdspth of month, year') FROM DUAL
  D. SELECT TO_DATE (TO_CHAR ('11-oct-2007'), `fmDdspth "of" Month, Year')) FROM DUAL
  A. SELECT TO_CHAR (TO_DATE ('11-oct-2007'), `fmDdthsp "of" Month, Year') FROM DUAL

• Question 373:

  Examine the description of EMPLOYEES table:

  

  Which three queries return all rows for which SALARY + COMMISSION is greater than 20000? (Choose three.)

  A. SELECT * FROM employees WHERE NVL2(salary + commission, salary + commission, salary) >= 20000;
  B. SELECT * FROM employees WHERE salary + NVL2(commission, commission, 0) >= 20000
  C. SELECT * FROM employees WHERE NVL(salary + commission, 0) >= 20000;
  D. SELECT * FROM employees WHERE salary + NULLIF(commission, 0) >= 20000;
  E. SELECT * FROM employees WHERE COALESCE(salary, commission) >= 20000;
  F. SELECT * FROM employees WHERE salary + NVL(commission, 0) >= 20000;
  A. SELECT * FROM employees WHERE NVL2(salary + commission, salary + commission, salary) >= 20000;
  B. SELECT * FROM employees WHERE salary + NVL2(commission, commission, 0) >= 20000
  F. SELECT * FROM employees WHERE salary + NVL(commission, 0) >= 20000;

• Question 374:

  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? (Choose three.)

  A. CREATE INDEX price_idx ON products (price);
  B. ALTER TABLE products SET UNUSED (expiry_date);
  C. DROP TABLE products;
  D. ALTER TABLE products DROP COLUMN expiry_date;
  E. TRUNCATE TABLE products;
  F. ALTER TABLE products DROP UNUSED COLUMNS;
  A. CREATE INDEX price_idx ON products (price);
  C. DROP TABLE products;
  F. ALTER TABLE products DROP UNUSED COLUMNS;

• Question 375:

  The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER(8,2). Evaluate this SQL statement:

  SELECT TO_CHAR(unit_price, '$9,999') FROM product_information;

  Which two statements are true about the output? (Choose two.)

  A. A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as $1,0236.
  B. A row whose UNIT_PRICE column contains the value 1023.95 will be displayed as $1,024.
  C. A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as $1,023.
  D. A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as #######.
  E. A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,023.
  B. A row whose UNIT_PRICE column contains the value 1023.95 will be displayed as $1,024.
  D. A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as #######.

• Question 376:

  Which three statements are true about Data Manipulation Language (DML)?

  A. UPDATE statements can have different subqueries to specify the values for each updated column.
  B. INSERT INTO...SELECT...FROM statements automatically commit.
  C. DML statements require a primary key be defined on a table.
  D. DELETE statements can remove multiple rows based on multiple conditions.
  E. INSERT statements can insert NULLs explicitly into a column.
  A. UPDATE statements can have different subqueries to specify the values for each updated column.
  D. DELETE statements can remove multiple rows based on multiple conditions.
  E. INSERT statements can insert NULLs explicitly into a column.

• Question 377:

  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.
  B. All existing rows in the ORDERS table are updated.
  C. The subquery is executed for every updated row in the ORDERS table.

• Question 378:

  These are the steps for a correlated subquery, listed in random order:

  1.

  The WHERE clause of the outer query is evaluated.

  2.

  A candidate row is fetched from the table specified in the outer query.

  3.

  This is repeated for the subsequent rows of the table, until all the rows are processed.

  4.

  Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.

  Which is the correct sequence in Which the Oracle server evaluates a correlated subquery?

  A. 2, 1, 4, 3
  B. 4, 1, 2, 3
  C. 4, 2, 1, 3
  D. 2, 4, 1, 3
  D. 2, 4, 1, 3

  ---

  Explanation/Reference:

  References: http://rajanimohanty.blogspot.co.uk/2014/01/correlated-subquery.html

• Question 379:

  In Which three situations does a transaction complete?

  A. when a PL/SQL anonymous block is executed
  B. when a DELETE statement is executed
  C. when a ROLLBACK command is executed
  D. when a data definition language (DDL) statement is executed
  E. when a TRUNCATE statement is executed after the pending transaction
  C. when a ROLLBACK command is executed
  D. when a data definition language (DDL) statement is executed
  E. when a TRUNCATE statement is executed after the pending transaction

  ---

  Explanation/Reference:

  References: https://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm

• Question 380:

  Which two statements are true about a self join?

  A. It can be a left outer join.
  B. It must be an equijoin.
  C. It must be a full outer join.
  D. The join key column must have an index.
  E. It can be an inner join.
  A. It can be a left outer join.
  E. It can be an inner join.