[Free] 2018(Jan) EnsurePass Examcollection Oracle 1z0-071 Dumps with VCE and PDF 21-30

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

Oracle Database 12c SQL

Question No: 21

Which statement is true regarding external tables?

  1. The default REJECT LIMIT for external tables is UNLIMITED.

  2. The data and metadata for an external table are stored outside the database.

  3. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.

  4. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table.

Answer: D

Question No: 22

Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME

————

Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikkilineni Julia Nayer

You need to display customers#39; second names where the second name starts with quot;Mcquot; or quot;MC.quot;

Which query gives the required output?

  1. SELECT SUBSTR(cust_name, INSTR(cust_name,#39; #39;) 1) FROM customers

    WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,#39; #39;) 1))=#39;Mc#39;;

  2. SELECT SUBSTR(cust_name, INSTR(cust_name,#39; #39;) 1) FROM customers

    WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,#39; #39;) 1)) LIKE #39;Mc%#39;;

  3. SELECT SUBSTR(cust_name, INSTR(cust_name,#39; #39;) 1) FROM customers

    WHERE SUBSTR(cust_name, INSTR(cust_name,#39; #39;) 1) LIKE INITCAP(#39;MC%#39;);

  4. SELECT SUBSTR(cust_name, INSTR(cust_name,#39; #39;) 1) FROM customers

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,#39; #39;) 1)) = INITCAP(#39;MC%#39;);

Answer: B

Question No: 23

Which two statements are true regarding multiple-row subqueries? (Choose two.)

  1. They can contain group functions.

  2. They always contain a subquery within a subquery.

  3. They use the lt; ALL operator to imply less than the maximum.

  4. They can be used to retrieve multiple rows from a single table only.

  5. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.

Answer: A,E

Question No: 24

Which statements are correct regarding indexes? (Choose all that apply.)

  1. When a table is dropped, the corresponding indexes are automatically dropped.

  2. For each DML operation performed, the corresponding indexes are automatically updated.

  3. Indexes should be created on columns that are frequently referenced as part of an expression.

  4. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.

Answer: A,B,D

Question No: 25

Evaluate the following statement:

INSERT ALL

WHEN order_total lt; 10000 THEN INTO small_orders

WHEN order_total gt; 10000 AND order_total lt; 20000 THEN INTO medium_orders

WHEN order_total gt; 2000000 THEN INTO large_orders

SELECT order_id, order_total, customer_id FROM orders;

Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

  1. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.

  2. They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.

  3. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.

  4. The INSERT statement would give an error because the ELSE clause is not present for support in case none of the WHEN clauses are true.

Answer: A

Question No: 26

Which three statements are true reading subquenes?

  1. A Main query can have many subqueries.

  2. A subquery can have more than one main query

  3. The subquery and main query must retrieve date from the same table.

  4. The subquery and main query can retrieve data from different tables.

  5. Only one column or expression can be compared between the subquery and main query.

  6. Multiple columns or expressions can be compared between the subquery and main query.

Answer: A,D,F

Question No: 27

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

Ensurepass 2018 PDF and VCE

The CUSTOMERS table contains the current location of all currently active customers. The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.

You need to find those customers who have never changed their address. Which SET operator would you use to get the required output?

  1. INTERSECT

  2. UNION ALL

  3. MINUS

  4. UNION

Answer: C

Question No: 28

Which statement is true about transactions?

  1. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.

  2. Each Data Definition Language (DDL) statement executed forms a single transaction.

  3. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.

  4. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.

Answer: D

Question No: 29

Evaluate the following SQL query;

Ensurepass 2018 PDF and VCE

What would be the outcome?

A. 200

B. 16 C. 160 D. 150 E. 100

Answer: C Explanation: Function Purpose

ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.)

TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero

Question No: 30

Evaluate the following SELECT statement and view the Exhibit to examine its output:

Ensurepass 2018 PDF and VCE

SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status FROM user_constraints WHERE table_name = ORDERS

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

  1. In the second column, indicates a check constraint.

  2. The STATUS column indicates whether the table is currently in use.

  3. The R_CONSTRAINT_NAME column gives the alternative name for the constraint.

  4. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.

Answer: A,D

100% Ensurepass Free Download!
Download Free Demo:1z0-071 Demo PDF
100% Ensurepass Free Guaranteed!
1z0-071 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.