They're good questions because they go past the two simplest query types:

    select from ... where ...;

and

    select from ... join ... where;

Those are the types 99% of programmers who use SQL for simple CRUD apps know. But they come up short for asking more useful business questions.

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.

For interview question, the keyword I like best would be EXISTS. Sadly, many developers write SQL for years without knowing its existence. If I am not mistaken, the propel ORM of symfony doesn't even have native support for it.

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"

You'd be surprised how many people claim knowledge of relational databases but who could not answer those questions.

Yeah they seem quite obvious really. Serious question: is this really the types of questions asked for a dev job interview? (was still interesting to see.)

>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

The empty department remark is the clue. They want all departments; if it's an empty department they still want to see that it's an empty department.

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.

They're looking for an outer join, I think.