/*select d.dept_no, dept_name, title, count
from departments as d
right outer join
(select dept_no, title, count(title) as count
from titles as t left outer join dept_emp as d
on t.emp_no = d.emp_no
group by dept_no,title) as dt
on d.dept_no = dt.dept_no
order by dept_no, title;
*/
select b.dept_no, a.dept_name, c.title, 
count(c.title)
from departments as a
right outer join dept_emp as b on a.dept_no = b.dept_no
left outer join titles as c on b.emp_no = c.emp_no
where title is not NULL
group by b.dept_no, c.title
order by b.dept_no, c.title;

差不多的方法