Actual of 1z0-047 answers materials and pack for Oracle certification for customers, Real Success Guaranteed with Updated 1z0-047 pdf dumps vce Materials. 100% PASS Oracle Database SQL Expert exam Today!

2016 May 1z0-047 Study Guide Questions:

Q21. View the Exhibit and examine the description of the ORDER_ITEMS and 

PRODUCT_INFORMATION tables. 

The ORDER_ITEM table has records pertaining to details for each product in an order. The 

PRODUCT_INFORMATION table has records for all the products available for ordering. 

Evaluate the following SOL statement: 

SELECT oi.order_id, pi.product_id 

FROM orderjtems oi RIGHT OUTER JOIN product_information pi 

ON (oi. product_id=pi. Product_id); 

Which statement is true regarding the output of this SOL statement? 



A. The query would return the ORDER_ID and PRODUCT_ID for only those products that are ordered. 

B. The query would return the ORDER_ID and PRODUCT_ID for the products that are ordered as well as for the products that have never been ordered. 

C. The query would return the ORDER_ID and PRODUCT_ID for the products that are ordered but not listed in the PRODUCT_INFORMATION table. 

D. The query would return the ORDER_ID and PRODUCT_ID for those products that are ordered as well as for the products that have never been ordered, and for the products that are not listed in the PRODUCT INFORMATION table. 

Answer: B


Q22. Which statement correctly differentiates a system privilege from an object privilege? 

A. System privileges can be granted only by the DBA whereas object privileges can be granted by DBAs or the owner of the object. 

B. System privileges give the rights to only create user schemas whereas object privileges give rights to manipulate objects in a schema. 

C. Users require system privileges to gain access to the database whereas they require object privileges to create objects in the database. 

D. A system privilege is the right to perform specific activities in a database whereas an object privilege is a right to perform activities on a specific object in the database. 

Answer: D


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


You want to display the expiration date of the warranty for a product. Which SQL statement would you execute? 

A. SELECT product_id, SYSDATE + warranty_period FROM product_information; 

B. SELECT product_jd, TO_YMINTERVAL(warranty_period) FROM product_information; 

C. SELECT product_id, TO_YMINTERVAL(SYSDATE) + warranty_period FROM product_information; 

D. SELECT product_jd, TO_YMINTERVAL(SYSDATE + warranty_period) FROM product_information; 

Answer: A


2passeasy.com

Most up-to-date 1z0-047 oracle database sql expert book pdf:

Q24. View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table. 


Evaluate the following SQL statement: 

ALTER TABLE emp DROP COLUMN first_name; 

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

A. The FIRST_NAME column would be dropped provided it does not contain any data. 

B. The FIRST_NAME column would be dropped provided at least one or more columns remain in the table. 

C. The FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the above SQL statement. 

D. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is used. 

Answer: BD


Q25. Evaluate the following CREATE TABLE commands: 

CREATE TABLE orders 

(ord_no NUMBER(2) CONSTRAINT ord_pk PRIMARY KEY, 

ord_date DATE, 

cust_id NUMBER(4)); 


CREATE TABLE ord_items 

(ord_no NUMBER(2), 

item_no NUMBER(3), 


qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200), 

expiry_date date CHECK (expiry_date > SYSDATE), 

CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no), 

CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no)); 


Why would the ORD_ITEMS table not get created? 


A. SYSDATE cannot be used with the CHECK constraint. 

B. TheCHECK constraint cannot be usedtwiceforthesame table. 

C. TheBETWEENclause cannot be used for the CHECK constraint. 

D. ORD_NO and ITEM_NO cannotbeused asacomposite primarykeybecause ORD_NO is also the FOREIGN KEY. 

Answer: A


Q26. Which statements are correct regarding indexes? (Choose all that apply.) 

A. When a table is dropped, the corresponding indexes are automatically dropped. 

B. For each DML operation performed, the corresponding indexes are automatically updated. 

C. Indexes should be created on columns that are frequently referenced as part of an expression. 

D. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index. 

Answer: ABD


2passeasy.com

Tested 1z0-047 dumps free:

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


Q28. Which two statements are true? (Choose two.) 

A. The USER_SYNONYMSviewcan provide information about private synonyms. 

B. The user SYSTEM owns all the base tables and user-accessible views of the data dictionary. 

C. All the dynamic performance views prefixed with V$ are accessible to all the database users. 

D. The USER_OBJECTS view can provide information about the tables and views created by the user only. 

E. DICTIONARY is a view thatcontains thenamesof allthe datadictionary views that theuser can access. 

Answer: AE


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


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