Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2009
    Posts
    3

    Unanswered: Rewriting an assertion using EXCEPT

    Hi,

    I am taking my first DB course and I need to rewrite the following asserting using the EXCEPT statement

    Code:
    CREATE ASSERTION NoEmptyCourses
        CHECK (NOT EXISTS (
                      SELECT * FROM Teaching T
                      WHERE
                         NOT EXISTS (
                         SELECT * FROM Transcript R
                         WHERE T.CrsCode = R.CrsCode
                             AND T.Semester = R.Semester)
                 ))
    Here is what I got:
    Code:
    Check ( Exists (
                   Select CrsCode, Semester From Teaching
                   Except
                   Select CrsCode, Semester From Transcript
             ))
    So it will check the existence of the course offering at some semester but nobody register in it (not in any student's Transcript).

    Am I right? Thank you in advance for pointing out my mistakes.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That won't work in T-SQL. Are you taking your course in SQL Server, or are you learning ISO\ANSI SQL?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    In sql use any one of these

    Select CrsCode, Semester From Teaching t
    WHERE NOT EXISTS (
    Select * From Transcript WHERE CrsCode = t.CrsCode and Semester = t.Semester)

    Select CrsCode, Semester From Teaching t
    WHERE CrsCode not in(Select CrsCode From Transcript)
    and Semester not in(Select Semester From Transcript)


    Select t.CrsCode, t.Semester From Teaching t
    left join transcript ts on ts.CrsCode = t.CrsCode and ts.Semester = t.Semester
    where
    ts.Semester IS NULL and ts.CrsCode IS NULL

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There's nothing wrong with the use of the EXCEPT operator, it is the ASSERTION that is the problem.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2009
    Posts
    3
    Quote Originally Posted by pootle flump
    That won't work in T-SQL. Are you taking your course in SQL Server, or are you learning ISO\ANSI SQL?
    Sorry, I didn't mention what course I am taking. I am taking the course in SQL.
    Thanks.

  6. #6
    Join Date
    Feb 2009
    Posts
    3
    Quote Originally Posted by bklr
    In sql use any one of these
    ...
    Thanks. But I have to use the EXCEPT.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    huge-ass hint:

    SELECT FROM Teaching
    EXCEPT
    SELECT FROM Transcript


    and next time, if you have an "SQL" homework problem, please do not post in a database-specific forum like Microsoft SQL Server (which, as you've seen, doesn't support standard SQL), try posting in the ANSI SQL forum instead

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Be careful. Your EXCEPT query won't behave quite like the EXISTS query in the presence of NULLs. You didn't specify whether the columns were nullable or not. If your tutor didn't tell you that then he was careless because it makes all the difference.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That is an excellent point, although I suspect in this case these columns are at least part of the composite PKs.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •