Question 1

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

There is only one customer with the cust_last_name column having value Roberts. 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?

Options :

A : INSERT INTO ordersVALUES(1, ’10-mar-2007’, ‘direct’,(SELECT customer_idFROM customersWHERE cust_last_name=’Roberts’ ANDcredit_limit=600), 1000);

B : INSERT INTO orders (order_id, order_data, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name=’Roberts’ ANDcredit_limit=600), order_total)VALUES(1, ’10-mar2007’, ‘direct’, &&customer_id, 1000);

C : INSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_idAND c.cust_last_name=’Roberts’ AND c.credit_limit=600 )VALUES (1, ’10-mar-2007’, ‘direct’, (SELECT customer_idFROM customersWHERE cust_last_name=’Roberts’ ANDcredit_limit=600), 1000);

D : INSERT INTO orders (order_id, order_data, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name=’Roberts’ ANDcredit_limit=600), order_total)VALUES (1, ’10-mar2007’, ‘direct’, &customer_id, 1000).

Answer: A

Question 2

The BOOKS_TRANSACTIONS table exists in your schema in this database.

You execute this SQL statement when connected to your schema in your database instance.

SQL> SELECT * FROM books_transactions ORDER BY 3;

What is the result?

Options :

A : The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name.

B : All table rows are displayed sorted in ascending order of the values in the third column.

C : The first three rows in the table are displayed in the order that they are stored.

D : Only the three rows with the lowest values in the key column are displayed in the order that they are stored.

Answer: B

Question 3

Which three statements about roles are true?

Options :

A : Roles are assigned to roles using the ALTER ROLE Statement

B : A role is a named group of related privileges that can only be assigned to a user

C : Roles are assigned to users using the ALTER USER statement

D : A single role can be assigned to multiple users.

E : A single user can be assigned multiple roles

F : Privileges are assigned to a role using the ALTER ROLE statement.

G : Privileges are assigned to a role using the GRANT statement.

Answer: A,D

Question 4

The ORDERS table a primary key constrain on the ORDER_ID column.

The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the

primary key of the ORDERS table.

The constraint is defined with ON DELETE CASCADS.

There are rows in the ORDERS table with an ORDER_TOTAL of less than 1000.

Which three DELETE statements execute successfully?

Options :

A : DELETE * FROM orders WHERE order_total < 1000;

B : DELETE FROM orders;

C : DELETE order_id FROM orders WHERE order_total < 1000;

D : DELETE orders WHERE order_total < 1000;

E : DELETE FROM orders WHERE order_total < 1000;

Answer: A,B,D

Question 5

Examine the data in the EMP table:

You execute this query:

SELECT deptno AS "Department", AVG(sal) AS AverageSalary, MAX(sal) AS "Max Salary"

FROM emp

WHERE sal >= 12000

GROUP BY "Department "

ORDER BY AverageSalary;

Why does an error occur?

Options :

A : An alias name must not be used in an ORDER BY clause.

B : An allas name must not contain space characters.

C : An alias name must not be used in a GROUP BY clause.

D : An alias name must always be specified in quotes.

Answer: C

Confidently Practice Online with Free 1Z0-071 Exam Cram

Buying Options: