SQL server BI Interview Questions

1) Find the last date of the given month.


declare   @date date;

set @date='2/5/2012'; --- here you can put what ever date you want

select dateadd(m,DATEDIFF(m,0,@date)+1,-1)

2)will the following query will execute

;with cte as


(

select * from dbo.table1

)

insert into dbo.table1

select 5,null

select * from cte
 
---- the above query will throw error because CTE is scope for single statement.
 
 
3)what is a derived table


select MAX(salary) from (select empid,salary from #temp

where salary between 3000 and 5000) as derivedtable

 
4) Getting 3rd Highest Salary without using top and ranking functions

5) Select top 2 highest salaries from emp;



1) select top 2 * from emp order by salary desc


or


2) ;with cte as


(


Select *, row_number() over (order by salary desc) as row


)


Select * from cte where row<3