Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2009
    Posts
    6

    Unanswered: SQL sever: Multiple table query

    hi everyone...

    can anyone help me with this question - Ive tried to do part a:

    Consider the following table schemas:

    Pet(petId, petName, sex, petTypeId*)
    PetType(petTypeId, animalType, breed)
    Owner(ownerId, firstName, lastName, homePhoneNumber, streetAddress, suburb, postcode)
    PetAndOwner(ownerId*, petId*)


    in which the underlined attributes form the primary key and the starred attributes are foreign keys.

    [a] List all the names of the pet owners and their home phone numbers in the suburb 'SmithVille'.

    SELECT firstName, lastName, homePhoneNumber
    FROM Owner
    WHERE suburb = 'SmithVille';

    [b] List all the nicknames owners have given to their pet of type 'cat'.


    [c] List owner names and their phone numbers for those whose pet dogs have been named 'Sally'.


    can anyone help me out with these?? thanks!!

  2. #2
    Join Date
    Aug 2009
    Posts
    262
    home work ( <- too short to post as a reply )

  3. #3
    Join Date
    Aug 2009
    Posts
    262

  4. #4
    Join Date
    Nov 2009
    Posts
    6
    Quote Originally Posted by mishaalsy View Post
    home work ( <- too short to post as a reply )
    its actually a sample question... got a final exam in about 12 hrs

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by roosterman View Post
    its actually a sample question... got a final exam in about 12 hrs
    Same thing.

    Your answer to [a] looks fine. What have you come up with for [b] and [c]?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please also don't cross post questions.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    Your answer to [a] looks fine.
    not to me

    just because there's a row in the Owners table doesn't mean that person actually owns a pet

    i believe a join or subquery is required
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think if Dave Portas were here he would refer back to a discussion you once had regarding SQL & mandatory relationships.
    Yes you are right - there is no reason to assume that a row in the owner table necessarily represents the owner of a pet, though of course one might ask what that row is doing there if it does not.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Nov 2009
    Posts
    6
    could anyone help me out with B or C? im completely stuck

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes, but we won't do it for you. What have you got so far?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Nov 2009
    Posts
    6
    [b]

    SELECT animalType
    FROM petType
    WHERE breed = 'cat';

    [c]

    SELECT firstName, lastName. homePhoneNumber
    FROM Owner JOIN Pet
    WHERE petName = 'Sally';

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just to cover this off - is English your first language? I ask because 'cat' is not a breed.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Nov 2009
    Posts
    6
    no it actually isnt
    Last edited by roosterman; 11-17-09 at 06:54.

  14. #14
    Join Date
    Aug 2009
    Posts
    262
    a) select a.firstname,
    a.lastname,
    a.homephonenumber
    from owner a,
    petandowner b
    where a.ownerid=b.ownerid
    and a.suburb = 'SmithVille'

    b)

    select a.petname
    from pet a,
    pettype b,
    petandowner d

    where a.pettypeid=b.pettypeid
    and a.petid=d.petid
    and b.breed='cat'
    and d.ownerid in (select ownerid from owner)

    c)

    select a.firstname+' '+a.lastname as name,
    a.homephonenumber
    from owner a,
    pettype b,
    pet c,
    petandowner d

    where a.ownerid=d.ownerid
    and d.petid=c.petid
    and b.breed='DOG'
    and c.petname='Sally'



    its a horrible version of joins .. i am sleepy ... goodluck with your exams since these queries wont let you pass it .( but working . )


    so now work on your own ... use your brain ... google and study .

    and no i did not do your homework , .. just wanted to give you a kick start ,,,, if you feel what i mean


    editted at 3:25 am
    Last edited by mishaalsy; 11-17-09 at 06:35.

  15. #15
    Join Date
    Nov 2009
    Posts
    6
    thank you for your help, mishaalsy...i think im getting the hang of it now
    Last edited by roosterman; 11-17-09 at 06:56. Reason: BTW this is not homework...its a sample question

Posting Permissions

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