Interview queries in sql server

    Hi

           In this article I write a some queries which is frequently asked during interviews for sql.
For more queries check my privious post interview queries for sql server
here i have discussed about those queries which is i faced during interviews.
if you have any queries ,questions ,problems then please send me by email or comments.

for your better understanding ,Create below table and fill some record in this.

create table #emp_master (emp_id int identity(1,1) ,emp_name varchar(100),emp_sal varchar(100) ,dept_id int)

insert into #emp_master (emp_name,emp_sal,dept_id ) values('sandipG','1000',1)
insert into #emp_master (emp_name,emp_sal,dept_id ) values('ajit','45321' ,1)
insert into #emp_master (emp_name,emp_sal,dept_id ) values('pranav','5000',2)
insert into #emp_master (emp_name,emp_sal,dept_id ) values('aditya','10000',3)

SQL QUERIES ::

1)delete duplicate records from table

delete from #emp_master where emp_id not in (select min(emp_id ) from #emp_master group by emp_name )

2)select 2nd highest salary

select max(Convert(int, emp_sal))sal from #emp_master where emp_sal not in(select max(Convert(int, emp_sal)) from #emp_master)

3) select N th highest salary

select max(Convert(int, emp_sal))sal from #emp_master where Convert(int, emp_sal) not in (select top n emp_sal from #emp_master order by Convert(int, emp_sal) desc)

4) select 3 rd highest salary from #emp_master

select max(Convert(int, emp_sal)) sal from #emp_master where Convert(int, emp_sal) not in (select top 2 emp_sal from #emp_master order by Convert(int, emp_sal) desc)

5) Add String with any column

select emp_name +' '+'SandipG-sql' names from #emp_master

6) add static column in select statement

select emp_name ,'SandipG-sql' MYID from #emp_master

7) select name with upper case and lower case

select upper(emp_name) emp_up ,lower(emp_name) emp_lo from #emp_master

8) Charindex function in sql server

select charindex('/','dfsdfs/dfs')
select charindex('s','dfsdfs/dfs')

Related Posts::

basic of view
insert into view with multiple tables
Insert into view with single table
Instead of update trigger and after update trigger in sql
Instead of Insert trigger and after insert trigger in sql
instead of delete trigger and after delete trigger in sql
basic of trigger and interview quetions on trigger
Knowledged Queries
Comman Table Expression
Functions In Sql