This is a very common question in any interview on SQL. Max salary can be calculated in different ways. Following are the few examples and their explanation.

SQL> SELECT MAX(sal)

FROM emp;

MAX(SAL)

----------

5000

This is to find the first max salary from EMP table. MAX is a group function which returns maximum value out of group of records or all the records.

Click Here, to know more about MAX

SQL> SELECT MAX(sal)

FROM emp

WHERE sal < (SELECT MAX(sal) FROM emp);

MAX(SAL)

----------

3000

Look at the above query closely, sal < max(sal) returns all the salaries less than first max salary. Calculating maximum salary out of all the salaries less than the first maximum salary will return second maximum salary.

So use multiple subqueries to get nth max salary.

ROWNUM is a psuedocolumn which retruns a number indicating the order in which Oracle selects the data.

SQL> SELECT Salary

FROM (SELECT rownum MAXSALARY, sal Salary FROM ( SELECT DISTINCT(sal) FROM emp ORDER BY sal DESC))

WHERE MAXSALARY = 3;

SALARY

----------

2975

Change the MAXSALARY condition to nth value to get nth max salary.

Click Here, to know more about ROWNUM.

SQL> SELECT MIN(sal) FROM emp WHERE sal IN (SELECT sal FROM (SELECT DISTINCT(sal) FROM emp ORDER BY sal DESC) where rownum<=3)

MIN(SAL)

----------

2975

SQL> SELECT LEVEL, MAX(sal) FROM emp

WHERE LEVEL = 3

CONNECT BY PRIOR sal>sal

GROUP BY LEVEL;

LEVEL MAX(SAL)

---------- ----------

3 2975

SELECT * FROM emp r1

WHERE &n = (SELECT COUNT(DISTINCT(sal))

FROM emp WHERE sal >= r1.sal);

**1. Using simple max function. (Retruns First Max Salary only)**SQL> SELECT MAX(sal)

FROM emp;

MAX(SAL)

----------

5000

This is to find the first max salary from EMP table. MAX is a group function which returns maximum value out of group of records or all the records.

Click Here, to know more about MAX

**2. Using subqueries.**SQL> SELECT MAX(sal)

FROM emp

WHERE sal < (SELECT MAX(sal) FROM emp);

MAX(SAL)

----------

3000

Look at the above query closely, sal < max(sal) returns all the salaries less than first max salary. Calculating maximum salary out of all the salaries less than the first maximum salary will return second maximum salary.

So use multiple subqueries to get nth max salary.

**3. Using ROWNUM**ROWNUM is a psuedocolumn which retruns a number indicating the order in which Oracle selects the data.

SQL> SELECT Salary

FROM (SELECT rownum MAXSALARY, sal Salary FROM ( SELECT DISTINCT(sal) FROM emp ORDER BY sal DESC))

WHERE MAXSALARY = 3;

SALARY

----------

2975

Change the MAXSALARY condition to nth value to get nth max salary.

Click Here, to know more about ROWNUM.

**4. Using MIN() function along with ROWNUM to calculate nth maximum salary.**SQL> SELECT MIN(sal) FROM emp WHERE sal IN (SELECT sal FROM (SELECT DISTINCT(sal) FROM emp ORDER BY sal DESC) where rownum<=3)

MIN(SAL)

----------

2975

**5. Using LEVEL**SQL> SELECT LEVEL, MAX(sal) FROM emp

WHERE LEVEL = 3

CONNECT BY PRIOR sal>sal

GROUP BY LEVEL;

LEVEL MAX(SAL)

---------- ----------

3 2975

**6. Using Co-Related Subqueries**SELECT * FROM emp r1

WHERE &n = (SELECT COUNT(DISTINCT(sal))

FROM emp WHERE sal >= r1.sal);

## Comments

2 WHERE &n=(SELECT COUNT(DISTINCT(SAL))

3 FROM EMP WHERE SAL>=r1.SAL);

Enter value for n: 1

This is for max sal.You can put any no like 1,2,3,4,5 for the "n" no of max salary.

The last query for nth max salary uses co-related subqueries to find nth max salary. Read through my presentation on Subqueries

HERE.I created a table like for eg:

create table t1(name varchar2(20),

cur blob)

/

here i want to insert the blob value but i could gettin how to insert blob values.Please help me on this.

BLOB datatypes are Binary in nature. So try inserting 1 or 0 within quotes and this will work and to display these values you will have to convert them to char or number.

Regards,

Mithun

Here is the answer.

SQL> select job, avg(sal) from emp where empno not in (7788,7790) group by job;

Mithun

FName LName Sex

ABC PQR MALE

XYZ MNO FEMALE

EGF STU MALE

My question is I want to insert one more column Name Phone in between LName and Sex.

This is not possible, infact it does not matter if Name, Phone are at the beginning, middle or at the end. All you have to do it to select the columns in what ever sequence you want while selection.

If at all you want this to be in the structure when you describe or do a select * from the table then you need to drop the entire table and recreate it again with required sequence.

Hope this is clear.

Regards,

Mithun

like yes than go in this entity otherwise in other entity.....

THANKS FR TEACHING SO WELL

I FOUND CASE MANIPULATION FUNCTIONS VERY INTERESTING AFTER Y0U GAVE US ASSIGNMENTS ON THIS.

maine pehle v sql padhne ki koshis ki thi ...and i don't knw y..mujhe laga main nhi sikh paungi..but, thanks to u...aap itna mast padhate ho ki jo na sikhna chahe wo v SQL sikhne aa jaye...WE LOVE YOU...............

please tell me difference between MSSQL And Oracle as point interview....please mail me pratap852006@gmail.com(BCE-2 batch Bull Temple Road)

thanx

please check the image wch i had attached to this message ... and please mail me the corresponding query to my mail id i.e., thanmathpradeep@gmail.com ................ it will be much useful for me if u send d query asap.........

thanks & regards,

Hanmath Pradeep

thanmathpradeep@gmail.com

9160160096

how to display five minimum salaries from emp table without any subquery

SELECT Deptno,Ename,Sal,(SELECT SUM(Sal) FROM Emp E2 WHERE E2.Deptno= E1.Deptno)SumSal FROM Emp E1

ORDER BY Deptno

2.Execute This Statement

BREAK ON SumSal SKIP 1

3.Again Executes This Query

SELECT Deptno,Ename,Sal,(SELECT SUM(Sal) FROM Emp E2 WHERE E2.Deptno= E1.Deptno)SumSal FROM Emp E1

ORDER BY Deptno

/

/

in correlation query, does the value from inner query sends each time to outer query after the complete iteration of the table used by inner query?

If max of salary is taken from different tables, how to merge the columns retrieved into a single column?

Display the list of employees who are earning 2nd highest salary in their respective departments without using corelated subqueries.

Write a query to display the 1st day of current month..

Write a query to display the 1st day of current month..

Write a query to fetch the name of the employees with top 5 salaries.

Please Reply

i want to display 10th maximum salary from emp table is this query correct..?

select * from emp x

where &10=(select count(DISTINCT(sal))

from emp where sal>=x.sal;

This is SADHANA

i want to display SQL query to find duplicate row in database? Is this query correct...?

select * from emp x

where rowid=( select max(rowid) from emp

where empno= x.empno);

Reply