We have two tables one is employee and another one is City. Employee table has empCityID column which is a foreign key of city table's CityID. Write a query such that all cities should come and count of employees in each city and result should be in descending order of number of employees in each city. If no employee in particular city then it should come with zero. The table is as follows:
City Table
cityID cityName
1 Chennai
2 Mumbai
3 New Delhi
4 Kolkatta

empID empName empCity
1 Naga 1
2 Siva 1
3 Shankar 2
4 Sundar 3
5 Kevin 1
6 Rajesh 1
7 Karthick 2
8 John 2
9 Shah 3
10 Lal 3
11 Paul 3
12 Zinda 3

 Posted by Nagasundar_Tn on 10/29/2012 | Category: Sql Server Interview questions | Views: 2185 | Points: 40

select COUNT(e.empCity) as TotalNoofEmp,c.cityName from 

dbo.city c left outer join dbo.Employees e
on e.empCity = c.cityID
group by c.cityID,c.cityName
order by COUNT(e.empCity) desc


The first point to be kept in mind is all cities should come, irrespective of employees.
So we should go for outer join. (Inner join is used only for matching records).
In our example the City Kokatta does not have any employees. As per left outer join concept, the all the rows from table which is in left hand side of keyword "left outer join " should come and the matching rows from right hand side of the keyword "left outer join " will come.

The second point is, we total number of employees for each city. To achieve this we must go for group by clause, so that we can get the group of employees with respect to city. The keyword count gives the total number of employees.

In this line I am using Left outer join between two tables based on EmpCity which is the common column between these two tables.

from dbo.city c  left outer join dbo.Employees e

on e.empCity = c.cityID

I have used alias name for City and Employees table to refer or get the columns of each table.

group by c.cityID,c.cityName

In above line I am grouping employees by citywise. Here the important point to be notes is I am grouping with cityId column of City table not with employee table. Because for non matching records of cityID of Employee's table will not have value.

In the following line I am soring by number of count using Orderby clause
order by COUNT(e.empCity) desc

Finally the select clause will return the result set.

select COUNT(e.empCity) as TotalNoofEmp,c.cityName 

The output will be

TotalNoofEmp	cityName

5 New Delhi
4 Chennai
3 Mumbai
0 Kolkatta

Asked In: In my last interview | Alert Moderator 

Comments or Responses

Login to post response

More Interview Questions by Nagasundar_Tn