Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Interview Questions Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, June 5, 2014 8:46 AM
 SSC-Addicted Group: General Forum Members Last Login: Thursday, December 18, 2014 2:30 AM Points: 413, Visits: 722
 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 #1Consider the tables given and the following code: Table_A ID CustomValue1 11 22 1Table_B ID CustomValue1 212 20 `SELECT *FROM Table_A A JOIN Table_B B ON (A.ID = B.ID)WHERE B.CustomValue = 21SELECT *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 = 21SELECT *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 #2Compare the two pieces of the code:`SELECT SurnameFROM T_EmployeeWHERE DATEDIFF(yy, DateOfBirth, GETDATE()) > 60SELECT SurnameFROM T_EmployeeWHERE DateOfBirth < DATEADD(yy, -60, GETDATE())`What is each trying to achieve? Which one might be the better considering performance? Why?Question #3Please 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)GOCREATE TRIGGER T_EmployeeUpdate ON T_Employee FOR UPDATE ASBEGIN DECLARE @EmployeeID int SELECT @EmployeeID = EmployeeID FROM Inserted UPDATE T_Employee SET UpdatedDate = GETDATE() WHERE EmployeeID = @EmployeeIDENDGO`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
 SSCarpal Tunnel Group: General Forum Members Last Login: Today @ 12:39 PM Points: 4,049, Visits: 9,211
 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 Group: General Forum Members Last Login: Today @ 11:28 AM Points: 13,641, Visits: 11,517
 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 Group: General Forum Members Last Login: Friday, June 6, 2014 11:36 AM Points: 10, Visits: 10
Post #1578268
 Posted Friday, June 6, 2014 6:39 AM
 SSChampion Group: General Forum Members Last Login: Today @ 11:28 AM Points: 13,641, Visits: 11,517
 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 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 211 2 1 21
Post #1578277
 Posted Friday, June 6, 2014 7:04 AM
 SSChampion Group: General Forum Members Last Login: Today @ 11:28 AM Points: 13,641, Visits: 11,517
 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 211 2 1 21The 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 NULL3rd 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 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 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 Group: General Forum Members Last Login: Today @ 11:28 AM Points: 13,641, Visits: 11,517
 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

 Permissions