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 do this. We've interviewed 52 programmers with resumes similar to yours and no one else got them all right. In fact, the highest score before you was 32."

Wow. Is this the state of our industry now?

And by the way, this happens for most industries, not just technology. McDonald's has the same problem. Their majority of applicants fail at tasks like having the literacy to fill out an application form or getting out of bed and showing up to work. This doesn't mean the majority of the population is that deficient, just the majority of deadweight floating around the would-be job pool.

More generally stated, the worse an applicant is for his desired job, the more times his incompetence will attend interviews to be seen. Quality performers in any business get hired quickly and don't stay in the interviewing pool. So equivalently, any interview pool will consist mostly of bad candidates.

We need a name for this effect so that we can just quote it whenever this topic comes up, like Dunning-Kruger. Anyone got a good suggestion? Joel Spolsky was the first to set it out well and become widely read[1] , but Spolsky's Law is already used for the Law of Leaky Abstractions.

[1] http://www.joelonsoftware.com/items/2005/01/27.html

Sounds a lot like the Market for Lemons:

http://en.wikipedia.org/wiki/The_Market_for_Lemons

"I was," I said. "One of those questions is wrong".

"Oh, no, it couldn't be - we have a team of experts who create these to the highest standards, blah blah blah.".

I asked again to go back, take a picture (crappy camera phone) and reviewed it again when I got home. It was wrong. Something to do with how references behaved in PHP5, and the exam's answer was right for PHP4, but the test was for PHP5.

Anyway, it was a bit depressing, and I don't want to have to go through those sorts of tests again if I don't have to.

I corrected two questions on a multiple-choice test that a finance firm gave me. They flushed me out after I handed in my personality test, so maybe it was for the best.

You're aware that not everyone tells the truth on their resume right?

> Is this the state of our industry now?

Now? It's always been this bad... Happens when demand for talent out strips supply - the bar keeps falling.

Traditionally this happens when HR demands "10 years experience with windows server 2007" and you somehow snuck thru (or maybe worked at MS on the dev team, or were in a beta program or...) anyway by definition the only people making it thru the filter are going to have a weird/cool/interesting background or are going to be pathological liars who inevitably will only get 32 on the 40 point test, because they filtered all the real applicants out by applying a bad filter.

I guess the SQL analogy would be something like:

SELECT COUNT(*)

FROM applicant

WHERE applicant.skill > 40

HAVING applicant.skill < 10 AND applicant.honesty < 0

I've just skimmed over this though, I'm not having a go

About 99% of the noise about mysql vs pgsql boils down to this overarching philosophical different of "try yer best" vs "only perfection is permissible". There are minor other differences aside from that, none of which I can remember at this time.

I was mostly trying to make a joke and making the psuedocode kinda sql inspired rather than cut and paste into a window like a stack exchange answer. I could have implemented it "properly" as a nested subquery I suppose. Or to make the point a little more .. obviously, just "select 0;"

example:

    select count(1) cnt, department
    from sales
    where department_id in (1, 2, 3, 4, 5)
    group by department
    having count(1) >= 100;

This is how Oracle and MS-SQL server work.

  select name, count(*)
  from queries
  having count(*) > 1

  -- List employees who have the biggest salary
  -- in their departments
  select
      Name
  from
      Employees e1
  where
      exists
      (
          select
              1
          from
              Employees e2
          where
              e2.DepartmentID = e1.DepartmentID
          having
              max(e2.Salary) = e1.Salary
      )

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.

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

You manage your workstation and your dev/test environments or VMs at most - they have the exact editor setup you like. The only interaction between your computers and "foreign" systems is the code version control system. Your editor, no matter how rare or exotic, is guaranteed to be installed on every system you work with - if you work on your systems, not manage systems of other people.

Even the OS doesn't need to match. You can easily code for Linux deployments on a MacOS or Win machine, and never touch any Linux computers. Heck, you even can code for Windows deployments on Linux machines, though sometimes testing that may be a mess and requires a VM - but you certainly can do that.

A few false negatives might be acceptable.

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.

I think an online test would help our cause some but I don't know how simple to make some of the questions. How long would you expect the candidate to be taking the test? Do you tailor the test to the candidates?

Some people go like "Oh I worked 5 years in that tool, I must be magnificient in this tool. I shall list myself as highly professional in this area!" and do so.

Me... I rather go like "Hmm... Well I worked with C for 3-4 years, but I'm rusty as hell... and I didn't touch many libraries much, I just worked on tiny microcontrollers and implemented a highly multithreaded programming language execution environment. Guess I'm gonna call me <somewhat experienced> there." Though afterwards I just roll over people in an interview.

    SELECT e1.Name FROM Employees e1 LEFT OUTER JOIN     Employees e2
    ON (e1.BossID = e2.EmployeeID)
    WHERE e1.Salary > e2.Salary

    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) < 3

    SELECT d.Name, SUM(e.Salary) FROM Department d INNER JOIN Employees e
    ON (d.DepartmentID = e.DepartmentID)
    GROUP BY d.Name

    SELECT e1.Name FROM Employees e1 LEFT OUTER JOIN        Employees e2
    ON (e1.BossID = e2.EmployeeID)
    WHERE e1.DepartmentID <> e2.DepartmentID

    SELECT d.Name, COUNT(e.EmployeeID) FROM Department d 
    LEFT OUTER JOIN Employees e
    ON (d.DepartmentID = e.DepartmentID)
    GROUP BY d.Name

  SELECT
    Department.Name,
    COUNT(Employees.EmployeeID)
  FROM Department
  JOIN Employees
    ON Employees.DepartmentID = Department.DepartmentID
  GROUP BY Department.Name
  HAVING COUNT(Employees.EmployeeID) < 3

I'd rather see:

    EmployeeReferences eFrom JOIN EmployeeReferrals eTo

    ON eFrom.ID = eTo.ID
    ...
    JOIN xyz
    ON eFrom.Source = ...

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

* I always specify columns as table.column, not just column as it makes things explicit where the could be ambiguity if a less experienced coder is looking (I know that column reference in a correlated sub-query refers to the most local instance of that table, but having the table name there explicitly states that referring to that was my intention and not an accident). Having short aliases saves typing in this instance (though not too short/arbitrary - the object names should still be meaningful in the context of the query: a, b, c, d, ... would generally be bad aliases)

* If the query gets more complex and needs to join objects in that have columns of the same names as those in existing objects (especially if you add another reference to an object already in use in this query), you've already got the aliases there for the first instance reducing the chance you'll get one wrong when adding them in for both instances of the same name.

Nitpicking, yes, but these questions certainly allow for a deeper discussion with the interviewer.

Isn't that why a left outer join is used?

Besides, if you are referring to question #1, a person without a boss can never be a part of the set of employees who have a higher salary than their boss.

Empty departments have less than 3 people

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.

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.

On the third one you don't list all departments; the empty ones are filtered out. Needs a LEFT 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 DBA to unwedge the machine. Began again. Ran out of connections again. Got the same DBA to unwedge the machine. Received a lecture about not opening so many connections, replied that I was tracking down a bug and had no choice. Showed him the bug. He was astounded.

Not long after I finished tracking it down and sent them the fix. Showed it to the DBA who verified that it had been reported already, and was fixed in the next release.

The bug was that any time there was a correlated subquery with no records, Oracle logged you out. My guess is that something, somewhere, followed a null pointer. The obvious solution was to move to a left join. If I remember correctly, the way it was being autogenerated made that hard. My solution was to have a correlated subquery which was a left join on DUAL so that there was always a record.

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! :)

If they aren't warned then it's reasonable to assume that every department has employees. Otherwise why would it exist?

Really, in a philosophical sort of way, it's the use of the query that determines which join to use. They want ALL departments, you give them ALL departments. Using a left join you protect yourself in the future - with a performance hit. As you say, if there was a guarantee that the data didn't contain empty departments you'd use an inner join.

Most importantly, a reasonable developer should know to ask the question - if not of the examiner, at least to themselves. Just making an assumption is not the right approach.

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

My point is, to try to be aware of the assumptions that you are otherwise unconsciously putting in to your program's business logic.

From the newbie's perspective, sometimes it can feel as though beneficial resources on the basics of subjects like relational algebra, SQL, and relational databases are somewhat sparse.

Unfortunately, I did not have enough time to finish the class completely (it required, as it should, a significant amount of time dedicated each week), but I'll definitely be re-enrolling next semester as long as it is offered online again.

1 particular company basically wanted an entire application to be developed in an evening, and I was giving strict instructions to focus on security and not allowed to use external libraries. After submitting this elaborate task, I was still criticized on using PDO (which is standard with PHP...).

IMHO, sometimes the lengths employees go through to find a developer are so ridiculous that they actually drive away people.

Anyway, some of the other questions were pretty silly like "Which of the following is a DDL command?", and many were SELECT statements with a syntax error that you had to pick out, and probably the one question that made sense was about the difference between WHERE and HAVING.

In interviews I've always been amazed how few people who claim to know SQL can use GROUP BY, HAVING and aggregate functions (or depending on the question self joins or sub queries that will allow them to achieve some of the same things).

My normal question is to present them a table with a level of duplication ask them to write something in SQL that identifies the duplications and something that removes them which covers much of the same syntax.

select min(id), count(*) as dupecount from yer_table group by some_hash_identifier_or_whatever having dupecount > 1

And then just iterate thru delete from yer_table where the id = the min(id) as fetched above.

Or maybe your business logic is to keep the oldest record and zap the newest. Or based on some column data rather than simply age.

Now the really interesting discussion is how often this happens (like once-off, or every 10 seconds, or), and how scalable you need it to be. Are you talking about 100 records or 100e6 records. Also literal duplicates as in "two" works pretty well but not so good if there's 50 duplicates and you need to delete 49 of them. Of course for 49 duplicates you could select the identifier hash and the lone lowest ID and delete all entries with the same identifier hash where the id isn't the lowest id for that hash...

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.

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.

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

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

Said wrongly, it implies that the company has crap people working at it, and that's generally not a way to get hired. But most companies know they're not getting the 'best of the best' when they hire - they're getting the best they can afford in their geographic area during a certain time frame.

If you answered 10, you just might find Guido or Josh Bloch on your interview panel.

Has there ever been a great company or great interviewer who would seriously ask this question?

Ask people about difference between LEFT JOIN and RIGHT JOIN, or using the schema from the article, to select all attributes of employees with the highest salary in their department in pure SQL and you will see how much or how little people know, in fact many webdevs don't even understand JOINs at all!

Check it out here:

http://www.slideshare.net/billkarwin/sql-antipatterns-strike...

Pick books focusing to SQL, not on databases overall.

Most database textbooks (Date, Ramakrishnan & Gehrke) cover a lot of ground, rather than just the language.

For a Web Developer the first weed out question is to tell me the difference between a GET and an POST. Here all I really want then to know is that a GET is what generally see in the URL and a POST is commonly what you see in HTML Forms. I want to see if all they ever did was ASP.NET WYSIWYG web development or if they actually know something about the internet.

These two questions can be done in a phone screen. The faster that you can weed out people the cheaper the hiring process is.

I used to be one of those guys, but I'm much less grumpy about it these days so I'd still pass your test. I have a sad suspicion that I'm on the progressive end of the spectrum when it comes to guys who deeply understand SQL.

Now I work on different project and we use join syntax, but I could easily imagine people that do joins all day, and not know JOIN .. ON .. syntax.

And the lack of a serial/autoincrement/identity type.

So. Many. Effing. Triggers.

And 32-character identifiers.

sigh

I prefer the way Oracle does it, you may have to do more work but it's more explicit and flexible that way.

I personally have used the *= syntax more than the LEFT syntax, but that concept has helped filter people since I am not allowed to "test" people.

[I've encountered this belief more than once working with PHP developers. I would hope that the answer was closer to something demonstrating knowledge of HTTP as a protocol.]

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.

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.

This is not complex problem solving, this is problem solving. I'm not sure what memorization has to do with any of this. SQL is a language with only maybe 10-15 important words. It's not like trying to get around Paris without a phrasebook as a non-French speaker.

I've found that using graphical query writers (I only know of the one in access) and ORMs are generally harder than writing the SQL. ORMs are good for keeping code db agnostic, though.

One thing I didn't get was one comment on the article that a guy could struggle thru this with phpmyadmin but not at the console. Maybe he was kidding or trolling. I recently install phpmyadmin to fool around and I can't imagine talking about using it, you'd have to click like fifty thousand times just to implement just a simple query and it would probably take 15 minutes, yet not reduce the cognitive load at all. How do you talk about GUIs in an interview? "Click on the icon of the fornicating centipedes, then on the cthulhu icon, then in the ribbon select the turtle crossing street sign" It makes talking about regex's seem humane in comparison.

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.

Someone who can put together a statement that's broadly right with small errors normally means someone who is rusty (or nervous) but knows their stuff rather than someone who is guessing and a couple of follow up questions will usually confirm that.

General rule for me: don't ask anything that a decent IDE or 10 seconds with a manual / help file / Google would have prevented (unless you've given them a decent ID or similar in which case it's fair game).

Find me employee objects which have a boss salary less than or equal to the salary.

There's a huge performance difference involved.

For the 99% use case, the performance hit fo the ORM is not significant enough to matter. Most projects have many tables, but only one table that actually needs to have any speed optimizations. That one table can go in NoSQL and the rest can be handled by a ORM.

It is however, a somewhat common practice of django devs to to do some post-processing on a queryset in Python. totally acceptable for small querysets with complicated logic, but, yeah, obviously unacceptable for large performance critical queries.

  from .models import Employee
  from django.db.models import F

  print Employee.objects.filter(
      salary__gt=F('boss__salary')
  )

    Employee.objects.filter(boss__salary__lte=F('salary')).values_list('names', flat=True)

I would also use some sample real world data to check if my queries scale well instead of inserting 10 rows and running all queries on it.

http://stackoverflow.com/questions/57068/good-databases-with...

    SELECT  Departments.name, SUM(COALESCE(salary,0))
    FROM Departments 
    LEFT JOIN employees USING departmentID
    GROUP BY 1

COALESCE(sum(salary), 0)

- - -

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.

Aggregates generally do the most-likely-to-be-right thing with NULL values if there is at least one non-null input to the aggregate. The thing is, if you depend on this, you'll run into real data situations where all the inputs are NULL, the result is NULL, and that's not what you expected.

If you are aggregating over an expression that can be NULL, and you always want a non-NULL answer, you probably need to use coalesce or something similar so that you don't have non-NULL inputs to the aggregate.

At least this should be quicker in an interview situation than the usual "normalize this database" or "given this situation, define a database schema".

https://gist.github.com/abkr/5662615

http://sqlfiddle.com/#!1/778bb/5

https://gist.github.com/dritterman/5662750

Writing a bad SELECT doesn't tend to have the same ramifications as an incorrect DELETE or UPDATE statement.

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.

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 distinction which can be hard to make, to be sure), then it makes sense to create the autoincrement key -- and it is the only real primary key. (That is, the autoincrement key represents the concept of identity which isn't present in any of the other data.)

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

No, you can't, because the "prikey", as you call it, is data, and has meaning -- specifically, it represents identity -- so rows which differ in it do not have "the same data".

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.

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

The obvious answer - something like

select Name, MAX(Salary) from Employees group by DepartmentId

is wrong.

-- List employees who have the biggest salary in their departments SELECT em.EmployeeID, em.departmentId, MAX(salary) as salary FROM employees em GROUP BY em.departmentId

* "em.departmentId" will contain one of the distinct values from the "departmentId" column

* "salary" will contain the maximum value of the "salary" column of the table rows whose "departmentId" equals "em.departmentId" of the given result set row.

* "em.EmployeeID" will contain the value of the "EmployeeID" column of one the table rows, whose "departmentID" equals "em.departmentId" of the given result set row, but it is UNDEFINED which one. It IS NOT quaranteed to be the one whose "salary" column equals "MAX(salary)".

See here for examples of how to achieve what is actually needed: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-colum...

As I said, tricky, and, judging from the difficulty level of the other questions, I suspect that the authors of the article have fallen for it themselves.

SQLAlchemy is a great example of this, I always get precisely the SQL query I would've written myself, except it's syntactically correct, easy to compose and has a chance of being portable.

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.

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.

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

Either of your examples are commonly implemented in SQL databases, too: this is a routine MySQL optimization to avoid subselects and, amusingly, one which an ORM makes significantly easier to implement:

SELECT * FROM a WHERE x; SELECT * FROM b WHERE pk IN (…list of IDs from first query…);

Again, the SQL vs. NoSQL question is about your data model and access patterns, not whether you use an ORM or magical thinking about CAP. The line between the two has become quite blurry since there are things like MySQL-backed key-value stores or Postgres extensions which allow it to handle document-store workloads without losing performance or giving up the ability to do flexible queries. This isn't a question of religion: it's just looking at your business, assessing how well you know the access patterns (SQL systems are generally more flexible) and performance requirements and picking the best solution. Anyone claiming to have a right answer for everyone is wrong.

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

You've now gone from wrong to very dangerously wrong: there is no scale which is immune to CAP and NoSQL has no magic for avoiding this. Eventual consistency is only appropriate for some problems and, as above, can be implemented on either system. No matter what storage system you choose you're still going to have to make careful decisions about business priorities and test carefully.

"Which is faster: select from a table or from a view?"

    select from ... where ...;

    select from ... join ... where;

Eyeballing the list, it tests subqueries, GROUP BY, HAVING, OUTER JOIN, IN/NOT IN and SUM. Fairly useful primitives for general query writing.

I'd try to add a question that relies on UNION, INTERSECT or EXCEPT.

As shown by codegeek, the 6 questions here can be answered without needing sub-query. Maybe we can add something like "List employees who are not working alone in their department"

>List all departments along with the number of people there (tricky - people often do an "inner join" leaving out empty departments)

inner join seems the non obvious way to do it really IMO.

    select 
    departments.name as "department name",  
    (select sum(salary) from employees where employees.departmentid = departments.departmentid) as "department total salary" 
    from departments

An inner join will hide that row because there's no equality between a set (departments) and an empty set (employees in that dept, of which there are none). A correctly structured outer join will.

http://sqlfiddle.com/#!2/9a84e

I respect interviews that go along the lines of: "What would you do if..." after giving a detailed description of their environment. But then again I'm a tools/OSes admin, so maybe it makes more sense for my job description. But anytime guys are too focused on third option you can give to the 'ls' command and don't ask real world questions related to potential or existing issues they have/had, I'm not really interested. I can use google, you know, if that answers your question.

I like questions where the interviewer can see years of experience not the amount of detail memorized from a manual.

I would imagine questions that are good to start with: how you as a developer usually start a design of a database? How do you plan it? I got this question once, it's really good. Can't answer that after reading sql book two days earlier. In contrast to his question. </Blunt>

If someone came to me and I asked them to write fizzbuzz, an answer of the form "I would google if-thens and modulo and print statements" would be a pretty obvious no-hire.

Indeed, one of the reasons normalisation is such a Big Deal to relational bigots like me is that it makes SQL's querying tools much more useful and versatile.

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.

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

Also: If you can read the SQL book two days earlier and answer these questions in a reasonable amount of time - sounds like an insta-hire to me. There's certainly nothing so hard about SQL that you couldn't do that - but some people who spend a lot of time working with databases seem to find the climb insurmountable.

Once you grasp that conceptually, SQL generally executes left-to-right, it's easier.

In (almost) all cases, a subquery with an extra `where` clause will have equal performance to a `having` clause in the main query.

See the final example in: http://en.wikipedia.org/wiki/Having_(SQL)

In terms of "what would you do if" type questions - they're often no less prone to book answers. What you really want is "give me a specific example of when this happened to you and how did you behave" as they're more likely to test real experience. Sure people can lie but when you start digging into it few people are good enough to continually make up exact details on the spot without getting suspiciously vague (or having behaved in a suspiciously perfect manner).

"Some people might say it is too basic, but that's not the point. The test's job is not to tell genius and rockstars from "normal" devs. The purpose is to save you time and quickly filter out DB-experienced guys from the ones that just claim to be."

(Not it!)

What that line is can be fuzzy.

Consider C, what is the difference between the stack and the heap? yes you can look this up but if a candidate doesn't know this cold then they just can't write C code properly.

Simiarly for sql if a candidate can't write a simple join then it's pretty obvious that they haven't really used sql before.

The sql questions asked here are somewhere around the fizzbuzz level of skill. Any sql user should be able to answer them given a properly setup dev environment.

Knowing why and which solution is best will get you the job.

Instead of paying a nominal price for people relearning some primitives for a specialized area of IT/software dev, nowadays most companies want the new kids to go to $80,000 worth of college for the same. That way, the employer gets to skip training, and the student is less likely to leave because he or she has too much damned debt to be mobile.

Maybe I'm projecting.

But I'm not a fan of people stating that see these kinds of tests as an attack, or see it as something which is unnecessary. There are a lot of people out there are unable to answer these questions, even with "google". And most of them are either unwilling or unable to improve themselves.

I'd rather have a very eager "junior" who loves what he does, and works hard to understand and develop him/herself than someone "senior" who knows a couple of tricks and is too arrogant to do these kinds of tests.

You keep people if: * they can improve / if they learn * if the colleagues are nice * and the product is interesting, aka they have meaning * management is done reasonably * there's a future for them

this is why startups are popular (learn lots of things, meaningful, good upside) as well as corporates (carreer path is flexible, good management, etc).

This person did not qualify to be _knowledgeable_, as he refused to do this test ;)