February 28, 2013 at 2:37 pm
I would also ask the candidate to prepare a question for the examiner.
February 28, 2013 at 9:13 pm
Thanks, this was a useful excercise. I didn't get 6, 13 and 15 right according to your answers.
6: I've never used ##globals. I know in procedural languages globals are frowned upon and I suspect the same applies in any language. I think I may have learned about them a long time ago but forgotten so thanks for the reminder.
13: I've learned something new here. I knew that complex joins were tricky and it depended on what type of join you do. I was confused by a recent QOTD about doing similar things in the ON clause of a MERGE statement and I think in both situations I would avoid doing any type of "theta" join (just learned what they are called too, thanks).
15: I knew that coalesce returned it's result in the datatype with the highest precedence. I looked at 'false' and 'true' and figured they wouldn't be able to be implicitly converted to the int of highest precedence and so it would error, but I suppose that's because they are never evaluated. What if those were int columns rather than hardcoded integers, and they were NULL on a particular row? Then the statement would fail. A static select with hardcoded values is artificial.
For example, something like this is a more realistic simulation of how coalesce is used, and it will fail:
Declare @IntTest Int
SELECT COALESCE(NULL, @IntTest, 'TRUE', 'FALSE')
I've also tried the equivalent Q15 query on Oracle and it fails too:
SELECT COALESCE(NULL, 1, 2, 3, 'TRUE', 'FALSE') from dual
Makes me wonder who is implementing COALESCE the ANSI way!
February 28, 2013 at 10:50 pm
I think the most important part of the article has been overlooked by those rightfully aghast at yet another seemingly “interview question” article. Here’s the quote. The emphasis is mine.
Remember also that [font="Arial Black"]this test is just one factor in your hiring decision[/font], and that resume, past experience and [font="Arial Black"]in-person interviewing skills [/font]should also inform your hiring decisions. In my consulting practice, I have found that people who score 10 or above on this test are very [font="Arial Black"]likely [/font]senior SQL developers or supporters with several years of full time SQL experience; whereas people who score 5 or less are likely junior SQL “beginners”. Scores between 6 and 9 lie in an uncertain nether land where you may need to use other questions or interviewing skills to make a determination.
“Likely” doesn’t mean “Is”. In other words, this test is a pre-interview or phone-interview filter to separate known chaff from potential wheat and I thought the author was clear on that. The author did not intend this simple test to be all to end all in the interview process for a valuable and very expensive asset. You MUST conduct an in-person interview with questions more fitting of a “Senior Level SQL Staff”. This test is just a simple and very quick method to avoid wasting real interview time on “posers” and “wannabes”. That’s all.
Another point that the author was trying to make that may have been lost on some is how to write a test. If you notice, there are no esoteric or “trick” questions, which serve only the ego of the writer of the questions. Instead, these are basic questions that good developers will surely know the answers to most. The other important part is there are no multiple-guess possibilities to most of the questions and, when there are, there are enough possibilities to limit guessing to only 12.5%, as the author cited.
I’ll also add that if you use such a line of questions on a phone-interview, keep track of how long the person takes to answer. These questions are simple enough so that if a person doesn’t start to answer almost immediately, then they’re frantically Googling for an answer because you can’t see them.
Shifting gears a bit, I’m glad that these are essay-style questions so that I could write in what I believe is a more correct answer in for question #4 (the one on 4 part naming for distributed queries). IMHO, no Senior Developer worth his or her salt would ever make the mistake of “deciding” to use the 4 part naming convention in their code. Yes, you need the linked server to avoid the 4 part naming but you should never use 4 part naming in your stored procedures, views, or other code objects other than the script to make the linked server. If you don't know how to avoid 4 part naming or the reasons why you should do so, then you'll need to look it up or ask a Senior Developer to help you because I try not to post answers to interview questions. 😉
Thomas, great article. I hope that people will read beyond the questions you included because there are some powerful lessons to be learned here.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2013 at 6:23 pm
A great article - A lot of these will be added to my list of interview questions.
I was got by a few of them and it prompted me to do some investigation.
I found that the answer to Q12 is not correct.
The following query works on SQL Server 2012, so it seems that sub-queries are allowed in the ORDER BY in this version of SQL:
[font="Courier New"]
USE AdventureWorks
GO
SELECT d.SalesOrderID, SUM(d.LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail d
GROUP BY d.SalesOrderID
HAVING SUM(LineTotal) > 100000.00
ORDER BY (SELECT totalDue from Sales.SalesOrderHeader h where h.SalesOrderID = d.SalesOrderID)[/font]
go
March 3, 2013 at 6:51 am
Jeff Moden (2/28/2013)
Shifting gears a bit, I’m glad that these are essay-style questions so that I could write in what I believe is a more correct answer in for question #4 (the one on 4 part naming for distributed queries). IMHO, no Senior Developer worth his or her salt would ever make the mistake of “deciding” to use the 4 part naming convention in their code. Yes, you need the linked server to avoid the 4 part naming but you should never use 4 part naming in your stored procedures, views, or other code objects other than the script to make the linked server. If you don't know how to avoid 4 part naming or the reasons why you should do so, then you'll need to look it up or ask a Senior Developer to help you because I try not to post answers to interview questions. 😉Thomas, great article. I hope that people will read beyond the questions you included because there are some powerful lessons to be learned here.
not to dwell on this comment, but are you saying to use a synonym. That way if a database name or a linked server name changes, you don't need to find all the places in code to change it.
or is there another reason?
March 3, 2013 at 12:44 pm
Thanks, very helpfull!
Only problem is now, our candidate saw the answers already 😀
March 3, 2013 at 4:04 pm
Geoff A (3/3/2013)
Jeff Moden (2/28/2013)
Shifting gears a bit, I’m glad that these are essay-style questions so that I could write in what I believe is a more correct answer in for question #4 (the one on 4 part naming for distributed queries). IMHO, no Senior Developer worth his or her salt would ever make the mistake of “deciding” to use the 4 part naming convention in their code. Yes, you need the linked server to avoid the 4 part naming but you should never use 4 part naming in your stored procedures, views, or other code objects other than the script to make the linked server. If you don't know how to avoid 4 part naming or the reasons why you should do so, then you'll need to look it up or ask a Senior Developer to help you because I try not to post answers to interview questions. 😉Thomas, great article. I hope that people will read beyond the questions you included because there are some powerful lessons to be learned here.
not to dwell on this comment, but are you saying to use a synonym. That way if a database name or a linked server name changes, you don't need to find all the places in code to change it.
or is there another reason?
Either a synonym or a pass-through view. And, yes, for the very reasons you stated.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2013 at 1:58 am
Answer 7 is misleading, isn't it?
7) A clustered index is the index of the physical order of the rows of the table on disk.
It's just the logical order - see here: http://www.sqlservercentral.com/Forums/FindPost1324333.aspx
March 4, 2013 at 7:57 pm
Actually, and just like many other threads with interview questions on them, a lot of the interview questions range from flat out wrong to excellent. The cool part is that if you ask candidates the questions that have the wrong answers and they answer with the same wrong answer, that you have either a faker or a poser on your hands and you don't need to take the time to interview them.
Silver lining in every cloud. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2013 at 11:21 pm
Jeff Moden (3/4/2013)
...they answer with the same wrong answer, that you have either a faker or a poser on your hands ...
Brilliant! Never seen it that way - gives the fakers and posers no chance! :w00t:
December 25, 2014 at 7:00 am
Excellent! Best overall set of SQL developer interview qs I've ever seen.
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".
December 25, 2014 at 7:52 am
Geoff A (3/3/2013)
Jeff Moden (2/28/2013)
Shifting gears a bit, I’m glad that these are essay-style questions so that I could write in what I believe is a more correct answer in for question #4 (the one on 4 part naming for distributed queries). IMHO, no Senior Developer worth his or her salt would ever make the mistake of “deciding” to use the 4 part naming convention in their code. Yes, you need the linked server to avoid the 4 part naming but you should never use 4 part naming in your stored procedures, views, or other code objects other than the script to make the linked server. If you don't know how to avoid 4 part naming or the reasons why you should do so, then you'll need to look it up or ask a Senior Developer to help you because I try not to post answers to interview questions. 😉Thomas, great article. I hope that people will read beyond the questions you included because there are some powerful lessons to be learned here.
not to dwell on this comment, but are you saying to use a synonym. That way if a database name or a linked server name changes, you don't need to find all the places in code to change it.
or is there another reason?
Jeff Moden (3/3/2013)
Geoff A (3/3/2013)
Jeff Moden (2/28/2013)
Shifting gears a bit, I’m glad that these are essay-style questions so that I could write in what I believe is a more correct answer in for question #4 (the one on 4 part naming for distributed queries). IMHO, no Senior Developer worth his or her salt would ever make the mistake of “deciding” to use the 4 part naming convention in their code. Yes, you need the linked server to avoid the 4 part naming but you should never use 4 part naming in your stored procedures, views, or other code objects other than the script to make the linked server. If you don't know how to avoid 4 part naming or the reasons why you should do so, then you'll need to look it up or ask a Senior Developer to help you because I try not to post answers to interview questions. 😉Thomas, great article. I hope that people will read beyond the questions you included because there are some powerful lessons to be learned here.
not to dwell on this comment, but are you saying to use a synonym. That way if a database name or a linked server name changes, you don't need to find all the places in code to change it.
or is there another reason?
Either a synonym or a pass-through view. And, yes, for the very reasons you stated.
I think that somewhat overstates the case against the direct use of linked servers (4-part naming), unless you've made the mistake of making your linked server names physical names, such as matching the instance name, rather than logical names, which relate to a given application or functionality. The linked server name itself should be a logical synonym (not a physical one).
Another consideration is that a linked server definition is server-wide, whereas synonyms are db by db. We have hundreds of dbs, so hundreds of changes vs. one. I suppose one could consolidate all the synonyms into one db, but I've never seen that consistently done.
Don't get me wrong, I do favor synonyms in many situations, but that's no reason to completely do away with 4-part names for any and all situations.
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".
December 25, 2014 at 8:01 am
Time and time again this is a gotcha questions to impress your boss during the interview.
Instead of asking how do you approach the new challenge we having or if you developed in your pass a tools that can help us
Or are you confident you can do the job what do you see as a challenge in this role
I'm a developer for 20 years and I don't think I will pass this test
If my code return error I will debug it and search as issues arose
Thanks,
Oded Dror
December 25, 2014 at 10:12 am
Nice list of questions. Thanks.
Could someone provide an example from question 10 where "HAVING without GROUP BY" works.
The SQL doc says you can do this and it behaves like a WHERE clause, but I've never seen anything but "is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause" when trying it.
Edit: Nevermind. Saw an example in another post. Still, I can't think of an example where this would be useful to know. Also, I would probably discourage usage of this construction just because there are simpler alternatives and it might lead to confusion.
December 25, 2014 at 10:34 am
wkampas (12/25/2014)
Nice list of questions. Thanks.Could someone provide an example from question 10 where "HAVING without GROUP BY" works.
The SQL doc says you can do this and it behaves like a WHERE clause, but I've never seen anything but "is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause" when trying it.
Edit: Nevermind. Saw an example in another post. Still, I can't think of an example where this would be useful to know. Also, I would probably discourage usage of this construction just because there are simpler alternatives and it might lead to confusion.
Two way street here 😉 ... Would you post the link to the sample you found? Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply