Interview Questions2

photo-1498184103684-bc1a70b0c068

admin Interview questions, Oracle

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;

Explanation:
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);

Explanation:
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);

Explanation:
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;

Explanation:
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);

Explanation:
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.
E.g.
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.

If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.

Thank you,
Kapil Kumar

Sharing is caring!

You May Also Like..

DUAL TABLE

Oracle_DUAL table

Hey geeks, DUAL table is a very important table available in the Oracle database. It gets created automatically during the […]

Concatenation Operator

Oracle_Concatenation Operator

Hey geeks, This is another chapter of Operators. Concatenation Operator helps to concatenate two or more strings or columns. The […]

Parentheses

Parentheses in SQL

Hey time travellers, Though Parentheses are used pretty much everywhere in SQL but in this blog, we will talk about […]

Leave a Reply

Your email address will not be published. Required fields are marked *