SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Interview Questions


Interview Questions

Author
Message
Julian Fletcher
Julian Fletcher
SSChasing Mays
SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)

Group: General Forum Members
Points: 624 Visits: 1017
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
ID CustomValue
1 1
1 2
2 1

Table_B
ID CustomValue
1 21
2 20

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?
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16347 Visits: 19076
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
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27179 Visits: 13268
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. ;-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
dotnetkim
dotnetkim
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 10
What are the answers?
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27179 Visits: 13268
kimberly_lehman (6/6/2014)
What are the answers?


Try to answer yourself. Post your answers here and we'll correct them if necessary ;-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
dotnetkim
dotnetkim
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 10
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
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27179 Visits: 13268
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
dotnetkim
dotnetkim
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 10
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?
dotnetkim
dotnetkim
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 10
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.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27179 Visits: 13268
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search