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

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

Which statements are true regarding the hierarchical query in Oracle Database 10g? (Choose all that apply.

  1. It is possible to retrieve data only in top-down hierarchy.

  2. It is possible to retrieve data in top-down or bottom-up hierarchy.

  3. It is possible to remove an entire branch from the output of the hierarchical query.

  4. You cannot specify conditions when you retrieve data by using a hierarchical query.

Answer: B,C

Question No: 72

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

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?

  1. Pivoting INSERT

  2. Unconditional INSERT

  3. Conditional ALL INSERT

  4. Conditional FIRST INSERT

Answer: C

Question No: 74

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

Ensurepass 2018 PDF and VCE

Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST LAST NAME is Roberts and CREDIT LIMIT is 600?

  1. INSERT INTO orders

    VALUES (1,#39;10-mar-2007#39;, #39;direct#39;,

    (SELECT customer_id FROM customers

    WHERE cust_last_name=#39;Roberts#39; AND credit_limit=600), 1000);

  2. INSERT INTO orders (order_id,order_date,order_mode,

    (SELECT customer_id FROM customers

    WHERE cust_last_name=#39;Roberts#39; AND credit_limit=600) .order_total)

    VALUES(1 ,#39;10-mar-2007#39;, #39;direct#39;, amp;amp;customer_id, 1000);

  3. INSERT INTO orders (order_id.order_date.order_mode, (SELECT customer_id

    FROM customers

    WHERE cust_last_name=#39;Roberts#39; AND credit _limit=600) .order_total)

    VALUES(1 ,#39;IO-mar-2007#39;, #39;direct#39;, amp;customer_id, 1000);

  4. INSERT INTO(SELECT o.order_id, o.order_date.o.orde_mode.c.customer_id, o.order_total FROM orders o, customers c

WHERE o.customer_id = c.customer_id

AND c.cust_last_name=#39;Roberts#39;ANDc. Credit_limit=600) VALUES (1,#39;10-mar-2007#39;, #39;direct#39;,(SELECT customer_id FROM customers

WHERE cust_last_name=#39;Roberts#39; AND Credit_limit=600), 1000);

Answer: A

Question No: 75

View the Exhibit and examine the structure of the ORDERS table. The columns ORDER_MODE and ORDER_TOTAL have the default values #39;direct#39; and 0 respectively.

Ensurepass 2018 PDF and VCE

Which two INSERT statements are valid? (Choose two.)

  1. INSERT INTO orders

    VALUES (1, O9-mar-2007#39;, #39;online#39;,quot;,1000);

  2. INSERT INTO orders

    (order_id ,order_date ,order_mode, customer_id ,order_total)

    VALUES(1 ,TO_DATE(NULL), #39;online#39;, 101, NULL);

  3. INSERT INTO

    (SELECT order_id ,order_date .customer_id FROM orders)

    VALUES (1,O9-mar-2007#39;, 101);

  4. INSERT INTO orders

    VALUES (1,09-mar-2007#39;, DEFAULT, 101, DEFAULT);

  5. INSERT INTO orders

(order_id ,order_date ,order_mode .order_total) VALUES (1 ,#39;10-mar-2007#39;,#39;online#39;,1000);

Answer: C,D

Question No: 76

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

Ensurepass 2018 PDF and VCE

You want to generate a hierarchical report for all the employees who report to the employee whose EMPLOYEE_ID is 100.

Which SQL clauses would you require to accomplish the task? (Choose all that apply.)

  1. WHERE

  2. HAVING

  3. GROUP BY

  4. START WITH

  5. CONNECT BY

Answer: A,D,E

Question No: 77

A subquery is called a single-row subquery when

  1. the inner query returns a single value to the main query

  2. the inner query uses an aggregate function and returns one or more values

  3. there is only one inner query in the main query and the inner query returns one or more values

  4. the inner query returns one or more values and the main query returns a single value as output

Answer: A

Question No: 78

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

Ensurepass 2018 PDF and VCE

You want to know the FIRST_NAME and SALARY for all employees who have the same manager as that of the employee with the first name #39;Neena#39; and have salary equal to or greater than that of#39;Neena#39;.

Which SQL statement would give you the desired result?

  1. SELECTfirst_name, salary FROM employees

    WHERE (manager_id, salary) gt;= ALL (SELECT manager_id, salary FROM employees WHERE first_name = #39;Neena#39;) AND first_name lt;gt; #39;Neena#39;;

  2. SELECTfirst_name, salary FROM employees

    WHERE (manager_id, salary) gt;= (SELECT manager_id, salary FROM employees

    WHERE first_name = #39;Neena#39;) AND first_name lt;gt; #39;Neena#39;;

  3. SELECT first_name, salary FROM employees

    WHERE (manager_id, salary) gt;= ANY (SELECT manager_id, salary FROM employees WHERE first_name = #39;Neena#39; AND first_name lt;gt; #39;Neena#39;;

  4. SELECT first_name, salary FROM employees WHERE (manager_id = (SELECT manager_id FROM employees

WHERE first_name = #39;Neena#39;) AND salary gt;= (SELECT salary FROM employees

WHERE first_name = #39;Neena#39;)) AND first name lt;gt; #39;Neena#39;;

Answer: D

Question No: 79

View the Exhibit and examine the data in EMP and DEPT tables.

Ensurepass 2018 PDF and VCE

In the DEPT table, DEPTNO is the PRIMARY KEY.

In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing the DEPTNO column in the DEPT table.

What would be the outcome of the following statements executed in the given sequence? DROP TABLE emp;

FLASHBACK TABLE emp TO BEFORE DROP;

INSERT INTO emp VALUES (2,COTT 10); INSERT INTO emp VALUES (3,ING 55);

  1. Both the INSERT statements would fail because all constraints are automatically retrieved when the table is flashed back.

  2. Both the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back.

  3. Only the first INSERT statement would succeed because all the constraints except the primary key constraint are automatically retrieved after a table is flashed back.

  4. Only the second INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back.

Answer: D

Question No: 80

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