Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 71:

  Which two are true about scalar subquery expressions?

  A. You cannot correlate them with a table in the parent statement
  B. You can use them as a default value for a column.
  C. You must enclose them in parentheses.
  D. They can return at most one row.
  E. They can return two columns.
  C. You must enclose them in parentheses.
  D. They can return at most one row.

• Question 72:

  Which three statements are true regarding subqueries? (Choose three.)

  A. The ORDER BY Clause can be used in a subquery.
  B. A subquery can be used in the FROM clause of a SELECT statement.
  C. If a subquery returns NULL, the main query may still return rows.
  D. A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.
  E. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.
  A. The ORDER BY Clause can be used in a subquery.
  B. A subquery can be used in the FROM clause of a SELECT statement.
  C. If a subquery returns NULL, the main query may still return rows.

• Question 73:

  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 from both select statements after eliminating duplicate rows.
  B. It will return rows common to both select statements.
  C. It will return rows both select statements including duplicate rows.
  D. It will return rows that are not common to both select statements.
  C. It will return rows both select statements including duplicate rows.

• Question 74:

  You are designing the structure of a table in Which two columns have the specifications:

  COMPONENT_ID - must be able to contain a maximum of 12 alphanumeric characters and must uniquely identify the row EXECUTION_DATETIME ?contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and

  is used for calculations and comparisons between components.

  Which two options define the data types that satisfy these requirements most efficiently? (Choose two.)

  A. The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type.
  B. The EXECUTION_DATETIME must be of TIMESTAMP data type.
  C. The EXECUTION_DATETIME must be of DATE data type.
  D. The COMPONENT_ID must be of ROWID data type.
  E. The COMPONENT_ID must be of VARCHAR2 data type.
  F. The COMPONENT_ID column must be of CHAR data type.
  C. The EXECUTION_DATETIME must be of DATE data type.
  F. The COMPONENT_ID column must be of CHAR data type.

• Question 75:

  Which statement falls to execute successfully?

  A. SELECT * FROM employees e JOIN department d WHERE e.department_id=d.department_id AND d.department_id=90;
  B. SELECT * FROM employees e JOIN departments d ON e.department_id=d.department_id WHERE d.department_id=90;
  C. SELECT * FROM employees e JOIN departments d ON e.department_id=d.department_id AND d.department_id=90;
  D. SELECT * FROM employees e JOIN departments d ON d.departments_id=90 WHERE e.department_id=d.department_id;
  D. SELECT * FROM employees e JOIN departments d ON d.departments_id=90 WHERE e.department_id=d.department_id;

• Question 76:

  Which two are true about unused columns?

  A. The DESCRIBE command displays unused columns
  B. A primary key column cannot be set to unused.
  C. A query can return data from unused columns, but no DML is possible on those columns.
  D. Once a column has been set to unused, a new column with the same name can be added to the table.
  E. A foreign key column cannot be set to unused.
  F. Unused columns retain their data until they are dropped
  D. Once a column has been set to unused, a new column with the same name can be added to the table.
  F. Unused columns retain their data until they are dropped

  ---

  Explanation/Reference:

  https://decidhermfosvs.worddress.com/2Q07/11/15/back-to-the-basics-droDDing-unused-columns-in-oracle/

• Question 77:

  Which two are true about rollbacks?

  A. The ROLLBACK statement does not release locks resulting from table updates.
  B. Data Control L anguage (DCL) statements, such as GRANT and REVOKE, can be rolled back.
  C. A transaction interrupted by a system failure is automatically rolled back.
  D. If the ROLLBACK statement is used without TO SAVEPOINT, then all savepoints in the transaction are deleted .
  E. Data consistency is not guaranteed after a rollback.
  C. A transaction interrupted by a system failure is automatically rolled back.
  D. If the ROLLBACK statement is used without TO SAVEPOINT, then all savepoints in the transaction are deleted .

• Question 78:

  View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.

  ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.

  Which DELETE statement would execute successfully?

  

  A. DELETE orders o, order_items i WHERE o.order_id = i.order_id;
  B. DELETE FROM orders WHERE (SELECT order_id FROM order_items);
  C. DELETE orders WHERE order_total < 1000;
  D. DELETE order_id FROM orders WHERE order_total < 1000;
  C. DELETE orders WHERE order_total < 1000;

• Question 79:

  Evaluate the following SQL statement:

  SELECT product_name || 'it's not available for order'

  FROM product_information

  WHERE product_status = 'obsolete';

  You received the following error while executing the above query:

  ERROR

  ORA-01756: quoted string not properly terminated

  What would you do to execute the query successfully?

  A. Remove the single quotation marks enclosing the character literal string in the SELECT clause
  B. Use the escape character to negate the single quotation mark within the literal character string in the SELECT clause
  C. Enclose the character literal string in the SELECT clause within double quotation marks
  D. Use the Oracle (q) operator and delimiter to allow the use of a single quotation mark within the literal character string in the SELECT clause
  D. Use the Oracle (q) operator and delimiter to allow the use of a single quotation mark within the literal character string in the SELECT clause

  ---

  Explanation/Reference:

  References: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm

• Question 80:

  The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type number.

  Which two queries execute successfully? (Choose two.)

  A. SELECT NVL2(cust_credit_limit * .15, 'Not Available') FROM customers;
  B. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers
  C. SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers;
  D. SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
  E. SELECT TO_CHAR(NVL(cust_credit_iimit * .15, 'Not Available')) FROM customers;
  B. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers
  D. SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;