Hello Oracle folks,
Here we have some more frequently asked interview questions. This is all about complex query writing. I will try to explain as much as possible.
1. How to find out 2nd highest salary of the employees?
In the first code, we added the rank based on the salary of the employees using the DENSE_RANK function. Though, we added ORDER BY DESC clause, we got the highest salary at the 1st rank and 2nd highest salary at the 2nd rank and so on.
In the second code, we did the same but this time without using DENSE_RANK function.
2. How to delete duplicate rows from the table.
To understand the whole process first select the duplicate rows using the ROW_NUMBER analytical function and then use this clause in the delete statement.
This concept is pretty simple. We just add row number using the ROW_NUMBER function. While adding the row number we made partition using the first name so that whenever first name column gets a new name row number gets reset to 1 and starts adding row number again from 1 and 2, 3 and so on for the duplicate first name records. Then we picked the ROWID of those records where row number (RN) is greater than 1 and deleted. Isn’t it very simple?
3. How to find the first and last inserted records?
When we add a new record to the table, Oracle assigns a unique ROWID to that records itself and this ROWID is added in a sequential manner and it never gets changed. So, least ROWID is assigned to very first record and highest ROWID to very last record.
I hope the concept is clear.
4. How to find first 5 five inserted and last 5 inserted records?
Although, we are fetching first and last 5 inserted rows but it is little different from the fetching first and last inserted row as we did in questions #3 (it sounds similar).
To fetch the first 5 rows, I used the ROWNUM PSEUDOCOLUMN and in case of last 5 records I did the same but in sub select query I fetched ROWID in descending order. By doing this I got very last record as very first record.
5. How to display below following using SQL query?
Sometimes it is asked to display the same in reverse manner.
In the 1st query, I started with the sub query and asked Oracle Engine to produce 1 to 5 counting row by row using the CONNECT BY LEVEL clause. Then in the main query, I used the LPAD function to generate the output.
LPAD function accepts 3 parameters. It returns first parameter with the length of 2nd parameter (2nd parameter is always a number) adding 3rd parameter as left padding.
LPAD(‘*’,5,’*’) = *****
It means if the 1st parameter has 4 characters, 2nd parameter is 10 and 3rd parameter is ‘a’ then Oracle engine will add 6 times ‘a’ as left padding to the 1st parameter to make its length 10.
In the 2nd query, I did the same but this time I asked Oracle Engine to produce 1 to 5 counting in reverse order using ROWNUM in DESCENDING order.
If you have any questions please comment below or write me an email.