Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 101:

  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');
  C. 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');

• Question 102:

  Examine these statements executed in a single Oracle session:

  

  Which three statements are true? (Choose three.)

  A. There is no row containing pencil.
  B. The code for pen is 10.
  C. There is no row containing fountain pen.
  D. The code for pen is 1.
  E. There is no row containing pen.
  F. The code for fountain pen is 3.
  A. There is no row containing pencil.
  B. The code for pen is 10.
  F. The code for fountain pen is 3.

• Question 103:

  Which two statements are true regarding indexes? (Choose two.)

  A. An update to a table can result in updates to any or all of the table's indexes.
  B. An update to a table can result in no updates to any of the table's indexes.
  C. A UNIQUE index can be altered to be non-unique.
  D. When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped.
  E. A table belonging to one user cannot have an index that belongs to a different user.
  A. An update to a table can result in updates to any or all of the table's indexes.
  B. An update to a table can result in no updates to any of the table's indexes.

• Question 104:

  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';
  D. PROMPT Enter table name andandx SELECT employee_id FROM andx WHERE last_name = `King';

• Question 105:

  Examine the command:

  

  What does ON DELETE CASCADE imply?

  A. When the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped.
  B. When the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted but the table structure is retained.
  C. When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the BOOKS table are also deleted.
  D. When a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in the BOOKS_TRANSACTIONS.BOOK_ID column.
  C. When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the BOOKS table are also deleted.

• Question 106:

  Examine the data in the EMP table:

  

  You execute this query:

  

  Why does an error occur?

  A. An alias name must not be used in an ORDER BY clause.
  B. An alias name must not contain space characters.
  C. An alias name must not be used in a GROUP BY clause.
  D. An alias name must always be specified in quotes.
  C. An alias name must not be used in a GROUP BY clause.

• Question 107:

  Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)

  A. You can use column alias in the GROUP BY clause.
  B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
  C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
  D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
  E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY cause.
  D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
  E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY cause.

• Question 108:

  View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables.

  You want to display PROD IDS whose promotion cost is less than the highest cost PROD ID in a pro motion time interval.

  Examine this SQL statement:

  

  Exhibit 1.

  

  Exhibit 2.

  

  What will be the result?

  A. It executes successfully but does not give the required result.
  B. It gives an error because the ALL keyword is not valid.
  C. It gives an error because the GROUP BY clause is not valid
  D. It executes successfully and gives the required result.
  A. It executes successfully but does not give the required result.

• Question 109:

  Evaluate the following SQL statement:

  

  Identify three ORDER BY clauses, any one of which will complete the query successfully. (Choose three.)

  A. ORDER BY CUST_NO
  B. ORDER BY 2, cust_id
  C. ORDER BY 2, 1
  D. ORDER BY "Last Name"
  E. ORDER BY "CUST_NO"
  B. ORDER BY 2, cust_id
  C. ORDER BY 2, 1
  D. ORDER BY "Last Name"

• Question 110:

  Examine these two queries and their output SELECT deptno, dnair.e FROM dept;

  

  SELECT emetine, job, deptno FROM emp ORDER 3Y deptno:

  

  Now examine this query:

  

  How many rows will be displayed?

  A. 64
  B. 6
  C. 3
  D. 12
  B. 6