7 ms·

They seem to be quite simple. For what profiles are you asking these questions?. I have used very similar questions for dev ops role.

• jacques_chester 13y ago
  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.
  • j-kidd 13y ago
    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"
• codeka 13y ago
  You'd be surprised how many people claim knowledge of relational databases but who could not answer those questions.
• arb99 13y ago
  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
  • jacques_chester 13y ago
    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.
  • jfb 13y ago
    They're looking for an outer join, I think.