=========================
Century issues with Dates
=========================

The dates show 20xx because the rows were inserted when your session date
format was DD-MON-YY. 
It does not matter if you change your date format AFTER the rows have
already been inserted. 

Your session date format can be changed as follows: 

SQL> alter session set nls_date_format='DD-MON-RR' ;


This would be in effect as long as your session lasts. That means if you log
out and log back in again, the date format would have been changed back to
default DD-MON-YY. 

Once you change the date format as above, Oracle would use a simple
algorithm to interpret the 2-digit year. If the year is above 50 it would
automatically put 19 for the first 2 digits and if it less than 50, it would
put 20. 
So, if you insert 05-OCT-02, 20 would be added in front of the year 02. 
If you insert 05-OCT-99 then 19 would be added. 


You can always explicitly specify 4-digit year as follows no matter what the
session date format is : 

INSERT INTO mytable(mydate) values( to_date('05-OCT-1999','DD-MON-YYYY') );
INSERT INTO mytable(mydate) values( to_date('05-OCT-2002','DD-MON-YYYY') );

This way you are not dependent on the session date format.

For now, if any of you dont want to "fix" your tables or dont have time that
is fine for assignments as long as your query is correct.  
You can however use the UPDATE statement to fix your data instead of
deleting all the rows and inserting them back again. Of course, you will
have to do a seperate update for each of the rows using the empno. 
This update would work no matter what the session date format is :
 
UPDATE employee SET hire_date = to_date('17-DEC-1980','DD-MON-YYYY') 
WHERE empno = 7369 ;







======================================
Using NVL to deal with NULL values
======================================

Whenever you are dealing with NULL the NVL function comes in handy. NVL
stands  for Null Value Logic. 
For example, if you are trying to find out all employees who are not a
salesman  your where clause would be :
WHERE job <> 'SALESMAN' 
But this would not get you the people who have NULL as a job. You have to
explicitly add OR job is NULL to the where clause. You can eliminate this
extra  step by saying 
WHERE nvl(job,'unknown') <> 'SALESMAN'.
In this case you have made (temporarily, just in the where clause) a NULL
value  a non-null value ('unknown') for comparison using any operators like
<>, = etc.  Remember, you cannot use NULL with these operators and you have
to use IS NULL  or IS NOT NULL otherwise. 
 
Similarly you can use NVL in a SELECT as well where you want to display
"something else" wherever it would not display anything due to the value
being  NULL. 
 
Use examples to get a good idea. Try the above example for searching as well
as  display.