June 7, 2013 at 6:17 am
Dird (6/7/2013)
Jeff Moden (6/6/2013)
These are Mid to Sr. Web Developers that also spend about 30% of their time writing T-SQL.As for why the questions aren't more difficult, someone in an interview is already nervous enough especially if they really need the job.
For example, the first couple/three questions in the technical interview start out with stupid simple things like "How do you get the current date and time using T-SQL?" That relaxes folks so they can start thinking instead of being scared. Of course, most people would think that "GETDATE()" would be an acceptable answer. It is but the folks that I ended up hiring answered that question with "to what resolution do you want the time element to be" and then explained the various methods to get the current date and time.
Then they're journeymen who are senior only in age if they don't know what modulus is (and fizzbuzz, old & played out) since most web devs would/should have used it at least once for alternating row styles; Google has limited it's value drastically. Even now, I could go for an interview with you & give a set-based solution with perfect syntax and you'd be like "oh sh*t, we've found the holy grail!"...I'd probably get caught out quickly after that though =( Saying that, many recruiters base senior on a minimum number of years that only journeymen can apply for.
The other people may have known about other date/time options too; just too nervous to mention it as you said~ Did you ask them of the different kinds? They may have known more than the others with the gift of the gab.
They all know what modulus is for the very reason you state. But, like I said, there are ways to write code and then there are ways. Even if they write just a While Loop, you can tell a lot from such a simple problem so FizzBuzz might not be as stale as you think. Heh... if someone actually came up with one of the many set based methods, it wouldn't be a holy grail moment, it would be more of a "well it's about freakin' time" moment.
Shifting gears, yes... if they don't volunteer it, I ask them more about dates and times especially since it's one of the more important aspects of T-SQL. One of the 4 questions on the pre-interview asks for a simple update using a date range. You can tell a whole lot from that code, as well. A WHOLE lot!
I'm not going to go into it much more because I don't actually want to give away the pre-interview questions I ask. Just know that they're disarmingly simple but I get a whole lot more from them than most can possibly imagine.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 7, 2013 at 7:50 am
[off topic]
Jeff, keep it up!
I've learned more from your articles and posts here and elsewhere just in the last year or so than in the previous decade. I started writing SQL code as a web developer using classic ASP with absolutely NO experience with databases. In fact, for many years I free-lanced developing apps in MS-Word and Excel using the old VB for applications. Talk about learning bad habits that were hard to break!
I landed my first actual SQL developer job in Feb 2000 and I had to learn the first day something as simple as INNER vs OUTER joins. I had no idea what a stored procedure was. So going from a linear, flat-file point-of-view to thinking in sets didn't come easily. When I even heard the word "set" all I could do is think back to 7th grade with visions of Venn diagrams.
Like Dirty Harry said, "A man's gotta know his limitations." By paying attention to you and a few other SQL experts my own limitations became clear. For a good while I was making use of functions and scripts that worked--but frankly, these were black boxes and I had a difficult time understanding HOW they worked. But sometime in the last two years it all started to make sense and I think it's one of those things that once you see it you can't "un-see" it.
So thanks for donating your valuable time and knowledge. It's right there out in the open for anyone willing to put in the effort to learn and then having the courage to write SQL code that gets it "right."
Steven Willis
[back to topic]
June 7, 2013 at 8:10 am
Jeff Moden (6/6/2013)[/b
...it would be a real pleasure if someone actually did it right, for a change...
Why not open the competition here for most generic FizzBuzz implementation (without dynamic SQL).
I will submit first entry:
declare @FizVals table (ApplyOrder int, ModCheckValue int, DisplayText varchar(100))
declare @MaxIntNum int
-- set required number of records (max supported here is 10^9):
set @MaxIntNum = 150000
-- set your FizzBuzz rules here:
insert @FizVals values (1,15,'FizzBuzz'),(2,3,'Fizz'),(3,5,'Buzz'),(0,77,'JackPot')
;with t101 as (select q.n from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) q(n)) -- 10^1
,t102 as (select a.n from t101 a cross join t101 b) -- 10^2
,t104 as (select a.n from t102 a cross join t102 b) -- 10^4
,t109 as (select a.n from t104 a cross join t104 b cross join t101 c) -- 10^9
,tR as (select top (@MaxIntNum) row_number() over (order by n) N from t109) -- required number of records
select r.N, isnull(fb.DisplayText,'') as Result
from tR as r
outer apply (select top 1 DisplayText from @FizVals fv where r.N % fv.ModCheckValue = 0 order by fv.ApplyOrder) fb
:w00t:
June 7, 2013 at 9:26 am
Evil Kraig F (6/6/2013)
ScottPletcher (6/6/2013)
What "expansion"??? This is an interview q only to test thinking ability, like "How many manhole covers are there in NYC?" and other MS classics.At one point in its development, MySQL didn't allow subqueries, so this actually could have been a real-life q tho.
Besides, all sorts of fixed restrictions exist.
There are always exactly: 24 hours in a day, 7 days in a week and 12 months in a year ... whether we like it or not. Therefore, I don't need to weekly code to be flexible enough to handle an 8-day week: that's just a waste of development time.
Trimming down the quote wall we were generating.
The MS question was "why are manhole covers round?", to my knowledge, but that's a different story. Hadn't heard about the count one.
Fixed restrictions are a different story, but really you're talking about containers there. If the restriction was fixed you could turn them into attributes, but we typically don't... but I'm digressing.
Your specific explanation there indicates containers for an 'n' rowset: days. Days fall into the containers you speak of, I agree. That doesn't limit the number of days associated to a separate entity, only how many are within that container for organization. Most customers have 'n' items, but only drag one particular shopping cart around the store... so you could limit it to '1' cart per person, right up until I arrive and I'm shopping for bulk stuff like toilet paper.
If anything, your solution (I'm purposely not posting it) actually takes more thought than the more robust solution which would locate the MID row via ROW_NUMBER() (with expected variance on even values). Now, is it as optimal compared to the non-subquery solution? Hell no. It's multi-pass. If that was a signficant consideration I could see having to re-wire to the business restriction and lose the robust abilities to avoid future recodes.
Also, to your comment about MySQL and subqueries, I thought we were discussing a SQL Server interview question. I could pull out a few wonky things Dataease or Access does, but I wouldn't bring them into an interview discussion about SQL Server as arbitrary coding rules.
No code is actually being written, so how "flexible" and "changeable" the non-existant code is, is naturally irrelevant.
It's good to ferret out those who want to over-engineer everything. At times when it's 4pm on Fri you need code that works by 5pm that day, not code that will work for some theoretical situation that may arise on Mar 11, 2017.
It also indicates whether the interviewee understands the (economics) concept of "opportunity cost", which is too often overlooked in 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".
June 7, 2013 at 10:35 am
I don't mind tests although I'm glad to say I've never had particularly technical questions over the phone. General questions are fine but any more specific problem solving sounds like you'd run the risk of misunderstanding from both sides. Generally in real work I wouldn't specify a piece of work based on one telephone conversation unless I really understood the subject matter or the person requesting the work.
Asking people if they're familiar with certain patterns is fine - and I'd certainly expect a veteran SQL developer to be familiar with the idea of tally/numbers tables - but it wouldn't be an automatical fail if they hadn't.
My pet hate is (non-phone) technical interviews which ask you to write SQL queries with a pen and paper. I understand you are just getting a feel for whether I am a random blagger or not - but why require I work in a totally artificial manner? The result will be an unreadable mess spread over multiple pages as I try in vain to use adequate whitespace.
June 7, 2013 at 11:38 am
dante.fs (6/7/2013)
My pet hate is (non-phone) technical interviews which ask you to write SQL queries with a pen and paper. I understand you are just getting a feel for whether I am a random blagger or not - but why require I work in a totally artificial manner? The result will be an unreadable mess spread over multiple pages as I try in vain to use adequate whitespace.
Since the problems I give can be solved with less than 6 lines of code, if someone can't do it, then they actually are not the kind of person I'm looking for so the pre-interview test works great for me.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 8, 2013 at 8:51 am
Steven Willis (6/7/2013)
[off topic]Jeff, keep it up!
I've learned more from your articles and posts here and elsewhere just in the last year or so than in the previous decade. I started writing SQL code as a web developer using classic ASP with absolutely NO experience with databases. In fact, for many years I free-lanced developing apps in MS-Word and Excel using the old VB for applications. Talk about learning bad habits that were hard to break!
I landed my first actual SQL developer job in Feb 2000 and I had to learn the first day something as simple as INNER vs OUTER joins. I had no idea what a stored procedure was. So going from a linear, flat-file point-of-view to thinking in sets didn't come easily. When I even heard the word "set" all I could do is think back to 7th grade with visions of Venn diagrams.
Like Dirty Harry said, "A man's gotta know his limitations." By paying attention to you and a few other SQL experts my own limitations became clear. For a good while I was making use of functions and scripts that worked--but frankly, these were black boxes and I had a difficult time understanding HOW they worked. But sometime in the last two years it all started to make sense and I think it's one of those things that once you see it you can't "un-see" it.
So thanks for donating your valuable time and knowledge. It's right there out in the open for anyone willing to put in the effort to learn and then having the courage to write SQL code that gets it "right."
Steven Willis
[back to topic]
Thanks, Steven. :blush: Comments like that really help folks like me keep going.
Shifting gears, I've been watching your responses both here and on LinkedIn. For a guy who started with such humble beginnings in databases, you've sure embraced the idea of getting it right. You've even taken up the mantra of proving things with code and test harnesses that make more than the usual 10 rows of data that a lot of people test with (if they test at all). Well done and, as you said, "Keep It Up"!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 61 through 66 (of 66 total)
You must be logged in to reply to this topic. Login to reply