Question 1

Use HR has CREATE SESSION, CREATE ANY TABLE and UNLIMITED TABLESPACE privileges.

User SCOTT has CREAT SESSION, CREATE TABLE and UNLIMITED TABLESPACE Privileges

HR successfully executes this statement:

Which will execute successfully?

Options :

A : 1 only

B : 2 and 3 only

C : 1,2 and 3

D : 2, 3 and 4

Answer: B

Question 2

Examine this query and its output:

Examine this query with an incomplete was WHERE clause:

Which two are true about operators that can be used In the WHERE clause?

Options :

A : Using not in or <> any will give the same result.

B : Using in will display all the product names

C : Using not in or <> all will give the same result

D : Using <> any will display all the product names except the product named Fork

E : Using <> any will display all the product names.

Answer: C,D

Question 3

Examine the structure of the INVOICE table.

Which two SQL statements would execute successfully? (Choose two.)

Options :

A : SELECT inv_no,NVL2(inv_date,'Pending','Incomplete')FROM invoice;

B : SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available')FROM invoice;

C : SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate)FROM invoice;

D : SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available')FROM invoice;

Answer: A,C

Question 4

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 5

View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)

Examine this SQL statement:

SELECT department_id "DEPT_ID", department_name, 'b'

FROM departments

WHERE departments_id=90

UNION

SELECT department_id, department_name DEPT_NAME, 'a'

FROM departments

WHERE department_id=10

Which two ORDER BY clauses can be used to sort the output? (Choose two.)

Options :

A : ORDER BY DEPT_NAME;

B : ORDER BY DEPT_ID;

C : ORDER BY 'b';

D : ORDER BY 3;

Answer: B,D

Smartly Prepare Exam with Free Online 1Z0-071 Practice Test

Buying Options: