This is another tricky example that worth your attention. When you adding a column with a DEFAULT value specified, then this default value, and not NULLS, will be populated in every row of a new column. Frankly speaking, it is not quite true, because starting from version 11.2, Oracle won’t […]
Yearly Archives: 2017
44 posts
The LISTAGG analytic function was not covered in Roopesh Ramklass book, but it is part of the exam. It makes it very easy to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list. In the example below, the concatenated list was ordered […]
An inline view is a SELECT statement in the FROM-clause of another SELECT statement. In the query below the INLINE VIEW is : (SELECT AVG(salary) avg_salary from employees ) Inline views are commonly used to simplify complex queries by removing join operations and condensing several separate queries into a single query. So, the purpose of INLINE VIEW is to […]
A non-equijoin is represented by the use of an operator other than equality operator. A non-equijoin is used when no corresponding columns exist between the tables in the query, but rather a relationship exists between two columns having compatible data types. Several conditions can be used to define a non-equijoin, […]
This is a tricky behavior of SUM() function which is going to be tested for sure on the exam; We perceive the main purpose of SUM() function as a summation of all elements it may be applied on. But two almost identical SUM() function usages will give you the different results, […]
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. […]