CareerCup

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..

The primary key for the first table should obviously be Emp_ID

select COUNT(*) from Emp, Dept where Emp.Emp_Name=Dept.Emp and Emp.Dept_ID=Dept.Dept_ID GROUP BY Dept_ID

This 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 has been mis-framed.
Table 1 - Emp_ID, Emp_Name, Dept_ID (foreign key)
Table 2 - Dept_Name, Dept_ID (primary key)
The solution to the question would be -
SELECT Dept_ID, count(Emp_ID)
FROM Table 1
GROUP BY Dept_ID ;

I think the question has been mis-framed.
Table 1 - Emp_ID, Emp_Name, Dept_ID (foreign key)
Table 2 - Dept_Name, Dept_ID (primary key)
The solution to the question would be -

SELECT Dept_ID, count(Emp_ID)
FROM Table 1
GROUP BY Dept_ID ;

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