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 (2041)
 SQL Server interview questions  SQL Server Interview Questions (1082)
 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 (54)
 SQL PLSQL interview questions  SQL PLSQL Interview Questions (776)
 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
 
What is difference beteen Migration and Upgrdation? Satyam 6
what is diffrent between store procedure and trigger   2
what is sql injection?   2
What are the advantages of using sql server over ms access or why should one use sql instead of ms access Impetus 2
how to generate XML out of QUERY? McAfee 1
Define the term DML, DDL and DTL?   1
hi i am having .mdf file and .ldf file ..how can i get the all table in my .mdf..plz give solution   2
how to select 5 to 7 rows from a table, which contains 10 rows? IBM 20
We create an index to fast the search. How it fast the query? Do we write any special keyword with query?   3
What is the difference between login and a user? TCS 12
What are primary keys and foreign keys?   3
To find second largest salary in Employee table CSS 17
 
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