Using foreach in sql query, Object Oriented Database

Question

The following is a scheme for a databse in an object-oriented setting. Every relation becomes a collection of objects. A Student is a person and a Faculty is also a person. These are shown as directed edges labeled "isa". All other directed edges show reference attributes. Note that PreReq attribute in Course is a set of references.

This is the query I need to write: For each classification, list the number of students and average GPA. The query can not use constants, such as "Freshman". In the output, each tuple should consist of Classification, NumOfStudents, and AvgGPA.

I think I would be able to do this if I could use the constants. I think some kind of foreach would work for the classification, but I can not figure out how to do this. Note that the query should just be written using basic mysql syntax.

Answer 1

Try this:

SELECT Classification, COUNT(*), AVG(GPA) 
FROM Student 
GROUP BY Classification

It looks like there should be some foreign keys because the Person table might need to be joined, but I don't see them in the design. At least the group by and avg and count functions should get you going down the right path.