We provide real oracle 1z0 047 exam questions and answers braindumps in two formats. Download PDF & Practice Tests. Pass Oracle 1z0 047 dumps Exam quickly & easily. The oracle 1z0 047 PDF type is available for reading and printing. You can print more and practice many times. With the help of our Oracle 1z0 047 pdf dumps pdf and vce product and material, you can easily pass the 1z0 047 dumps exam.


♥♥ 2017 NEW RECOMMEND ♥♥

Free VCE & PDF File for Oracle 1z0-047 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 1z0-047 Exam Dumps (PDF & VCE):
Available on: http://www.surepassexam.com/1z0-047-exam-dumps.html

Q1. Which statements are true? (Choose all that apply.) 

A. The data dictionary is created and maintained by the database administrator. 

B. The data dictionary views can consist of joins of dictionary base tables and user-defined tables. 

C. The usernames of all the users including the database administrators are stored in the data dictionary. 

D. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies. 

E. Both USER_ODBJECTS and CAT views provide the same information about all the objects that are owned by the user. 

F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary 

Answer: CDF


Q2. View the Exhibit and examine the structure of the PRODUCT_INFORMATION table. 

You want to see the product names and the date of expiration of warranty for all the products, if the product is purchased today. The products that have no warranty should be displayed at the top and the products with maximum warranty period should be displayed at the bottom. 

Which SQL statement would you execute to fulfill this requirement? 

A. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER BY SYSDATE-warranty_period; 

B. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER BY SYSDATE+warranty_period; 

C. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER BY SYSDATE; 

D. SELECT product_name, category_id, SYSDATE+warranty_period "Warranty expire date" FROM product_information WHERE warranty_period >SYSDATE; 

Answer: B


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

Evaluate the following SQL statement: 

SELECT order_id, customer_id 

FROM orders 

WHERE order_date > 'June 30 2001' 

Which statement is true regarding the execution of this SQL statement? 

A. It would not execute because 'June 30 2001' in the WHERE condition is not enclosed within double quotation marks. 

B. It would execute and would return ORDER_ID and CUSTOMER_ID for all records having ORDER_DATE greater than 'June 30 2001'. 

C. It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_DATE conversion function for proper execution. 

D. It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_CHAR conversion function for proper execution. 

Answer: C


Q4. View the Exhibit and examine the data in the PRODUCTS table. 

Which statement would add a column called PRICE, which cannot contain NULL? 

A. ALTER TABLE products 

ADD price NUMBER(8,2) NOT NULL; 

B. ALTER TABLE products 

ADD price NUMBER(8,2) DEFAULT NOT NULL; 

C. ALTER TABLE products 

ADD price NUMBER(8,2) DEFAULT 0 NOT NULL; 

D. ALTER TABLE products 

ADD price NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL; 

Answer: C


Q5. 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_ORDERS table would not get created because the DEFAULT value can not be specified in the column definition. 

B. The NEW_ORDERS table would get created and only the NOT NULL constraint definedon the specified columns would bepassed to the new table. 

C. The NEW_ORDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match. 

D. The NEW_ORDERS 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


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


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


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

Your company wants to give 5% bonus to all the employees on their annual salary. The SALARY column stores the monthly salary for an employee. To check the total for annual salary and bonus amount for each employee, you issued the following SQL statement: 

SELECTfirst_name, salary, salary*!2+salary*12*.05 "ANNUAL SALARY + BONUS" FROM employees; 

Which statement is true regarding the above query? 

A. It would execute and give you the desired output. 

B. It would not execute because the AS keyword is missing between the column name and the alias. 

C. It would not execute because double quotation marks are used instead of single quotation marks for assigning alias for the third column. 

D. It would execute but the result for the third column would be inaccurate because the parentheses for overriding the precedence of the operator are missing. 

Answer: A


Q9. 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 statementwouldexecute and give the desired results. 

B. Thestatement wouldnotexecute becausethe= ANY comparison operator is used instead of=. 

C. Thestatement wouldnot execute because themain queryblock usesthequery name beforeitis even created. 

D. The statement wouldnot execute because the commaismissing betweenthemain query block and the query name. 

Answer: C


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