we provide Tested Oracle 1z0 047 pdf free draindumps which are the best for clearing oracle database sql expert 1z0 047 test, and to get certified by Oracle Oracle Database SQL Expert. The oracle 1z0 047 Questions & Answers covers all the knowledge points of the real oracle 1z0 047 exam. Crack your Oracle 1z0 047 pdf Exam with latest dumps, guaranteed!

Q11. View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables. 

In the ORDERS table, ORDER_ID is the PRIMARY KEY and ORDER_DATE has the DEFAULT value as SYSDATE. 

Evaluate the following statement: 

UPDATE orders 

SET order_date=DEFAULT 

WHERE order_id IN (SELECT order_id FROM order_items 

WHERE qty IS NULL); 

What would be the outcome of the above statement? 

A. The UPDATEstatementwould not work because the main queryandthe subquery usedifferenttables. 

B. The UPDATEstatement would not work becausetheDEFAULTvaluecan be used only in INSERT statements. 

C. TheUPDATEstatementwould changeall ORDER_DATE values to SYSDATE provided the current ORDER_DATE is NOT NULLand QTYis NULL 

D. The UPDATE statement would change all the ORDER_DATE values to SYSDATE irrespective of what the current ORDER_DATE value is for all orders where QTY is NULL 

Answer: D


Q12. View the Exhibitl and examine the descriptions of the EMPLOYEES and DEPARTMENTS tables. 

The following SQL statement was executed: 

SELECT e.department_id, e.job_id, d.location_id, sum(e.salary) total, 

GROUPING(e.department_id)GRP_DEPT, 

GROUPING(e.job_id) GRPJOB, 

GROUPING(d. location_id) GRP_LOC 

FROM employees e JOIN departments d 

ON e.department_id = d.department_id 

GROUP BY ROLLUP (e.department_id, e.job_id, d.location_id); 

View the Exhibit2 and examine the output of the command. 

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

A. The value 1 inGRP_LOC means that the LOCATION_ID column is taken into account to generate the subtotal. 

B. The value 1 in GRP_JOB and GRP_LOC means that JOB_ID and LOCATION_ID columns are not taken into account to generate the subtotal. 

C. The value 1 in GRP_JOB and GRP_LOC means that the NULL value in JOB_ID and LOCATIONJD columns are taken into account to generate the subtotal. 

D. The value 0 in GRP_DEPT, GRPJOB, and GRP_LOC means that DEPARTMENT_ID, JOB_ID, and LOCATION_ID columns are taken into account to generate the subtotal 

Answer: BD


Q13. Which CREATE TABLE statement is valid? 

A. CREATE TABLE ord_details 

(ord_no NUMBER(2) PRIMARY KEY, 

item_no NUMBER(3)PRIMARY KEY, 

ord_date date NOT NULL); 

B. CREATE TABLE ord_details 

(ord_no NUMBER(2) UNIQUE, NOT NULL, 

item_no NUMBER(3), 

ord_date date DEFAULT SYSDATE NOT NULL); 

C. CREATE TABLE ord_details 

(ord_no NUMBER(2) , 

item_no NUMBER(3), 

ord_date date DEFAULT NOT NULL, 

CONSTRAINT ord_uq UNIQUE (ord_no), 

CONSTRAINT ord_pk PRIMARY KEY (ord_no)); 

D. CREATE TABLE ord_details 

(ord_no NUMBER(2), 

item_no NUMBER(3), 

ord_date date DEFAULT SYSDATE NOT NULL, 

CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no)); 

Answer: D


Q14. View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the 

PRIMARY KEY in the ORDERS table. 

Evaluate the following CREATE TABLE command: 

CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id) 

AS 

SELECT order_id.order_date,customer_id 

FROM orders; 

Which statement is true regarding the above command? 

A. The NEW_IDRDERS table would not get created because the DEFAULT value can not be specified in the column definition. 

B. The NEW_IDRDERS table would get created and only the NOTNULL constraint defined on the specified columns would be passed to the new table. 

C. The NEW_IDRDERS table would not get created because the column names in the CREATE TABLE commandand the SELECT clause do not match. 

D. The NEW_IDRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table. 

Answer: B


Q15. Evaluate the following command: 

CREATE TABLE employees (employee_id NUMBER(2) PRIMARY KEY, last_name VARCHAR2(25) NOT NULL, department_id NUMBER(2), job_id VARCHAR2(8), salary NUMBER(10,2)); 

You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization: 

CREATE OR REPLACE VIEW sales_staff_vu AS SELECT employee_id, last_name job_id FROM employees WHERE job_id LIKE 'SA_%' WITH CHECK OPTION; 

Which statements are true regarding the above view? (Choose all that apply.) 

A. It allows you to insert details of allnewstaff into the EMPLOYEES table. 

B. Itallowsyou todeletethedetails of the existing sales staff fromtheEMPLOYEES table. 

C. It allows you to updatethejob ids oftheexisting sales staff to any other job id in the EMPLOYEES table. 

D. It allows you to insert the IDs, last names and job ids of the sales staff from theviewif it is used in multitable INSERT statements. 

Answer: BD


Q16. Which statement is true regarding the CUBE operator in the GROUP BY clause of a SQL statement? 

A. It produces only aggregates for the groups specified in the GROUP BY clause. 

B. It finds all the NULL values in the superaggregates for the groups specified in the GROUP BY clause. 

C. It produces 2 n possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause. 

D. It produces n+1 possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause. 

Answer: C


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

Your company decided to give a monthly bonus of $50 to all the employees who have completed five years in the company. The following statement is written to display the LAST_NAME, 

DEPARTMENT_ID, and the total annual salary: 

SELECT last_name, department_id, salary+50*12 "Annual Compensation" FROM employees WHERE MONTHS_BETWEEN(SYSDATE, hire_date)/12 >= 5; 

When you execute the statement, the "Annual Compensation" is not computed correctly. What changes would you make to the query to calculate the annual compensation correctly? 

A. Change the SELECT clause to SELECT last_name, department_id, salary*12+50 "Annual Compensation". 

B. Change the SELECT clause to SELECT last_name, department_id, salary+(50*12) "Annual Compensation". 

C. Change the SELECT clause to SELECT last_name, department_id, (salary +50)*12 "Annual Compensation". 

D. Change the SELECT clause to SELECT last_name, department_id, (salary*12)+50 "Annual Compensation". 

Answer: C


Q18. Which statements are true regarding the hierarchical query in Oracle Database 10g? (Choose all that apply.) 

A. It is possible to retrieve data only in top-down hierarchy. 

B. Itis possible to retrieve data in top-down or bottom-up hierarchy. 

C. It is possible to remove an entire branch from the output of the hierarchical query. 

D. You cannot specify conditions when you retrieve data by using a hierarchical query. 

Answer: BC


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


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