Re: sql views for denomalizing

On 30 Jul 2005 15:18:58 -0700, "Marshall Spight" <marshall.spight_at_gmail.com> wrote:

>dawn wrote:
>>
>> Querying for all students currently taking a math course who are not
>> math majors is non-trivial in SQL (due to the negation along with
>> multiple 1 to many relationships, if I'm thinking clearly) and would be
>> trivial in both datatrieve and pick:
>>
>> list students with courseDept = "Math" and with every major <> "Math"
>
>Ha! I've been trying to get examples of the value of MV out of
>you for ages, and at last you slip up and reveal one!
>
>Let me make sure I understand this: students have many courses;
>courses have a courseDept; students have many majors; majors
>is a string. Yes? So if we represented this in SQL, we'd use
>three tables, Students, Courses (with FK to Students, and with a
>courseDept) and Majors (with FK to Students, and major.) The
>Pick version of this would have a multivalued attribute major
>inside Students, and also a multivalued attribute inside Student
>that listed the courses that student was taking. Please correct
>or confirm.
>
>This seems like a really good example query to study.

     My try at the SQL version is:

     select * from students
     where
      students.studentnumber in
       (select studentnbr from courses where
       coursedept="Math"
       and
       "Math" not in
        (select major from studentmajors
        where courses.studentnbr=studentmajors.studentnbr))

Sincerely,

Gene Wirchenko Received on Mon Aug 01 2005 - 19:20:29 CEST