Oracle 1Z0-071 Quiz 67 Topic 15 Questions 331-335

Question: 1

View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)

q1_1Z0-071

You executed this SQL statement:

SELECT first_name, department_id, salary

FROM employees

ORDER BY department_id, first_name, salary desc;

Which two statements are true regarding the result?

AThe values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.

BThe values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column.

CThe values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.

DThe values in the all columns would be returned in descending order.

EThe values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.

Show Answer

Question: 2

Examine the structure of the SALES table. (Choose two.)

q2_1Z0-071

Examine this statement:

SQL > CREATE TABLE sales1 (prod_id, cust_id, quantity_sold, price)

AS

SELECT product_id, customer_id, quantity_sold, price

FROM sales

WHERE 1 = 2;

Which two statements are true about the SALES1 table?

AIt will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.

BIt will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.

CIt will not be created because of the invalid WHERE clause.

DIt is created with no rows.

EIt has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.

Show Answer

Question: 3

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

q3_1Z0-071

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?

AThe DELETE_RULE column indicates the desired state of related rows in the child table when the corresponding row is deleted from the parent table.

BThe R_CONSTRAINT_NAME column contains an alternative name for the constraint.

CIn the second column, 'c' indicates a check constraint.

DThe STATUS column indicates whether the table is currently in use.

Show Answer

Question: 4

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

q4_1Z0-071

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?

AIt would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.

BIt would not execute because two tables cannot be referenced in a single UPDATE statement.

CIt would execute and restrict modifications to the columns specified in the SELECT statement.

DIt would not execute because a SELECT statement cannot be used in place of a table name.

Show Answer

Question: 5

View the Exhibit and examine the structure of the PRODUCTS table. (Choose the best answer.)

q5_1Z0-071

You must display the category with the maximum number of items.

You issue this query:

SQL > SELECT COUNT(*), prod_category_id

FROM products

GROUP BY prod_category_id

HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM porducts);

What is the result?

AIt generates an error because = is not valid and should be replaced by the IN operator.

BIt executes successfully but does not give the correct output.

CIt executes successfully and gives the correct output.

DIt generate an error because the subquery does not have a GROUP BY clause.

Show Answer

Oracle 1Z0-071 Quiz:67 Topic:15 Questions:331-335