Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Our SQL interview questions

jitbit.com

Post reply on HN
Print view View original

91–100 of 227 posts

Re: Our SQL interview questions

#91

Post by jfb »

xanadohnt wrote: post #84

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

If you can't handle these sorts of queries in a forgiving interview format, then by definition you are not a strong developer in SQL. That is not to say that you are not a strong developer in general; or that you couldn't handle a job where you had to interact with a SQL datastore; merely that the interviewer is not going to be able to talk SQL with you.

Re: Our SQL interview questions

#93

Post by a8m88 »

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.

Using COALESCE here is correct, but sum() returns NULL if there were no non-NULL inputs, so COALESCE should go around it:

COALESCE(sum(salary), 0)

Re: Our SQL interview questions

#94

Post by jfb »

chris_wot wrote: post #67
clubhi wrote: post #62

Earlier quoted context omitted.

I don't see why that is a bad question. A reasonable answer is selecting from a table. Of course it depends on many factors. I often ask questions like this just to get the candidate to tell me why there is not an absolute answer.

Unless, of course, the view is a materialized view.

If a candidate can talk intelligently about materialized views, I think we're past the "explain HAVING" stage of the interview.

Re: Our SQL interview questions

#95

Post by aidos »

These 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 could excuse someone not knowing about left joins. If it were for a position that had any sort of focus on db work I would pass on the candidate (caveat, when hiring juniors I look for desire to learn above most everything else).

Obviously I'm getting old. "Back in my day" a basic understanding of SQL was just part of the job. Didn't matter what you worked on - you should be able to work with relational database. I'm concerned that the attitude of "I don't need to know that - my ORM does that for me" has become the default outlook. Over the last few years I've had to convince developers several times that the complex aggregation they're writing in their script would be easiest solved by using SQL. Unfortunately, increasingly it seems that newer developers aren't even aware that these tools are available - or how to use them.

If nothing else, relational algebra is a wonderful and elegant subject that is worth learning.

Darn kids, get off my lawn! :)

Re: Our SQL interview questions

#96

Post by specialist »

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…

We also set up an online test to screen candidates. Because HR and the recruiters kept sending us such terrible candidates.

I just can't understand how people don't score 100%. Alas, not many do.

It's saved us devs a HUGE amount of time.

Re: Our SQL interview questions

#97

Post by saosebastiao »

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…

The positions on my team usually call for people that have a strong mix of business and technical skills. We get business analysts with programming that they've picked up along the way or programmers with MBAs. Typically, when the programmers get dropped from the process, it is when we test their SQL. Sad, but yes, this is the state of the industry.

Re: Our SQL interview questions

#98

Post by jeremysmyth »

codegeek wrote: post #75
ohwp wrote: post #70

Earlier quoted context omitted.

Curious question: why do you always use table aliases? To keep your query shorter? When I don't need an alias I just use the full table name for readability: SELECT Department.Name, COUNT(Employees.EmployeeID) FROM Department JOIN Employees ON Employees.DepartmentID = Department.DepartmentID GROUP BY Department.Name HAVING COUNT(Employees.EmployeeID)

Good question. I always find it easier to have the aliases because sometimes, table names are too long for me to remember. Also, tehre are times when we join the same table by itself and at that point, I use x1, x2 etc. In general, aliases always work while direct table names may not work for all cases. So i just keep it simple.

There's also the cognitive overload of reading long identifiers, multiplied by the naming conventions of some large corporate databases.

I'd rather see:

    EmployeeReferences eFrom JOIN EmployeeReferrals eTo
...and then see

    ON eFrom.ID = eTo.ID
    ...
    JOIN xyz
    ON eFrom.Source = ...
rather than have to read acres of EmployeeRe-something 4 or 5 times through an 8-table BI join.

Similarly, I've had to deal with (admittedly legacy) tablenames like A12R18SALE and A12B14PROD. Aliases come in really handy there.

Re: Our SQL interview questions

#99

Post by jfb »

ohwp wrote: post #70
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…

Curious question: why do you always use table aliases? To keep your query shorter? When I don't need an alias I just use the full table name for readability: SELECT Department.Name, COUNT(Employees.EmployeeID) FROM Department JOIN Employees ON Employees.DepartmentID = Department.DepartmentID GROUP BY Department.Name HAVING COUNT(Employees.EmployeeID)

Too much typing.

Re: Our SQL interview questions

#100

Post by lucb1e »

Liked them; not too hard but also not too easy. I'd have succeeded on the interview if I had been given the chance to test them (and if I wasn't too nervous about it I guess). Never had an interview with technical questions like this before; are you commonly given a chance to test them?

My database and answers dump (Warning: spoilers!) http://pastebin.com/HGBpemHn

Post reply on HN