Online 1Z0-071 Practice Test

Question 1

In your session NLS_ DATE_ FORMAT is set to DD–MON_RR. Which two queries display the year as four digits?

Options :

A : SELECT TO_DATE(TO_CHAR(SYSDATE,'MM/DD/YYYY'),'MM/DD/YYYY') FROM DUAL;

B : SELECT TO_CHAR (ADD_MONTHS (SYSDATE,6)) FROM DUAL;

C : SELECT TO_DATE (SYSDATE, 'RRRR-MM-DD') FROM DUAL;

D : SELECT TO_DATE (ADD_MONTHS(SYSDATE,6), 'dd-mon-yyyy') FROM DUAL;

E : SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY') FROM DUAL;

F : SELECT TO_CHAR (ADD_MONTHS (SYSDATE, 6), 'dd-mon-yyyy') FROM DUAL;

Answer: A,E

Question 2

Examine the data in the EMP table:

You execute this query:

SELECT deptno AS "Department", AVG(sal) AS AverageSalary, MAX(sal) AS "Max Salary"

FROM emp

WHERE sal >= 12000

GROUP BY "Department "

ORDER BY AverageSalary;

Why does an error occur?

Options :

A : An alias name must not be used in an ORDER BY clause.

B : An allas name must not contain space characters.

C : An alias name must not be used in a GROUP BY clause.

D : An alias name must always be specified in quotes.

Answer: C

Question 3

Which two statements are true about Oracle databases and SQL?

Options :

A :

Updates performed by a database user can be rolled back by another user by using the ROLLBACK command.

B : The database guarantees read consistency at select level on user-created tablers.

C : When you execute an UPDATE statement, the database instance locks each updated row.

D : A query can access only tables within the same schema.

E : A user can be the owner of multiple schemas In the same database.

Answer: B,C

Question 4

Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database?

Options :

A : The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC).

B :

A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row

C : A TIMESTAMP data type column contains information about year, month, and day

D : The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

E : The CURRENT_TIMESTAMP function returns data without time zone information

Answer: A,B,D

Question 5

Which two statements are true about single row functions?

Options :

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

EXPERTY DESIGNED 1Z0-071 PRACTICE TEST