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 got it to work, but thanks for your help. Used the following query:
SELECT DISTINCT faculty.name, faculty.salary / (
SELECT COUNT( student.studentid ) AS StudentPerTeacher
FROM student, faculty, courseoffering, gradereport
WHERE student.studentid = gradereport.studentid
AND gradereport.courseid = courseoffering.courseid
AND courseoffering.instructor = faculty.name ) AS "Ratio Per Student"
ORDER BY "Ratio Per Student" DESC