SQL exercises for job applicants

  • Hello,

    I'm not sure if this is the right forum for this question but here it goes.

    It looks like we may be looking for a jr. sql developer in the near future, but I was wondering if there are any set of exercises that we can use as part of the interview for the applicant(s). I think this would help us better assess their skills and fit for the job.

    Regards,

    john

  • You could ask them how to find duplicate rows in a table. Possibly even finding the highest id value if the id is an integer column, say perhaps an identity. You should be able to learn a lot about them by how they solve this.

  • A lot of exercises at SQL Exercises.

  • Brandon Leach (3/7/2012)


    You could ask them how to find duplicate rows in a table. Possibly even finding the highest id value if the id is an integer column, say perhaps an identity. You should be able to learn a lot about them by how they solve this.

    I think this is a good approach.

    I'd start by asking them how to solve various issues pertinent to your environment that maybe you have encountered and resolved in the past. It would give a lot of insight.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I like the FizzBuzz puzzle.

    I use it to identify if an applicant

    - prefers using a set or loop based method where both methods are possible

    - knows enough T-SQL to generate a numbers list from scratch (or if the person knows a method to generate such a list based on a system table)

    - understand how the CASE fuction works

    - knows how deal with error messages (usually an applicant would simply add "ELSE number" leading to a conversion error)

    This simple test is also a good start to talk about some rather basic SQL internals: (Why does SQL Server try to convert "fizz" to a numeric value?)

    The test to find duplicate rows is a good test for solving a common task.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • LutzM (3/11/2012)


    I like the FizzBuzz puzzle.

    I use it to identify if an applicant

    - prefers using a set or loop based method where both methods are possible

    - knows enough T-SQL to generate a numbers list from scratch (or if the person knows a method to generate such a list based on a system table)

    - understand how the CASE fuction works

    - knows how deal with error messages (usually an applicant would simply add "ELSE number" leading to a conversion error)

    This simple test is also a good start to talk about some rather basic SQL internals: (Why does SQL Server try to convert "fizz" to a numeric value?)

    The test to find duplicate rows is a good test for solving a common task.

    Unless it's a very smart Junior, I wouldn't expect them to even know what a numbers list is never mind be able to generate one. Sure, the code is simple but I've worked with many supposedly Sr. Level Developers and DBAs that have never heard of things when it comes to numbering things. Shoot, even ROW_NUMBER has been out for over 8 years and I'm still finding Seniors that know nothing about it.

    --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 (4/1/2013)


    LutzM (3/11/2012)


    I like the FizzBuzz puzzle.

    I use it to identify if an applicant

    - prefers using a set or loop based method where both methods are possible

    - knows enough T-SQL to generate a numbers list from scratch (or if the person knows a method to generate such a list based on a system table)

    - understand how the CASE fuction works

    - knows how deal with error messages (usually an applicant would simply add "ELSE number" leading to a conversion error)

    This simple test is also a good start to talk about some rather basic SQL internals: (Why does SQL Server try to convert "fizz" to a numeric value?)

    The test to find duplicate rows is a good test for solving a common task.

    Unless it's a very smart Junior, I wouldn't expect them to even know what a numbers list is never mind be able to generate one. Sure, the code is simple but I've worked with many supposedly Sr. Level Developers and DBAs that have never heard of things when it comes to numbering things. Shoot, even ROW_NUMBER has been out for over 8 years and I'm still finding Seniors that know nothing about it.

    That wouldn't be a problem with some of the students coming from one of the colleges here in town if they would higher me to teach a MS SQL Server class. Using a Tally or Numbers table has become an important part of what I do on a regular basis almost. Using ROW_NUMBER() as well.

  • If you're looking for a junior I hope you wouldn't restrict yourself by solely asking questions that are all SQL Server specific...at least give them the option to use psuedocode. You might have a whizzkid fresh out of uni in the room with you who doesn't know any MSSQL currently but given a couple months may start showing up a couple of your mid-level staff


    Dird

Viewing 8 posts - 1 through 7 (of 7 total)

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