Online 1Z0-071 Practice Test

Question 1

Which two are true about the MERGE statement?

Options :

A : The WHEN NOT MATCHED clause can be used to specify the deletions to be performed.

B : The WHEN NOT MATCHED clause can be used to specify the inserts to be performed.

C : The WHEN MATCHED clause can be used to specify the inserts to be performed.

D : The WHEN NOT MATCHED clause can be used to specify the updates to be performed.

E : The WHEN MATCHED clause can be used to specify the updates to be performed.

Answer: B,E

Question 2

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 3

Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?

Options :

A :

SELECT deptno,LISTAGG(ename, ' , ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno;

B :

SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno ORDER BY ename;

C :

SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (GROUP BY deptno) AS employee_list FROM emp ORDER BY ename;

D :

SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;

Answer: D

Question 4

View the exhibit and examine the structure of the PROMOTIONS table.

You have to generate a report that displays the promo name and start date for all promos that

started after the last promo in the ‘INTERNET’ category.

Which query would give you the required output?

Options :

A : SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date)FROM promotions) ANDpromo_category= ‘INTERNET’;

B : SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE promo_category= ‘INTERNET’);

C : SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = ‘INTERNET’);

D : SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category= ‘INTERNET’);

Answer: C

Question 5

Examine this query which executes successfully;

Select job,deptno from emp

Union all

Select job,deptno from jobs_history;

What will be the result?

Options :

A : It will return rows from both select statements after eliminating duplicate rows.

B :

It will return rows common to both select statements.

C :

It will return rows both select statements including duplicate rows.

D :

It will return rows that are not common to both select statements.

Answer: C

EXPERTY DESIGNED 1Z0-071 PRACTICE TEST