Exambible [productsort] Oracle Database SQL Expert practice examination will be the most suitable choice so that you can plan for the Oracle examination. In order to be a prominent Exambible technicians, you can not pass by the Exambible [productsort] Oracle Database SQL Expert materials provision. You can study not only the true [productsort] examination questions and answers but also the in depth description. Passing Oracle [productsort] qualification is the very first most essential thing, in addition, you can study numerous specific knowledge and encounters which will found in the way forward for practice perform.

2016 Aug oracle 1z0-047 dumps free download:

Q111. Which two statements are true regarding roles? (Choose two.) 

A. A role can be granted to itself. 

B. A role can be granted to PUBLIC. 

C. A user can be granted only one role at any point of time. 

D. The REVOKE command can be used to remove privileges but not roles from other users. 

E. Roles are named groups of related privileges that can be granted to users or other roles. 

Answer: BE


Q112. View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables. 



You need to create a view that displays the ORDER ID, ORDER_DATE, and the total number of items in each order. 


Which CREATE VIEW statement would create the view successfully? 

A. CREATE OR REPLACE VIEW ord_vu (order_id,order_date) 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) 

"NO OF ITEMS" 

FROM orders o JOIN order_items i 

ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date; 


B. CREATE OR REPLACE VIEW ord_vu 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) 

"NO OF ITEMS" 

FROM orders o JOIN order_items i 

ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date; 


C. CREATE OR REPLACE VIEW ord_vu 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) 

FROM orders o JOIN order_items i ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date; 


D. CREATE OR REPLACE VIEW ord_vu 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)ll’ NO OF ITEMS' 

FROM orders o JOIN order_items i 

ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date 

WITH CHECK OPTION; 


Answer: B


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


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


Q115. Evaluate the following statement: 

CREATE TABLE bonuses(employee_id NUMBER, bonus NUMBER DEFAULT 100); 

The details of all employees who have made sales need to be inserted into the BONUSES table. You can obtain the list of employees who have made sales based on the SALES_REP_ID column of the ORDERS table. The human resources manager now decides that employees with a salary of $8,000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who have made sales get a bonus of 1 % of their salary and also a salary increase of 1 %. The salary of each employee can be obtained from the EMPLOYEES table. 

Which option should be used to perform this task most efficiently? 

A. MERGE 

B. Unconditional INSERT 

C. ConditionalALLINSERT 

D. Conditional FIRST INSERT 

Answer: A


certifyforall.com

Far out oracle database certified sql expert exam 1z0-047:

Q116. View the Exhibit and examine the description of the ORDERS table. 


Your manager asked you to get the SALES_REP_ID and the total numbers of orders placed by each of the sales representatives. Which statement would provide the desired result? 

A. SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders 

GROUP BY sales_rep_id; 

B. SELECT sales_rep_id, COUNT(order_id)total_orders FROM orders GROUP BY sales_rep_id, total_orders; 

C. SELECT sales_rep_id, COUNT(order_id)total_orders FROM orders; 

D. SELECT sales_rep_id, COUNT(order_id)total_orders FROM orders WHERE sales_rep_id IS NOT NULL; 

Answer: A


Q117. View the Exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables. 


Evaluate the following MERGE statement: 

MERGE INTO orders_master o USING monthly_orders m ON (o.order_id = m.order_id) WHEN MATCHED THEN UPDATE SET o.order_total = m.order_total DELETE WHERE (m.order_total IS NULL) WHEN NOT MATCHED THEN INSERT VALUES (m.order_id, m.order_total); 

What would be the outcome of the above statement? 

A. The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2. 

B. The ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 3. 

C. The ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 4. 

D. The ORDERS_MASTER table would contain the ORDER IDs 1,2,3 and 4. 

Answer: C


Q118. User OE, the owner of the ORDERS table, issues the following command: 

GRANT SELECT,INSERT 

ON orders 

TO hr 

WITH GRANT OPTION; 

The user HR issues the following command: GRANT SELECT ON oe. orders TO scott; 

Then, OE issues the following command: 

REVOKE ALL ON orders FROM hr; 

Which statement is correct? 

A. The user SCOTT loses the privilege to select rows fromOE.ORDERS. 

B. The user SCOTT retains the privilege to select rows from OE.ORDERS. 

C. The REVOKE statement generates an error because OE has to first revoke the SELECT privilege from SCOTT. 

D. The REVOKE statement generates an error because the ALL keyword cannot be usedforprivilegesthathave been granted using WITH GRANT OPTION. 

Answer: A


Q119. Evaluate the following ALTER TABLE statement: 

ALTER TABLE orders 

SET UNUSED order_date; 

Which statement is true? 

A. The DESCRIBE command would still display the ORDER_DATE column. 

B. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table. 

C. The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully. 

D. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table. 

Answer: D


Q120. View the Exhibit and examine the description of EMPLOYEES and DEPARTMENTS tables. 

You want to display the EMPLOYEE_ID, LAST_NAME, and SALARY for the employees who get 

the maximum salary in their respective departments. The following SQL statement was written: 

WITH 

SELECT employee_id, last_name, salary 

FROM employees 

WHERE (department_id, salary) = ANY (SELECT* 

FROM dept_max) 


dept_max as (SELECT d.department_id, max(salary) 

FROM departments d JOIN employees j 

ON (d. department_id = j. department_id) 

GROUP BY d. department_id); 

Which statement is true regarding the execution and the output of this statement? 


A. The statement would execute and give the desired results. 

B. The statement would not execute because the = ANY comparison operator is used instead of=. 

C. The statement would not execute because the main query block uses the query name before it is even created. 

D. The statement would not execute because the comma is missing between the main query block and the query name. 

Answer: C