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 […]
Yearly Archives: 2018
25 posts
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 […]
It is normal if some or all grouping attributes will not be present in SELECT statement. The grouping will work anyway. SELECT statement is for presentation, while actual grouping is done by GROUP BY. An example below demonstrated a typical case for usage SELECT attributes and GROUP BY attributes. Both […]
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 […]
The common perception is that SUBQUERY can’t be used inside the VALUES clause. And frankly speaking, there are many situations on the exam to verify that knowledge. Many people including myself have taken a rule – SUBQUERY can’t be used within VALUES clause. Take for instance an example below. This […]
You may have asked yourself will VIEW affect performance while querying from it if compared with querying directly from the table. Some people think VIEW may slow down a little bit the query, but it is not true. Let’s create a view to verify it. Now let’s query it. Execution time was […]
If you wish to rename a table, you may have to remember some obstacles here. For instance, you are not allowed to rename somebody else’s table using their schema.table name even if you have access to this table. Oracle won’t allow it during the SQL parse phase. See below So the […]
Pay attention, TO_NUMBER needs a dot, not a comma On the exam, you will be limited in time and may not pay attention to details. And may lose some points therefore on simple questions like that. You will have a good portion of questions which requires attention rather than knowledge or […]
The sequence keywords CURVAL and NEXTVAL can’t be used in: the condition of a CHECK constraint the DEFAULT value of a table column the WHERE clause of a SELECT statement
It may look strange from the first glance, but it will work actually. Subquery will produce a single value – the SUM of all salaries. The query will execute successfully and return the FIRST_NAME and LAST_NAME column values of every row in EMPLOYEES table. Each row will have a third […]