sql query .. confused

Question

I am a newbie and i am not sure how to go about this .. can anybody help me ?

Given the following tables ...

DEPARTMENT

DEPT_ID     NAME 
1   HR 
2   Technical 
3   Marketing 
4   Sales 

EMPLOYEE

ID  NAME    DEPT_ID     SALARY  MANAGER_ID 
1   Alex    2   2000    
2   Sally   1   2000    
3   Amit    2   1500    1 
4   Jason   1   1500    1 
...     ...     ...     ...     ... 

Using the DEPARTMENT and EMPLOYEE tables from earlier, write a SQL query to print the number of employees working in each department. The output should look something like this:

NAME    COUNT 
HR  2 
Technical   2 
Marketing   0 
Sales   0 

Please note that not all departments have been staffed yet, so some departments may not have any employees. We still want these departments to appear in the results, with a zero count.

Using the EMPLOYEE table from earlier, write a SQL query to print out the number of employees working under each manager. The output should look something like this:

NAME    COUNT 
Alex    2 
Sally   0 

Answer 1

Try this it may help you :

a)

 select d.Name as DepartmentName, count(e.id) as EmployeeCount from employee as e, department as d where e.DEPT_ID = d.DEPT_ID group by d.Name;

b)

select a.name as Manager_Name,Count(a.name) as EmployeeCount from employee a, employee b where a.id=b.MANAGER_ID  group by a.name;

Answer 2

Solution to the first part of the problem:

select d.NAME, count(e.NAME)
from EMPLOYEE as e
right join DEPARTMENT as d
on e.DEPT_ID = d.DEPT_ID
group by d.DEPT_ID

Solution to the second part of the problem (I'm not sure whether this one is correct or not):

select NAME, count(NAME)
from EMPLOYEE 
group by MANAGER_ID

Please check the sqlfiddle: http://sqlfiddle.com/#!2/5e802/13

Answer 3

You need to join the tables, group by department, and take the count of rows per department.
Try This Link