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
1. How to fetch all the duplicate records from the table.

2. How to fetch the second highest salary from the table.
 Question Submitted By :: SQL-Server
I also faced this Question!!     Answer Posted By  
 
Answer
# 1
These are the easiest and best queries and 100% tested.

1) 2nd highest salary


select top 1 sal from (Select top 2 sal from emp group by
sal order by sal desc) e order by sal


like wise you can find n highet salar(e.g 5th largest 6th
largest)

select top 1 sal from (Select top [n] sal from emp group by
sal order by sal desc) e order by sal


2) fetch all duplicated records from the table

e.g If table has three columns named 'col1', 'col2'
and 'col3'

select col1, col2, col3 from tbl group by col1, col2, col
having count(col1)>1
 
Is This Answer Correct ?    12 Yes 3 No
Kunal Gupta
 
Answer
# 2
2. Second highest salary from the table

select top 1 salary from emp where salary NOT IN
(SELECT MAX(salary) FROM emp)order by salary desc
 
Is This Answer Correct ?    3 Yes 0 No
Ashwini
 
 
 
Answer
# 3
(1) Query to fetch 2'nd highest salary:

Select max(sal) from emptable where sal not in (Select
max(sal) from emptable)

(2) How to fetch all the duplicate records from the table.

SELECT COUNT(*), <COLUMN_NAME> FROM EDUCATION GROUP BY
<COLUMN_NAME>
 
Is This Answer Correct ?    10 Yes 9 No
Amit Kumar
 
Answer
# 4
ANS 1:
select p.ColumnName from tablname p group by p.ColumnName

ans2:

Select min(salary)FROM emp WHERE (salary IN (SELECT
TOP 2 salary FROM emp ORDER BY salary DESC))
Or
Select top 1 salary from (select top 2 salary from emp
order by salary desc) emp order by salary
 
Is This Answer Correct ?    1 Yes 0 No
Rajkumar V
 
Answer
# 5
How to fetch the second highest salary from the table.
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n > 1 (n is always greater than one)
 
Is This Answer Correct ?    1 Yes 0 No
Saradhi
 
Answer
# 6
Ans:1
select Column_Name from Table_Name group by Column_Name
having count(Column_Name )>1

Ans:2
salary - column name

SELECT TOP 1 salary FROM (SELECT DISTINCT TOP 2 salary FROM
Table_Name ORDER BY salary DESC) A ORDER BY salary
 
Is This Answer Correct ?    1 Yes 0 No
Rajkumar V
 
Answer
# 7
select max(salary) from emp where salary<(select max(salary)
from emp)
 
Is This Answer Correct ?    2 Yes 2 No
Oracle.hemant
 
Answer
# 8
select max(salary) from employees where rownum<= 2
order by salary desc
 
Is This Answer Correct ?    0 Yes 0 No
Neelu
 
Answer
# 9
select salary from user order by salary desc LIMIT 1, 1 
Is This Answer Correct ?    0 Yes 0 No
Anbarasan K Kanagaraj
 
Answer
# 10
even i m suffering from this Q. plz let me knw? 
Is This Answer Correct ?    1 Yes 5 No
Jyoti
 

 
 
 
Other SQL Server Interview Questions
 
  Question Asked @ Answers
 
What is the use of Port no? Wipro 1
how to rest identity columns in sql server Matrix 3
How to delete particular value in the column of a table.Is it possible or not?if possible give it as in query.   7
CREATE TABLE [dbo].[HPMS_CompetencyTypes](CompetencyType varchar(50) ) go create trigger hpms_create_Insert on HPMS_CompetencyTypes for insert as if Exists ( select * from [HPMS_CompetencyTypes] where CompetencyType=(select * from [HPMS_CompetencyTypes])) begin Rollback tran Raiserror ('duplicate value',12,6) go insert HPMS_CompetencyTypes (CompetencyType) values ('new') I'm new to trigger can any one tell me where is the issue. Please.   2
I am Having tables T1 and T2 both having same data how to check (or) compare the data in both table are same?   3
how to rename the table   3
Hi All, I want to display all duplicate records in the table. My query has to fetch all the records which are duplicate(First Name or Last Name). Also I want the ability to also pull names where there might be a middle initial placed in the end of the first name field, (i.e., "Maria Z. " vs. "Maria") as well. Please guide me to find this. Table: ID FirstName LastName 1 Zach H Hoffman 2 Zach Hoffman 3 Troy Hoffman 4 Shawn Livermore 5 Prem S 6 Jony Hoffman H 7 Zach Modan I need the query to filter......... ID FirstName LastName 1 Zach H Hoffman 2 Zach Hoffman 3 Troy Hoffman 6 Jony Hoffman H 7 Zach Modan I hope this example will give you clear idea..... Thanks in Advance Prem   2
What is Full Outer Join?   3
How to check what was the last restore transaction LSN on Log-Shipping and Mirroring when we doesn't have a Monitor server and Witness server? IBM 2
Difference between Function and Procedure-in general?   6
What is the difference between Userdefined function and stored procedure? Explain and give the example also   5
how to update a null value field in sql server eg a table contains 3 fields id,name,salary and 3 records salary of 1 record is null i want update the nullfield 111 arun 300 112 ddd 200 113 ttt null i want to update table with add 100 to every record include null after updation the recrds should be 111 arun 400 112 ddd 300 113 ttt 100 HCL 6
 
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