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?
--Using DENSE_RANK() analytical function SELECT first_name,salary FROM( SELECT first_name,salary,DENSE_RANK() OVER ( ORDER BY salary DESC) AS rank FROM employees) WHERE rank=2; --Using self join SELECT first_name,salary FROM employees a WHERE (SELECT COUNT(DISTINCT salary) FROM employees b WHERE a.salary<=b.salary)=2;
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.
--select the duplicate rows SELECT rowid,first_name FROM ( (SELECT first_name,ROW_NUMBER () OVER (PARTITION BY first_name ORDER BY first_name) AS rn FROM employees ) B ) WHERE b.rn>1 and first_name=b. first_name; --delete the duplicate rows DELETE FROM employees WHERE rowid in ( SELECT rowid,first_name FROM ( (SELECT first_name,ROW_NUMBER () OVER (PARTITION BY first_name ORDER BY first_name) AS rn FROM employees ) B ) WHERE b.rn>1 and first_name=b. first_name);
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?
--first inserted record SELECT * FROM employees WHERE ROWID = (SELECT min(ROWID) FROM employees); --last inserted record SELECT * FROM employees WHERE ROWID = (SELECT MAX(ROWID) FROM employees);
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?
--first 5 inserted record SELECT * FROM employees WHERE rownum<=5; --last 5 inserted record SELECT * FROM ( SELECT * FROM employees ORDER BY rowid DESC) WHERE rownum<=5;
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?
SELECT LPAD('*',ROW_NUMBER,'*') AS output FROM ( SELECT LEVEL AS ROW_NUMBER FROM DUAL CONNECT BY LEVEL<=5);
Sometimes it is asked to display the same in reverse manner.
SELECT LPAD('*',ROW_NUMBER,'*') AS output FROM ( SELECT LEVEL AS ROW_NUMBER FROM DUAL CONNECT BY LEVEL<=5 ORDER BY rownum DESC);
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.