Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 1:

  Examine the following query: SQL> SELECT prod_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS ONLY;

  What is the output of this query?

  A. It displays 5 percent of the products with the highest amount sold.
  B. It displays the first 5 percent of the rows from the SALES table.
  C. It displays 5 percent of the products with the lowest amount sold.
  D. It results in an error because the ORDER BY clause should be the last clause.
  C. It displays 5 percent of the products with the lowest amount sold.

  ---

  Explanation/Reference:

  References: https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1

• Question 2:

  In your session, the NLS._DAE_FORMAT is DD- MM- YYYY.There are 86400 seconds in a day.Examine this result:

  1.

  DATE

  2.

  02-JAN-2020

  Which statement returns this?

  A. SELECT TO_ CHAR(TO_ DATE(`29-10-2019') +INTERVAL `2'; MONTH + INTERVAL `5'; DAY INTERVAL `86410' SECOND, ` DD-MON-YYYY') AS "date" FROM DUAL;
  B. SELECT TO_ CHAR(TO_ DATE(`29-10-2019') + INTERVAL `3' MONTH + INTERVAL `7' DAY INTERVAL `360' SECOND, ` DD-MON-YYYY') AS "date" FROM DUAL;
  C. SELECT To CHAR(TO _DATE(`29-10-2019') + INTERVAL `2' NONTH + INTERVAL `5' DAY INEERVAL `120' SECOND, ` DD-MON-YYY) AS "date" FROM DUAL;
  D. SELECT-TO_CHAR(TO _DATE(`29-10-2019'+ INTERVAL `2' MONTH+INTERVAL `6' DAYINTERVAL `120' SECOND, `DD-MON-YY') AS "daTe" FROM DUAL;
  E. SELECT-TO_CHAR(TO _DATE(`29-10-2019'+ INTERVAL `2' MONTH+INTERVAL `4' DAYINTERVAL `120' SECOND, `DD-MON-YY') AS "daTe" FROM DUAL;
  C. SELECT To CHAR(TO _DATE(`29-10-2019') + INTERVAL `2' NONTH + INTERVAL `5' DAY INEERVAL `120' SECOND, ` DD-MON-YYY) AS "date" FROM DUAL;

• Question 3:

  Examine this partial statement: SELECT ename, sal,comm FROM emp Now examine this output:

  

  WHICH ORDER BY clause will generate the displayed output?

  A. ORDER BY NVL(enam,0) DESC, ename
  B. ORDER BY NVL(comm,0) ASC NULLS FIRST, ename
  C. ORDER BY NVL(comm,0) ASC NULLS LAST, ename
  D. ORDER BY comm DESC NULLS LAST, ename
  D. ORDER BY comm DESC NULLS LAST, ename

• Question 4:

  Which statement is true regarding the INTERSECT operator?

  A. The names of columns in all SELECT statements must be identical.
  B. It ignores NULL values.
  C. Reversing the order of the intersected tables alters the result.
  D. The number of columns and data types must be identical for all SELECT statements in the query.
  D. The number of columns and data types must be identical for all SELECT statements in the query.

  ---

  Explanation/Reference:

  INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates.

  The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.

• Question 5:

  Which three actions can you perform by using the ORACLE_DATAPUMP access driver? (Choose three.)

  A. Create a directory object for a flat file.
  B. Create a directory object for an external table.
  C. Read data from an external table and load it into a table in the database.
  D. Execute DML statements on an external table.
  E. Read data from a table in the database and insert it into an external table.
  F. Query data from an external table.
  C. Read data from an external table and load it into a table in the database.
  E. Read data from a table in the database and insert it into an external table.
  F. Query data from an external table.

• Question 6:

  Examine this list of requirements for a sequence:

  1.

  Name:EMP_SEQ

  2.

  First value returned:1

  3.

  Duplicates are never permitted.

  4.

  Provide values to be inserted into the EMPLOYEES.EMPLOYEE_ID COLUMN.

  5.

  Reduce the chances of gaps in the values.

  Which two statements will satisfy these requirements?

  A. CREATE SEQUENCE emp_seq START WITH 1 INCRENENT BY 1 NOCACHE;
  B. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CYCLE;
  C. CREATE SEQUENCE emp_seq NOCACHE;
  D. CREATE SEQUENCE emp_seq START WITH 1 CACHE;
  E. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE;
  F. CREATE SEQUENCE emp_seq;
  A. CREATE SEQUENCE emp_seq START WITH 1 INCRENENT BY 1 NOCACHE;
  C. CREATE SEQUENCE emp_seq NOCACHE;

• Question 7:

  Which three statements are true about external tables? (Choose three.)

  A. They can be temporary tables.
  B. DML statements can modify them.
  C. They can be used in queries containing joins.
  D. They can be used in queries containing sorts.
  E. They can be indexed.
  F. Their metadata is stored in the database.
  C. They can be used in queries containing joins.
  D. They can be used in queries containing sorts.
  F. Their metadata is stored in the database.

• Question 8:

  Examine the description of the CUSTOMERS table:

  

  Which two SELECT statements will return these results: (Choose two.)

  CUSTOMER_ NAME

  Mandy Mary

  A. SELECT customer_name FROM customers WHERE UPPER(customer_name) LIKE `MA*';
  B. SELECT customer_name FROM customers WHERE customer_name = `*Ma*';
  C. SELECT customer_name FROM customers WHERE customer_name LIKE `Ma*';
  D. SELECT customer_name FROM customers WHERE UPPER(customer_name) LIKE `MA%';
  E. SELECT customer_name FROM customers WHERE customer_name LIKE `%a%';
  F. SELECT customer_name FROM customers WHERE customer_name LIKE `Ma%';
  G. SELECT customer_name FROM customers WHERE customer_name LIKE `*Ma*';
  E. SELECT customer_name FROM customers WHERE customer_name LIKE `%a%';
  F. SELECT customer_name FROM customers WHERE customer_name LIKE `Ma%';

• Question 9:

  Which three statements are true regarding the data types? (Choose three.)

  A. The minimum column width that can be specified for a VARCHAR2 data type column is one.
  B. Only one LONG column can be used per table.
  C. A TIMESTAMP data type column stores only time values with fractional seconds.
  D. The BLOB data type column is used to store binary data in an operating system file.
  E. The value for a CHAR data type column is blank-padded to the maximum defined column width.
  A. The minimum column width that can be specified for a VARCHAR2 data type column is one.
  B. Only one LONG column can be used per table.
  E. The value for a CHAR data type column is blank-padded to the maximum defined column width.

• Question 10:

  Which two join conditions in a FROM clause are non-equijoins? (Choose two.)

  A. table1 JOIN table2 USING (column1, column2)
  B. table1 JOIN table2 ON (table1.column BETWEEN table2.column1 AND table2.column2)
  C. table1 NATURAL JOIN table2
  D. table1 JOIN table2 ON (table1.column >= table2.column)
  E. table1 JOIN table2 ON (table1.column = table2.column) WHERE table2.column LIKE `A%'
  B. table1 JOIN table2 ON (table1.column BETWEEN table2.column1 AND table2.column2)
  D. table1 JOIN table2 ON (table1.column >= table2.column)