Our SQL interview questions

It's been like that since at least the late 90s when the spike in wages versus most other jobs both increased the financial benefits from overstating your experience and decreased the cost of having to find a new job if you were actually caught, which is surprisingly uncommon.

One major factor: companies delegate a great deal of hiring to recruiters, in part because it's expensive to maintain in-house experts. Since recruiters are usually paid by the hire rather than for sending qualified applicants there's pressure to simply use the shotgun approach of sending as many remotely plausible applicants over and hoping one of them will be hired rather than spending the non-trivial amount of time needed to find a great fit. Sometimes resumes are even altered by the recruiter to add required skills – and that probably does work well at large organizations which either don't hire well or where HR tosses every word they've used in past listings into the job description.

There is a very simple way of testing SQL knowledge. And you don't need any of this online tests or white board programming stuff.

Build your self a small sqlite database. Nothing much, but sufficient enough to test the candidates ability write queries. Give him a manual. No internet connection and now give him problems(a few select queries, joins, inserts and may be a few tests here and there to test how good the guy is in schema design). If the guy can write queries after reading the documentation, then hire him.

If he can't write queries, I mean practically on the computer and show you results he is not of much use. Even if he can answer all your white board answers.

This is applicable to any programming interview. If a person can read documentation well and find his way to write a program to solve a problem such a person makes a good hire.

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 you use the full name as the "primary key"?

Problem: What if the business logic of what a distinct user is changes from unique "full name" to unique "full name" and "telephone number". Whoops now all your foreign keys need messing with, its just a bad scene. Ditto schema changes like you finally change from ascii to utf8 or something, now all your foreign keys need changing (well thats maybe a bad example unless your ascii datatype enforces 7 bits or you're running into byte length vs character length limits...) Or you change the length, which changes the truncation perhaps, which changes your foreign keys. Also you can't just use a rule like all foreign keys are BIGINT now some are CHAR(20) some are FLOAT who knows.

On the other hand lets say you implement just a prikey. Now you can have multiple rows with the same data, because you never set up a UNIQUE INDEX.

Generally speaking if you KNOW absolutely KNOW that your schema will never change, you should probably optimize it to not have multiple keys aka a primary key and unique indexes, or data definition will never change. Very few people can guarantee it so they're better off in the real world with imaginary prikeys.

You can read a lot more about this in "SQL antipatterns" I think chapter 4 or so, but always keep in mind that beyond noob level of being able to define the overall issue, short term snapshots will occasionally (but not always) conflict with longer term thinking.

“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 because it's designed for a different problem.

CAP says nothing whatsoever about whether you want a relational or non-relational database, merely what tradeoffs you'll have to make to satisfy your business needs.

Using an ORM doesn't factor into this discussion at all other than for providing a convenient place to implement whatever system you devise to meet those needs.

It is a good way to expose real world experience.

Client requirements are usually vague enough (heck, internal requirements are sometimes vague enough) for there to be problems like this discovered later. or a given business it might be valid for there to be departments that are empty at a particular time.

Someone with extensive real world experience will know to never assume any detail not included in the specification no matter how much of a non-brainer that assumption might seem at the time. They will either ask if empty sets need to be considered, or they will preface/suffix their answer with "assuming there are no empty departments or you don't want to report on them if there are" or "assuming there might be empty departments and you want them included in the report" - either way they are showing an ability to parse requirements and identify possible ambiguity that should to be queried.

You've got a multi-level differentiator there:

* The bad candidates will not be able to give a working answer

* The fine candidates will give a working answer though might miss the exact requirement (as it isn't properly stated and they just assume)

* The best candidates will spot the deficiency in the spec