Anyone else bothered when primary keys are not given the name 'ID'?
Our SQL interview questions
101–110 of 227 posts
Re: Our SQL interview questions
#102These questions are very simple, though I guess they cover a few of the core concepts. Basic selects, joins, joining the same table twice, left joins and group by. I'm most worried by the comment "(tricky - people often do an "inner join" leaving out empty departments)" . That's a basic question and if that's considered "tricky" you've got a real problem on your hands. Maybe if you're hiring for a junior position you…
> That's a basic question and if that's considered "tricky" you've got a real problem on your hands.
If they aren't warned then it's reasonable to assume that every department has employees. Otherwise why would it exist?
Re: Our SQL interview questions
#103If you can only answer these using an ORM, then you really shouldn't put knowledge of SQL on your resume. I am not really into giving programming tests to interviewees, but if you claim to have experience with something you should be able to answer simple questions about it.
I wouldn't treat this sort of thing as dispositive, and if I were doing hard-core SQL development, I'd dismiss it entirely and start the interview with much hairier wizardry; but for a generic, gonna write some queries but mostly live outside the database kind of role, the five minutes or so this sort of test takes at the beginning of the interview gives me a strong indicator of how to assess what the candidate actually knows, rather than what is represented on their resume. It is a guide for the actual meat of the interview.
Re: Our SQL interview questions
#104If the position you're filling is directly dependent on more-than-average SQL experience - creating a DB driver, an ORM, for ex. - then SQL-specific questions are applicable. But, by and large, this type of specific-knowledge testing is not very useful. I want to see a developer's general abilities at problem solving and the source code to back it up. If you have solved complex problems in C# - and can prove it - the…
Since SQL is a declarative language, you have to use it very differently than procedural or functional languages. I've met many people who don't understand the difference and just write procedural code in their SQL queries (cursors, etc). The output may be correct, but performance on anything bigger than a toy dataset is terrible.
If your team builds massively parallel systems in Erlang, you need to make sure a candidate understands at least the basics of its process model and message passing. If your team build high-performance web apps, you need to make sure a candidate understands at least the basics of HTTP and the difference between client and server. For SQL, the same is true: they need to understand at least the basics of the relational model and declarative programming.
Re: Our SQL interview questions
#105Anyone else bothered when primary keys are not given the name 'ID'?
I'm bothered by primary keys full stop.
Re: Our SQL interview questions
#106What is the preferred way to aggregate with nulls? SELECT Departments.name, SUM(COALESCE(salary,0)) FROM Departments LEFT JOIN employees USING departmentID GROUP BY 1 The above is how I would solve the last one, but I often feel like I abuse COALESCE.
Aggregates generally do the right thing with null without the coalesce.
Thanks. It appears I need to stop overusing coalesce. I was told that sql NULL means "A value that is not yet known", which nicely explains why 1+NULL, 1 NULL, 1 = NULL is always NULL. Now I know that AVG(test_scores) produces the average of the known values automatically.
- - -
I just did a test, and it appears the COALESCE is needed in this case. Running an aggregate where all values are null, results in NULL (the empty department). You need to do something because the total salary of an empty department is known to be zero.
Re: Our SQL interview questions
#107Earlier quoted context omitted.
Employee.objects.filter(boss__salary__lte=F('salary')) Find me employee objects which have a boss salary less than or equal to the salary.
Out of curiosity, would the ORM map to the same SQL query? Or would it request all employee-boss pairs and filter them outside of the DB? There's a huge performance difference involved.
I believe Django would do this as a single query.
Re: Our SQL interview questions
#108I took a similar test, on-line while being watched. 4 sets of 10 multiple choice questions: SQL, unix commands, vi, & HTML. Make your 10 choices, click submit, get your score. It was kinda silly, but what the heck... It was ridiculously easy and I got all 40 right without much thinking, as many people here would also, I imagine. Then I asked, "Why bother with this after reading my resume?" They answered, "We have to…
Maybe the previous person was an emacs guy? Seriously, what would be a valid reason to sort employees based on which specific editor they are habitually using?
> Seriously, what would be a valid reason to sort employees based on which specific editor they are habitually using?
It's not for filtering out really good candidates, it's for filtering out abysmally bad candidates. Show me the dumbest Vi user and the dumbest Eclipse user you can find and you might learn something.
Re: Our SQL interview questions
#109These are nice, but you need simpler questions.
Hear me out: Take away two questions (the first four are enough anyway) and add two to start out with that are much simpler. You will be shocked how many people will fall out at that level.
Years ago, when I first started hiring, a friend told me about this and I didn't believe him, but I tried it anyway. I was astounded how many people were completely bluffing. It helps expedite the whole process.
Re: Our SQL interview questions
#110This is much more useful than the typical question I've received at some companies - "On a scale of 1 to 10, how would you rate your SQL knowledge?"
I've had this and have answered something like:
"Compared to most other devs I work with, 7 or sometimes 8. Compared to people who do SQL for a living, possibly a 4, or a 5 if I'm feeling cocky. It all depends on what the '10' really represents - the best of the best, or the best of the people I'd be working with."
Well, something like that. That last bit - I've said it more tactfully in the past.