[Free] 2018(Jan) EnsurePass Dumpsleader Oracle 1z0-047 Dumps with VCE and PDF 101-110

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: 101

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

Ensurepass 2018 PDF and VCE

Evaluate the following SQL statements that are executed in a user session in the specified order:

CREATE SEQUENCE ord_seq; SELECT ord_seq.nextval FROM dual;

INSERT INTO ord

VALUES (ord_seq.CURRVAL, 25-jan-2007#39;,101);

UPDATE ord

SET ord_no= ord_seq.NEXTVAL WHERE cust_id =101;

What would be the outcome of the above statements?

  1. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUSTJD 101.

  2. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.

  3. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified.

  4. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20.

Answer: A

Question No: 102

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

Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)

Ensurepass 2018 PDF and VCE

  1. WHERE order_date gt; TO_DATE(#39;JUL 10 2006#39;,#39;MON DD YYYY)

  2. WHERE TO_CHAR(order_date,#39;MON DD YYYY) = #39;JAN 20 2003#39;

  3. WHERE order_date gt; TO_CHAR(ADD_MONTHS(SYSDATE,6),#39;MON DD YYYYquot;)

  4. WHERE order_date IN (TO_DATE(#39;Oct 21 2003#39;,#39;Mon DD YYYYquot;), TO_CHAR(#39;NOV 21 2003#39;,#39;Mon DD YYYYquot;))

Answer: A,B

Question No: 103

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

You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees.

Ensurepass 2018 PDF and VCE

Which SQL statement would you execute?

  1. SELECT m.last_name quot;Managerquot;, e.last_name quot;Employeequot; FROM employees m JOIN employees e

    ON m.employee_id = e.manager_id WHERE m.manager_id=100;

  2. SELECT m.last_name quot;Managerquot;, e.last_name quot;Employeequot; FROM employees m JOIN employees e

    ON m.employee_id = e.manager_id WHERE e.managerjd=100;

  3. SELECT m.last_name quot;Managerquot;, e.last_name quot;Employeequot; FROM employees m JOIN employees e

    ON e.employee_id = m.manager_id WHERE m.manager_id=100;

  4. SELECT m.last_name quot;Managerquot;, e.last_name quot;Employeequot; FROM employees m JOIN employees e

WHERE m.employee_id = e.manager_id AND e.managerjd=100;

Answer: B

Question No: 104

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

  1. It is used to find the groups forming the subtotal in a row.

  2. It is used to identify the NULL value in the aggregate functions.

  3. It is used to form the group sets involved in generating the totals and subtotals.

  4. It can only be used with ROLLUP and CUBE operators specified in the GROUP BY clause.

Answer: A,D

Question No: 105

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

Ensurepass 2018 PDF and VCE

Evaluate the following SQL statement:

SELECT employee_id, last_name, job_id, manager_id FROM employees

START WITH employee_id = 101

CONNECT BY PRIOR employee_id=manager_id;

Which statement is true regarding the output for this command?

  1. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by his or her peers.

  2. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by the employee to whom he or she reports.

  3. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by employees below him or her in the hierarchy.

  4. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is101, followed by employees up to one level below him or her in the hierarchy.

Answer: C

Question No: 106

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

Ensurepass 2018 PDF and VCE

Evaluate the following SQL statements executed in the given order:

ALTER TABLE cust

ADD CONSTRAINT cust_id_pk PRIMARY KEY(cust_id) DEFERRABLE INITIALLY DEFERRED;

INSERT INTO cust VALUES (1,’RAJ1); -row 1 INSERT INTO cust VALUES (1,#39;SAM); -row 2 COMMIT;

SET CONSTRAINT cust_id_pk IMMEDIATE;

INSERT INTO cust VALUES (1,’LATA’); -row 3 INSERT INTO cust VALUES (2,’KING’); -row 4 COMMIT;

Which rows would be made permanent in the CUST table?

  1. row 4 only

  2. rows 2 and 4

  3. rows 3 and 4

  4. rows 1 and 4

Answer: C

Question No: 107

View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables.

Ensurepass 2018 PDF and VCE

The query should display the employee IDs of all the employees who have held the job SA_MAN at any time during their tenure.

Choose the correct SET operator to fill in the blank space and complete the following query.

SELECT employee_id FROM employees

WHERE job_id = #39;SA_MAN#39;

Ensurepass 2018 PDF and VCE

SELECT employee_id FROMjob_history WHERE job_id=#39;SA_MAN#39;

  1. UNION

  2. MINUS

  3. INTERSECT

  4. UNION ALL

Answer: A,D

Question No: 108

View the Exhibit and examine the details for the CATEGORIES_TAB table.

Ensurepass 2018 PDF and VCE

Evaluate the following incomplete SQL statement:

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 REGEXP_LIKE (category_description, #39;hard .s’);

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

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

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

Answer: A,B

Question No: 109

View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables.

Ensurepass 2018 PDF and VCE

Which SET operator should be used in the blank space in the following SQL statement to display the cities that have departments located in them?

SELECT location_id, city FROM locations

SELECT location_id, city

FROM locations JOIN departments USING(location_id);

  1. UNION

  2. MINUS

  3. INTERSECT

  4. UNION ALL

Answer: C

Question No: 110

View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.

ORDER ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.

Ensurepass 2018 PDF and VCE

Which DELETE statement would execute successfully?

  1. DELETE order_id FROM orders

    WHERE order_total lt; 1000;

  2. DELETE orders

    WHERE order_total lt; 1000;

  3. DELETE FROM orders

    WHERE (SELECT order_id FROM order_items);

  4. DELETE orders o, order_items i WHERE o.order id = i.order id;

Answer: B

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.