February 28, 2013 at 12:04 am
Comments posted to this topic are about the item 15 Quick Short Interview Questions Useful When Hiring SQL Developers
February 28, 2013 at 3:07 am
Nice selection of questions. Got busted on 3,10 & 12
February 28, 2013 at 4:03 am
i got caught on questions
10
11
12
7
6
i got confused with question 12.
the answer states Group By and ORDER BY cannot contain a sub query, but isn't this considered a subquery?
select * from table
order by (case when exists(select 1 from table2) then col2 end)
February 28, 2013 at 5:42 am
A pretty good test and similar to ones I've used myself. I particularly liked the fact that there were several questions in there that I hadn't come across previously by doing the usual google for "sql interview questions".
I genuinely tripped up on 8 (had a brain fart and forgot about OUTPUT... even though I've used it hundreds of times) and 14 (I never knew there was anunknown state and always just thought that Null never equalled null)
I do think some of the wording could be tightened up a bit on some questions as there were several I might have given the wrong answer to because I missunderstood the question. 1. I would have said No because I took inner join to mean INNER JOIN so wouldn't have considered comma separated table lists joined in the where clause. 9. I would have said No on the basis that a cartesian product is a result of multiplying two sets while a Cross Join is syntactical method to do so. Those may be ppedantic differences but, if administered as a written test where you couldn't probe a candidates answers, this might have been just enough to drop them into the discard pile when they were actually a good candidate.
One final bit of pedantry, I don't think a clustered index is necessarily the order the records are stored in on the disk. It's the order they're logically stored on the B-Tree, but that's abstracted from the disk so the physical ordering could be entirely different. At least, that's what I've read but I'll freely admit I'm going out on a limb there. The difference has never been material for me.
I hope I haven't come across as being too critical here, I thought it was a very good set of questions and certainly one of the better selections I've come across.
February 28, 2013 at 5:45 am
Related to "A test with at least one tough question ": it's useful to see how people react to things that they don't know. I'd sooner see someone admit to not being sure of an answer, than guessing a wrong answer with absolute confidence.
February 28, 2013 at 5:51 am
The SQL test included in this article is not a sophisticated test. However, it can quickly, in 15 minutes or less, and with reasonable success, separate senior SQL developers and maintainers from junior staff or those who “just read the book” or “just finished a class” on SQL programming or who have just 1 or 2 years of SQL programming full time equivalent experience. This test does not purport to do more than that.
I appreciate the article exploring this area, as database candidates are often very poorly screened candidates for job interviews (usually because people without DB backgrounds are hiring them). However it is my opinion that the above statement isn't true. As I am approaching Senior level in my career, the most critical part of my job NOW is interpreting Execution Plans, minimizing logical reads, minimizing deadlocking, etc...none of which this test even touches. You can any find a lot of Codemonkeys that know all the tricks of T-SQL and can consolidate 8 pages of code into 1 line - but that same person can be absolutely clueless between the difference between an table scan and an index seek. IMO those are the people who "fake" their way into being Senior level.
I apologize if this comes off extremely critical, but I've had to work with "senior" people like this, and fix their mistakes along the way, so you may have hit a nerve :crazy:
February 28, 2013 at 6:40 am
Surely posting the questions in a public article like this just means any prospective candidates need to ensure they memorise the correct responses? They're even helpfully given in the article!
February 28, 2013 at 7:45 am
...approaching Senior level in my career, the most critical part of my job NOW is interpreting Execution Plans, minimizing logical reads, minimizing deadlocking, etc...none of which this test even touches.
My thoughts were similar. This is really a challenging, yet basic, T-SQL test, not a full scope SQL Server developer's test. There is nothing on dynamic SQL, very little on actually calling and executing stored procedures and functions, let alone anything on cohesiveness and decoupling, procedural versus declarative, ETL, SSIS or tuning.
Still it is a challenging T-SQL test. I just wish it was labeled as such.
February 28, 2013 at 7:56 am
All,
The one thing I really appreciate about this site is how much I can learn something new from what seems like a simple list in interview questions. Never used the OUTPUT clause before. Thanks for making me keep learning this stuff.
I have to agree that the list doesn't address more advanced skills like tuning, execution plans, SSIS, etc. But, the test will at least filter out the people who didn't spend the time and make the effort to look around and study these kinds of interview "lists".
The next part of the interview should include asking more detailed follow up questions on these questions and even more detailed follow questions on the more advanced skills with examples from the candidate on how he used the execution plans, statistics, SSIS to solve a problem or implement a program.
Thanks,
Anton
February 28, 2013 at 8:36 am
It's a good review for DBA's below the guru level, as we may get calls to debug code using some of the items mentioned. Or worse yet, requests from the developers manager to open a support incident with Microsoft because 'there is a bug is SQL Server'. The question about putting the WHERE clause on the join was on my to-do list to look up, so thanks for taking care of it for me 🙂
February 28, 2013 at 8:41 am
Yes, very interesting.
By coincidence I am also one of those boffins with qualifications and experience in Educational Psychology, but I won't get all technical about test validation techniques!
Most of your questions are good, I reckon. Some need work. All these questions have 'face validity' in that they look as though they'll distinguish between good and bad SQL Developers, but you will need to weed out the questions that have no relationship to whether a developer knows his stuff generally . Your most obvious wrong'un is the one about the HAVING clause. One can have a good and fruitful career as a SQL Developer without knowing the useless fact that a HAVING clause can be used in a select statement without a GROUP BY.
To winnow out this sort of question and other types of question that don't do what you think they do, the easiest solution is to take a reasonably large group that are still struggling with SQL and a reasonably large group of people who are acknowledged to be good at it and give the test to both. If a question is almost always failed by the neophytes, and is passed by the experts, it is a good one. If it isn't passed by either, it is wastefully difficult. If both groups get it then it is too easy. If the neophytes get it right and the experts get it wrong then oh dear, oh dear out it goes. You are left with just the few that distinguish between the good SQL people and the ones who aren't.
Curiously, it doesn't usually matter much if you actually let slip the answers, because the really good questions need a good understanding to answer them. The clustered index one would have to be entirely memorized unless you understood. Questions about statistics are good that way too. I like questions that bring out the way that SQL is only a specification of the result that you want rather a specification of HOW you get that result, so anything about the query optimiser and performance, or cacheing are good. For some reason I've never quite understood, questions about how you go about finding the difference in the data held in two tables with the same definition never fails to separate the sheep from the goats
Best wishes,
Phil Factor
February 28, 2013 at 9:37 am
Thanks for the Interview test.
In a former consulting job (not SQL developer), I interviewed a candidate who did a great job of reading the answers from the questions and got hired, but was absolutely clueless about how to program anything. He lasted maybe two weeks.
Taught me that I was way too trusting and naive in the interview process and that I really needed some type of test like this to get a quick feel of their real skill set.
Thanks. Wonderful tool that I am sure will be quite useful.
Challenging and learning for me too.
Thanks.
February 28, 2013 at 11:24 am
I think the answer to question 11 is a bit subjective. First, in many companies, there isn't a distinction between database developers and application developers, the same person typically will do coding in .Net and SQL Server, and from an application developers standpoint you are parameterizing queries. Second, even within SQL Server, there are places where Microsoft refers to parameterized queries, such as in SSIS and SP_EXECUTESQL.
The problem with a number of these questions, as others have stated, is that they have memorizable answers. Questions can be worded to require some applied knowledge so you know the candidate has the skills to code well, not just knowledge from a 100 level class on databases from college.
February 28, 2013 at 12:19 pm
Re: #10
Even with Technet article to "prove" it, you cannot use HAVING without GROUP BY clause.
SELECT TOP 1000 [AddressID]
,[AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
,[rowguid]
,[ModifiedDate]
FROM [AdventureWorks].[Person].[Address]
HAVING PostalCode='98011'
Generates:
Msg 8121, Level 16, State 1, Line 11
Column 'AdventureWorks.Person.Address.PostalCode' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
using just 1=1 in HAVING clause generates other error, but still not allows you to use it.
Msg 8120, Level 16, State 1, Line 2
Column 'AdventureWorks.Person.Address.AddressID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
February 28, 2013 at 1:08 pm
dkrasnikov (2/28/2013)
Re: #10Even with Technet article to "prove" it, you cannot use HAVING without GROUP BY clause.
Sure you can, if you do it correctly:
select count(AddressID)
from Address
having count(AddressID) > 1
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply