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

2016 Jun 1z0-047 Study Guide Questions:

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


You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL clause would let you choose the direction to walk through the hierarchy tree? 

A. WHERE 

B. HAVING 

C. GROUP BY 

D. STARTWITH 

E. CONNECT BY PRIOR 

Answer: E


Q82. View the Exhibit and examine the structure of the ORDERS table. 

NEW_IDRDERS is a new table with the columns ORD_ID, ORD_DATE, CUST_ID, and ORD_TOTAL that have the same data types and size as the corresponding columns in the ORDERS table. 

Evaluate the following INSERT statement: 

INSERT INTO new_orders (ord_id, ord_date, cust_id, ord_total) VALUES

(SELECT order_id.order_date.customer_id.order_total FROM orders WHERE order_date > ‘31-dec-1999’); 

Why would the INSERT statement fail? 


A. because column names in NEWORDERS and ORDERS tables do not match 

B. because the VALUES clause cannot be used in an INSERT with a subquery 

C. because the WHERE clause cannot be used in a subquery embedded in an INSERT statement 

D. because the total number of columns in the NEW ORDERS table does not match the total number of columns in the ORDERS table 

Answer: B


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


2passeasy.com

Most up-to-date oracle sql expert certification 1z0-047 dumps:

Q84. Evaluate the CREATE TABLE statement: 

CREATE TABLE products 

(product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY, 

product_name VARCHAR2(15)); 

Which statement is true regarding the PROD_ID_PK constraint? 

A. Itwould becreated only if a unique index is manually created first. 

B. Itwould becreated andwould use an automatically created unique index. 

C. It would be createdandwould use an automaticallycreatednonunique index. 

D. Itwouldbecreated and remainsinadisabledstatebecauseno indexis specified in the command. 

Answer: B


Q85. The user SCOTT who is the owner of ORDERS and ORDERJTEMS tables issues the following GRANT command: 

GRANT ALL ON orders, order_items TO PUBLIC; 

What correction needs to be done to the above statement? 

A. PUBLICshould bereplacedwithspecific usernames. 

B. ALL should be replaced with a list of specific privileges. 

C. WITH GRANTOPTIONshould beaddedto the statement. 

D. Separate GRANT statements are required for ORDERSandORDERJTEMS tables. 

Answer: D


Q86. Which three statements are true regarding single-row functions? (Choose three.) 

A. They can accept only one argument. 

B. They can be nested up to only two levels. 

C. They can return multiple values of more than one data type. 

D. They can be used in SELECT, WHERE, and ORDER BY clauses. 

E. They can modify the data type of the argument that is referenced. 

F. They can accept a column name, expression, variable name, or a user-supplied constant as arguments. 

Answer: DEF


2passeasy.com

Exact 1z0-047 exam:

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

You executed the following SQL statement: 

SELECT first_name, department_id, salary 

FROM employees 

ORDER BY department_id, first_name, salary desc; 

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


A. The valuesinall the columns wouldbe sortedin the descending order. 

B. Thevalues in theSALARYcolumn wouldbesorted indescendingorder for all the employees havingthesame valueinthe DEPARTMENT_ID column. 

C. The values intheFIRST_NAME column would be sortedinascending order for alltheemployees having the same valueinthe DEPARTMENT_ID column. 

D. Thevalues in the FIRST_NAME column would be sorted in the descendingorder forall the employees having the same valueinthe DEPARTMENT_ID column. 

E. Thevalues in the SALARY column wouldbe sortedin descending order foralltheemployeeshaving thesame value intheDEPARTMENT_IDandFIRST_NAME column. 

Answer: CE


Q88. Evaluate the following SELECT statement and view the Exhibit to examine its output: 

SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status FROM user_constraints WHERE table_name = ORDERS 

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


A. Inthe secondcolumn, indicates a check constraint. 

B. TheSTATUS columnindicateswhether the tableiscurrently in use. 

C. The R_CONSTRAINT_NAME column givesthealternative name for the constraint. 

D. The column DELETE_RULE decides the state oftherelated rows inthechild tablewhenthe corresponding row is deleted from the parent table. 

Answer: AD


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

Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL? 


A. SELECT COUNT(list_price) 

FROM product_information 

WHERE list_price IS NULL; 


B. SELECT COUNT(list_price) 

FROM product_information 

WHERE list_price = NULL; 


C. SELECT COUNT(NVL(list_price, 0)) 

FROM product_information 

WHERE list_price IS NULL; 


D. SELECT COUNT(DISTINCT list_price) 

FROM product_information 

WHERE list_price IS NULL; 


Answer: C


Q90. The details of the order ID, order date, order total, and customer ID are obtained from the ORDERS table. If the order value is more than 30000, the details have to be added to the LARGE_DRDERS table. The order ID, order date, and order total should be added to the ORDER_HISTORY table, and order ID and customer ID should be added to the CUST_HISTORY table. Which multitable INSERT statement would you use? 

A. Pivoting INSERT 

B. Unconditional INSERT 

C. ConditionalALLINSERT 

D. Conditional FIRST INSERT 

Answer: C