Re: SQL Select
Date: 1997/10/10
Message-ID: <343d7d9b.30893182_at_newshost>#1/1
Just to keep the thread going :) it was pointed out that:
>> select S.StudentId SID
>> from Student S, Taken T
>> where S.StudentId = T.StudentId
>> and T.ClassTitle in ( 'C1', 'C2', 'C3' )
>> group by S.StudentId
>> having count(*) = 3
would actually return the *wrong* answer to the question. If the student took course C1 3 times, it would incorrectly return that record. the 'right' query could be:
select S.StudentId SID
from Student S, Taken T
where S.StudentId = T.StudentId
and T.ClassTitle in ( 'C1', 'C2', 'C3' )
group by S.StudentId
having count(distinct t.ClassTitle) = 3
^^^^^^^^^^^^^^^^^^^^^
On Thu, 09 Oct 1997 13:58:55 -0700, Sunder Rajan <rajan_at_t-iii.com> wrote:
>A Single Select Statement to do this would be with a Corelated subquery.
>
>Select * From Student S
>Where &Num_Courses < (Select Count(*) From Taken
>Where SID = S.SID);
>
>Sunder Rajan.
>
>
>Thomas Kyte wrote:
>
>> On 8 Oct 1997 02:07:53 GMT, "Steve H" <steveh_at_redshift.com> wrote:
>>
>> >Thats really two different questions. To find out which students
have
>> >taken more than one course, this will work:
>> >
>> >select SID from
>> > (select S.StudentID SID, count(S.StudentID) x from STUDENT S,
TAKEN T
>> > where S.StudentID=T.StudentID
>> > group by S.StudentID)
>> > where x > 1
>> >
>> >To find which students have taken a specific set of 3 classes is a
little
>> >more complex and should probably be undertaken in procedural code
rather
>> >than trying to construct a single SQL query.
>> >
>>
>> Not really (it is perhaps less complicated from an amount of work to
>> perform
>> standpoint), expanding on your initial query just a little:
>>
>> >select SID from
>> > (select S.StudentID SID, count(S.StudentID) x from STUDENT S,
TAKEN T
>> > where S.StudentID=T.StudentID
AND T.ClassTitle in ( 'C1', 'C2', 'C3' )
>> > group by S.StudentID)
>> where x = 3
>>
>> which could also be written as
>>
>> select S.StudentId SID
>> from Student S, Taken T
>> where S.StudentId = T.StudentId
>> and T.ClassTitle in ( 'C1', 'C2', 'C3' )
>> group by S.StudentId
>> having count(*) = 3
>>
>> >You asked for "the most efficient" way to tackle the above 2
problems.
>> >That cannot be answered without knowing more details, for example:
>> >
>> > a. What columns are indexed?
>> > b. What is the cardinality of the data in the indexed columns?
>> > c. What optimizers are available?
>> > etc.
>> >
>> >
>> >
>> >Mark McCubbin <mccubbin_at_mich.com> wrote in article
>> ><34316AAE.3C99_at_mich.com>...
>> >> I need help with an SQL query to determine which students have
taken two
>> >> or more courses. For example, what is the most efficient way to
answer
>> >> the question: who has taken all three classes CS101, CS200, and
CS300?
>> >>
>> >> The tables are as follows (the relationship is one to many):
>> >>
>> >> STUDENT
>> >> StudentID
>> >> FirstName
>> >> LastName
>> >>
>> >> TAKEN
>> >> ClassTitle
>> >> StudentID
>> >>
>>
>> Thomas Kyte
>> tkyte_at_us.oracle.com
>> Oracle Government
>> Bethesda MD
>>
>> http://govt.us.oracle.com/ -- downloadable utilities
>>
>>
>> ---------------------------------------------------------------------------
>>
>> Opinions are mine and do not necessarily reflect those of Oracle
>> Corporation
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Oct 10 1997 - 00:00:00 CEST