Exam Code: 1z0-047 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database SQL Expert
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1z0-047 Exam.

2016 Jun 1z0-047 Study Guide Questions:

Q21. View the Exhibit and examine the description of the EMPLOYEES table. 

You want to display the EMPLOYE_ID, FIRST_NAME, and DEPARTMEN_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYE_ID 104. To accomplish the task, you execute the following SQL statement: 

SELECT employee_id, first_name, department_id 

FROM employees 

WHERE (manager_id, department_id) =(SELECT department_id, manager_id FROM employees WHERE employee_id = 104) 

AND employee_id <> 104; 

When you execute the statement it does not produce the desired output. What is the reason for this? 

A. The WHERE clause condition in the main query is using the = comparison operator, instead of EXISTS. 

B. TheWHERE clause condition in themainquery is usingthe= comparison operator,insteadof theINoperator. 

C. The WHERE clause condition in themainquery is using the=comparison operator,insteadof the =ANYoperator. 

D. The columns in the WHERE clause condition of the main query andthecolumns selected inthesubquery should be in the same order. 

Answer: D


Q22. View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables. 


Which SET operator should be used in the blank space in the following SQL statement to display the cities that have departments located in them? 

SELECT location_id, city 

FROM locations 

SELECT location_id, city 

FROM locations JOIN departments 

USING(location_id); 

A. UNION 

B. MINUS 

C. INTERSECT 

D. UNIONALL 

Answer: C


Q23. 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. WHEREREGEXP_LIKE(category_description, 'hard+.s’); 

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

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

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

Answer: AB


certifyforall.com

Improve 1z0-047 dumps free download:

Q24. View the Exhibit and examine the details of the PRODUCT_INFORMATION table. 


You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement: 

SELECT product_name, list_price 

FROM product_information 

WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; 

Which statement is true regarding the execution of the query? 

A. Itwould executebut theoutput would return no rows. 

B. It would execute and the outputwould displaythedesired result. 

C. It wouldnotexecute because the entireWHEREclause conditionisnot enclosedwithinthe parentheses. 

D. Itwould not execute becausethesame column has been used in both sidesoftheANDlogical operatortoform the condition. 

Answer: A


Q25. View the Exhibit and examine the structure of ORD and ORD_ITEMS tables. 


In the ORD table, the PRIMARY KEY is ORD_NO and in the ORD_ITEMS tables the composite PRIMARY KEY is (ORD_NO, ITEM_NO). 

Which two CREATE INDEX statements are valid? (Choose two.) 


A. CREATE INDEX ord_idx 

ON ord(ord_no); 


B. CREATE INDEX ord_idx 

ON ord_items(ord_no); 


C. CREATE INDEX ord_idx 

ON ord_items(item_no); 


D. CREATEINDEX ord_idx 

ON ord,ord_items(ord_no, ord_date,qty); 


Answer: BC


Q26. Given below is a list of functions and their purpose in random order. 

Function Purpose 1)NVL a) Used for evaluating NOT NULL and NULL values 2)NULLIF b) Used to return the first non- null values in a list of expressions 3)COALESCE c) Used 

to compare two expressions. If both are same, it returns NULL; otherwise, it returns only the first expression. 

4)NVL2 d) Used to convert NULL values to actual values 

Identify the correct combination of functions and their usage. 

A. 1-a,2-c,3-b,4-d 

B. 1-d,2-c,3-b,4-a 

C. 1-b,2-c,3-d,4-a 

D. 1-d,2-b,3-c,4-a 

Answer: B


1z0-047  torrent

Exact 1z0-047 dumps pdf:

Q27. Which two statements are true about the GROUPING function? (Choose two.) 

A. Itis used to find the groups forming the subtotal in a row. 

B. It is used to identify the NULL value in the aggregate functions. 

C. It is used to form the group sets involved in generating the totals and subtotals. 

D. It can only be used with ROLLUP and CUBE operators specified in the GROUP BY clause. 

Answer: AD


Q28. Which statement is true regarding synonyms? 

A. Synonyms can be created for tables but not views. 

B. Synonyms are used to reference only those tables that are owned by another user. 

C. A public synonym and a private synonym can exist with the same name for the same table. 

D. The DROP SYNONYM statement removes the synonym, and the status of the table on which the synonym has been created becomes invalid. 

Answer: C


Q29. Evaluate the following SELECT statement and view the Exhibit to examine its output: 

SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status FROM user_constraints WHERE table_name = ORDERS 

Which two statements are true about the output? (Choose two.) 


A. Inthe secondcolumn, indicates a check constraint. 

B. TheSTATUS columnindicateswhether the tableiscurrently in use. 

C. The R_CONSTRAINT_NAME column givesthealternative name for the constraint. 

D. The column DELETE_RULE decides the state oftherelated rows inthechild tablewhenthe corresponding row is deleted from the parent table. 

Answer: AD


Q30. View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table. 

Evaluate the following SQL statement: 

ALTER TABLE emp DROP COLUMN first_name; 

Which two statements are true regarding the above command? (Choose two.) 


A. The FIRST_NAME column wouldbedropped provided it does notcontainany data. 

B. The FIRST_NAME column would bedropped providedat least one or more columnsremain inthe table. 

C. The FIRST_NAME column can be rolledback providedthe SET UNUSED option is added to the above SQL statement. 

D. The FIRST_NAME column can be dropped evenif itis part ofacomposite PRIMARY KEY provided the CASCADE option is used. 

Answer: BD