Sql Fundimentals (Time Zones And Dates)

Front 1

Alter the session to set the NLS_DATE_FORMAT to DD-MON-YYYY HH24:MI:SS

Back 1

ALTER SESSION SET NLS_DATE_FORMAT = 'dd-mon-yyyy HH24:MI:SS';

Front 2

Write queries to display the time zone offsets (TZ_OFFSET), for the following time zones.
US/Pacific-New
Singapore
Egypt

Back 2

SELECT TZ_OFFSET ('US/PACIFIC-NEW') FROM DUAL;
SELECT TZ_OFFSET ('SINGAPORE') FROM DUAL;
SELECT TZ_OFFSET ('EGYPT') FROM DUAL;

Front 3

Alter the session to set the TIME_ZONE parameter value to the time zone offset of US/Pacific-New.

Back 3

ALTER SESSION SET TIME_ZONE = '-7:00';

Front 4

Display the CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP for this session

Back 4

SELECT CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

Front 5

Alter the session to set the TIME_ZONE parameter value to the time zone offset of Singapore

Back 5

ALTER SESSION SET TIME_ZONE = '+8:00';

Front 6

Display the CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP for this session.
Note: The output might be different, based on the date when the command is executed.
Note: Observe in the preceding practice that CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP are all sensitive to the session time zone.

Back 6

SELECT CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

Front 7

Write a query to display the DBTIMEZONE and SESSIONTIMEZONE.

Back 7

SELECT DBTTIMEZONE, SESSIONTIMEZONE FROM DUAL;

Front 8

Write a query to extract the YEAR from the HIRE_DATE column of the EMPLOYEES table for those employees who work in department 80.

Back 8

SELECT last_name, EXTRACT (YEAR FROM HIRE_DATE) FROM employees WHERE department_id = 80

Front 9

Alter the session to set NLS_DATE_FORMAT to DD-MON-YYYY.

Back 9

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

note: there are No (parentheses) in this statement

Front 10

Create a query to retrieve last names from the EMPLOYEES table and calculate review status. If the year hired was 2000, display Needs Review for the review status, otherwise display not this year! Name the review status column Review. Sort the results by the HIRE_DATE column.Hint: Use a CASE expression with EXTRACT function to calculate the review status.

Back 10

SELECT emp.last_name, (CASE Extract (year from emp.hire_date)
WHEN 1998 THEN ' Needs Review ' ELSE ' Not this Year! ' END ) AS "Review" From employees emp ORDER BY emp.hire_date; NOTE: "I gave the employees table the nickname EMP"

Front 11

Display the CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP for this session.
Note: The output might be different, based on the date when the command is executed.
Note: Observe in the preceding practice that CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP are all sensitive to the session time zone.

Back 11

SELECT CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

Front 12

Write a query to display the DBTIMEZONE and SESSIONTIMEZONE.

Back 12

SELECT DBTTIMEZONE, SESSIONTIMEZONE FROM DUAL;

Front 13

Write a query to extract the YEAR from the HIRE_DATE column of the EMPLOYEES table for those employees who work in department 80.

Back 13

SELECT last_name, EXTRACT (YEAR FROM HIRE_DATE) FROM employees WHERE department_id = 80

Front 14

Alter the session to set NLS_DATE_FORMAT to DD-MON-YYYY.

Back 14

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

note: there are No (parentheses) in this statement

Front 15

Create a query to retrieve last names from the EMPLOYEES table and calculate review status. If the year hired was 2000, display Needs Review for the review status, otherwise display not this year! Name the review status column Review. Sort the results by the HIRE_DATE column.Hint: Use a CASE expression with EXTRACT function to calculate the review status.

Back 15

SELECT emp.last_name, (CASE Extract (year from emp.hire_date)
WHEN 1998 THEN ' Needs Review ' ELSE ' Not this Year! ' END ) AS "Review" From employees emp ORDER BY emp.hire_date; NOTE: "I gave the employees table the nickname EMP"