Hi,

I’m going to calculate a ratio between two entities but are having some trouble with the query.

The principal is the same to, say a forum, where you say: A user gets points for every new thread. Then, calculate the ratio of points for the number of threads.

Example: User A has 300 points. User A has started 6 thread. The point ratio is: 50:6

My schemas look as following:

student(studentid, name, class, major)

course(courseid, coursename, department)

courseoffering(courseid, semester, year, instructor)

faculty(name, office, salary)

gradereport(studentid, courseid, semester, year, grade)

The relations is a following:

Faculity(name) = courseoffering(instructor)

Student(studentid) = gradereport (studentid)

Courseoffering(courseid) = course(courseid)

Gradereport(courseid) = courseoffering(courseid)

I have this query to select the faculty names there is teaching one or more students:

SELECT COUNT(faculty.name) FROM faculty, courseoffering, gradereport, student WHERE faculty.name = courseoffering.instructor AND courseoffering.courseid = gradereport.courseid AND gradereport.studentid = student.studentid

My problem is to find the ratio between the faculty members salary in regarding to the number of students they are teaching.

Say, a teacher get 10.000 in salary and teaches 5 students, then his ratio should be 1:5.

I hope that someone has an answer to my problem and understand what I'm having trouble with.

Thanks

Mestika