DECODE output may be type sensitive Assuming current months is MAY Since MM format will return ’05’ it won’t match ‘5’ as shown below select decode(to_char(sysdate,'MM'),'05','MAY','other month') from dual; --MAY select decode(to_char(sysdate,'MM'),'5','MAY','other month') from dual; -- other month At the same time, if 05 or 5 will be used with […]
Roopesh Ramklass Book
16 posts
NULL is not determinate value and is not counted when the COUNT function As we can see when you supply NULL explicitly to COUNT it will produce 0, even if the query was executed against dual table. In plain words, you can translate this operation as “nothing to count” since […]
The following table summarizes the equivalents for ANY and ALL: Let’s have a look for each case: < ANY ( LESS THAN THE HIGHEST ) select * from employees where salary < ANY (select salary from employees where department_id = 80) ; The query will show all employees with […]
NULL is not determinate value and is not counted when the COUNT function is used with the DISTINCT keyword For instance, if you SELECT DISTINCT MANAGER_ID from EMPLOYEES; you will get 19 rows with NULL among them. However, if you do SELECT COUNT(DISTINCT MANAGER_ID) from EMPLOYEES; you will get […]
Once commit is executed, the update is made permanent. Savepoint A is lost after COMMIT has been executed, therefore you can’t roll back anymore to this Savepoint A. As so, the rollback attempt is failed. Be careful, this is a guaranteed situation on the exam. This is a little tricky, […]
SQL is a strongly typed language: columns are defined as a certain data type, and an attempt to enter a value of a different data type will usually fail. However, you may get away with this because Oracle’s implementation of SQL will, in some circumstances, do automatic type casting. An example below […]
Using NOT IN is fraught with problems because of the way SQL handles NULLs. As a general rule, do not use NOT IN unless you are certain that the result set will not include a NULL. Let’s see an example below. In this example, we want to query all department records from […]
This is a typical situation that can confuse an average student on the exam. We all know that using a subquery that returns multiple rows if forbidden with parent query if parent query refer subquery with any of those =, >, >=, <, <=, <>, != If we use =, […]
Most likely you may have this situation on the exam. It is incomplete date format used with TO_DATE function. How will Oracle behave? Let’s consider an example when we are trying to get a date value from Oracle using TO_DATE function while having only one single value instead of three. […]