[May 2016] 1z0-047 pdf free download

Download of 1z0-047 download materials and preparation for Oracle certification for customers, Real Success Guaranteed with Updated 1z0-047 pdf dumps vce Materials. 100% PASS Oracle Database SQL Expert exam Today!

2016 May 1z0-047 Study Guide Questions:

Q151. Given below are the SQL statements executed in a user session: 

CREATE TABLE product 

(pcode NUMBER(2), 

pnameVARCHAR2(10)); 

INSERT INTO product VALUES(1, ‘pen’); 

INSERT INTO product VALUES (2,'pencil); 

SAVEPOINT a; 

UPDATE product SET pcode = 10 WHERE pcode = 1; 

SAVEPOINT b; 

DELETE FROM product WHERE pcode = 2; 

COMMIT; 

DELETE FROM product WHERE pcode=10; 

ROLLBACK TO SAVEPOINT a; 

Which statement describes the consequences? 

A. No SQL statementwouldberolledback. 

B. Both the DELETE statements would be rolled back. 

C. Only the second DELETE statementwould be rolledback. 

D. Both theDELETE statementsand the UPDATE statement would berolledback. 

Answer: A


Q152. Which statements are correct regarding indexes? (Choose all that apply.) 

A. When a table is dropped, the corresponding indexes are automatically dropped. 

B. For each DML operation performed, the corresponding indexes are automatically updated. 

C. Indexes should be created on columns that are frequently referenced as part of an expression. 

D. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index. 

Answer: ABD


Q153. Which statement is true regarding external tables? 

A. The default REJECT LIMIT for external tables is UNLIMITED. 

B. The data and metadata for an external table are stored outside the database. 

C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table. 

D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table. 

Answer: D


1z0-047  download

Most recent 1z0-047 study guide:

Q154. View the Exhibit and examine the data in the LOCATIONS table. 

Evaluate the following SOL statement: SELECT street_address FROM locations WHERE REGEXP_INSTR(street_address,'[^[: alpha:]]’) = 1; 

Which statement is true regarding the output of this SOL statement? 


A. It would display all the street addresses that do not have a substring 'alpha1. 

B. It would display all the street addresses where the first character is a special character. 

C. It would display all the street addresses where the first character is a letter of the alphabet. 

D. It would display all the street addresses where the first character is not a letter of the alphabet. 

Answer: D


Q155. Which two statements are true regarding multiple-row subqueries? (Choose two.) 

A. They can containgroupfunctions. 

B. They always contain a subquery within a subquery. 

C. They use the < ALL operator to imply less than the maximum. 

D. They can be used to retrieve multiple rows from a single table only. 

E. Theyshouldnot be used withthe NOTIN operator inthemainquery if NULLislikelytobea part ofthe result of thesubquery. 

Answer: AE


Q156. View the Exhibit and examine the structure of the EMPLOYEES table. 


You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL clause would let you choose the direction to walk through the hierarchy tree? 

A. WHERE 

B. HAVING 

C. GROUP BY 

D. STARTWITH 

E. CONNECT BY PRIOR 

Answer: E


1z0-047  download

Tested oca 1z0-047 dumps free download:

Q157. View the Exhibit and examine the description of the CUSTOMERS table. 


You want to add a constraint on the CUST_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers. 

Which SOL statement would you use to accomplish the task? 

A. ALTER TABLE CUSTOMERS 

ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name1'^A-Z’))NOVALIDATE; 

B. ALTER TABLE CUSTOMERS

 ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'^[0-9]’))NOVALIDATE; 

C. ALTER TABLE CUSTOMERS

 ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:alpha: ]]’))NOVALIDATE; 

D. ALTER TABLE CUSTOMERS 

ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:digit: ]]’))NOVALIDATE ; 

Answer: C


Q158. View the Exhibit and examine the data in the CUST_DET table. 


You executed the following multitable INSERT statement: 

INSERT FIRST 

WHEN credit_limit >= 5000 THEN 

INTO cust_1 VALUES(cust_id, credit_limit, grade, gender) 

WHEN grade = THE 

INTO cust_2 VALUES(cust_id, credit_limit, grade, gender) 

WHEN gender = THE 

INTO cust_3 VALUES(cust_id, credit_limit, grade, gender) 

INTO cust_4 VALUES(cust_id, credit_limit, grade, gender) 

ELSE 

INTO cust_5 VALUES(cust_id, credit_limit, grade, gender) 

SELECT * FROM cust_det; 

The row will be inserted in________. 

A. CUST_1 table only because CREDIT_LIMIT condition is satisfied 

B. CUST_1and CUST_2 tables because CREDIT_LIMIT and GRADE conditions are satisfied 

C. CUST_1,CUST_2 and CUST_5 tables because CREDIT_LIMIT and GRADE conditions are satisfied but GENDER condition is not satisfied 

D. CUST 1, CUST 2 and CUST 4 tables because CREDIT LIMIT and GRADE conditions are satisfied for CUST 1 and CUST 2, and CUST 4 has no condition on it 

Answer: A


Q159. View the Exhibit and examine the structure of the CUSTOMERS table. 


CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERS table. 

CUSTOMERS needs to be updated to reflect the latest information about the customers. 

What is the error in the following MERGE statement? 

MERGE INTO customers c USING customer_vu cv ON (c.customer_id = cv.customer_id) WHEN MATCHED THEN UPDATE SET c.customer_id = cv.customer_id, c.cust_name = cv.cust_name, 

c.cust_email = cv.cust_email, 

c.

 income_level = cv. Income_level 

WHEN NOT MATCHED THEN 

INSERT VALUESfcv.customer_id.cv.cus_name.cv.cus_email.cv.income_level) 

WHERE cv. Income_level >100000; 

A. The CUSTOMER_ID column cannot be updated. 

B. TheINTO clause is misplaced in the command. 

C. The WHERE clause cannot be usedwithINSERT. 

D. CUSTOMER VU cannot beusedasadata source. 

Answer: A


Q160. View the Exhibit and examine the details for the CATEGORIES_TAB table. Evaluate the following incomplete SQL statement: 


SELECT category_name ,category_description FROM categories_tab 

You want to display only the rows that have 'harddisks' as part of the string in the CATEGORY_DESCRIPTION column. 

Which two WHERE clause options can give you the desired result? (Choose two.) 

A. WHEREREGEXPJJKE(category_description, 'hard+.s’); 

B. WHERE REGEXPJJKE(category_description,‘^H|hard+.s’); 

C. WHERE REGEXPJJKE (category_description, '^H|hard+.s$'); 

D. WHEREREGEXPJJKE (category_description, '[^Hlhard+.s]'); 

Answer: AB



see more 1z0-047 dumps