1z0-047 | Avant-garde Oracle 1z0-047 exam


Q151. Evaluate the following SQL statement: 

SELECT product_name || 'it's not available for order' 

FROM product_information 

WHERE product_status = 'obsolete' 

You received the following error while executing the above query: 

ERROR: 

ORA-01756: quoted string not properly terminated 

What would you do to execute the query successfully? 

A. Enclose the character literal string intheSELECT clause within thedoublequotation marks. 

B. Do not enclosethecharacter literal string in the SELECT clausewithinthesingle quotation marks. 

C. Use Quote (q) operator anddelimiterto allowtheuse of single quotation mark in the literal character string. 

D. Use escape charactertonegatethesingle quotationmarkinside theliteralcharacter string in the SELECT clause. 

Answer: C

Q152. Which statement is true regarding Flashback Version Query? 

A. It returns versions of rows only within a transaction. 

B. It can be used in subqueries contained only in a SELECT statement. 

C. It will return an error if the undo retention time is less than the lower bound time or SCN specified. 

D. It retrieves all versions including the deleted as well as subsequently reinserted versions of the rows. 

Answer: D

Q153. Which CREATE TABLE statement is valid? 

A. CREATE TABLE ord_details 

(ord_no NUMBER(2) PRIMARY KEY, 

item_no NUMBER(3)PRIMARY KEY, 

ord_date date NOT NULL); 

B. CREATE TABLE ord_details 

(ord_no NUMBER(2) UNIQUE, NOT NULL, 

item_no NUMBER(3), 

ord_date date DEFAULT SYSDATE NOT NULL); 

C. CREATE TABLE ord_details 

(ord_no NUMBER(2) , 

item_no NUMBER(3), 

ord_date date DEFAULT NOT NULL, 

CONSTRAINT ord_uq UNIQUE (ord_no), 

CONSTRAINT ord_pk PRIMARY KEY (ord_no)); 

D. CREATE TABLE ord_details 

(ord_no NUMBER(2), 

item_no NUMBER(3), 

ord_date date DEFAULT SYSDATE NOT NULL, 

CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no)); 

Answer: D

Q154. Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.) 

A. The outer query stops evaluating the result set of the inner query when the first value is found. 

B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query. 

C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query. 

D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed. 

Answer: AC

Q155. View the Exhibit and examine the structure of the MARKS_DETAILS and MARKStables. 

Which is the best method to load data from the MARKS DETAILStable to the MARKStable? 

A. Pivoting INSERT 

B. Unconditional INSERT 

C. Conditional ALL INSERT 

D. Conditional FIRST INSERT 

Answer: A

Q156. View the Exhibit and examine the description of the PRODUCT_INFORMATION table. 

You want to display the expiration date of the warranty for a product. Which SQL statement would you execute? 

A. SELECT product_id, SYSDATE + warranty_period FROM product_information; 

B. SELECT product_jd, TO_YMINTERVAL(warranty_period) FROM product_information; 

C. SELECT product_id, TO_YMINTERVAL(SYSDATE) + warranty_period FROM product_information; 

D. SELECT product_jd, TO_YMINTERVAL(SYSDATE + warranty_period) FROM product_information; 

Answer: A