Prepare Smarter for the 1z0-071 Exam
Build your exam confidence with flexible preparation resources designed around the latest 1z0-071 exam objectives. Practice at your own pace using PDF questions, online exam simulations, or desktop practice software.
Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database?
The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)
A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row
A TIMESTAMP data type column contains information about year, month, and day
The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)
The CURRENT_TIMESTAMP function returns data without time zone information
Correct Answer: A
A: True . The DBTIMEZONE function returns the database ' s time zone offset from UTC (Coordinated Universal Time). In Oracle 12c, DBTIMEZONE can return the time zone of the database in terms of a region name or as a numeric offset from UTC. This is stated in the Oracle documentation for managing time zones.
B: True . TIMESTAMP WITH LOCAL TIME ZONE is a data type that adjusts the data stored in the database to the time zone of the session that is querying or inserting the data. When data is stored, Oracle converts it from the session time zone to UTC, and upon retrieval, it converts it back to the session time zone. This is a feature designed to allow the same data to be viewed in different time zones automatically, making it highly useful in global applications.
D: True . SESSIONTIMEZONE function returns the time zone offset of the current session from UTC. This is useful for understanding and managing data conversions in applications that are used across different time zones. The time zone can be displayed as an offset from UTC or as a named region depending on the environment settings.
In the PROMOTIONS table, the PROMO_BEGTN_DATE column is of data type DATE and the default date format is DD-MON-RR.
Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a query?
TO_NUMBER(PROMO_BEGIN_DATE)-5 will return number
TO_DATE(PROMO_BEGIN_DATE * 5) will return a date
PROMO_BEGIN_DATE-SYSDATE will return a number.
PROMO_BEGIN_DATE-5 will return a date.
PROMO_BEGIN_DATE-SYSDATE will return an error.
Correct Answer: A
A. This statement is incorrect because TO_NUMBER expects a character string as an argument, not a date. Directly converting a date to a number without an intermediate conversion to a character string would result in an error. B. This statement is incorrect. Multiplying a date by a number does not make sense in SQL, and attempting to convert such an expression to a date will also result in an error. C. This statement is correct. Subtracting two dates in Oracle SQL results in the number of days between those dates, hence the result is a number. D. This statement is correct. Subtracting a number from a date in Oracle SQL will subtract that num ber of days from the date, returning another date. E. This statement is incorrect. As stated in C, subtracting a date from SYSDATE correctly returns the number of days between those two dates, not an error.
These concepts are explained in the Oracle Database SQL Language Reference, which details date arithmetic in SQL.
Which two statements are true about date/time functions in a session where NLS_DATE_PORMAT is set to DD-MON-YYYY SH24:MI:SS
SYSDATE can be used in expressions only if the default date format is DD-MON-RR.
CURRENT_TIMESTAMP returns the same date as CURRENT_DATE.
CURRENT_DATE returns the current date and time as per the session time zone
SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server.
CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of functional seconds.
SYSDATE can be queried only from the DUAL table.
Correct Answer: A
In Oracle Database 12c SQL, regarding date/time functions and considering a session where NLS_DATE_FORMAT is set to DD-MON-YYYY SH24:MI:SS:
C. CURRENT_DATE returns the current date and time as per the session time zone. This is correct as CURRENT_DATE returns the current date and time in the time zone of the current SQL session, as set by the ALTER SESSION command.
D. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server. This is partially correct. SYSDATE returns the current date and time from the operating system of the database server. However, CURRENT_DATE returns the date and time set for the client ' s operating system environment, adjusted to the session time zone.
Options A, B, E, and F are incorrect based on Oracle ' s documentation:
A is incorrect because SYSDATE is independent of the NLS_DATE_FORMAT setting.
B is incorrect because CURRENT_TIMESTAMP includes time zone information, which can differ from CURRENT_DATE .
E is incorrect because CURRENT_TIMESTAMP differs from SYSDATE by including fractional seconds and time zone.
F is incorrect as SYSDATE can be queried in any SELECT statement, not just from DUAL.
Which three are true about system and object privileges
Correct Answer: A
A: True . The WITH GRANT OPTION allows the grantee to grant the object privileges they received to another user or role. This can be used for both system and object privileges, thereby extending the privilege chain.
D: True . When an object privilege granted with the WITH GRANT OPTION is revoked, it also revokes the privileges that the grantee had re-granted to others. This is referred to as a cascading effect, which can impact multiple users and roles depending on the extent of re-granting.
F: True . To add a foreign key constraint that references a table in another schema, the user must have the REFERENCES object privilege on the target table in the other schema. This privilege is specific and necessary for the integrity constraints involving foreign keys.
You execute this query:
SELECT TO CHAR (NEXT_DAY(LAST_DAY(SYSDATE),’MON’ ),’ dd“Monday for” fmMonth rrr’) FROM DUAL;
What is the result?
Correct Answer: B
The query uses TO_CHAR and NEXT_DAY functions to format and determine dates:
B. It returns the date for the first Monday of the next month: The function NEXT_DAY(LAST_DAY(SYSDATE), ' MON ' ) finds the next Monday after the last day of the current month, effectively giving the first Monday of the next month. The TO_CHAR formatting is used to return this in a readable format.