Question 1

View the Exhibit and examine the data in the PROMOTIONS table.

PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.

You need to produce a report that provides the name, cost, and start date of all promos in the POST

category that were launched before January 1, 2000.

Which SQL statement would you use?

Options :

A : SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category = 'post' AND promo_begin_date < '01-01-00';

B : SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00';

C : SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000';

D : SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00';

Answer: D

Question 2

View the exhibit and examine the description of SALES and PROMOTIONS tables.

You want to delete rows from the SALES table, where the PROMO_NAME column in the

PROMOTIONS table has either blowout sale or everyday low price as values.

Which three DELETE statements are valid? (Choose three.)

Options :

A : DELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promo_name = ‘blowout sale’)AND promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = ‘everyday low price’)FROM promotionsWHERE promo_name = ‘everyday low price’);

B : DELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = ‘blowout sale’)OR promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = ‘everyday low price’)

C : DELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = ‘blowout sale’)OR promo_name = ‘everyday low price’);

D : DELETEFROM salesWHERE promo_id IN (SELECT promo_idFROM promotionsWHERE promo_name IN = ‘blowout sale’,‘everyday low price’));

Answer: B,C,D

Question 3

Examine the description of the SALES1 table:

SALES2 is a table with the same description as SALES1.

Some sales data is duplicated in both tables.

You want to display the rows from the SALES1 table which are not present in the SALES2 table.

Which set operator generates the required output?

Options :

A : SUBTRACT

B : INTERSECT

C : UNION ALL

D : UNION

E : MINUS

Answer: A

Question 4

Which two are true about the WITH GRANT OPTION clause?

Options :

A :

The grantee can grant the object privilege to any user in the database, with of without including this option.

B : The grantee must have the GRANT ANY OBJECT PRIVILEGE system prvilege to use this option.

C : It can be used when granting privileges to roles.

D : It can be used for system and object privileges.

E : It cannot be used to pass on privileges to PUBLIC by the grantee.

F : It can be used to pass on privileges to other users by the grantee.

Answer: E,F

Question 5

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

Free 1Z0-071 Mock Exam – Practice Online Confidently

Buying Options: