2.13 Reporting the Size of a Set's Complement2.13.1 ProblemYou want to report the number of missing values for a set. As an example, assume that each student must submit four term papers for each course. Not all students have submitted all required papers, and you want to generate a report showing the number that each student has yet to submit for each course. 2.13.2 SolutionSELECT s.StudentName, s.CourseId, 4-COUNT(TermPaper) Missing FROM Students s GROUP BY s.StudentName, s.CourseId ORDER BY s.StudentName Using the sample data provided for this chapter, the query in this solution should return the following result: StudentName CourseId Missing ------------ --------- ----------- Andrew ACCN101 1 Andrew MGMT120 1 Bert ACCN101 1 Cindy ACCN101 2 Cindy MGMT120 1 2.13.3 DiscussionThis query is very straightforward. It's an aggregate query that summarizes results by course and student combination. The aggregate function COUNT(TermPaper) is used to count the number of papers a student submits for a course. That result is then subtracted from the required number ” four in this case ” and the result is the number of missing term papers. |