Unfortunately, FULL OUTER JOIN does not work with traditional Oracle join syntax. You cannot do something like this : select e.last_name, e.first_name, t.task_name from employee e, task t where e.employee_id(+) = t.employee_id(+); You can only engage FULL OUTER JOIN with ANSI syntax like this : select e.last_name, e.first_name, t.task_name from employee […]
DEFERRABLE INITIALLY IMMEDIATE will check constraint violation after each statement being made As you can see the constraint was fired twice – each time we inserted NULL values
The general rule about DEFERRABLE constraints : [NOT DEFERRABLE] is default, and means that every time a database modification statement is executed, the constraint is checked immediately afterwards, if the modification could violate the constraint. However, if we declare a constraint to be DEFERRABLE, then we have the option of having it wait until […]
From first glance, this subquery looks absolutely legit. Unfortunately, it won’t work as a subquery but will work as a standalone query. This is why many students failed this question on the exam. The syntax checker doesn’t accept the ORDER BY in the subquery and complaints about missing of the […]
It looks like you have a very big chance of getting this question on your exam. In first insert statement, we just created the first row by using standard syntax. In second insert statement we’ve tried to insert same values but using subquery and it failed. You should have used […]
It is a new feature in 12c – now you can use sequence while creating a column. In the example below, the sequence wasn’t utilized because the first parameter inserted in VALUES clause was ” (or NULL in other words) So, in order to utilize this new 12c feature, we must […]
You can’t use SYSDATE within CHECK condition. SYSDATE it is a pseudo-column function. A function is supposed to return a value and this is not how CHECK constraint was designed. With CHECK constraint you can only use explicit column values or literals. CHECK won’t do any function calls from inside. […]