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:

Q101. View the Exhibit and examine the structure of the EMPLOYEES and DEPARTMENTS tables. 


Which SET operator would you use in the blank space in the following SQL statement to list the departments where all the employees have managers? 

SELECT department_id FROM departments 

SELECT department_id FROM employees WHERE manager_id IS NULL; 

A. UNION 

B. MINUS 

C. INTERSECT 

D. UNION ALL 

Answer: B


Q102. Which statement is true regarding the SESSION_PRIVS dictionary view? 

A. It contains the current object privileges available in the user session. 

B. It contains the current system privileges available in the user session. 

C. It contains the object privileges granted to other users by the current user session. 

D. It contains the system privileges granted to other users by the current user session. 

Answer: B


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


Evaluate the following SQL statement: 

SELECT employee_id, last_name, job_id, manager_id 

FROM employees 

START WITH employee_id = 101 

CONNECT BY PRIOR employee_id=manager_id; 

Which statement is true regarding the output for this command? 

A. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by his or her peers. 

B. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by the employee to whom he or she reports. 

C. Itwouldreturn a hierarchical outputstarting withthe employeewhose EMPLOYEE_IDis101, followed by employeesbelowhim orherin thehierarchy. 

D. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is101, followed by employees up to one level below him or her in the hierarchy. 

Answer: C


certifyforall.com

Renew 1z0-047 oracle database sql expert pdf:

Q104. Evaluate the following SQL statements that are issued in the given order: 

CREATE TABLE emp 

(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, 

enameVARCHAR2(15), 

salary NUMBER(8,2), 

mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp); 


ALTER TABLE emp 

DISABLE CONSTRAINT emp_emp_no_pk CASCADE; 


ALTER TABLE emp 

ENABLE CONSTRAINT emp_emp_no_pk; 


What would be the status of the foreign key EMP_MGR_FK? 

A. It would be automatically enabled and deferred. 

B. It would be automatically enabled and immediate. 

C. It would remain disabled and has to be enabled manually using the ALTER TABLE command. 

D. It would remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it. 

Answer: C


Q105. Evaluate the following CREATE TABLE command: 

CREATE TABLE order_item (order_id NUMBER(3), 

Item_id NUMBER(2), 

qty NUMBER(4), 

CONSTRAINT ord_itm_id_pk 

PRIMARY KEY (order_id jtem_id) 

USING INDEX 

(CREATE INDEX ord_itm_idx 

ON order_item(order_id item_id))); 


Which statement is true regarding the above SOL statement? 

A. Itwould execute successfullyandonly ORD_ITM_IDX index would be created. 

B. It would give an error because the USING INDEX clause cannot be used onacomposite primarykey. 

C. It wouldexecutesuccessfully and two indexes ORD_ITM_IDXand ORD_ITM_ID_PKwould becreated. 

D. Itwould give an error becausetheUSING INDEX clause isnotpermitted in the CREATETABLEcommand. 

Answer: A


Q106. Which two statements are true regarding the types of table joins available in Oracle Database 10g? (Choose two.) 

A. You can use the JOIN clause to join only two tables. 

B. You can explicitly provide the join condition with a NATURAL JOIN. 

C. You can use the USING clause to join tables on more than one column. 

D. You can use the ON clause to specify multiple conditions while joining tables. 

Answer: CD


1z0-047  exam question

Vivid 1z0-047 questions:

Q107. 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


Q108. In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause? 

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

B. to create group-wise grand totals for the groups specified within a GROUP BY clause 

C. to create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals 

D. to create a grouping for expressions or columns specified within a GROUP BY clause in all possible directions, which is cross-tabular report for calculating the subtotals 

Answer: C


Q109. View the Exhibit and examine the descriptions of the DEPT and LOCATIONS tables. 


You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department. 

Which SOL statement would you execute to accomplish the task? 


A. UPDATE deptd 

SET city = ANY (SELECT city FROM locations I); 


B. UPDATE deptd 

SET city = (SELECT city 

FROM locations I) 

WHERE d.location_id = l.location_id; 


C. UPDATE deptd 

SET city = (SELECT city 

FROM locations I 

WHERE d.location_id = l.location_id); 


D. UPDATE deptd 

SET city = ALL (SELECT city 

FROM locations I 

WHERE d.location_id = l.location_id); 


Answer: C


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

Your company wants to give 5% bonus to all the employees on their annual salary. The SALARY column stores the monthly salary for an employee. To check the total for annual salary and bonus amount for each employee, you issued the following SQL statement: 


SELECTfirst_name, salary, salary*!2+salary*12*.05 "ANNUAL SALARY + BONUS" FROM employees; 

Which statement is true regarding the above query? 


A. It would execute and give you the desired output. 

B. It would not execute because the AS keyword is missing between the column name and the alias. 

C. It would not execute because double quotation marks are used instead of single quotation marks for assigning alias for the third column. 

D. It would execute but the result for the third column would be inaccurate because the parentheses for overriding the precedence of the operator are missing. 

Answer: A