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 (2036)
 SQL Server interview questions  SQL Server Interview Questions (1076)
 MS Access interview questions  MS Access Interview Questions (24)
 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 (773)
 Databases AllOther interview questions  Databases AllOther Interview Questions (106)
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
 
which query u can write to sql server doesn't work inbetween 7.00PM to nextday 9.00AM Wipro 5
what is difference between having and where clause ?   2
Normalization and denormalization Wipro 3
wht's the differece between sqlserver05 and sqlserver2000   3
What's the difference between DELETE TABLE and TRUNCATE TABLE commands?   9
What is the difference between DTS and SSIS? Allianz 3
Difference between Function and Stored Procedure? C1-India 9
What are the steps you will take to improve performance of a poor performing query?   1
is it possible to use a variable in a query with the IN clause (a,b,c..z), without getting quotes or conversion errors?   2
Consider a table with 8 rows 4 rows contains value 0 and 4 rows contains value 1.Now write a single update query to make all 0's as 1 and all 1's as 0 TCS 7
What does the INSTEAD OF trigger do?   3
write a query for list of owner who are having multiple bikes in below table 1 shanker pulsar 2 shanker Honda 3 shanker car 4 Balu pulsar 5 Balu Honda 6 Balu car 7 Shyam pulsar 8 Jaya Honda 9 Deepa car 10 vasu car TCS 12
 
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