June 5, 2014 at 8:46 am
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?
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
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply