Exercises in t-SQL
-
ScottPletcher (12/4/2012)
It would take far longer than 2 minutes to give the full background you wanted.This for a simple, introductory interview q, when I often had two more people to interview the same day.
Just not practical. If that simple a q requires 30 minutes of background detail to get an answer, I'd hate to even have to bring up replication:
"I'm doing merge replication and ...". It could take hours to fill in all the background on the whys/wherefores of that. Realistically there just isn't time.
"Why merge and not transactional? What if a site changes locations?" And so on. Impossible to do all that in the space of an interview for every q.
Without other direct knowledge, I'd believe the person probably couldn't answer the q. Typically introducing such irrelevancies is just a way to deflect the q.
If it takes more than 2 minutes to explain that you want Mode, Mean, Median, or some some other method of handling values that are not the Min nor the Max, then I'd say that you've flunked your own interview or urgent fix, which ever is the case.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (12/4/2012)
ScottPletcher (12/4/2012)
It would take far longer than 2 minutes to give the full background you wanted.This for a simple, introductory interview q, when I often had two more people to interview the same day.
Just not practical. If that simple a q requires 30 minutes of background detail to get an answer, I'd hate to even have to bring up replication:
"I'm doing merge replication and ...". It could take hours to fill in all the background on the whys/wherefores of that. Realistically there just isn't time.
"Why merge and not transactional? What if a site changes locations?" And so on. Impossible to do all that in the space of an interview for every q.
Without other direct knowledge, I'd believe the person probably couldn't answer the q. Typically introducing such irrelevancies is just a way to deflect the q.
If it takes more than 2 minutes to explain that you want Mode, Mean, Median, or some some other method of handling values that are not the Min nor the Max, then I'd say that you've flunked your own interview or urgent fix, which ever is the case.
That's quite different from your original q, but no more relevant.
Take it just for what it is: an interview q. It's a purposeful q. There's no need to gratuitously insult me by any implication that I don't know that most production applications are not hard-coded to 3 values. Presumably any two beyond-neophyte database people would know that.
So the q must have a different purpose. If you choose to pursue a pointless deadend from it just for the sake of "going CELKO" ("you've 'violated' a 'standard', it's the equivalent of punch cards!"), rather than just answer the q (with perhaps a note to yourself to bring it up at the "open q" at the end), then, yes, "NEXT CANDIDATE".
I was at International Paper for more than 10 years. We provided secondary SQL Server support for mills throughout the country. There was never a DBA on site at a mill. Generally a computer professional but not always. When they called up for support, oddly enough they never seemed to want a pointless diatribe about whatever table structure they happened to have but a solution to their problem. AFTER the problem is resolved you can work with them to prevent future issues, if necessary.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
ScottPletcher (12/5/2012)
Jeff Moden (12/4/2012)
ScottPletcher (12/4/2012)
It would take far longer than 2 minutes to give the full background you wanted.This for a simple, introductory interview q, when I often had two more people to interview the same day.
Just not practical. If that simple a q requires 30 minutes of background detail to get an answer, I'd hate to even have to bring up replication:
"I'm doing merge replication and ...". It could take hours to fill in all the background on the whys/wherefores of that. Realistically there just isn't time.
"Why merge and not transactional? What if a site changes locations?" And so on. Impossible to do all that in the space of an interview for every q.
Without other direct knowledge, I'd believe the person probably couldn't answer the q. Typically introducing such irrelevancies is just a way to deflect the q.
If it takes more than 2 minutes to explain that you want Mode, Mean, Median, or some some other method of handling values that are not the Min nor the Max, then I'd say that you've flunked your own interview or urgent fix, which ever is the case.
That's quite different from your original q, but no more relevant.
Take it just for what it is: an interview q. It's a purposeful q. There's no need to gratuitously insult me by any implication that I don't know that most production applications are not hard-coded to 3 values. Presumably any two beyond-neophyte database people would know that.
So the q must have a different purpose. If you choose to pursue a pointless deadend from it just for the sake of "going CELKO" ("you've 'violated' a 'standard', it's the equivalent of punch cards!"), rather than just answer the q (with perhaps a note to yourself to bring it up at the "open q" at the end), then, yes, "NEXT CANDIDATE".
I was at International Paper for more than 10 years. We provided secondary SQL Server support for mills throughout the country. There was never a DBA on site at a mill. Generally a computer professional but not always. When they called up for support, oddly enough they never seemed to want a pointless diatribe about whatever table structure they happened to have but a solution to their problem. AFTER the problem is resolved you can work with them to prevent future issues, if necessary.
BWAAA-HAAA!!! Listen to you! I'm not the one that started this, Scott.
I've also not called you any names and I am trying to have an intelligent conversation. You've called me arrogant, accused me of "going Celko" on you (which isn't always a bad thing, contrary to popular belief), and have accused me of not understanding the urgency or nature of a production problem or that such an obviously oesoteric sounding interview question wasn't born from an actual problem (they almost always are). You were the one that brought up that the interview question was based on a production problem and I'm saying that there's no difference. To keep from having to do it at 3 in the morning again, a couple of simple questions need to be asked and it only takes 2 minutes to figure out if something can be made more bullet proof or not and all I get back from you is insults.
Further and as poorly worded as they may have done it but are still trying to help YOU, people like Celko have suggested that if the requirement is to have only 3 or less "marks" to draw from, that the logical and correct thing to do would be to have some form of constraint to guarantee that requirement. In an interview, I would give extra points for the candidate that would point that out. In the production world, I'd take out the two minutes to make absolutely sure they wanted to guarantee only 3 marks or if they wanted something with more scale to prevent from having to fix the code yet again when requirements change, fix the production problem according to that two minute conversation, do a check to make sure that none of the items had more than 3 marks, and then add such a constraint.
And I'd do it all without calling someone arrogant or any of the other things you've insinuated. 😉
And, no... I wasn't personnally trying to imply you didn't know better than to require 3 items without some form of constraint. I'm suggesting that you shouldn't penalize an interviewee nor an employee for asking such questions. I'm also suggesting that I'd walk away from an interview if I was subjected to all that you've dished out on this thread during an interview.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (12/5/2012)
ScottPletcher (12/5/2012)
Jeff Moden (12/4/2012)
ScottPletcher (12/4/2012)
It would take far longer than 2 minutes to give the full background you wanted.This for a simple, introductory interview q, when I often had two more people to interview the same day.
Just not practical. If that simple a q requires 30 minutes of background detail to get an answer, I'd hate to even have to bring up replication:
"I'm doing merge replication and ...". It could take hours to fill in all the background on the whys/wherefores of that. Realistically there just isn't time.
"Why merge and not transactional? What if a site changes locations?" And so on. Impossible to do all that in the space of an interview for every q.
Without other direct knowledge, I'd believe the person probably couldn't answer the q. Typically introducing such irrelevancies is just a way to deflect the q.
If it takes more than 2 minutes to explain that you want Mode, Mean, Median, or some some other method of handling values that are not the Min nor the Max, then I'd say that you've flunked your own interview or urgent fix, which ever is the case.
That's quite different from your original q, but no more relevant.
Take it just for what it is: an interview q. It's a purposeful q. There's no need to gratuitously insult me by any implication that I don't know that most production applications are not hard-coded to 3 values. Presumably any two beyond-neophyte database people would know that.
So the q must have a different purpose. If you choose to pursue a pointless deadend from it just for the sake of "going CELKO" ("you've 'violated' a 'standard', it's the equivalent of punch cards!"), rather than just answer the q (with perhaps a note to yourself to bring it up at the "open q" at the end), then, yes, "NEXT CANDIDATE".
I was at International Paper for more than 10 years. We provided secondary SQL Server support for mills throughout the country. There was never a DBA on site at a mill. Generally a computer professional but not always. When they called up for support, oddly enough they never seemed to want a pointless diatribe about whatever table structure they happened to have but a solution to their problem. AFTER the problem is resolved you can work with them to prevent future issues, if necessary.
BWAAA-HAAA!!! Listen to you! I'm not the one that started this, Scott.
I've also not called you any names and I am trying to have an intelligent conversation. You've called me arrogant, accused me of "going Celko" on you (which isn't always a bad thing, contrary to popular belief), and have accused me of not understanding the urgency or nature of a production problem or that such an obviously oesoteric sounding interview question wasn't born from an actual problem (they almost always are). You were the one that brought up that the interview question was based on a production problem and I'm saying that there's no difference. To keep from having to do it at 3 in the morning again, a couple of simple questions need to be asked and it only takes 2 minutes to figure out if something can be made more bullet proof or not and all I get back from you is insults.
Further and as poorly worded as they may have done it but are still trying to help YOU, people like Celko have suggested that if the requirement is to have only 3 or less "marks" to draw from, that the logical and correct thing to do would be to have some form of constraint to guarantee that requirement. In an interview, I would give extra points for the candidate that would point that out. In the production world, I'd take out the two minutes to make absolutely sure they wanted to guarantee only 3 marks or if they wanted something with more scale to prevent from having to fix the code yet again when requirements change, fix the production problem according to that two minute conversation, do a check to make sure that none of the items had more than 3 marks, and then add such a constraint.
And I'd do it all without calling someone arrogant or any of the other things you've insinuated. 😉
And, no... I wasn't personnally trying to imply you didn't know better than to require 3 items without some form of constraint. I'm suggesting that you shouldn't penalize an interviewee nor an employee for asking such questions. I'm also suggesting that I'd walk away from an interview if I was subjected to all that you've dished out on this thread during an interview.
My suggested solution did indeed check to see whether more than 3 tests were present, which is what I would hope to see from someone (although I was pilloried for that suggestion as well, with the standard implication about not knowing how to design a system). I believe that's a far more appropriate check for this query, and it adds no overhead (or so little I'd bet it's virtually unmeasurable).
It's a simple interview q. That's it. I really do think it would be bizarre to nitpick it in an interview.
Based on that, I can only imagine how you'd react to the certification qs/tests. I'm sure they have quite a few hanging threads there too.
But the point of either type of q isn't to simulate a perfect production environment. It's to test a specific area of knowledge. And to gauge the appropriateness of the answer. And perhaps to see if you can think on your feet well enough in a situation that is not perfect w/o having to make it perfect first.
I would expect someone experienced in SQL to recognize the q for what it is: to see how you use GROUP BY and related functions. And to do a little creative thinking to get the middle value: I've been told several times by responders that the middle test value "can't be determined" with the query restrictions given; I can actually see why that is said, because until you think of that specific approach, it's not apparent how you will be able to derive it!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
I understand where you're coming from. And, I do agree with you that it's a simple test for someone that actually does know T-SQL as well as we'd like them to. I guess I just don't agree with what started this whole conversation to begin with. I wouldn't actually tell someone that they had to do it in a single query. If they're good, they'll do one of two things... either do it in a single query, or do it in a multistep query that might just rock the world... or not. 🙂
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (12/4/2012)
So far as no offer being extended, that's fine because I don't want to work for a company that wants a code monkey.I'll take that job. And you're invited at any time to call me a monkey for doing so. Doing honest work for a living is nothing to be ashamed of.
-
patrickmcginnis59 (12/6/2012)
Jeff Moden (12/4/2012)
So far as no offer being extended, that's fine because I don't want to work for a company that wants a code monkey.I'll take that job. And you're invited at any time to call me a monkey for doing so. Doing honest work for a living is nothing to be ashamed of.
I agree.... nothing to be ashamed of. But, if you find are problem with an assignment, would you speak up of write the code exactly as told? If someone does that, then they should be ashamed.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (12/6/2012)
patrickmcginnis59 (12/6/2012)
Jeff Moden (12/4/2012)
So far as no offer being extended, that's fine because I don't want to work for a company that wants a code monkey.I'll take that job. And you're invited at any time to call me a monkey for doing so. Doing honest work for a living is nothing to be ashamed of.
I agree.... nothing to be ashamed of. But, if you find are problem with an assignment, would you speak up of write the code exactly as told? If someone does that, then they should be ashamed.
I'm not going to attach any sort of moral value to Scott's desiring the solution to be a single statement, as far as I know his request is perfectly legitimate and I would hunker down and give it a shot. Otherwise, I couldn't answer your question unless I knew what you considered to be a problem.
-
patrickmcginnis59 (12/6/2012)
Jeff Moden (12/6/2012)
patrickmcginnis59 (12/6/2012)
Jeff Moden (12/4/2012)
So far as no offer being extended, that's fine because I don't want to work for a company that wants a code monkey.I'll take that job. And you're invited at any time to call me a monkey for doing so. Doing honest work for a living is nothing to be ashamed of.
I agree.... nothing to be ashamed of. But, if you find are problem with an assignment, would you speak up of write the code exactly as told? If someone does that, then they should be ashamed.
I'm not going to attach any sort of moral value to Scott's desiring the solution to be a single statement, as far as I know his request is perfectly legitimate and I would hunker down and give it a shot. Otherwise, I couldn't answer your question unless I knew what you considered to be a problem.
Forget about the single statement thing... think "production". If you were given an assignment to write some code and you found a possible problem in the requirements, would you just write the code according to the requirements or would you bring the problem you've found to someone's attention?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (12/6/2012)
patrickmcginnis59 (12/6/2012)
Jeff Moden (12/6/2012)
patrickmcginnis59 (12/6/2012)
Jeff Moden (12/4/2012)
So far as no offer being extended, that's fine because I don't want to work for a company that wants a code monkey.I'll take that job. And you're invited at any time to call me a monkey for doing so. Doing honest work for a living is nothing to be ashamed of.
I agree.... nothing to be ashamed of. But, if you find are problem with an assignment, would you speak up of write the code exactly as told? If someone does that, then they should be ashamed.
I'm not going to attach any sort of moral value to Scott's desiring the solution to be a single statement, as far as I know his request is perfectly legitimate and I would hunker down and give it a shot. Otherwise, I couldn't answer your question unless I knew what you considered to be a problem.
Forget about the single statement thing... think "production". If you were given an assignment to write some code and you found a possible problem in the requirements, would you just write the code according to the requirements or would you bring the problem you've found to someone's attention?
I don't think its possible for me to determine, I lack the experience. You'd probably have to describe a hypothetical situation for this thread to proceed. All the requirements I have ever received during my many years of employment have been astonishingly perfect, practically delivered by angels to float gently downward toward my admiring gaze until with a wisp of jasmine and rainbows they land perfectly aligned for me to behold in my private penthouse office in Disneyland or whatever other luxury appointments I happen to be graced with at the time.
But this is your thread. Describe me one of these "problems" you speak of, and I'll try to slum it for the time it takes for you to make your point.
-
I don't think its possible for me to determine, I lack the experience.
In that case, let's leave it as that. +100 for honesty. 🙂
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
A lot of good reading, everything started by T4FF who asked directions to implement something simple, like this:
The thread evolved with several suggestions, routes, ideas, etc, it was looking like this:
Somehow, it looks like spaghetti :hehe: leading to several proposed solutions, take a look below :w00t:
Now, the most remarkable is that nobody :smooooth: yes, nobody :w00t: ever, mentioned the word " It depends" :hehe:
Cheers, actually, beside the joke, this is one of the best threads I read since I join early this year 🙂
Hope this helps,
Rock from VbCity -
Jeff Moden (12/6/2012)
patrickmcginnis59 (12/6/2012)
Jeff Moden (12/6/2012)
patrickmcginnis59 (12/6/2012)
Jeff Moden (12/4/2012)
So far as no offer being extended, that's fine because I don't want to work for a company that wants a code monkey.I'll take that job. And you're invited at any time to call me a monkey for doing so. Doing honest work for a living is nothing to be ashamed of.
I agree.... nothing to be ashamed of. But, if you find are problem with an assignment, would you speak up of write the code exactly as told? If someone does that, then they should be ashamed.
I'm not going to attach any sort of moral value to Scott's desiring the solution to be a single statement, as far as I know his request is perfectly legitimate and I would hunker down and give it a shot. Otherwise, I couldn't answer your question unless I knew what you considered to be a problem.
Forget about the single statement thing... think "production". If you were given an assignment to write some code and you found a possible problem in the requirements, would you just write the code according to the requirements or would you bring the problem you've found to someone's attention?
There are many situations in production where I don't have absolute control over all the code on a given db. Most often the DBA gets his/her wishes regarding using, say, triggers to enforce constraints, at other times not.
The given request is simple enough that the 3-value check can, and should, be done inside the q itself. For NO additional overhead, the code is self-checking. Given that, and that it's initially (just) an interview q, is this really worth cavilling about?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Rock from VbCity (12/7/2012)
Now, the most remarkable is that nobody yes, nobody ever, mentioned the word " It depends"BWAAA-HAAA!!!! Maybe not it so many words but that's exactly the point I've been trying to make. 🙂
Good post... I love that old "evolution of a problem" poster you posted. Even though it first appeared decades ago, it's still so very appropriate.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 14 posts - 61 through 73 (of 73 total)
You must be logged in to reply to this topic. Login to reply