June 5, 2014 at 9:29 am
They don't seem extremely difficult. Unfortunately, many people will get them wrong.
It seems like a nice test which will separate good from bad ones. However, on question 2, the queries aren't exactly the same and results could vary from one and another. 😉
June 6, 2014 at 1:31 am
They are not too difficult in my opinion. Anyone with at least a year of SQL experience should be able to answer these more or less correct.
(and I'm just a measly BI developer :-D)
#3 might be a tad more difficult if you have never worked with triggers before. But then again, I almost never work with them and I immediately saw it cannot handle updates on multiple rows at the same time. 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 6, 2014 at 6:29 am
What are the answers?
June 6, 2014 at 6:39 am
kimberly_lehman (6/6/2014)
What are the answers?
Try to answer yourself. Post your answers here and we'll correct them if necessary 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 6, 2014 at 6:58 am
Ok, well in question 1, it looks to me like all 4 would return the same result, but I didn't test it! Putting a predicate on the ON clause should be the same as putting it in the where clause with an inner join. And the outer join shouldn't change anything because there aren't any non-matching rows.
1 1 1 21
1 2 1 21
June 6, 2014 at 7:04 am
kimberly_lehman (6/6/2014)
Ok, well in question 1, it looks to me like all 4 would return the same result, but I didn't test it! Putting a predicate on the ON clause should be the same as putting it in the where clause with an inner join. And the outer join shouldn't change anything because there aren't any non-matching rows.1 1 1 21
1 2 1 21
The last query is different.
You only take one row of table B (since customValue = 21), but you match it against every row of table A.
The third row doesn't match (A.ID <> B.ID), so the columns from B are NULL
3rd row: 2 1 NULL NULL.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 6, 2014 at 7:14 am
Aha! I knew there had to be something I was missing. So now to answer the OPs question, did most interviewees give you the same response I gave? Or were most of them way off?
June 6, 2014 at 7:16 am
Number 2 is selecting names of employees over 60. Or specifically, people who were 60 years old by the end of last year, since this is only comparing by year. I have no idea which ones provides better performance. In my experience I either haven't worked with large enough data sets that I needed to spend time optimizing queries, or the companies I worked for were large enough that DBAs would handle the optimizing of queries that took too long to run.
June 6, 2014 at 7:18 am
kimberly_lehman (6/6/2014)
Number 2 is selecting names of employees over 60. Or specifically, people who were 60 years old by the end of last year, since this is only comparing by year. I have no idea which ones provides better performance. In my experience I either haven't worked with large enough data sets that I needed to spend time optimizing queries, or the companies I worked for were large enough that DBAs would handle the optimizing of queries that took too long to run.
The second one is better for performance. In the first one, an index on DateOfBirth cannot be used, as the column is inside a calculation.
The second one doesn't have this problem.
Regarding the remark of Luis saying the results can be different:
I think this is becausue DATEDIFF(year,date1,date2) isn't exactly accurate. 20131231 and 20140101 gives a 1 year difference, but in reality they are just 1 day apart.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 6, 2014 at 7:33 am
For #3, I'd have to Google whether or not an update inside an update trigger would cause an infinite loop. I don't know that off the top of my head because I try to avoid triggers when not absolutely necessary. I've found that they can sometimes cause issues that are time-consuming to diagnose because nobody ever remembers to check if there are triggers on the table. For the purpose of updating a last modified date, I would include that in the stored proc unless there was a reason it couldn't be done that way.
June 6, 2014 at 7:37 am
kimberly_lehman (6/6/2014)
For #3, I'd have to Google whether or not an update inside an update trigger would cause an infinite loop. I don't know that off the top of my head because I try to avoid triggers when not absolutely necessary. I've found that they can sometimes cause issues that are time-consuming to diagnose because nobody ever remembers to check if there are triggers on the table. For the purpose of updating a last modified date, I would include that in the stored proc unless there was a reason it couldn't be done that way.
The problem with the trigger here is that it assumes only one row will be updated at the time.
If multiple rows are updated, the logic will fail.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 6, 2014 at 7:47 am
I saw you mentioned that, but I would have had to Google whether an update trigger fires once per update or once per row.
June 6, 2014 at 7:51 am
kimberly_lehman (6/6/2014)
I saw you mentioned that, but I would have had to Google whether an update trigger fires once per update or once per row.
It is fired for the update event, which means for all of the updated rows at the same time.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 6, 2014 at 8:02 am
kimberly_lehman (6/6/2014)
I saw you mentioned that, but I would have had to Google whether an update trigger fires once per update or once per row.
All SQL triggers fire per operation. There's no such thing as a per-row trigger in SQL Server (Oracle has them iirc)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 6, 2014 at 8:14 am
julian.fletcher (6/5/2014)
And yet ... we're constantly surprised (and disappointed) how badly most people do.
You said a mouthful, there. If you really want a surprise, add just one more question. Ask "Using T-SQL, how do you get the current date and time"?
Yeah, I know... seems way too simple. I've lost track of how many people, both SQL "developers" and "DBAs" (notice the quotes) can't answer that simple question even when I tell them that I don't ask any trick questions and that they should go for the obvious answers to all.
On question 3, I wouldn't ask Front-End Developers to build a trigger at work because I wouldn't want them to. Although the question isn't difficult, it's just something I wouldn't ask of that position. For someone who's going to be an SQL Developer or DBA, then that question is fine.
One of the most important things there is in SQL is knowing how to "count" or create a sequence. I don't go so far as to ask the "FIZZ BUZZ" question simply because a lot of folks don't actually know what MODULUS is, but I do ask them to write the code (and, yes, they should be able to do it on paper) necessary to create and populate a temp table with sequential integers from 1 to a million. You can learn a HUGE amount from that simple test. If they use a WHILE loop, then they are probably a decent but average developer that can be taught. If the use a recursive CTE, then you know they've studied a bit more than average and just need to be educated in methods of performance. If they do a CREATE TABLE and populate it with a ROW_NUMBER() in a CROSS JOIN query (pseudo-cursor), hire them. If they use SELECT/INTO using the same method but use the Identity FUNCTION to create and populate the table all in one step, hire them at a higher rate. 😀 If they used a Tally Table or Tally CTE (cascading Cross Join of constants), don't let them leave the building until they agree to be hired. :w00t: Make sure that you ask them why they used whatever method they used and use it as an opportunity to talk about performance methods, minimal logging, etc, to make sure it's not something they just learned by rote. If they answer "It just seemed like the right thing to do and can't answer any other questions about how it actually works, then they learned it by rote and if you hire them, you're hiring a liar. And, yeah... there will be those that can't even begin to write such code even though it's so simple.
Of course, I'm just kidding about using just one code question as the reason to hire someone but... it's an incredibly simple question that anyone worth their salt should be able to do and, as I suggest in the paragraph above, is going to be a stong indicator of how the rest of the interview is going to go.
As an FYI, I've been known to stop an interview if they can't answer the date and time question and that's always the first question. Keep in mind that some people will "fumble about" a bit because they can't believe that you're asking such a simple question and might need an assurance that the question really is that simple but, if they don't have a clue, (and I've tested this by continuing some of the interviews after a person gets it wrong), I absolutely guarantee the rest of the inteview will prove that the person is not qualified for the job in any way, shape, or form no matter how much "experience" they supposedly have on their resume.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 48 total)
You must be logged in to reply to this topic. Login to reply