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

191–200 of 227 posts

Re: Our SQL interview questions

#191

Post by lkrubner »

This comment was very good:

"This is exactly why reliance upon ORMs has had a huge negative impact on engineering. Most of these are easily solved with Group By, Having, and/or other aggregate functions, but the ORMs have created this veil of complexity."

If ORMs really simplified the underlying complexity, so I didn't have to think about it, then ORMs might be worth it, but I have never worked on a large project where, at some point, I was wholly free of the underlying technology. If its a project that I work on for a year or more, there is always some moment when I need to drop down to SQL.

Re: Our SQL interview questions

#192

Post by benjiweber »

gcr wrote: post #160
kenko wrote: post #128

Earlier quoted context omitted.

And people who've never used vi are ipso facto abysmally bad?

Ignoring ed, vi is the only sane editor that you can depend on to be installed on every system you work with. Even if you know emacs, it's common to at least be familior with enough vi basics to fix a broken system (hjkl, i, :q, :w)

Unless you have the misfortune of having to work with windows systems :)

Re: Our SQL interview questions

#193

Post by 3825 »

metaphorm wrote: post #169
twistedpair wrote: post #142

Earlier quoted context omitted.

Fibonacci? That's a recent invention, right?

not sure what you mean? coding a simple function for evaluating the fibonacci sequence is a reasonable alternative to FizzBuzz that allows for some slightly more sophisticated requirements: like "code a recursive function that evaluates the first N members of the fib sequence. use memoization in your implementation and show how this runs in O(n) time complexity."

I think he was joking based on the fact that the Fibonacci number is at least an eight hundred year old concept.

Re: Our SQL interview questions

#194

Post by genwin »

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…

On the first and fourth queries you don't need LEFT JOIN because the WHERE clause guarantees that only INNER JOIN rows will match. (Presumably neither question applies to the top person.)

On the third one you don't list all departments; the empty ones are filtered out. Needs a LEFT JOIN.

Re: Our SQL interview questions

#195

Post by EGreg »

acdha wrote: post #178
EGreg wrote: post #166

It's an interesting debate. While I also feel that developers should know the underlying SQL, however all that stuff like joins, indexes etc. are actually very hard to scale beyond one machine. MySQL cluster does attempt to do it automatically, but even it has limits, and places most stuff in memory. In short, if I was looking for developers to do sharding, I would actually prefer to AVOID queries with joins, non-pk…

“that if you are using an ORM, you probably don't want a relational database. You should learn something like Riak and let it handle the distribution and provide all the partitioning and availability for you” These are not the same concept: a relational database makes sense when your application relies on relations between records. If you need to do lots of joins across many records, Riak is going to perform horribly…

As I said, it's a heuristic. If you find that you are telling your developers to use your ORM, then you probably should have gone with a NoSQL database like Riak. You can still do joins, etc. but it's in the context of things like map-reduce, and it makes sure that you can scale despite the joins.

MySQL way: SELECT * FROM a JOIN b ON x WHERE y

NoSQL way: 1) SELECT * FROM a WHERE x 2) Perform join in app layer or stored procedure.

Like it or not, when you scale you will lose one of the CAP, and NoSQL databases do the hard task of delivering an eventually consistent data store to you and letting you express yourself in the RIGHT context, which is not SQL.

Re: Our SQL interview questions

#196

Post by genwin »

twistedpair wrote: post #151
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…

You assume that all people have bosses. The top boss has no boss. You also assume that all department names are unique. Nitpicking, yes, but these questions certainly allow for a deeper discussion with the interviewer.

Two assumptions to be clarified in the interview: 4th question above doesn't apply to the top boss (who can't have a greater salary than a boss he/she doesn't have, so no issue on the 1st question). Department table has an alternate key on department name.

Re: Our SQL interview questions

#197

Post by krsunny »

btilly wrote: post #148

The question List all departments along with the number of people there has an answer using a correlated subquery, rather than a join. I have a relevant story about that. About 9 years ago now, another developer escalated a bug to me. Every time they ran a complicated auto-generated query, they got logged out of Oracle. No way! I tried it. Happened to me. Began trying to narrow it down. Ran out of connections. Got a…

Relevant story? You're hired!

Re: Our SQL interview questions

#198

Post by VLM »

dragonwriter wrote: post #182
VLM wrote: post #176

Earlier quoted context omitted.

A concrete example is some folks like the abstraction of a row as your primary "thing" and some folks like the abstraction that the data defines a row and rows don't really exist just the data. Consider the hated multiple primary key situation where you've got a autoincrementing prikey and a "real" key where you make an unique index off "full name" or something. So which is the real conceptual primary key? Shouldn't…

> Consider the hated multiple primary key situation where you've got a autoincrementing prikey and a "real" key where you make an unique index off "full name" or something. So which is the real conceptual primary key? If the full name is a real conceptual primary key , you shouldn't have introduced an autoincrement key. If the uniqueness of the fullname is a business rule but not a real conceptual restriction (a dist…

The business concept of the "real conceptual primary key" can change, perhaps dramatically, over time, as the business model changes. A real prikey never changes.

Re: Our SQL interview questions

#199

Post by genwin »

codegeek wrote: post #131
pluies wrote: post #124

Earlier quoted context omitted.

My own answers, with test data: https://gist.github.com/Pluies/5663135 Despite thinking I knew SQL reasonably well, I wouldn't have fared very well at all in an interview setting. :/ Took more time and googling than expected.

didnt google but I had to create some example data to think about the solutions. So yes, i would not be able to do it in 5 min.

I'd give someone props for getting these questions all correct in half an hour, even if some could do it sooner.

Re: Our SQL interview questions

#200

Post by dragonwriter »

VLM wrote: post #198
dragonwriter wrote: post #182

Earlier quoted context omitted.

> Consider the hated multiple primary key situation where you've got a autoincrementing prikey and a "real" key where you make an unique index off "full name" or something. So which is the real conceptual primary key? If the full name is a real conceptual primary key , you shouldn't have introduced an autoincrement key. If the uniqueness of the fullname is a business rule but not a real conceptual restriction (a dist…

The business concept of the "real conceptual primary key" can change, perhaps dramatically, over time, as the business model changes. A real prikey never changes.

> The business concept of the "real conceptual primary key" can change, perhaps dramatically, over time, as the business model changes. A real prikey never changes.

This confuses two different concepts:

If the conceptual model changes, then, yes, the candidate keys (including primary keys) of entities may change between the old model and the new model. This can be a pragmatic difficulty in migrating between different conceptual models, but that's a problem inherent in different conceptual models.

The value of a well-chosen primary key of an entity within any given model should not change, as the primary key should always be a value which identifies the entity such that a different primary key means a different identity.

Post reply on HN