SQL-Working With Dates 1-Example exercise based on date data type of employee table for HR schema

--1. sysdate,hiredate  as general format
================================

SQL> select sysdate from dual;

SYSDATE
---------
14-JUL-17

SQL> select hire_date from employees where employee_id=100;

HIRE_DATE
---------
17-JUN-87

SQL>

--2 Display the only year of hire date  for employee_id 100,101,102;
===============================================================

SQL> select employee_id,to_char(hire_date,'YYYY')year from employees where employee_id in (100,101,102);

EMPLOYEE_ID YEAR
-----------               ----
        100               1987
        101               1989
        102               1993

SQL> select to_char(sysdate,'YYYY')year from dual;

YEAR
----
2017

SQL>

--3 Display the only month in full word of hire date  for employee_id 100,101,102;
===============================================================

SQL> select employee_id,to_char(hire_date,'MONTH')MONTH from employees where employee_id in (100,101,102);

EMPLOYEE_ID  MONTH
-----------               ---------
        100               JUNE
        101               SEPTEMBER
        102              JANUARY

--4 Display the only DAY_of_week of hire date for employee_id 100,101,102;
==========================================================

SQL> select employee_id,to_char(hire_date,'DAY')DAY_of_week from employees where employee_id in (100,101,102);

EMPLOYEE_ID    DAY_OF_WE
-----------                 ---------
        100                WEDNESDAY
        101                THURSDAY
        102                WEDNESDAY

SQL>

--5 Display the only DAY_of_week in three letter  of hire date  for employee_id 100,101,102;
=====================================================================

SQL> select employee_id,to_char(hire_date,'DY')DAY_of_week from employees where employee_id in (100,101,102);

EMPLOYEE_ID       DAY
-----------                      ---
        100                    WED
        101                    THU
        102                    WED

--6 Display the only month in three letters word  of hire date  for employee_id 100,101,102;
==================================================================

SQL> select employee_id,to_char(hire_date,'MON')MON from employees where employee_id in (100,101,102);

EMPLOYEE_ID MON
-----------                ---
        100               JUN
        101               SEP
        102               JAN

--7 Display the only year in words of hire date for employee_id 100,101,102;
==============================================================
SQL> select employee_id,to_char(hire_date,'YEAR')year from employees where employee_id in (100,101,102);

EMPLOYEE_ID                 YEAR
-----------                   ------------------------------------------
        100                   NINETEEN EIGHTY-SEVEN
        101                   NINETEEN EIGHTY-NINE
        102                  NINETEEN NINETY-THREE

SQL>

--8 Write a query to find the list of employees who were hire before 1990.
=======================================================

SQL> select last_name,to_char(hire_date,'DD-Mon-yyyy') from employees where to_date(hire_date,'DD-Mon-RR')<'01-Jan-1990';

LAST_NAME                 TO_CHAR(HIR
-------------------------     -----------
King                                 17-Jun-1987
Kochhar                           21-Sep-1989
Whalen                             17-Sep-1987

SQL>

--9  find the numetic day of month whose last_name are King,Austin,Lorentz;
=========================================================

SQL> select last_name,hire_date,to_char(hire_date,'DD')"Numeric Day of Month" from employees where last_name in('King','Austin','Lorentz');

LAST_NAME           HIRE_DATE     Numeric Day Of Month
------------------------- ---------                     --
Austin                    25-JUN-97                 25
King                      30-JAN-96                  30
King                      17-JUN-87                  17
Lorentz                   07-FEB-99                 07


10-find two digit of  value of month whose last_name are King,Austin,Lorentz;
=========================================================
LAST_NAME  HIRE_DATE Two Digit Value Of Month
Austin 25-JUN-97      06
King 30-JAN-96      01
King 17-JUN-87      06
Lorentz 07-FEB-99      02