Some faculty are grumbling that certain professors have an easy teaching load, defined as only a...

 

Transcribed Image Text:

Some faculty are grumbling that certain professors have an easy teaching load, defined as only a few courses with low capacity, while others have a heavy load of many courses with high seat counts. Run a query to help answer this question. Your output should contain the faculty ID, faculty name (first and last-it's fine to have this as two separate fields, also fine to concatenate together), the largest capacity class the faculty member is assigned to teach, the maximum possible total student load (sum of all the course capacities), and the number of classes the faculty member is teaching. Include all faculty members, even if they are not assigned to teach any courses. Include courses only if they are taught by a faculty member. Sort your output ascending by the number of courses taught, with the professors with the fewest courses at the top. For example, if you had the following input data (the blank row at the bottom means Professor Poldark did not teach anything): Faculty ID 1 1 1 1 2 2 3 Faculty Name Michael Mouse Michael Mouse Michael Mouse Michael Mouse Nancy Drew Nancy Drew Ross Poldark Term Fall Spring Summer Summer Fall Spring Class Ears for the Novice Ears for the Novice Comedic Voices Calculus II Calculus I Calculus I Class capacity 50 20 10 20 30 10 Your desired out- put would be something like this: Faculty ID Faculty Name 3 Ross Poldark 2 Nancy Drew 1 Michael Mouse Max capacity Some indica- tion he did not teach anything 30 50 Sum of capacity Some indication he did not teach any- thing 40 100 How many classes Some indication he did not teach anything 2 4 Some faculty are grumbling that certain professors have an easy teaching load, defined as only a few courses with low capacity, while others have a heavy load of many courses with high seat counts. Run a query to help answer this question. Your output should contain the faculty ID, faculty name (first and last-it's fine to have this as two separate fields, also fine to concatenate together), the largest capacity class the faculty member is assigned to teach, the maximum possible total student load (sum of all the course capacities), and the number of classes the faculty member is teaching. Include all faculty members, even if they are not assigned to teach any courses. Include courses only if they are taught by a faculty member. Sort your output ascending by the number of courses taught, with the professors with the fewest courses at the top. For example, if you had the following input data (the blank row at the bottom means Professor Poldark did not teach anything): Faculty ID 1 1 1 1 2 2 3 Faculty Name Michael Mouse Michael Mouse Michael Mouse Michael Mouse Nancy Drew Nancy Drew Ross Poldark Term Fall Spring Summer Summer Fall Spring Class Ears for the Novice Ears for the Novice Comedic Voices Calculus II Calculus I Calculus I Class capacity 50 20 10 20 30 10 Your desired out- put would be something like this: Faculty ID Faculty Name 3 Ross Poldark 2 Nancy Drew 1 Michael Mouse Max capacity Some indica- tion he did not teach anything 30 50 Sum of capacity Some indication he did not teach any- thing 40 100 How many classes Some indication he did not teach anything 2 4

Answer rating: 100% (QA)

Answer SELECT FFacultyID CONCATFFirstName FLastName AS FacultyNa... View the full answer