Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Interview Questions Expand / Collapse
Author
Message
Posted Thursday, June 5, 2014 8:46 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 384, Visits: 694
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?
Post #1577843
Posted Thursday, June 5, 2014 9:29 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:25 AM
Points: 3,613, Visits: 8,105
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1577880
Posted Friday, June 6, 2014 1:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:22 AM
Points: 13,017, Visits: 10,798
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 )

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

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1578182
Posted Friday, June 6, 2014 6:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 6, 2014 11:36 AM
Points: 10, Visits: 10
What are the answers?
Post #1578268
Posted Friday, June 6, 2014 6:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:22 AM
Points: 13,017, Visits: 10,798
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1578272
Posted Friday, June 6, 2014 6:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 6, 2014 11:36 AM
Points: 10, 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
Post #1578277
Posted Friday, June 6, 2014 7:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:22 AM
Points: 13,017, Visits: 10,798
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1578279
Posted Friday, June 6, 2014 7:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 6, 2014 11:36 AM
Points: 10, 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?
Post #1578284
Posted Friday, June 6, 2014 7:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 6, 2014 11:36 AM
Points: 10, 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.
Post #1578285
Posted Friday, June 6, 2014 7:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:22 AM
Points: 13,017, Visits: 10,798
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1578287
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse