#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    6
    Rep Power
    0

    Help with mysql homework


    2. what is the complete command to create a primary
    key for the branch_services table? Assume the table has no
    primary key yet defined.

    here is the branch_services table
    is there anyway i can import this to my database?

    Table branches
    +------+-----+--------------------------------+------+
    | bid | cid | bdesc | bloc |
    +------+-----+--------------------------------+------+
    | 1011 | 101 | Corporate HQ | CA |
    | 1012 | 101 | Accounting Department | NY |
    | 1013 | 101 | Customer Grievances Department | KA |
    | 1031 | 103 | N Region HO | ME |
    | 1032 | 103 | NE Region HO | CT |
    | 1033 | 103 | NW Region HO | NY |
    | 1041 | 104 | Branch Office (East) | MA |
    | 1042 | 104 | Branch Office (West) | CA |
    | 1101 | 110 | Head Office | CA |
    +------+-----+--------------------------------+------+
    Table branches_services
    +------+-----+
    | bid | sid |
    +------+-----+
    | 1011 | 1 |
    | 1012 | 1 |
    | 1041 | 1 |
    | 1042 | 1 |
    | 1101 | 1 |
    | 1011 | 2 |
    | 1031 | 2 |
    | 1011 | 3 |
    | 1031 | 3 |
    | 1032 | 3 |
    | 1031 | 4 |
    | 1033 | 4 |
    | 1041 | 4 |
    | 1013 | 5 |
    | 1011 | 6 |
    | 1042 | 6 |
    +------+-----+
    Table clients
    +-----+--------------------+
    | cid | cname |
    +-----+--------------------+
    | 101 | Browns Realty |
    | 102 | Big O Tire |
    | 103 | The Tile Outlet |
    | 104 | Great Foods Inc |
    | 110 | The Best Pet Store |
    +-----+--------------------+
    Table services
    +-----+------------------+---------+
    | sid | sname | sfee |
    +-----+------------------+---------+
    | 1 | Accounting | 1500.00 |
    | 2 | Recruitment | 500.00 |
    | 3 | Data Management | 300.00 |
    | 4 | Administration | 500.00 |
    | 5 | Customer Support | 2500.00 |
    | 6 | Security | 600.00 |
    +-----+------------------+---------+
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    11
    is there anyway i can import this to my database?
    Not very easily, but the point of the homework is to come up with the statement, not to do it in phpmyadmin and copy/paste the result :-)
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by ch1p
    is there anyway i can import this to my database?
    piece of cake

    use the LOAD DATA INFILE command
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    11
    piece of cake
    Care to illustrate? :-)

    It would be easy if the data was CSV, but this is ASCII.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by Vinny42
    Not very easily, but the point of the homework is to come up with the statement, not to do it in phpmyadmin and copy/paste the result :-)
    Umm what ? All we have done are basic queries how in the world am I suppose to know how to assign a primary key. When the book has 30 different ways of doing it. Id appreciate if someome who is actually helpful could help.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    11
    Id appreciate if someome who is actually helpful could help.
    You haven't said what you need help with. You just post a bunch of tables and say that your homework is to create a PK for it.

    If your book has 30 ways to do it, which it doesn't because there are only two ways to do it, then tell me what you don't understand about it and I can tell you what they mean.

    Nobody is going to do your homework for you.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by Vinny42
    Care to illustrate?
    nope, not me, that's what da manual is for

    Originally Posted by Vinny42
    It would be easy if the data was CSV, but this is ASCII.
    how do you know?

    all we've seen so far is table displays
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    11
    how do you know?

    all we've seen so far is table displays

    Indeed, so what makes you say it's easy to load?
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by Vinny42
    Indeed, so what makes you say it's easy to load?
    experience
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    11
    experience
    Right...
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    piece of cake

    use the LOAD DATA INFILE command
    Errors

    http://pastebin.com/X5LrmsD5

    checked with ideone...

    http://ideone.com/Z70gTg
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by ch1p
    Errors
    you're gonna make us do the work?

    how easy would it have been to paste that text here instead of pasting it into pastebin

    anyhow, here's your problem --
    Code:
    CREATE TABLE branches_services; (bid INT(3), sid VARCHAR(8));
    the semicolon terminates the CREATE statement, and the rest of that is treated as garbage, i.e. that's where the error message actually says the error ocurred near

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

IMN logo majestic logo threadwatch logo seochat tools logo