ALLInterview.com :: Home Page            
 Advertise your Business Here     
Browse  |   Placement Papers  |   Company  |   Code Snippets  |   Certifications  |   Visa Questions
Post Question  |   Post Answer  |   My Panel  |   Search  |   Articles  |   Topics  |   ERRORS new
   Refer this Site  Refer This Site to Your Friends  Site Map  Bookmark this Site  Set it as your HomePage  Contact Us     Login  |  Sign Up                      
Google
   
 
Categories >> Software >> Databases >> SQL Server
 
 


 

 
 Oracle interview questions  Oracle Interview Questions (2040)
 SQL Server interview questions  SQL Server Interview Questions (1079)
 MS Access interview questions  MS Access Interview Questions (25)
 MySQL interview questions  MySQL Interview Questions (194)
 Postgre interview questions  Postgre Interview Questions (6)
 Sybase interview questions  Sybase Interview Questions (22)
 DB Architecture interview questions  DB Architecture Interview Questions (5)
 DB Administration interview questions  DB Administration Interview Questions (213)
 DB Development interview questions  DB Development Interview Questions (53)
 SQL PLSQL interview questions  SQL PLSQL Interview Questions (775)
 Databases AllOther interview questions  Databases AllOther Interview Questions (107)
Question
How to find the second largest salary in the emp database and
also How to find 3rd,4th and so on ........ in the emp database

plz mail the answer @ mak2786@gmail.com
 Question Submitted By :: SQL-Server
I also faced this Question!!     Answer Posted By  
 
Answer
# 1
TO FIND THE SECOND HIGHEST SAL IN EMP TABLE

select max(sal) from emp where sal not in(select max(sal)
from emp)

TO FIND THE 3RD HIGHEST SAL IN EMP TABLE

select max(sal) from emp where sal not in(select distinct
top 2 sal from emp order by sal desc)

TO FIND THE Nth HIGHEST SAL IN EMP TABLE

select max(sal) from emp where sal not in(select distinct
top N-1 sal from emp order by sal desc)

yagneswara babu
yagnesh_sn@yahoo.co.in
 
Is This Answer Correct ?    39 Yes 6 No
S N Yagneswara Babu
 
Answer
# 2
To find second highest salary
select max(sal) from emp where sal not in (select max(sal)
from emp)

to find order
select top 5 8 from emp order by sal desc
 
Is This Answer Correct ?    30 Yes 17 No
Saikrishna Reddy .k
 
 
 
Answer
# 3
1.To find second highest salary

select max(sal) from emp_record
where sal not in (select max(sal)from emp_record)


2.To find second highest salary

select max(sal) from emp_record
where sal < (select max(sal)from emp_record)


3.create table punit(rn varchar(30),salary int)
insert into punit values('a',10000)
insert into punit values('b',12000)
insert into punit values('c',15000)
insert into punit values('e',25000)
insert into punit values('d',22000)
insert into punit values('f',30000)
insert into punit values('h',35000)
insert into punit values('g',32000)
insert into punit values('i',32000)
select * from punit order by salary desc
Select * from punit a where 1=(select count(distinct b.salary)
from punit b where a.salary <=b.salary)
Select * from punit a where 2=(select count(distinct b.salary)
from punit b where a.salary <=b.salary)
Select * from punit a where 3=(select count(distinct b.salary)
from punit b where a.salary <=b.salary)
Select * from punit a where 4=(select count(distinct b.salary)
from punit b where a.salary <=b.salary)



by:-

PUNIT CHAUHAN

DELHI
MCA STUDENT STUDIS IN NOIDA
 
Is This Answer Correct ?    9 Yes 0 No
Punit Kumar
 
Answer
# 4
select salary from emp
where salary = (select max(salary) from emp
where salary <>(select max(salary) from emp)
 
Is This Answer Correct ?    8 Yes 3 No
Neon
 
Answer
# 5
first find second largest salary in the emp database:

select min(sal)sal from(select sal from emp order by sal
desc)
where rownum<3
u can find 3rd,4th... so on ,only change rownum
like rownum<4,rownum<5 ... so on
 
Is This Answer Correct ?    10 Yes 8 No
Subhranghshu Bhattacharjee
 
Answer
# 6
sol1> select MAX(salary) from emp where salary< (select MAX(salary) from emp);

sol2> select salary from emp sort by salary desc limit 1,1;

for finding nth position

select salary from emp sort by salary desc limit n-1,1;
 
Is This Answer Correct ?    4 Yes 2 No
Amar Banerjee
 
Answer
# 7
select top 1 sal from emp where empid in(select top 2 empid
from emp order by sal desc) order by
sal asc

if you want to get 3rd one than put 'top 3' in subquery and
same for 4th, 5th....
 
Is This Answer Correct ?    15 Yes 14 No
Pankaj Arya
 
Answer
# 8
SELECT MAX(VCRG_ID) FROM dbo.TRN_VEHICLE_CARGO WHERE VCRG_ID< (SELECT MAX (VCRG_ID) FROM dbo.TRN_VEHICLE_CARGO) 
Is This Answer Correct ?    3 Yes 2 No
Prafulla Borade
 
Answer
# 9
select max(salary) from (select salary from table1 where
salary<(select max(salary) from table1))
 
Is This Answer Correct ?    1 Yes 0 No
Prathapshankarshiva
 
Answer
# 10
create table #a(rn varchar(30),salary int)
insert into #a values('a',10000)
insert into #a values('b',12000)
insert into #a values('c',15000)
insert into #a values('e',25000)
insert into #a values('d',22000)
insert into #a values('f',30000)
insert into #a values('h',35000)
insert into #a values('g',32000)
insert into #a values('i',32000)
select * from #a order by salary desc
Select * from #a a where 1=(select count(distinct b.salary)
from #a b where a.salary <=b.salary)
Select * from #a a where 2=(select count(distinct b.salary)
from #a b where a.salary <=b.salary)
Select * from #a a where 3=(select count(distinct b.salary)
from #a b where a.salary <=b.salary)
Select * from #a a where 4=(select count(distinct b.salary)
from #a b where a.salary <=b.salary)
 
Is This Answer Correct ?    2 Yes 2 No
Narendra Soni
 

 
 
 
Other SQL Server Interview Questions
 
  Question Asked @ Answers
 
Explain what are db_options used for? InterGraph 1
What are constraints? Explain different types of constraints? Polaris 25
How to Create Login and User through query in Sql server 2005.and also how to delete Login and User through query?   2
When do u use clustered index and non-clustered index? IBM 3
How can we Use Linked Server? Uses of Linked server IBM 1
what is difference between nchar and char in Sql server ?   3
How do you find the number of rows in a table?   7
How many jobs will create for Mirroring, Log Shipping, and Transactional Replication? IBM 4
what is lazy writer? Wipro 1
I have a table in which phno is one of the columns.i do have some values in tht phno column.i need to update phno column values with 0(zero) as prefix.give me a correct solution plz... Value-Labs 7
What is the use of Port no? Wipro 1
can anybody tell us, how to select 2nd max salary from table. my id is ashish.akk@gmail.com Symphony 27
 
For more SQL Server Interview Questions Click Here 
 
 
 
 
 


   
Copyright Policy  |  Terms of Service  |  Articles  |  Site Map  |  RSS Site Map  |  Contact Us
   
Copyright 2013  ALLInterview.com.  All Rights Reserved.

ALLInterview.com   ::  KalAajKal.com