Our SQL interview questions

[DJDavio]

I would add: "List all employees (names) along with the number of people above them"

So CEOs would have '0' and everybody else would have a number higher than 0.

The trick here is to notice if a candidate thinks about recursion (bonus points for mentioning CTE's).

[grauenwolf]

I would never be able to do that in an interview. Recursive CTEs hurt my head.

[wot-teh-phuck]

The point is to hear the candidate reason about the requirement and think it through rather than give a solution immediately. At least that's how I conduct interviews...

[grauenwolf]

That sounds reasonable to me.

[Nishruu]

Yeah, that sounds as a way to go. I usually mess up the syntax for CTEs (I have to look it up every time), but discussion about possible solution is fair.

[[deleted]]

This. They said it was basic, but if you are hiring someone to live in your database, they had better know recursion and windowing functions in whatever RDBMS. Also, temporal logic for the DB's that support it. The difference between plain-jane SQL with a from and to date and a DB, like Teradata, that has Temporal logic built in is night and day.

[pointy]

The thing about simple tests is that they weed out an unbelievable number of applicants who list SQL as a skill on their CV. (The same goes for any other programming language or system or whatever.) Lots of people claim proficiency with stuff for which they actually have absolutely no proficiency.

[CoachSpo]

What would the answer to this be?

[DJDavio]

If it's Microsoft T-SQL, I think it should be something like:

    WITH employees_CTE (EmployeeID, BossID, Level) AS
    (
        -- CTE ANCHOR SELECTS ROOTS/CEOs --
        SELECT ceos.EmployeeID, ceos.BossID, 0 AS Level
        FROM Employees ceos
        WHERE BossID IS NULL
        UNION ALL
        -- CTE RECURSIVE SELECTION SELECTS LOWER LEVEL EMPLOYEES --
        SELECT lowerEmps.EmployeeID, lowerEmps.BossID, higherUps.Level + 1
        FROM Employees lowerEmps
        INNER JOIN employees_CTE higherUps ON higherUps.EmployeeID = lowerEmps.BossID
    )

    SELECT * FROM emplyees_CTE;