Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 261:

  Which two are true about external tables that use the ORACLE_DATAPUMP access driver? (Choose two.)

  A. Creating an external table creates a directory object.
  B. When creating an external table, data can be selected only from a table whose rows are stored in database blocks.
  C. When creating an external table, data can be selected from another external table or from a table whose rows are stored in database blocks.
  D. Creating an external table creates a dump file that can be used by an external table in the same or a different database.
  E. Creating an external table creates a dump file that can be used only by an external table in the same database.
  A. Creating an external table creates a directory object.
  D. Creating an external table creates a dump file that can be used by an external table in the same or a different database.

• Question 262:

  Examine thee statements Which execute successfully:

  

  Which two are true? (Choose two.)

  A. User FIN_CLERK can grant SELECT on SCOTT.EMP to user FIN_MANAGER.
  B. Dropping user FINANCE will automatically revoke SELECT on SCOTT.EMP from user FIN_CLERK.
  C. User FINANCE can grant CREATE SESSION to user FIN_MANAGER.
  D. Revoking SELECT on SCOTT.EMP from user FINANCE will also revoke the privilege from user FIN_CLERK.
  E. User FINANCE is unable to grant all on SCOTT.EMP to FIN_MANAGER.
  D. Revoking SELECT on SCOTT.EMP from user FINANCE will also revoke the privilege from user FIN_CLERK.
  E. User FINANCE is unable to grant all on SCOTT.EMP to FIN_MANAGER.

• Question 263:

  View the Exhibit and examine the structure of the CUSTOMERS table.

  

  Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of 15% for all customers.

  Customers with no credit limit should have "Not Available" displayed.

  Which SQL statement would produce the required result?

  A. SELECT NVL(TO_CHAR(cust_credit_limit*.15), `Not Available') "NEW CREDIT" FROM customers;
  B. SELECT TO_CHAR(NVL(cust_credit_limit*.15, `Not Available')) "NEW CREDIT" FROM customers;
  C. SELECT NVL(cust_credit_limit*.15, `Not Available') "NEW CREDIT" FROM customers;
  D. SELECT NVL(cust_credit_limit, `Not Available')*.15 "NEW CREDIT" FROM customers;
  A. SELECT NVL(TO_CHAR(cust_credit_limit*.15), `Not Available') "NEW CREDIT" FROM customers;

• Question 264:

  Which two statements are true about the results of using the intersect operator in compound queries?

  A. intersect ignores nulls.
  B. Reversing the order of the intersected tables can sometimes affect the output.
  C. Column names in each select in the compound query can be different.
  D. intersect returns rows common to both sides of the compound query.
  E. The number of columns in each select in the compound query can be different.
  C. Column names in each select in the compound query can be different.
  D. intersect returns rows common to both sides of the compound query.

• Question 265:

  Examine these statements and results:

  

  How many rows are retrieved by the last query?

  A. 28
  C. 14
  D. 42
  C. 14

• Question 266:

  Examine the data in the ORDERS table:

  

  Examine the data in the INVOICES table:

  

  Examine this query:

  SELECT order_ id, order_ date FROM orders

  INTERSECT

  SELECT order_ 1d, order_ date FROM invoices;

  Which two rows will it return?

  A. 3
  B. 2
  C. 1
  D. 5 01-MAR-2019
  E. 4 01-FEB-2019
  F. 3 01-JAN-2019
  C. 1
  E. 4 01-FEB-2019

• Question 267:

  Which three statements are true regarding group functions? (Choose three.)

  A. They can be used on columns or expressions.
  B. They can be passed as an argument to another group function.
  C. They can be used only with a SQL statement that has the GROUP BY clause.
  D. They can be used on only one column in the SELECT clause of a SQL statement.
  E. They can be used along with the single-row function in the SELECT clause of a SQL statement.
  A. They can be used on columns or expressions.
  B. They can be passed as an argument to another group function.
  E. They can be used along with the single-row function in the SELECT clause of a SQL statement.

  ---

  Explanation/Reference:

  References: https://www.safaribooksonline.com/library/view/mastering-oracle-sql/0596006322/ch04.html

• Question 268:

  Which two statements are true regarding the USING and ON clauses in table joins?

  A. Both USING and ON clauses can be used for equijoins and nonequijoins.
  B. A maximum of one pair of columns can be joined between two tables using the ON clause.
  C. The ON clause can be used to join tables on columns that have different names but compatible data types.
  D. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.
  C. The ON clause can be used to join tables on columns that have different names but compatible data types.
  D. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.

• Question 269:

  View the exhibit and examine the structure of ORDERS and CUSTOMERS tables.

  

  Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600? Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.

  A. INSERT INTO (SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' AND c.credit_limit=600) VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
  B. INSERT INTO orders (order_id, order_date, order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), order_total) VALUES (1,'10-mar-2007', 'direct', andcustomer_id, 1000);
  C. INSERT INTO orders VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
  D. INSERT INTO orders (order_id, order_date, order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), order_total) VALUES (1,'10-mar-2007', 'direct', andandcustomer_id, 1000);
  C. INSERT INTO orders VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);

• Question 270:

  Which three statements are true about Oracle synonyms? (Choose three.)

  A. A synonym cannot be created for a PL/SQL package.
  B. A synonym can be available to all users.
  C. A SEQUENCE can have a synonym.
  D. Any user can drop a PUBLIC synonym.
  E. A synonym created by one user can refer to an object belonging to another user.
  B. A synonym can be available to all users.
  C. A SEQUENCE can have a synonym.
  E. A synonym created by one user can refer to an object belonging to another user.