15 Quick Short Interview Questions Useful When Hiring SQL Developers

  • Glen Cooper

    SSCommitted

    Points: 1603

    I would also ask the candidate to prepare a question for the examiner.

    R Glen Cooper

  • davoscollective

    SSCertifiable

    Points: 6325

    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!

  • Jeff Moden

    SSC Guru

    Points: 994667

    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


    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • R M Buda

    SSCrazy

    Points: 2221

    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

  • Geoff A

    SSChampion

    Points: 11407

    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?

  • remi.bos

    Grasshopper

    Points: 23

    Thanks, very helpfull!

    Only problem is now, our candidate saw the answers already 😀

  • Jeff Moden

    SSC Guru

    Points: 994667

    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


    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • e-ghetto

    SSChasing Mays

    Points: 601

    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

  • Jeff Moden

    SSC Guru

    Points: 994667

    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


    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • e-ghetto

    SSChasing Mays

    Points: 601

    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:

  • ScottPletcher

    SSC Guru

    Points: 98120

    Excellent! Best overall set of SQL developer interview qs I've ever seen.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • ScottPletcher

    SSC Guru

    Points: 98120

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • odeddror

    SSC Eights!

    Points: 878

    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

  • wkampas

    Grasshopper

    Points: 17

    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.

  • Jeff Moden

    SSC Guru

    Points: 994667

    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


    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 16 through 30 (of 44 total)

You must be logged in to reply to this topic. Login to reply