Practice 1Z0-071 Exam Online

Question 1

View the exhibit and examine the ORDERS table.

The ORDERS table contains data and all orders have been assigned a customer ID. Which statement

would add a NOT NULL constraint to the CUSTOMER_ID column?

A : ALTER TABLE ordersMODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);

B : ALTER TABLE ordersADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);

C : ALTER TABLE ordersMODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);

D : ALTER TABLE ordersADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;

Answer: C

Question 2

You issued this command: SQL > DROP TABLE employees; Which three statements are true? (Choose three.)

A : Sequences used in the EMPLOYEES table become invalid.

B : If there is an uncommitted transaction in the session, it is committed.

C : All indexes and constraints defined on the table being dropped are also dropped.

D : The space used by the EMPLOYEES table is always reclaimed immediately.

E : The EMPLOYEES table can be recovered using the ROLLBACK command.

F : The EMPLOYEES table may be moved to the recycle bin.

Answer: A,B,C

Question 3

View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.

You executed the following query to display PRODUCT_NAME and the number of times the product

has been ordered:

SELECT p.product_name, i.item_cnt

FROM (SELECT product_id, COUNT (*) item_cnt

FROM order_items

GROUP BY product_id) i RIGHT OUTER JOIN products p

ON i.product_id = p.product_id;

What would happen when the above statement is executed?

A : The statement would execute successfully to produce the required output

B : The statement would not execute because inline views and outer joins cannot be used together.

C : The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.

D : The statement would not execute because the GROUP BY clause cannot be used in the inline.

Answer: A

Question 4

Which is true about the & and && prefixes with substitution variables?

A : & can prefix a substitution variable name only in queries. DML

B : An & prefix to an undefined substitution variable, which is referenced twice in the same query, will prompt for a value twice .

C : The && prefix will not prompt for a value even if the substitution variable is not previously defined in the session.

D : An && prefix to an undefined substitution variable, which is referenced multiple times in multiple queries, will prompt for a value once per query.

E : Both & and && can prefix a substitution variable name in queries and DML statements.

Answer: A,B

Question 5

You execute these commands:

SQL> DEFINE hiredate = '01-APR -2011';

SQL> SELECT employee_id, first_name, salary FROM employees WHERE hire date > &hiredate AND manager_id >&mgr_id;

For which substitution variables will you be prompted?

A : none

B : &hiredate and &mgr_id

C : only &hiredate

D : only &mgr_id

Answer: D

Name:	Oracle Database 12c SQL
Exam Code:	1Z0-071
Certification:	Oracle Database 12c
Vendor:	Oracle
Total Questions:	544
Last Updated:	Apr 25, 2024

Oracle 1Z0-071