[Free] 2018(Jan) EnsurePass Testinsides Oracle 1z0-047 Dumps with VCE and PDF 51-60

Ensurepass.com : Ensure you pass the IT Exams
2018 Jan Oracle Official New Released 1z0-047
100% Free Download! 100% Pass Guaranteed!
http://www.EnsurePass.com/1z0-047.html

Oracle Database SQL Expert

Question No: 51

Which two statements best describe the benefits of using the WITH clause? (Choose two.)

  1. It enables users to store the results of a query permanently.

  2. It enables users to store the query block permanently in the memory and use it to create complex queries.

  3. It enables users to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.

  4. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the user#39;s temporary tablespace.

Answer: C,D

Question No: 52

Evaluate the following SQL statement:

SELECT 2 col1,ycol2 FROM dual

UNION SELECT 1 ,#39;x#39;

FROM dual

UNION

SELECT 3 .NULL

FROM dual ORDER BY 2;

Which statement is true regarding the output of the SQL statement?

  1. It would execute and the order of the values in the first column would be 3,2,1.

  2. It would execute and the order of the values in the first column would be 1,2,3.

  3. It would not execute because the column alias name has not been used in the ORDER BY clause.

  4. It would not execute because the number 2 in the ORDER BY clause would conflict with the value 2 in the first SELECT statement.

Answer: B

Question No: 53

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

Ensurepass 2018 PDF and VCE

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

  1. ALTER TABLE products

    ADD price NUMBER(8,2) NOT NULL;

  2. ALTER TABLE products

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

  3. ALTER TABLE products

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

  4. ALTER TABLE products

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

Answer: C

Question No: 54

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?

Ensurepass 2018 PDF and VCE

  1. SELECT UPPER(product_name) FROM product_information WHERE product_jd = 2254;

  2. SELECT UPPER(product_name) FROM product_jnformation;

  3. SELECT product_id FROM product_jnformation

    WHERE UPPER(product_name) IN (#39;LASERPRO#39;, #39;Cable);

  4. SELECT product_jd, UPPER(product_name) FROM product_information

WHERE UPPER(product_name)=#39;LASERPRO#39; OR list_price gt; 1000;

Answer: C

Question No: 55

View the Exhibit and examine the details for the CATEGORIES_TAB table. Evaluate the following incomplete SQL statement:

Ensurepass 2018 PDF and VCE

SELECT category_name ,category_description FROM categories_tab

You want to display only the rows that have #39;harddisks#39; as part of the string in the CATEGORY_DESCRIPTION column.

Which two WHERE clause options can give you the desired result? (Choose two.)

  1. WHERE REGEXPJJKE (category_description, #39;hard .s’);

  2. WHERE REGEXPJJKE (category_description, ‘^H|hard .s’);

  3. WHERE REGEXPJJKE (category_description, #39;^H|hard .s$#39;);

  4. WHERE REGEXPJJKE (category_description, #39;[^Hlhard .s]#39;);

Answer: A,B

Question No: 56

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

Ensurepass 2018 PDF and VCE

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 quot;Annual Compensationquot; FROM employees WHERE MONTHS_BETWEEN(SYSDATE, hire_date)/12 gt;= 5;

When you execute the statement, the quot;Annual Compensationquot; is not computed correctly. What changes would you make to the query to calculate the annual compensation correctly?

  1. Change the SELECT clause to SELECT last_name, department_id, salary*12 50 quot;Annual Compensationquot;.

  2. Change the SELECT clause to SELECT last_name, department_id, salary (50*12) quot;Annual Compensationquot;.

  3. Change the SELECT clause to SELECT last_name, department_id, (salary 50)*12 quot;Annual Compensationquot;.

  4. Change the SELECT clause to SELECT last_name, department_id, (salary*12) 50 quot;Annual Compensationquot;.

Answer: C

Question No: 57

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

Ensurepass 2018 PDF and VCE

Evaluate the following SQL statement:

SELECT order_id, customer_id FROM orders

WHERE order_date gt; #39;June 30 2001#39;;

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

  1. It would not execute because #39;June 30 2001#39; in the WHERE condition is not enclosed within double quotation marks.

  2. It would execute and would return ORDER_ID and CUSTOMER_ID for all records having ORDER_DATE greater than #39;June 30 2001#39;.

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

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

Answer: C

Question No: 58

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

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

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

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

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

Question No: 59

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

Ensurepass 2018 PDF and VCE

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?

  1. WHERE

  2. HAVING

  3. GROUP BY

  4. START WITH

  5. CONNECT BY PRIOR

Answer: E

Question No: 60

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?

  1. It would execute successfully and only ORD_ITM_IDX index would be created.

  2. It would give an error because the USING INDEX clause cannot be used on a composite primary key.

  3. It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID_PK would be created.

  4. It would give an error because the USING INDEX clause is not permitted in the CREATE TABLE command.

Answer: A

100% Ensurepass Free Download!
Download Free Demo:1z0-047 Demo PDF
100% Ensurepass Free Guaranteed!
1z0-047 Dumps

EnsurePass ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.