Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Unanswered: A join and result set on a single row?

    I ran across this interview question and am a bit stumped on exactly how to answer it. Here is the question:

    Say you have a table layout like the following

    Name
    CourseNo

    CourseName

    Name
    CourseNo

    Instructor

    Create a query which will print the all the courses and if one instructor is present display their name, if two instructors are present print both of their names on the row in sorted order, if more than two instructors are present instead of the instructors names display “committee“.

    For instance your output would look something like this (Example provided)
    Code:
    CourseNo 	Instructor1 		Instructor2 
    0 		Edward Yourdon 	
    1 		Edward Dijkstra 	Nicholas Wirth 
    2 		Comittee
    How is it possible to have multiple instructors on the same line as in CourseNo 1 and how is it possible to echo "Committee" if there is more than 1 instance of an instructor? I can do this with application code but I don't see how this is possible with sql.

    Can someone shed some light on this for me?

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you would use CASE expressions when joining the tables, then an outer query to aggregate the individual rows to a single result row per course

    more or less just like in this example --
    http://www.dbforums.com/showthread.php?t=1636008
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I think splitting the instructors into 2 fields makes it more difficult. If you kept the list of instructors as one field then you could do something like :
    Code:
    select c.CourseNo, 
           if( count(*) > 2,
               "Comitee",
               group_concat( i.Instructor order by i.Instructor ) ) as Instructors
    from  Course c, CourseInstructor i
    where c.CourseNo = i.CourseNo
    group by c.CourseNo
    That's still a pretty awkward interview question though.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mike, please get with the program --

    CASE not IF

    JOIN not WHERE

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

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    chill out dude

    It was meant to show a direction for Frank to head in rather than be fully supported production code. As such I think it would of been fine as an answer during an interview - I certainly wouldn't mark people down for such things. My post was meant as a help to Frank to answer similar questions if they reappear - if it's not good enough for you Rudy then, quite honestly, I couldn't care less

  6. #6
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Rudy and Mike, thank you both for your input. The link you provided was great Rudy, thanks!

    This is what the candidate answered for that question in case anyone wants to know.

    Again, my background is not in database design or DBA, but I know basic SQL queries. This would be a trivial answer once I consult the SQL reference books. I believe I would use a combination of SELECT … FROM … WHERE COUNT … and SELECT … FROM … WHERE COUNT …. ORDER BY
    At least I knew that there was some sort of an IF conditional that was needed to echo "Committee" and that was what threw me off. I have never used CASE before but I am going to write some for the heck of it to learn.

    Are these sort of queries expected for an entry level position? I thought is was just a bit advanced, personally.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, that partickiller query was quite advanced
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Hope I'm not going off topic here but I figured I would post question number 2 from this test. It has to do with data modelling. If anyone thinks it should be moved, feel free.

    Quote Originally Posted by Prospective Employer
    How do you represent a tree, such as a directory structure where a node can have many children and each child can also have children, in a relational database? Are there any issues with this implementation vs. one where you will know there will only be a fixed number of a few, say 3-4, different levels in the tree?
    Is he referring to an entity or table when he talks about a node? I also have no idea what he is talking about in the last sentence.

    I can visually picture what he is saying which I believe to be a parent and it's child tables and each child table would be a parent to other child tables.

    What is this guy driving at?

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    You normally have one table that holds the whole structure with a field that points to it's parent record. There might be a type field to say what type of record is being held. The data for the child records may, or may not, be held as fields in the same table. There are other ways of storing hierarchies in the database as Rudy and Blindman showed me in this thread.

    Having separate tables to hold each level means it's more difficult to expand the structure and you'd normally need more code to support this type of structure but at least you don't have to navigate the whole tree to find the parents. You could try setting up a table with your family tree using all the methods mentioned and trying to write queries to find the grandfather of any person or all those under a particular person.

    When he mentions there might be only a few levels then he might be suggesting it may be better to store the whole hierarchy, say a grandfather_id and father_id, as separate fields in your normal records.

  10. #10
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Hey Mike,

    Thanks for the link and the explanation. I found one of Rudy's posts in the link that you posted that made a lot of sense to me. he said:

    Quote Originally Posted by r937
    i recently did a consulting job for someone with a product inventory that involved multiple sizes and colours and so forth, and he had a "hieararchy" as well

    you would not believe the complexity when this hits the queries

    my sincere advice: do not bother with the hierarchy

    in the real world, every different variation has its own "stock keeping unit" and barcode (do a search for GTIN and GS1)

    keep your database simple and your queries will also be simple
    What I am understanding from Rudy's post is that instead of using a hierarchical tree and gaining a lot of complexity, just design the database to use "stock keeping" and "barcode" instead; whatever that may entail.

    My feeling is that development is hard enough so why complicate things if we can keep it simple, especially when we have another option available to us that can offer the same bottom line.

    Maybe this company giving this sort of test is not doing "real world" development in which case I would just want to keep looking for a different employer. This is very interesting stuff though and I wouldn't mind seeing some DDL for this type of design.


  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fjm1967
    Is he referring to an entity or table when he talks about a node?
    "node" refers to an entry in a hierarchy

    if it's at the top of a path (has no parent), it's usually called a "root node"

    if it's at the bottom (has no children), it's called a "leaf node"

    as to why a tree is almost always drawn with the root at the top and the leaves at the bottom, i have no idea



    Quote Originally Posted by fjm1967
    This is very interesting stuff though and I wouldn't mind seeing some DDL for this type of design.
    here's my article about hierarchies: Categories and Subcategories

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

  12. #12
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    The "tree" structure is something is very familiar to me given my UNIX background. I never really thought about a tree growing from the root, up.

    I looked at the lesson you provided (Excellent by the way, thanks). To me, that is dead simple.

    I am curious Rudy.. Where is the complexity you are talking about in the queries? I see a lot of joins but is not anything that I am not already doing with a normalized dataset.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the complexity was in the other thread, where buddy wanted to have sizes and colours and styles all represented in a tree

    without looking back at the thread, it was something like

    mens shirts
    + large
    -- blue
    -- white
    -- striped
    + medium
    -- blue
    -- white
    -- striped
    + small
    -- blue
    -- white
    -- striped

    that was sizes and colours, now imagine styles factored into the tree -- french cuffs, button down collar, etc.

    dat hierarchy be a mess

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

  14. #14
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    I see where that could get problematic. Thanks for the explanation Rudy!

Posting Permissions

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