The schema of the DB is as follows:
Department(DeptNum, Descrip, Instname, DeptName, State, Postcode)
Academic(AcNum, *DeptNum, FamName, GiveName, Initials, Title)
Field(FieldNum, ID, Title)
Interest(*FieldNum, *AcNum, Descrip)
I'm struggling with the conditions in the first part of the question.
This key to this query is the GROUP BY clause that can be used with a SELECT statement. The GROUP BY clause instructs the DBMS to combine rows of data based on the columns that are specified in the GROUP BY clause.
Consider the query:
SELECT * FROM interest
This query will return all of the rows from the interest table. There are lots of rows with the same FieldNum value. If the GROUP BY clause is added then we only get one row per unique FieldNum.
SELECT * FROM interest GROUP BY FieldNum
The power of GROUP BY is in the fact that you can aggregate the combined rows. The SELECT can show the number of rows that have the same FieldNum:
SELECT *, count(FieldNum) FROM ...
This solution provides a detailed example of how to use the GROUP BY clause with a SQL SELECT statement.