When you modify existing column with DEFAULT value, then modification to add a DEFAULT value takes effect only from subsequent insertions to the table. Let’s have a look at EMP table below. All DEFAULT values are set to NULL at this point Let’s modify EMP .COMMISION_PCT DEFAULT value to 0.777 Now, the EMP .COMMISION_PCT […]
It is a known fact that NLS_FORMAT_DATE default format is DD-MON-RR, but the date literal can be used in multiple forms and still be fit the format. I’ve found the following question a bit tricky because the right answers are A and C. See below for more. All these DATE literals are […]
I’ve found this example very interesting and making you understand DATE data type better. SYSDATE function returns the current system date and time on your local database. But, if your NLS_DATE_FORMAT setting was set to DD-MON-RR, then it will return accordingly per specified format. Same is true about DATE data […]
To suppress duplicates in a SELECT statement both clauses DISTINCT and UNIQUE can be used, though only DISTINCT is mentioned more widely than UNIQUE. Both clauses are valid and work. ( see below ) DISTINCT UNIQUE
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 […]