INTERVIEW QUESTIONS DATABASE SQL DETAILS

Question: How to find second maximum value from a table?

Answer: select max(field1) from tname1 where field1=(select max(field1) from tname1 where field1<(select max(field1) from tname1);

Field1- Salary field

Tname= Table name.

select max(field1) from tname1 where field1=(select max(field1) from tname1 where field1<(select max(field1) from tname1);

Field1- Salary field

Tname= Table name.
Source: CoolInterview.com

select max(column_name) from table_name where column_name not in (select max(no1) from table_name) Source: CoolInterview.com

SELECT MIN(QTY) FROM SALES WHERE QTY IN (SELECT TOP 2 QTY FROM SALES ORDER BY QTY DESC) Source: CoolInterview.com

select sal from emp where sal=
(select min(sal) from
(select sal from emp order by sal desc)
where rownum<=&2); Source: CoolInterview.com

select max(salary) from employee
where salary not in(select max(salary) from employee)
Source: CoolInterview.com

Syntax:

Select max(Column Name) from Table Name where Column Name < (Select max(Column Name) from Table Name);

Query:

Select max(Salary) from Employee where salary < (Select max(Salary) from Employee); Source: CoolInterview.com

select max(field1)from(select field1 from table minus select max(field1) from table); Source: CoolInterview.com

select * from emp where amount<(select min(amount) from emp)


Desc: emp-Table name
amount-column name Source: CoolInterview.com

select top 1 * from salary where salary <(select max(salary) from salary) Source: CoolInterview.com

select last_name,job_id,salary
from employees e
where &n=(select count(salary) from(select distinct salary from employees)
where e.salary<=salary)
-------------------- by this query we can find nth maximum salary(in this time) very easily Source: CoolInterview.com

Instead of writing 2-3 times you can use this...

select max(id) from award where id <(select max(id) from award) Source: CoolInterview.com

select * from emp a where 2 =(select count(distinct sal) from emp b where a.sal<=b.sal) Source: CoolInterview.com

select max(sal) from emp where sal< select max(sal) from emp Source: CoolInterview.com

Below is the easiest query for findind nth highest or lowest salary.....

select max(sal) from emp where level=2 connect by prior sal>sal

for finding the nth lowest salary we can use sal<sal..........

Source: CoolInterview.com

Suppose We can get the second highest salary from emp table by using level as......
select level,max(sal)
from emp
connect by prior sal>sal
where level=&level
group by level;
Source: CoolInterview.com

if you need to see only second max salary try this..


select rn,sal from
(select rownum rn,sal from
(select sal from emp order by sal desc)) where nm = 2; Source: CoolInterview.com

select * from emp a where 1 =(select count(distinct sal) from emp b where a.sal<=b.sal)

Source: CoolInterview.com

select *from emp e where $n = (select *from emp m where e.sal<=m.sal)

it may ask u like

enter the value of n : 3
gives u top 3 salary
Source: CoolInterview.com

If you have the better answer, then send it to us. We will display your answer after the approval.

Rules to Post Answers in CoolInterview.com:-

• There should not be any Spelling Mistakes.
• There should not be any Gramatical Errors.
• Answers must not contain any bad words.
• Answers should not be the repeat of same answer, already approved.
• Answer should be complete in itself.

Name :*
Email Id :*
Answer :*
Verification Code	Not readable? Load New Code
Enter the above shown code: *
	Inform me about updated answers to this question