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
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)
CourseNo Instructor1 Instructor2
0 Edward Yourdon
1 Edward Dijkstra Nicholas Wirth
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.
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
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.
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.
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.
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.
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:
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.