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.

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.

Hope that someone has an answer to my problem.


7 Years
Discussion Span
Last Post by svmvishnu

please try thus

select count(studentid), c.instructor,f.salary from gradereport as g inner join courseoffering as c on c.courseid = g.courseid inner join faculty as f on f.name = c.instructor group by f.name;

Edited by svmvishnu: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.