All About 1Z0-071 exam topics Oct 2017

Our pass rate is high to 98.9% and the similarity percentage between our 1Z0-071 study guide and real exam is 90% based on our seven-year educating experience. Do you want achievements in the Oracle 1Z0-071 exam in just one try? I am currently studying for the Oracle 1Z0-071 exam. Latest Oracle 1Z0-071 Test exam practice questions and answers, Try Oracle 1Z0-071 Brain Dumps First.


Free VCE & PDF File for Oracle 1Z0-071 Real Exam
(Full Version!)

Pass on Your First TRY 100% Money Back Guarantee Realistic Practice Exam Questions

Free Instant Download NEW 1Z0-071 Exam Dumps (PDF & VCE):
Available on:

Q11. Which statement correctly grants a system privilege?


ON prod



ON tablel TO used;


TO used ,user2;


Answer: C

Q12. You execute the following commands:

For which substitution variables are you prompted for the input?

A. None, because no input required

B. Both the substitution variables 'hiredate' and 'mgr_id\\

C. Only 'hiredate'

D. Only 'mgr_id'

Answer: B

Q13. You are designing the structure of a table in which two columns have the specifications:

COMPONENT_ID – must be able to contain a maximum of 12 alphanumeric characters and uniquely identify the row

EXECUTION_DATETIME – contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and is used for calculations and comparisons between components.

Which two options define the data types that satisfy these requirements most efficiently?


B. The EXECUTION _DATETIME must be of TIMESTAMP data type.

C. The EXECUTION_DATATIME must be of DATE data type.

D. The COMPONENT_ID must be of ROWID data type.

E. The COMPONENT_ID must be of VARCHAR2 data type.

F. The COMPONENT_ID column must be of CHAR data type.

Answer: C,E

Q14. Which three statements are true reading subquenes?

A. A Main query can have many subqueries.

B. A subquery can have more than one main query

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

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

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

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

Answer: A,D,F

Q15. Which two statements are true regarding the COUNT function? (Choose two.)

A. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns

B. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column

C. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column

D. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE clause

E. The COUNT function can be used only for CHAR, VARCHAR2 and NUMBER data types

Answer: A,C


Using the COUNT Function

The COUNT function has three formats: COUNT(*)


COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns. If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of rows that satisfy the condition in the WHERE clause.

In contrast,

COUNT(expr) returns the number of non-null values that are in the column identified by expr.

COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by expr.

Q16. Which two statements are true about sequences created in a single instance database? (Choose two.)

A. CURRVAL is used to refer to the last sequence number that has been generated

B. DELETE <sequencename> would remove a sequence from the database

C. The numbers generated by a sequence can be used only for one table

D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement

E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted

Answer: A,D


Gaps in the Sequence

Although sequence generators issue sequential numbers without gaps, this action occurs independent of a commit or rollback. Therefore, if you roll back a statement containing a sequence, the number is lost.

Another event that can cause gaps in the sequence is a system crash. If the sequence caches values in memory, those values are lost if the system crashes.

Because sequences are not tied directly to tables, the same sequence can be used for multiple tables.

However, if you do so, each table can contain gaps in the sequential numbers.

Modifying a Sequence

If you reach the MAXVALUE limit for your sequence, no additional values from the sequence are allocated and you will receive an error indicating that the sequence exceeds the MAXVALUE. To continue to use the sequence, you can modify it by using the ALTER SEQUENCE statement

To remove a sequence, use the DROP statement:

DROP SEQUENCE dept_deptid_seq;

Q17. See the Exhibit and Examine the structure of the CUSTOMERS table:

Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.

Which SQL statement would produce the required result?

A. SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT" FROM customers;

B. SELECT NVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT" FROM customers;

C. SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT" FROM customers;

D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT" FROM customers;


Explanation: NVL Function

Converts a null value to an actual value:

Data types that can be used are date, character, and number. Data types must match:

– NVL(commission_pct,0)

– NVL(hire_date,'01-JAN-97')

– NVL(job_id,'No Job Yet')

Q18. Evaluate the following SQL statement:

SQL> SELECT cust_id, cust_last_name "Last Name" FROM customers

WHERE country_id = 10 UNION

SELECT cust_id CUST_NO, cust_last_name FROM customers

WHERE country_id = 30;

Which ORDER BY clause are valid for the above query? (Choose all that apply.)



C. ORDER BY 2,cust_id


E. ORDER BY "Last Name"

Answer: A,C,E


Using the ORDER BY Clause in Set Operations

– The ORDER BY clause can appear only once at the end of the compound query.

– Component queries cannot have individual ORDER BY clauses.

– The ORDER BY clause recognizes only the columns of the first SELECT query.

– By default, the first column of the first SELECT query is used to sort the output in an ascending order.

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

Which DELETE statement would execute successfully?

A. DELETE order_id FROM orders

WHERE order_total < 1000;

B. DELETE orders

WHERE order_total < 1000;


FROM orders

WHERE (SELECT order_id FROM order_items);

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

Answer: B

Q20. Which statement is true regarding the INTERSECT operator?

A. It ignores NULL values

B. The number of columns and data types must be identical for all SELECT statements in the query

C. The names of columns in all SELECT statements must be identical

D. Reversing the order of the intersected tables the result

Answer: B


INTERSECT Returns only the rows that occur in both queries’ result sets, sorting them and removing duplicates.

The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.