MarketRX Interview Question
Software Engineer / DevelopersThis was pretty easy question .. to make things a bit complicated the Interviewer can ask you , if say some department (for eg. dept 20 has no employees) ... Then the above query wont work .....
Then use Outer join in the Where clause ...
where emp.dept_id(+) = dept.dept_id ... Also note the position of the outer join , the most important part...
why would two tables be needed? Either table has Dept_ID, Emp_ID or Emp_Name shouldn't matter because we just need distinctive count
Would this work?
SELECT COUNT (DISTINCTIVE Emp_ID) FROM Emp GROUP BY Dept_ID
Assume Emp_ID is the primary key. Of course the fact that there's a second table with foreign key suggests some sort of JOIN but ...
I think the question is, for each dept, given by name, I want to know how many employees are in that department. As someone suggested above, a dept may not have any employees. In that case, I was to see a '0' against that department. The query would be
Select Dept_Name, Count(*) as NoOfEmpls
from
(
Select E.Emp_ID, D.Dept_ID, D.Dept_Name
from Employees E
rightjoin
Department D
On E.Dept_ID=D.Dept_ID
)
By doing a rightjoin you are ensuring that even if a department doesn't have employees, it would still be counted.
Rajesh
Since the Dept_Id is the primary key in the first table ,which is quite bizzare....it makes sense that the each dept has one employee..
- Anonymous February 11, 2007