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:

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

Evaluate the following SQL statement: 

SELECTfirst_name, employee_id, NEXr_DAY(ADD_MONTHS(hire_date, 6), 1) "Review" FROM employees; 


The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees. The review date is the first Monday after the completion of six months of the hiring. The 

NLS_TERRITORY parameter is set to AMERICA in the session. Which statement is true regarding this query? 

A. The query would execute to give the desired output. 

B. The query would not execute because date functions cannot be nested. 

C. The query would execute but the output would give review dates that are Sundays. 

D. The query would not execute because the NEXT_DAY function accepts a string as argument. 

Answer: C


Q32. Evaluate the following CREATE TABLE command: 

CREATE TABLE order_item 

(order_id NUMBER(3), 

item_id NUMBER(2), 

qty NUMBER(4), 

CONSTRAINT ord_itm_id_pk 

PRIMARY KEY (order_id item_id) 

USING INDEX 

(CREATE INDEX ord_itm_idx 

ON order_item(order_id,item_id))); 

Which statement is true regarding the above SOL statement? 

A. Itwould execute successfullyandonly ORD_ITM_IDX index would be created. 

B. It would give an error because the USING INDEX clause cannot be used onacomposite primarykey. 

C. It wouldexecutesuccessfully and two indexesORD_ITM_IDXand ORD_ITM_ID_PKwould becreated. 

D. Itwould give an error becausetheUSING INDEX clause isnotpermitted in the CREATETABLEcommand. 

Answer: A


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


certifyforall.com

Improve 1z0-047 dumps free download:

Q34. 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, departmentjd, salary450*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


Q35. View the Exhibit and examine the data in the LOCATIONS table. 


Evaluate the following SOL statement: 

SELECT street_address 

FROM locations 

WHERE 

REGEXP_INSTR(street_address,'[^[: alpha:]]’) = 1; 

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

A. It would displayallthe street addresses thatdo nothaveasubstring 'alpha'. 

B. It would displayallthestreetaddresseswhere the first character isaspecial character. 

C. It would display allthe streetaddresses wherethefirst character is aletterofthealphabet. 

D. It would displayall thestreet addresses where the first character isnota letter of the alphabet. 

Answer: D


Q36. View the Exhibit and examine the structure of the ORDER_ITEMS table. 

You need to display the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table. 

Which query would produce the desired output? 


A. SELECT order_id 

FROM order_items 

WHERE(unit_price*quantity) = MAX(unit_price*quantity) 

GROUP BY order_id; 


B. SELECT order_id 

FROM order_items 

WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items) 

GROUP BY order_id; 


C. SELECT order_id 

FROM order_items 

WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items 

GROUP BY order_id); 


D. SELECT order_id 

FROM order_items 

GROUP BY order_id 

HAVING SUM(unit_price*quantity) =(SELECT MAX(SUM(unit_price*quantity)) 

FROM order_items GROUP BY order_id); 


Answer: D


1z0-047  torrent

Exact 1z0-047 dumps pdf:

Q37. Which statement is true regarding the ROLLUP operator specified in the GROUP BY clause of a SQL statement? 

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

B. It produces only the grand totals for the groups specified in the GROUP BY clause. 

C. It produces higher-level subtotals, moving from right to left through the list of grouping columns specified in the GROUP BY clause. 

D. It produces higher-level subtotals, moving in all the directions through the list of grouping columns specified in the GROUP BY clause. 

Answer: C


Q38. Evaluate the following SQL statement: 

CREATE INDEX upper_name_idx 

ON product_information(UPPER(product_name)); 

Which query would use the UPPER_NAME_IDX index? 



A. SELECT UPPER(product_name) 

FROM product_information 

WHERE product_jd = 2254; 


B. SELECT UPPER(product_name) 

FROM product_jnformation; 


C. SELECT product_id 

FROM product_jnformation 

WHERE UPPER(product_name) IN ('LASERPRO', 'Cable); 


D. SELECT product_jd, UPPER(product_name) 

FROM product_information 

WHERE UPPER(product_name)='LASERPRO' OR list_price > 1000; 


Answer: C


Q39. 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_id, TO_YMINTERVAL(warranty_period) FROM product_information; 

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

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

Answer: A


Q40. You need to create a table for a banking application with the following considerations: 

1) You want a column in the table to store the duration of the credit period. 

2) The data in the column should be stored in a format such that it can be easily added and subtracted with 3) date type data without using the conversion functions. 

4) The maximum period of the credit provision in the application is 30 days. 

5) The interest has to be calculated for the number of days an individual has taken a credit for. 

Which data type would you use for such a column in the table? 

A. INTERVAL YEAR TOMONTH 

B. INTERVALDAYTO SECOND 

C. TIMESTAMP WITHTIMEZONE 

D. TIMESTAMP WITH LOCAL TIME ZONE 

Answer: B