MS SQL Server interview questions 之二

1  the query to find the top 2nd highest salary is below:

 

                       select top 1* from Employee where Salary not in
              (select max(Salary) from Employee) order by Salary desc

2  you can get the fifth or n th highest salary of Employee table by using this statement.

             方法1 ：   Select Min(Salary) From Employee Where Salary In
         (
                Select Top(5) Salary
                From Employee
                Order By Salary DESC
           )

         方法2 ：

     select max(salary) from emp where salary not in(select top(n-1) salary from emp order by salary desc）;

注意： The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

3  how to delete duplicate rows in table having rowid
        consider the table employe table with rowid,name and email .

               delete from tblemploye where rowid not in

              ( select max(rowid) from tblemploye group by name, email having count(*)>1)

 

4  find all tables in a database

      Select * from sysobjects where xtype = ‘u’

5