Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: SQL Assignment help

    Hi
    I have a question I need to answer for an assignment.
    Here is the databse schema:
    Code:
    LIKES(drinker,beer);
    FREQUENTS(drinker,bar);
    SERVES(bar,beer,cost);
    The question is:
    Code:
    Find bars that serve at least one beer liked by Joe, but do not serve any beer liked by
    Michael.
    Here is what I have:
    Code:
    SELECT S.bar
    FROM LIKES L, SERVES S
    WHERE L.beer = S.beer AND L.drinker='Joe'
    EXCEPT 
    SELECT S.bar
    FROM LIKES L , SERVES S
    WHERE L.beer = S.beer AND L.drinker='Michael'
    Does this look ok? I am not sure if this enforces the 'at least one beer' constraint.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that looks okay to me

    what happened when you tested it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2011
    Posts
    3
    Hi
    I haven't tested it as its a written assignment.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    gee, that's too bad

    couldn't you just test it anyway?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2011
    Posts
    3
    ok, just tested it in pgAdmin and it seems to work fine. I'll do some more checks now.

    If I have to implement a constraint such as the bar has to serve at least 2 beers they like how would I do that?

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's not technically a constraint, it's just a business rule. Constraint has a different meaning in DB-land so it might be confusing to say it that way.


    Anywho, have a look at GROUP BY, COUNT and HAVING. You need to use those to satisfy the requirements given.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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