Re: Advice on SQL and records

<fran_beta_at_hotmail.com> wrote in message news:1124611376.749896.208510_at_o13g2000cwo.googlegroups.com...
> I'm teaching in computing. A question appeared in a trial paper
> recently and I'm trying to establish whether the suggested answer is
> correct.
>
> The question was:
>
> SELECT Students.Surname, Students.Name
> FROM Courses, Classes, StudentClasses, Students
> WHERE Courses.CourseID="10MA"
>
> AND
> Courses.CourseID=Classes.CourseID
> AND
> Classes.ClassID=StudentClasses.ClassID AND
>
> StudentClasses.StudentID=STudents.StudentID
>
> ORDER BY Students.Surname, Students.Name
>
>
> Which of the tables is likely to contain the most records?
>
> The suggested answer from Courses; Classes; StudentClassses and
> Students was:
>
> StudentClasses
>
> How would this be determined? Why wouldn't Students contain the most
> records?
>
> Thanks
>
> Fran
>

Assuming that the database contains only data about students that are currently taking courses, there will be at least one row in StudentClasses for each row in Student. Every student that's taking more than one course will have additional rows in StudentClasses. So Student classes has more rows.

A similar argument applies to Classes versus StudentClasses. Since every class has at least one student, and most have more, there are more rows in StudentClasses than in Classes.

There are likely to be more rows in Classes than in Courses, unless there are a lot of courses that are currently not being taught.

A better question might be whether the optimizer can use the (approximate) cardinality of the tables, at the time of the query, in addition to other cues, to pick the best strategy for performing the query. Received on Mon Aug 22 2005 - 10:40:46 CEST