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).
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading