Tags: oracle certification exam tips mistakes dates null unknown not known date arithmetic, test-taking tips
One component of my job as the Oracle Content Developer at Kaplan/Transcender is to review the trouble tickets that we receive from customers using our practice exams. This gives me invaluable insight into “why” students sometimes choose the wrong answer 0n our practice test (and by extension the live exam), even when their technical knowledge of that subject matter is quite good.
I’d like to share with you two of the common errors, as well as strategies which hopefully will serve you well when taking an exam, so you can hopefully avoid these types of mistakes.
Error #1: Dating Yourself
One very common error involves the use of dates, especially when sorting. Remember that if a column in a table or a variable in a PL/SQL block of code is defined as DATE, that value internally stores all the detail to point to an exact second anywhere between 4712BC and 9999AD. Also, you can subtract dates, which creates a difference that has a datatype of NUMBER. That number will represent the number of days (and fractional parts of a day) between the two dates. Suppose your SELECT statement looks like this:
SQL> SELECT id, name, (sysdate – hiredate) AS SENIORITY FROM emp ORDER BY SENIORITY;
If you want this report sorted by seniority, with the person working at the company for the longest period of time to be first, is this the correct way to sort, or should you sort in descending order? Well, the difference between sysdate and hiredate will be the largest number when hiredate is the earliest date possible (since SYSDATE stays constant if you perform this operation for all employees at the same time). Since you want the person where that difference is the greatest to be first, you need to sort descending (DESC) on SENIORITY. Some people find this counter-intuitive, so be sure to think this through carefully on the exam.
Error #2: Evaluating NULL
The use of NULL can sometimes throw off a student who is well prepared for the exam. NULL can be assigned to a variable, in which case it means you don’t know the value of that variable. NULL can also be assigned to the truth value of an expression, in which case it means you don’t know whether the statement is TRUE or FALSE. If you remember NULL like this, things will make sense. Let’s try a few examples.
Evaluate the following:
a. x + 5 where x is 4.
The answer is 9.
b. X + 5, where x is NULL
Since I don’t know what x is, I can’t figure out x+5. Thus the answer for x + 5 is NULL (I don’t know)
c. 6 (x+2) / x * 7 – 3, where x is NULL.
If you don’t know x, you can’t figure this out. Thus the answer for this expression is I don’t know (NULL).
d. WHERE X + 2 > 10, where X is 5.
This is an expression which has a truth value. The choices are either TRUE, FALSE, or NULL. In this case, since x is 5, the expression becomes WHERE 5 + 2 > 10, which is false. The expression’s TRUTH VALUE is FALSE. If that WHERE clause was part of a SELECT statement, when Oracle was searching through the table and got to the row where x is 5, that row would not be displayed since only rows that evaluate to TRUE are displayed.
e. WHERE X + 2 > 10, where x is NULL.
This is also an expression which has a truth value. Since x is NULL, I can’t compute x + 2. Therefore, I can’t determine whether the statement x + 2 > 10 is TRUE or FALSE. Consequently, the TRUTH VALUE of this expression is NULL. If that WHERE clause was part of a SELECT statement, when Oracle was searching through the table and got to the row where x is NULL, that row would not be displayed since only rows that evaluate to TRUE are displayed.
f. WHERE x + 2 > 10 OR y + 3 = 10, where x is NULL (unknown) and y is 7.
This is an expression and consequently has a truth value. It is a compound expression separated by an OR. Since x is unknown, the value of x + 2 is also unknown. Since x + 2 is not known, we can’t tell whether the statement x + 2 > 10 is true or false. Therefore, it is NULL. For the second condition in the WHERE clause, since y is 7, then 7+ 3 is 10, and hence the second condition is TRUE. Therefore, the truth value is now NULL OR TRUE. Since the separator is OR, only one of the two conditions needs to be true to make the entire expression true.
To put this another way, if the first condition was TRUE, the overall expression would be TRUE. However, if the first condition was FALSE, the overall expression would still be TRUE. Therefore, it doesn’t matter what the truth value of the first part of the expression is, the result will still always be TRUE. So if the first part is NULL, the overall result will still be TRUE.
Just for fun, Evaluate these expressions to either TRUE, FALSE, or NULL. The answers are located at the bottom.
1. Condition A OR Condition B where Condition A is True and Condition B is False
2. Condition A OR Condition B where Condition A is False and Condition B is NULL
3. Condition A AND Condition B where Condition A is NULL and Condition B is TRUE
4. Condition A AND Condition B where Condition A is False and Condition B is NULL
5. NOT Condition A where Condition A is False
6. NOT Condition B where Condition B is NULL
7. Condition A OR Condition B where both Conditions are NULL
8. Condition A AND Condition B where both Conditions are NULL
Until next time,
Bob Bungenstock aka Orcltestguy
Answers 1. TRUE 2. FALSE 3. NULL 4. FALSE 5. TRUE 6. NULL 7. NULL 8. NULL