Our SQL interview questions • Hacker News Board

Re: Our SQL interview questions

#81

Post by jfb » Tue, May 28, 2013, 1:36 PM UTC

jacques_chester wrote: post #54

k__ wrote: post #48

Earlier quoted context omitted.

True story, but such questions are just senseless. If someone knows about set-theory and the ideas behind query-languages, knowing SQL isn't a deal breaker, it's just a nice to have.

Folk generally don't study relational algebra, on its own, for fun.

SQL is one of those technologies that caps how much enjoyment you can derive from understanding the related theory. Scheme, for instance, never stops giving, but you can hit a point with SQL where you start to wonder: where's my Tutorial D?

Re: Our SQL interview questions

#82

Post by PeterisP » Tue, May 28, 2013, 1:36 PM UTC

edw519 wrote: post #33

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

Re: Our SQL interview questions

#83

Post by zrail » Tue, May 28, 2013, 1:36 PM UTC

jcampbell1 wrote: post #78

What 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.

Re: Our SQL interview questions

#84

Post by xanadohnt » Tue, May 28, 2013, 1:36 PM UTC

If 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 - then you certainly as hell can solve complex problems in Go despite not having any experience there yet. Sure, if I'm trying to fill a Go position and someone has proof they're an excellent developer _and_ it's in Go then they'll get top consideration.

Being able to write SQL queries from memory has little correlation to a candidate's level of ability. Personally I consider myself a fairly strong developer and it hasn't been only until the last year that I can now write pretty complex joins from memory. And I've been developing for 20 years. Only because of a recent project and the volume of queries I had to write did my method change from using a graphical query writer to simply memorizing the syntax I need. Indeed, this very type of adaptation is something I look for in candidates.

Re: Our SQL interview questions

#85

Post by 3pt14159 » Tue, May 28, 2013, 1:37 PM UTC

codegeek wrote: post #55

shawabawa3 wrote: post #50

Earlier quoted context omitted.

> people often do an "inner join" leaving out empty departments Empty departments have less than 3 people

correct. Edited.

I was following along (without peeking ahead) and I briefly thought "What about NULLs and empty joins?" But I figured, it is an idealized test. For example, what happens when a boss has a NULL department id? Would it be safe to say that they are in a different department than their underling? SQL says no.

Besides that, I think this is a great test. Personally, I start off a bit slower so I don't embarrass people that don't know SQL.

Re: Our SQL interview questions

#86

Post by slc » Tue, May 28, 2013, 1:39 PM UTC

codegeek wrote: post #44

"List employees (names) who have a bigger salary than their boss" SELECT e1.Name FROM Employees e1 LEFT OUTER JOIN Employees e2 ON (e1.BossID = e2.EmployeeID) WHERE e1.Salary > e2.Salary "List departments that have less than 3 people in it" SELECT d.Name, COUNT(e.EmployeeID) FROM Department d LEFT OUTER JOIN Employees e ON (d.DepartmentID = e.DepartmentID) GROUP BY d.Name HAVING COUNT(e.EmployeeID) "List all departme…

Probably not the best idea to group on the Departments.Name instead of Departments.DepartmentId ..

Re: Our SQL interview questions

#87

Post by [deleted] » Tue, May 28, 2013, 1:39 PM UTC

zrail wrote: post #83

jcampbell1 wrote: post #78

What 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.

[deleted]

Re: Our SQL interview questions

#88

Post by PeterisP » Tue, May 28, 2013, 1:39 PM UTC

dalore wrote: post #32

tomp wrote: post #15

Earlier quoted context omitted.

How would you solve the first one using Django ORM?

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.

Re: Our SQL interview questions

#89

Post by jacques_chester » Tue, May 28, 2013, 1:42 PM UTC

jfb wrote: post #81

jacques_chester wrote: post #54

Earlier quoted context omitted.

Folk generally don't study relational algebra, on its own, for fun.

SQL is one of those technologies that caps how much enjoyment you can derive from understanding the related theory. Scheme, for instance, never stops giving, but you can hit a point with SQL where you start to wonder: where's my Tutorial D?

And where are my proper temporal primitives?

It sucks, but it's what we have for now.

Re: Our SQL interview questions

#90

Post by jebblue » Tue, May 28, 2013, 1:43 PM UTC

oneandoneis2 wrote: post #37

edw519 wrote: post #33

I 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…

You've heard of the FizzBuzz test, right?

I heard about it in the last year or so here on HN, otherwise I wouldn't have known what a FizzBuzz was. I've been coding for 20 years professionally and 30 for fun. I've never coded a Fibonacci either. I think colleges need to teach how to code a microcontroller to do something, build a multi-platform application, build a database application, set up a CI server, etc.