Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 251:

  Examine the description of the EMPLOYEES table:

  

  Examine this query:

  

  Which line produces an error?

  A. Line 7
  B. Line 8
  C. Line 3
  D. Line 5
  C. Line 3

• Question 252:

  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.
  D. The DELETE command will immediately return an error.

• Question 253:

  Which two statements are true regarding single row functions? (Choose two.)

  A. MOD : returns the quotient of a division.
  B. TRUNC : can be used with NUMBER and DATE values.
  C. CONCAT : can be used to combine any number of values.
  D. SYSDATE : returns the database server current date and time.
  E. INSTR : can be used to find only the first occurrence of a character in a string.
  F. TRIM : can be used to remove all the occurrences of a character from a string.
  B. TRUNC : can be used with NUMBER and DATE values.
  D. SYSDATE : returns the database server current date and time.

• Question 254:

  Which two are true about virtual columns? (Choose two.)

  A. They can be referenced In the where clause of an update or debete statement.
  B. They can be referenced in the set clause of an update statement as the name of the column To be updated.
  C. They can be indexed.
  D. They cannot have a data type explicitly specified.
  E. They can be referenced in the column expression of another virtxial column.
  A. They can be referenced In the where clause of an update or debete statement.
  C. They can be indexed.

• Question 255:

  View the Exhibit and examine the description of the ORDERS table.

  

  Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)

  A. WHERE order_date_IN ( TO_DATE('OCT 21 2003','MON DD YYYY'), TO_CHAR('NOV 21 2003','MON DD YYYY') )
  B. WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY')
  C. WHERE TO_CHAR(order_date,'MON DD YYYY') = 'JAN 20 2003'
  D. WHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY')
  C. WHERE TO_CHAR(order_date,'MON DD YYYY') = 'JAN 20 2003'
  D. WHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY')

• Question 256:

  Which two statements are true about conditional INSERT ALL?

  A. Each row returned by the subquery can be inserted into only a single target table.
  B. It cannot have an ELSE clause.
  C. The total number of rows inserted is always equal to the number of rows returned by the subquery
  D. A single WHEN condition can be used for multiple INTO clauses.
  E. Each WHEN condition is tested for each row returned by the subquery.
  D. A single WHEN condition can be used for multiple INTO clauses.
  E. Each WHEN condition is tested for each row returned by the subquery.

• Question 257:

  Examine the structure of the ORDERS table: (Choose the best answer.)

  

  You want to find the total value of all the orders for each year and issue this command:

  SQL> SELECT TO_CHAR(order_date,'rr'), SUM(order_total) FROM orders

  GROUP BY TO_CHAR(order_date, 'yyyy');

  Which statement is true regarding the result?

  A. It executes successfully but does not give the correct output.
  B. It executes successfully and gives the correct output.
  C. It returns an error because the TO_CHAR function is not valid.
  D. It return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.
  D. It return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

• Question 258:

  Examine this query Which executes successfully:

  SELECT job, deptno FROM emp

  UNION ALL

  SELECT job, deptno FROM jobs_ history;

  What will be the result?

  A. It will return rows common to both SELECT statements.
  B. It will return rows from both SELECT statements after eliminating duplicate rows.
  C. It will return rows that are not common to both SELECT statements.
  D. It will return rows from both SELECT statements including duplicate rows.
  D. It will return rows from both SELECT statements including duplicate rows.

• Question 259:

  Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables:

  

  You want to generate a list of all department IDs along with any course IDs that may have been assigned to them. Which SQL statement must you use?

  A. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c. department_id);
  B. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c. department_id);
  C. SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d. department_id);
  D. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d. department_id);
  B. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c. department_id);

• Question 260:

  In your session NLS_DATE_FORMAT is set to DD-MON-RR.

  Which two queries display the year as four digits? (Choose two.)

  A. SELECT TO_DATE(SYSDATE, 'RRRR-MM-DD') FROM DUAL;
  B. SELECT TO_CHAR(SYSDATE, MM/DD/YYYY) FROM DUAL;
  C. SELECT TO_DATE(ADD_MONTHS(SYSDATE, 6), 'dd-non-yyyy') FROM DUAL;
  D. SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 6)) FROM DUAL;
  E. SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'dd-mon-yyyy') FROM DUAL;
  F. SELECT TO_DATE(TO_CHAR(SYSDATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') FROM DUAL;
  B. SELECT TO_CHAR(SYSDATE, MM/DD/YYYY) FROM DUAL;
  E. SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'dd-mon-yyyy') FROM DUAL;