Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 101:

  Evaluate the following two queries:

  

  Which statement is true regarding the above two queries?

  A. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column.

  B. There would be no change in performance.

  C. Performance would degrade in query 2.

  D. Performance would improve in query 2.

  Correct Answer: B

• Question 102:

  View the exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.

  

  You want to update EMPLOYEES table as follows:

  Update only those employees who work in Boston or Seattle (locations 2900 and 2700).

  Set department_id for these employees to the department_id corresponding to London (location_id 2100).

  Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department.

  Set the employees' commission in location_id 2100 to 1.5 times the average commission of their department.

  You issue the following command:

  

  What is outcome?

  A. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement.

  B. It generates an error because a subquery cannot have a join condition in a UPDATE statement.

  C. It executes successfully and gives the desired update

  D. It executes successfully but does not give the desired update

  Correct Answer: D

• Question 103:

  View the Exhibit and examine the structure of CUSTOMERS table.

  Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "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') "NEW CREDIT" FROM customers;

  Correct Answer: A

• Question 104:

  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;

  Correct Answer: C

• Question 105:

  Which statement is true about TRUNCATE and DELETE?

  A. For tables with multiple indexes and triggers DELETE is faster than TRUNCATE.

  B. For large tables TRUNCATE is faster than DELETE.

  C. You can never TRUNCATE a table if foreign key constraints will be violated.

  D. You can never DELETE rows from a table if foreign key constraints will be violated.

  Correct Answer: B

• Question 106:

  Which two statements are true about INTERVAL data types?

  A. INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.

  B. INTERVAL DAY TO SECOND columns support fractions of seconds.

  C. INTERVAL YEAR TO MONTH columns support yearly intervals.

  D. The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value.

  E. INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.

  F. The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column.

  Correct Answer: BC

• Question 107:

  Which three statements are true about Data Manipulation Language (DML)?

  A. UPDATE statements can have different subqueries to specify the values for each updated column.

  B. INSERT INTO...SELECT...FROM statements automatically commit.

  C. DML statements require a primary key be defined on a table.

  D. DELETE statements can remove multiple rows based on multiple conditions.

  E. INSERT statements can insert NULLs explicitly into a column.

  Correct Answer: ADE

• Question 108:

  Which two are true about multitable INSERT statements?

  A. The conditional INSERT FIRST statement always inserts a row into a single table.

  B. The unconditional INSERT ALL statement must have the same number of columns in both the source and target tables.

  C. They can transform a row from a source table into multiple rows in a target table.

  D. The conditional INSERT ALL statement inserts rows into a singe table by aggregating source rows.

  E. They always use subqueries.

  Correct Answer: CE

• Question 109:

  Which statement is true about using functions in WHERE and HAVING?

  A. Using aggregate functions in the WHERE clause requires a subquery.

  B. Using aggregate functions in the HAVING clause requires a subquery.

  C. Using single-row functions in the WHERE clause requires a subquery.

  D. Using single-row functions in the HAVING clause requires a subquery.

  Correct Answer: A

• Question 110:

  Which three statements are true about performing DML operations on a view with no INSTEAD OF triggers defined? (Choose three.)

  A. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.

  B. Delete statements can always be done on a table through a view.

  C. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.

  D. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.

  E. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword,

  F. Insert statements can always be done on a table through a view.

  Correct Answer: CDE