Employees Per Department

Twitter Interview Online Test SQL

A company uses 2 data tables, Employee and Department, to store data about its employees and departments.

Table Name: Employee

Attributes:

ID Integer,

NAME String,

SALARY Integer,

DEPT_ID Integer

Table Name: Department

Attributes:

DEPT_ID Integer,

Name String,

LOCATION String

View sample tables:

https://s3-us-west-2.amazonaws.com/aonecode/techblog/50cfcdd1d61f1bd6002cf4d3b4a61deb-min.jpeg

Write a query to print the respective Department Name and number of employees for all departments in the Department table (even unstaffed ones).

Sort your result in descending order of employees per department; if two or more departments have the same number of employees, then sort those departments alphabetically by Department Name.

https://www.careercup.com/question?id=6271976011202560

select d.Name, count(e.ID) as ID_COUNT
from Department d
left join Employee e
on e.DEPT_ID = d.DEPT_ID
group by d.Name
order by ID_COUNT desc, NAME