Free 1Z0-071 Exam Braindumps

Examine this SELECT statement and view the Exhibit to see its output: (Choose two.)

SELECT constraints_name, constraints_type, search_condition, r_constraints_name, delete_rule, status, FROM user_constraints
WHERE table_name = 'ORDERS';
Which two statements are true about the output?

1. A. The DELETE_RULE column indicates the desired state of related rows in the child table when the corresponding row is deleted from the parent table.
2. B. The R_CONSTRAINT_NAME column contains an alternative name for the constraint.
3. C. In the second column, 'c' indicates a check constraint.
4. D. The STATUS column indicates whether the table is currently in use.

Correct Answer: AC

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. (Choose the best answer.)

You executed this UPDATE statement: UPDATE
( SELECT order_date, order_total, customer_id FROM orders) Set order_date = '22-mar-2007'
WHERE customer_id IN
(SELECT customer_id FROM customers
WHERE cust_last_name = 'Roberts' AND credit_limit = 600); Which statement is true regarding the execution?

1. A. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
2. B. It would not execute because two tables cannot be referenced in a single UPDATE statement.
3. C. It would execute and restrict modifications to the columns specified in the SELECT statement.
4. D. It would not execute because a SELECT statement cannot be used in place of a table name.

Correct Answer: C

Which two statements best describe the benefits of using the WITH clause? (Choose two.)

1. A. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace.
2. B. It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
3. C. It enables sessions to store a query block permanently in memory and use it to create complex queries.
4. D. It enables sessions to store the results of a query permanently.

Correct Answer: AB

Which three statements are true regarding the SQL WHERE and HAVING clauses?

1. A. The HAVING clause conditions can have aggregating functions.
2. B. The HAVING clause conditions can use aliases for the columns.
3. C. The WHERE and HAVING clauses cannot be used together in a SQL statement.
4. D. The WHERE clause is used to exclude rows before grouping data.
5. E. The HAVING clause is used to exclude one or more aggregated results after grouping data.

Correct Answer: ADE

You want to display 5 percent of the rows from the SALES table for products with the lowest AMOUNT_SOLD and also want to include the rows that have the same AMOUNT_SOLD even if this causes the output to exceed 5 percent of the rows.
Which query will provide the required result?

1. A. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS WITH TIES;
2. B. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;
3. C. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS WITH TIES ONLY;
4. D. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS ONLY;

Correct Answer: A