Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5

Thread: help please!

  1. #1
    New to the CF scene
    Join Date
    Oct 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    help please!

    hi guys!
    Last edited by rawkas; 11-15-2012 at 07:37 PM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Quote Originally Posted by rawkas View Post
    hi guys, any chance with help on the following question? would be much appreciated!

    MySQL is a relational database management system. The data is structured as a set of tables linked by relations. Assume the following database has been setup for you.


    Properties
    propId

    postCode
    1

    CB1 4PY
    2

    CB2 9GH
    3

    CB23 4DG

    Persons
    perId

    propId

    firstName

    lastName
    1

    1

    Fred

    Bloggs
    2

    2

    Arthur

    Brown
    3

    1

    Mary

    Bloggs
    4

    4

    Nigel

    Wilson

    3.3.1 Write the SQL to list postcodes and names of people who live there.
    3.3.2 Write the SQL to list postcodes and people who live there (where applicable) for all properties regardless of whether the property is inhabited.
    3.3.3 Write the SQL to list people and postcodes (where applicable) for all people regardless of whether they live in a property that exists.
    3.3.4 Given values for postCode, firstName, lastName write the SQL statements to create suitable entries for each table. HINT you may need to use Last_INSERT_ID() - assume propId and perId columns have been defined as AUTO_INCREMENT.





    thanks alot!

    This hasn't a thing to do with PHP. Moving to MySQL forum. In the future please choose a better title for your threads.

    This is clearly homework. Please post your current SQL attempts and the problem you are currently seeing with it.
    Last edited by Fou-Lu; 11-15-2012 at 07:39 PM.

  • #3
    New to the CF scene
    Join Date
    Nov 2012
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Okay, I feel the need to chip in and help my associate present this question more clearly.

    This is how the question is supposed to look.


    We really do appreciate any help you can provide as we're both stumped. Thanks for your time.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    And that's fine, but where is the current SQL in use and the problem you are having with it?
    You can't just present us with a homework assignment and expect that we should do it for you. That would be called cheating; however we can help you fix existing code (or in this case queries) by walking through the problem and guiding you to the proper solution.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    And on top of that, if the database is PROPERLY DESIGNED and uses correct RELATIONAL INTEGRITY, then task 3.3.4 is impossible. For that matter, the data as presented there is impossible. So somebody is teaching you bad database design.

    Specifically, propid in the PERSONS table should be a foreign key to propid in the PROPERTIES table. And so the propid of 4 in the PERSONS table would be impossible in a correctly designed and implemented database.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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