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


♥♥ 2017 NEW RECOMMEND ♥♥

Free VCE & PDF File for Oracle 1z0-047 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 1z0-047 Exam Dumps (PDF & VCE):
Available on: http://www.surepassexam.com/1z0-047-exam-dumps.html

Q141. View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables. 

You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered: 

SELECT p.product_name, i.item_cnt 

FROM (SELECT product_id, COUNT (*) item_cnt 

FROM order_items 

GROUP BY product_id) i RIGHT OUTER JOIN products p 

ON i.product_id = p.product_id; 

What would happen when the above statement is executed? 

A. Thestatement would execute successfullytoproducetherequired output. 

B. Thestatement wouldnotexecute because inlineviewsandouterjoins cannot be usedtogether. 

C. The statementwouldnot execute because the ITEM_CNT alias cannotbedisplayedintheouter query. 

D. The statement wouldnot execute because the GROUP BYclausecannot be used intheinline view. 

Answer: A


Q142. Which two statements are true regarding operators used with subqueries? (Choose two. 

A. The NOT IN operator is equivalent to IS NULL 

B. The <ANY operator means less than the maximum. 

C. =ANY and =ALL operators have the same functionality. 

D. The IN operator cannot be used in single-row subqueries. 

E. TheNOT operator can be used with IN, ANY and ALL operators. 

Answer: BE


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

You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees. 

Which SQL statement would you execute? 

A. SELECT m.last_name "Manager", e.last_name "Employee" 

FROM employees m JOIN employees e 

ON m.employee_id = e.manager_id 

WHERE m.manager_id=100; 

B. SELECT m.last_name "Manager", e.last_name "Employee" 

FROM employees m JOIN employees e 

ON m.employee_id = e.manager_id 

WHERE e.manager_id=100; 

C. SELECT m.last_name "Manager", e.last_name "Employee" 

FROM employees m JOIN employees e 

ON e.employee_id = m.manager_id WHERE m.manager_id=100; 

D. SELECT m.last_name "Manager", e.last_name "Employee" 

FROM employees m JOIN employees e 

WHERE m.employee_id = e.manager_id AND e.manager_id=100; 

Answer: B


Q144. Which two statements best describe the benefits of using the WITH clause? (Choose two.) 

A. It enables users to store the results of a query permanently. 

B. It enables users to store the query block permanently in the memory and use it to create complex queries. 

C. It enables users to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query. 

D. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the user's temporary tablespace. 

Answer: CD


Q145. Evaluate the following SQL statement: 

SELECT 2 col1,ycol2 

FROM dual 

UNION 

SELECT 1 ,'x' 

FROM dual 

UNION 

SELECT 3 .NULL 

FROM dual 

ORDER BY 2; 

Which statement is true regarding the output of the SQL statement? 

A. It would execute and the order of the values in the first column would be 3,2,1. 

B. It would execute and the order of the values in the first column would be 1,2,3. 

C. It would not execute because the column alias name has not been used in the ORDER BY clause. 

D. It would not execute because the number 2 in the ORDER BY clause would conflict with the value 2 in the first SELECT statement. 

Answer: B


Q146. View the Exhibit button and examine the structures of ORDERS and ORDER_ITEMS tables. 

In the ORDERS table, ORDER_ID is the PRIMARY KEY and in the ORDER_ITEMS table, ORDER_ID and LINE_ITEM_ID form the composite primary key. 

Which view can have all the DML operations performed on it? 

A. CREATE VIEWV1 

ASSELECTorder_id, product_id 

FROMorder_items; 

B. CREATE VIEW V4(or_no, or_date, cust_id) 

ASSELECTorder_id, order_date, customer_id 

FROM orders 

WHEREorder_date < 30-mar-2007' 

WITH CHECK OPTION; 

C. CREATEVIEW V3 

ASSELECT o.order_id,o.customer_id, i.product_id 

FROM orders o, order_items i 

WHERE o.order_id=i.order_id; 

D. CREATE VIEWV2 

AS SELECT order_id, line_item_id, unit_price*quantity total 

FROM order items; 

Answer: B


Q147. Evaluate the following SQL statement: 

CREATE INDEX upper_name_idx 

ON product_information(UPPER(product_name)); 

Which query would use the UPPER_NAME_IDX index? 

A. SELECT UPPER(product_name) 

FROM product_information 

WHERE product_jd = 2254; 

B. SELECT UPPER(product_name) 

FROM product_jnformation; 

C. SELECT product_id 

FROM product_jnformation 

WHERE UPPER(product_name) IN ('LASERPRO', 'Cable); 

D. SELECT product_jd, UPPER(product_name) 

FROM product_information 

WHERE UPPER(product_name)='LASERPRO' OR list_price > 1000; 

Answer: C


Q148. Which mandatory clause has to be added to the following statement to successfully create an external table called EMPDET? 

CREATE TABLE empdet 

(empno CHAR(2), ename CHAR(5), deptno NUMBER(4)) 

ORGANIZATION EXTERNAL 

(LOCATION ('emp.daf)); 

A. TYPE 

B. REJECTLIMIT 

C. DEFAULT DIRECTORY 

D. ACCESS PARAMETERS 

Answer: C


Q149. View the Exhibit and examine the structure of the EMP table. 

You executed the following command to add a primary key to the EMP table: 

ALTER TABLE emp 

ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id) 

USING INDEX emp_id_idx; 

Which statement is true regarding the effect of the command? 

A. The PRIMARY KEY is created alongwitha new index. 

B. The PRIMARY KEY is created anditwould use an existinguniqueindex. 

C. The PRIMARY KEYwould becreated in a disabled state because it is usinganexisting index. 

D. The statementproduces an error because the USING clause is permitted only intheCREATE TABLE command. 

Answer: B


Q150. Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.) 

A. it can be used to concatenate two strings. 

B. it can be used to find out the total length of the string. 

C. it can be used for string manipulation and searching operations. 

D. it can be used to format the output for a column or expression having string data. 

E. it can be used to find and replace operations for a column or expression having string data. 

Answer: CDE