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 […]

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 ; […]

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) […]

Alias is not allowed in HAVING clause and this is why. Again, we have to consider how Oracle parses the SQL statement: 1)FROM Clause 2)WHERE Clause 3)GROUP BY Clause 4)HAVING Clause 5)SELECT 6)ORDER BY Clause As you can see, the SELECT statement is processed in the end. All aliases are […]

It does not make any sense to sort data before passing them to an outer query from inner query.  The subquery will be used in some outer query, and that outer query will have to do ordering anyway, so there’s no point ordering the subquery. The outer query will print the […]

INSERT ALL Since Oracle 9 we can perform multi-table inserts, allowing us to distribute the data from one source in one or more tables. For the first example we have a sales table (can be external or remote) like this: SELECT * FROM sales; DEALER                Q1        Q2      Q3      Q4 ——-               […]

There are some questions on the exam to verify your knowledge about finding the second highest value of the table or column. It is easy to find the highest value, you just need to sort data using ORDER BY DESC, but don’t forget that ordering data using DESC by default […]

Both techniques are going to be tested during the exam. IN and EXISTS are not the same. And they work absolutely differently. IN picks the list of matching values. EXISTS returns the boolean values like true or false. This is what makes them differ. IN picks, EXISTS returns. In turn, EXISTS returns the boolean […]

NESTED GROUP FUNCTIONS (2 levels deep) Group functions may only be nested two levels deep. An example could be the following : select SUM(AVG(salary)) from employees group by department_id; So, first of all, let’s determine what this  SQL statement with two nested group functions is supposed to do? Obviously, the […]