Interview Questions

  • I work for a company of about 50 people in {REDACTED} and am involved in the recruitment process - we're looking for developers with at least a couple of years' experience in SQL and C#. Part of the process involves asking the candidate a couple of SQL questions, questions which we think aren't too tricky and which shouldn't prove to be too difficult for the sort of people we're looking for. And yet ... we're constantly surprised (and disappointed) how badly most people do. Although the occasional interviewee does answer without any trouble (and usually ends up with a job offer!), we were wondering if the questions are Just Too Difficult.

    Here they are. What do you think? Any opinions gratefully received.

    Question #1

    Consider the tables given and the following code:

    Table_A

    IDCustomValue

    11

    12

    21

    Table_B

    IDCustomValue

    121

    220

    SELECT *

    FROM Table_A A

    JOIN Table_B B ON (A.ID = B.ID)

    WHERE

    B.CustomValue = 21

    SELECT *

    FROM Table_A A

    JOIN Table_B B ON (A.ID = B.ID AND B.CustomValue = 21)

    SELECT *

    FROM Table_A A

    LEFT OUTER JOIN Table_B B ON (A.ID = B.ID)

    WHERE

    B.CustomValue = 21

    SELECT *

    FROM Table_A A

    LEFT OUTER JOIN Table_B B ON (A.ID = B.ID AND B.CustomValue = 21)

    Write down exactly what each of the four select statements would return.

    Question #2

    Compare the two pieces of the code:

    SELECT Surname

    FROM T_Employee

    WHERE DATEDIFF(yy, DateOfBirth, GETDATE()) > 60

    SELECT Surname

    FROM T_Employee

    WHERE DateOfBirth < DATEADD(yy, -60, GETDATE())

    What is each trying to achieve? Which one might be the better considering performance? Why?

    Question #3

    Please take a look on the following code:

    CREATE TABLE T_Employee

    (

    EmployeeID int NOT NULL Identity(1, 1),

    Surname varchar(100) NOT NULL,

    FirstName varchar(100) NOT NULL,

    DateOfBirth datetime NOT NULL,

    CreatedDate datetime NOT NULL,

    UpdatedDate datetime NULL

    )

    GO

    CREATE TRIGGER T_EmployeeUpdate ON T_Employee FOR UPDATE AS

    BEGIN

    DECLARE @EmployeeID int

    SELECT @EmployeeID = EmployeeID

    FROM Inserted

    UPDATE T_Employee

    SET UpdatedDate = GETDATE()

    WHERE EmployeeID = @EmployeeID

    END

    GO

    What is the trigger trying to achieve? Will it always succeed? If not, how could it be fixed?

  • 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. 😉

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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

  • What are the answers?

  • 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

  • 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

  • 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

  • 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?

  • 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.

  • 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

  • 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.

  • 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

  • I saw you mentioned that, but I would have had to Google whether an update trigger fires once per update or once per row.

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 47 total)

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