Free Oracle 1Z0-071 Exam Questions

Become Oracle Certified with updated 1Z0-071 exam questions and correct answers

Page: 1 / 113

Total 561 Questions | Updated On: Sep 22, 2026

Add To Cart

Question 1

The PRODUCTS table has the following structure.

Evaluate the following two SQL statements:

SQL>SELECT prod_id, NVL2 (prod_expiry_date, prod_expiry_date + 15, ‘ ‘) FROM products;

SQL>SELECT prod_id, NVL (prod_expiry_date, prod_expiry_date + 15) FROM products;

Which statement is true regarding the outcome?

A : Both the statements execute and give different results

B : Only the second SQL statement executes successfully

C : Both the statements execute and give the same result

D : Only the first SQL statement executes successfully

Answer: A

Question 2

Which two statements are true about single row functions?

A : CONCAT: can be used to combine any number of values

B : MOD: returns the quotient of a division operation

C : CEIL: can be used for positive and negative numbers

D : FLOOR: returns the smallest integer greater than or equal to a specified number

E : TRUNC: can be used with NUMBER and DATE values

Answer: C,E

Question 3

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables:

You want to generate a list of all department IDs along with any course IDs that may have been assigned to them. Which SQL statement must you use?

A : SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c.department_id);

B : SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c.department_id);

C : SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d.department_id);

D : SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d.department_id);

Answer: B

Question 4

Evaluate these commands which execute successfully CREATE SEQUENCE ord_seq

INCREMENT BY 1

START WITH 1

MAXVALUE 100000

CYCLE

CACHE 5000;

Create table ord_items(

ord_no number(4) default ord_seq.nextval not null,

Item_no number(3),

Qty number(3),

Expiry_date date,

Constraint it_pk primary key(ord_no,item_no),

Constraint ord_fk foreign key (ord_no) references orders(ord_no));

Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?

A : Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.

B : Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO.

C : Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times

D : IF sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.

E : Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.

Answer: B,D

Question 5

Examine the data in the NEW_EMPLOYEES table:

Update existing employee details in the EMPLOYEES table with data from the NEW_EMPLOYEES

table.

2. Add new employee details from the NEW_EMPLOYEES table to the EMPLOYEES table.

Which statement will do this?

A :

B :

C :

D :

Answer: C

Page: 1 / 113

Total 561 Questions | Updated On: Sep 22, 2026

Add To Cart