Help with SQL query (Calculate a ratio between two entitiess)
- by Mestika
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
