Examcollection offers free demo for 1Z0-071 exam. "Oracle Database 12c SQL", also known as 1Z0-071 exam, is a Oracle Certification. This set of posts, Passing the Oracle 1Z0-071 exam, will help you answer those questions. The 1Z0-071 Questions & Answers covers all the knowledge points of the real exam. 100% real Oracle 1Z0-071 exams and revised by experts!

♥♥ 2017 NEW RECOMMEND ♥♥

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: http://www.surepassexam.com/1Z0-071-exam-dumps.html

Q11. Which three statements are true regarding group functions? (Choose three.)

A. They can be used on columns or expressions.

B. They can be passed as an argument to another group function.

C. They can be used only with a SQL statement that has the GROUP BY clause.

D. They can be used on only one column in the SELECT clause of a SQL statement.

E. They can be used along with the single-row function in the SELECT clause of a SQL statement.

Answer: A,B,E

Q12. View the Exhibit and examine the ORDERS table.

The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?


ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);


MODIFY customer_id CONSTRAINT orders_cust_id_nn NOT NULL;


MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);


ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;

Answer: B

Q13. Examine the structure of the members table:

What is the outcome?

A. It fails because the alias name specified after the column names is invalid.

B. It fails because the space specified in single quotation marks after the first two column names is invalid.

C. It executes successfully and displays the column details in a single column with only the alias column heading.

D. It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.

Answer: D

Q14. Examine the structure of the employees table.

There is a parent/child relationship betweenEMPLOYEE_IDandMANAGER_ID.

You want to display the last names and manager IDs of employees who work for the same manager asthe employee whoseEMPLOYEE_ID123.

Which query provides the correct output? 





A. Option A

B. Option B

C. Option C

D. Option D

Answer: B

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. Evaluate the following SQL statements that are issued in the given order:


(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, enameVARCHAR2(15),

salary NUMBER(8,2),

mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);



ENABLE CONSTRAINT emp_emp_no_pk;

What would be the status of the foreign key EMP_MGR_FK?

A. It would be automatically enabled and deferred.

B. It would be automatically enabled and immediate.

C. It would remain disabled and has to be enabled manually using the ALTER TABLE command.

D. It would remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.

Answer: A,B,D

Q17. Which two tasks can be performed by using Oracle SQL statements?

A. changing the password for an existing database

B. connecting to a database instance

C. querying data from tables across databases

D. starting up a database instance

E. executing operating system (OS) commands in a session

Answer: C,E

Q18. You issue the following command to drop the PRODUCTS table: SQL>DROP TABLE products;

What is the implication of this command? (Choose all that apply.)

A. All data in the table are deleted but the table structure will remain

B. All data along with the table structure is deleted

C. All viewsand synonyms will remain but they are invalidated

D. The pending transaction in the session is committed

E. All indexes on the table will remain but they are invalidated

Answer: B,C,D

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

Q20. When does a transaction complete? (Choose all that apply.)

A. When a PL/SQL anonymous block is executed

B. When a DELETE statement is executed

C. When a data definition language statement is executed

D. When a TRUNCATE statement is executed after the pending transaction

E. When a ROLLBACK command is executed

Answer: C,D,E