As you suggested, related tables are related through their primary and foreign keys. In order to retrieve related data, we simply join the tables on all the columns in their keys:
select dept.DVID as dept_division , dept.DPID as dept_id , sect.SCID as section_id , sect.SCNAME as section_name , count(EMPNO) as staff_count from DEPARTMENT as dept inner join SECTION as sect on dept.DVID = sect.DVID and dept.DPID = sect.DPID left outer join EMPLOYEE as empl on sect.DVID = empl.DVID and sect.DPID = empl.DPID and sect.SCID = empl.SCID group by dept.DVID , dept.DPID , sect.SCID , sect.SCNAME
For additional examples involving these tables, see Composite foreign keys (13 September 2002).
This was first published in September 2005