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 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
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading