Proper study guides for Improve Oracle Oracle Database SQL Expert certified begins with Oracle 1z0-047 preparation products which designed to deliver the Guaranteed 1z0-047 questions by making you pass the 1z0-047 test at your first time. Try the free 1z0-047 demo right now.

2016 Jul 1z0-047 questions:

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


To retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, the following SQL statement was written: 

SELECT employee_id, first_name, department_name 

FROM employees NATURAL JOIN departments; 

The desired output is not obtained after executing the above SQL statement. What could be the reason for this? 

A. The NATURAL JOIN clause is missing the USING clause. 

B. The table prefix is missing for the column names in the SELECT clause. 

C. The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause. 

D. The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type. 

Answer: D


Q172. Evaluate the SQL statements: 

CREATE TABLE new_order 

(orderno NUMBER(4), 

booking_date TIMESTAMP WITH LOCAL TIME ZONE); 

The database is located in San Francisco where the time zone is -8:00. 

The user is located in New York where the time zone is -5:00. 

A New York user inserts the following record: 

INSERT INTO new_order 

VALUES(1, TIMESTAMP ?007-05-10 6:00:00 -5:00?); 

Which statement is true? 

A. When theNewYorkuserselects the row, booking_date is displayed as 007-05-10 3.00.00.000000' 

B. When the New York user selectstherow, booking_date is displayed as 2007-05-10 6.00.00.000000 -5:00'. 

C. WhentheSan Francisco user selectstherow, booking_date is displayed as 007-05-103.00.00.000000' 

D. When the San Francisco user selectstherow, booking_dateis displayed 

as 007-05-103.00.00.000000-8:00' 

Answer: C


Q173. View the Exhibit and examine the structure of the CUST table. 


Evaluate the following SQL statements executed in the given order: 


ALTER TABLE cust 

ADD CONSTRAINT cust_id_pk PRIMARY KEY(cust_id) DEFERRABLE INITIALLY DEFERRED; 


INSERT INTO cust VALUES (1,’RAJ1); --row 1 

INSERT INTO cust VALUES (1,'SAM); --row 2 

COMMIT; 


SET CONSTRAINT cust_id_pk IMMEDIATE; 


INSERT INTO cust VALUES (1,’LATA’); --row 3 

INSERT INTO cust VALUES (2,’KING’); --row 4 

COMMIT; 


Which rows would be made permanent in the CUST table? 


A. row 4 only 

B. rows 2 and 4 

C. rows 3 and 4 

D. rows 1 and 4 

Answer: C


Q174. The following are the steps for a correlated subquery, listed in random order: 1) The WHERE clause of the outer query is evaluated. 

2) The candidate row is fetched from the table specified in the outer query. 

3) The procedure is repeated for the subsequent rows of the table, till all the rows are processed. 

4) Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query. Identify the option that contains the steps in the correct sequence in which the Oracle server evaluates a correlated subquery. 

A. 4,2,1,3 

B. 4,1,2,3 

C. 2,4,1,3 

D. 2,1,4,3 

Answer: C


Q175. View the Exhibit and examine the data in EMP and DEPT tables. 

In the DEPT table, DEPTNO is the PRIMARY KEY. 

In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing 


the DEPTNO column in the DEPT table. 

What would be the outcome of the following statements executed in the given sequence? 

DROP TABLE emp; 

FLASHBACK TABLE emp TO BEFORE DROP; 

INSERT INTO emp VALUES (2.COTT 10); 


INSERT INTO emp VALUES (3,ING 55); 




A. Both the INSERT statements would fail because all constraints are automatically retrieved when the table is flashed back. 

B. Both the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back. 

C. Only the first INSERT statement would succeed because all the constraints except the primary key constraint are automatically retrieved after a table is flashed back. 

D. Only the second INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back. 

Answer: D


certifyforall.com

Renewal 1z0-047 exam:

Q176. View the Exhibit and examine the description of the PRODUCT_INFORMATION table. 




SELECT product_name, list_price, min_price, list_price - min_price Difference FROM product_information 


Which options when used with the above SQL statement can produce the sorted output in ascending order of the price difference between LIST_PRICE and MIN_PRICE? (Choose all that apply.) 

A. ORDERBY4 

B. ORDER BY MIN_PRICE 

C. ORDER BY DIFFERENCE 

D. ORDER BY LIST_PRICE 

E. ORDERBYLIST_PRICE–MIN_PRICE 

Answer: ACE


Q177. Which two statements are true regarding subqueries? (Choose two.) 

A. Only two subqueries can be placed at one level. 

B. A subquery can be used to access data from one or more tables or views. 

C. If the subquery returns 0 rows, then the value returned by the subquery expression is NULL. D. The columns in a subquery must always be qualified with the name or alias of the table used. E. A subquery in the WHERE clause of a SELECT statement can be nested up to three levels only. 

Answer: BC


Q178. The first DROP operation is performed on PRODUCTS table using the following command: 

DROP TABLE products PURGE; 

Then you performed the FLASHBACK operation by using the following command: 

FLASHBACK TABLE products TO BEFORE DROP; 

Which statement describes the outcome of the FLASHBACK command? 

A. It recovers only thetablestructure. 

B. It recovers thetablestructure,data,andtheindexes. 

C. It recovers thetablestructure anddatabutnotthe related indexes. 

D. It is not possible to recover the table structure, data, or the related indexes. 

Answer: D


Q179. EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table? 

A. UPDATE empdet SET ename ='Amit' WHERE empno = 1234; 

B. DELETE FROM empdet WHERE ename LIKE 'J%' C. CREATE VIEWempvu AS SELECT* FROMempdept; 

D. CREATEINDEX empdet_idx ON empdet(empno); 

Answer: C


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

ORDER__ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option. 



Which DELETE statement would execute successfully? 

A. DELETEorder_id 

FROMorders 

WHEREorder_total< 1000; 


B. DELETEorders 

WHERE order_total < 1000; 


C. DELETE 

FROM orders 

WHERE (SELECTorder_id 

FROM order_items); 


D. DELETE orders o, order_itemsi 

WHEREo.order id = i.order id; 


Answer: B