If you have a table with existing data ( as shown below) and you’ve been asked to add a NOT NULL column to a table that already has data populated, it is not that simple as may seem at first glance. It did not work because the table must have […]
Rules for HAVING clause is not so easy as it may look like at first glance. It is an assumption that HAVING clause can contain the same grouping/aggregation as SELECT and it will work just fine because the role of HAVING is to filter whatever has left after aggregation done […]
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 […]
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 […]
This is one more common mistake the one can make when it comes to NULL engaged with comparison Consider these two statements, they will produce different results: select * from emp where commission_pct in (0.1, NULL) ; select * from emp where commission_pct = 0.1 or commission_pct IS NULL ; […]
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 […]
Oracle won’t let you create a table with more than one LONG data type. Only one LONG is allowed per table. And of course, you can’t make GROUP BY or ORDER BY operations over LONG data type fields ( similar to any LOB data type like CLOB, BLOB and BFILE) […]
You can’t have LOB data types (CLOB, BLOB, BFILE) ordered. Oracle will trigger an error when you try to order on LOB fields Same with grouping. You can’t apply GROUP BY on LOB fields. It will raise the same error.