Question

Create a query to list for each department, the name of the department head and the names of the department’s employees. Your query must list on each row of the result set the department name, the head’s last name, and the last name of each employee. Sequence your results by department name, and within department by employee last name. (MS ACCESS)

Answer 1

Assuming we have two tables as follows -

1. Table A with following fields -
   1. DEPT_NAME
   2. HEAD_LN
2. Table B with following fields -
   1. DEPT_NAME
   2. EMP_LN

For simplicity we are ignoring other fields in both tables. (Also in question details of tables are not provided)

Basically we need to inner join on two tables and then output the result and order by the department name followed by employee name.

So in order to list for each department, the name of the department head and the names of the department’s employees we can use following query :

select A.DEPT_NAME, A.HEAD_LN, B.EMP_LN
from A inner join B on A.DEPT_NAME = B.DEPT_NAME
order by A.DEPT_NAME, B.EMP_LN;