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)
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!

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 --
pivot on non-aggregates

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 :
That's still a pretty awkward interview question though.

mike, please get with the program --

CASE not IF

JOIN not WHERE

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

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.

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.

yes, that partickiller query was quite advanced

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.

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?

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.

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:

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.

"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



here's my article about hierarchies: Categories and Subcategories

enjoy

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.

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

I see where that could get problematic. Thanks for the explanation Rudy!