Interview:: Sql queries for sql server

Interview Queries::

    Hi

           In this article I write a some queries which is frequently asked during interviews for sql.
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.

table1 ::

create table #emp_master (row_id int ,emp_name varchar(100),salary int,dept_no int )

insert into #emp_master (row_id ,emp_name ,salary ,dept_no)values (1,'sandip',5000,1)
insert into #emp_master (row_id ,emp_name ,salary ,dept_no)values (2,'aditya',2000,1)
insert into #emp_master (row_id ,emp_name ,salary ,dept_no)values (3,'pranav',3000,1)

insert into #emp_master (row_id ,emp_name ,salary ,dept_no)values (4,'savita',5000,2)
insert into #emp_master (row_id ,emp_name ,salary ,dept_no)values (5,'neha',500,2)
insert into #emp_master (row_id ,emp_name ,salary ,dept_no)values (6,'dipali',1500,2)

insert into #emp_master (row_id ,emp_name ,salary ,dept_no)values (7,'ajit',50002,3)
insert into #emp_master (row_id ,emp_name ,salary ,dept_no)values (8,'anukrushna',10000,3)
insert into #emp_master (row_id ,emp_name ,salary ,dept_no)values (9,'rao',1500,3)

table2 ::

create table dept (dept_id int,dept_name varchar(100))
insert into dept (dept_id ,dept_name ) values(1,'IT')
insert into dept (dept_id ,dept_name ) values(2,'admin')
insert into dept (dept_id ,dept_name ) values(3,'board')
insert into dept (dept_id ,dept_name ) values(4,'Production')
insert into dept (dept_id ,dept_name ) values(5,'Cleaner')

SQL QUERIES ::

1) Count the totalsalary deptno wise where more than 2 employees exist.

SELECT dept_no, sum(salary) As totalsal FROM #emp_master GROUP BY dept_no HAVING COUNT(emp_name) > 2

2) To fetch ALTERNATE records from a table. (ODD NUMBERED)

select * from #emp_master where row_id in (select row_id from #emp_master where row_id%2>0)

3) To select ALTERNATE records from a table.(EVEN NUMBERED)

select * from #emp_master where row_id in (select row_id from #emp_master where row_id%2=0)

5) select only one records with 3 max salary

select top 1 * from (select top 3 row_id ,emp_name ,salary ,dept_no from #emp_master order by salary desc)tab order by salary asc

6) select all records with 3 min salary

select top 1 * from (select top 3 row_id ,emp_name ,salary ,dept_no from #emp_master order by salary desc)tab order by salary asc

7) select only one records with 3 min salary

select top 1 * from (select top 3 row_id ,emp_name ,salary ,dept_no from #emp_master order by salary asc)tab order by salary desc

8) Select FIRST n records from a table.

select top(n) * from #emp_master
select top(3) * from #emp_master

9) Select LAST n records from a table

select * from #emp_master where row_id not in
(select top ((select count(*) from #emp_master)-3) row_id from #emp_master)

10) List dept no., Dept name for all the departments in which there are no employees in the
department.

select * from dept where dept_id not in (select dept_no from #emp_master)

11) How to get 3 Max salaries ?

select distinct top 3 * from #emp_master order by salary desc

12) How to get 3 Min salaries ?

select distinct top 3 salary from #emp_master order by salary asc

13) Select DISTINCT RECORDS from emp table.

select distinct * from #emp_master

14) How to delete duplicate rows in a table?

check my post for delete duplicate row from table

15) Count of number of employees in department wise.

select Count(emp_name) no_of_emp, dept_name from #emp_master inner join dept on dept_id=dept_no group by dept_name

16) Suppose there is annual salary information provided by emp table. How to fetch monthly
salary of each and every employee?

select ename,salary/12 as monthlysal from #emp_master

17)  Select all record from emp table where deptno =1 or 3.

select * from #emp_master where dept_no=3 or dept_no=1
alternate solutions
select * from #empmaster where dept_no in (3,1)

18) Select all record from emp table where deptno=3 and sal>1500.

select * from #emp_master where dept_no=3 and salary>1500

19) Select all records where ename starts with ‘S’ and its lenth is 6 char.

select * from #emp_master where emp_name like'S_____'

20) Select all records where ename may be any no of character but it should end with ‘p’.

select * from #emp_master where emp_name like'%p'

21) How can I create an empty table emp1 with same structure as emp?

select * into #emp1 from #emp_master where 1=2; -- with data
select * into #emp2 from #emp_master where 1=1; -- with all data

22) How to retrive record where sal between 1000 to 2000?

select * from #emp_master where salary between 1000 and 2000

23) If there are two tables #emp_master and emp1, and both have common record.
How can I fetch all the recods but common records only once?

(Select * from #emp_master) Union (Select * from #emp1)

24) How to fetch only common records from two tables emp and #emp2?

(Select * from #emp_master) Intersect (Select * from #emp2)

25) How can I retrive all records of #emp_master those should not present in emp2?

Select * from #emp_master where emp_name not in (Select emp_name from #emp2)

26) Select salary less than 3000 from emp table.

select * from #emp_master where salary<3000 data-blogger-escaped-div="">

27) Select all the employee group by deptno and sal in descending order.

select emp_name,salary,dept_no from #emp_master order by dept_no, salary desc