"List employees (names) who have a bigger salary than their boss"
SELECT e1.Name FROM Employees e1 LEFT OUTER JOIN Employees e2
ON (e1.BossID = e2.EmployeeID)
WHERE e1.Salary > e2.Salary
"List departments that have less than 3 people in it"
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
"List all departments along with the total salary there"
SELECT d.Name, SUM(e.Salary) FROM Department d INNER JOIN Employees e
ON (d.DepartmentID = e.DepartmentID)
GROUP BY d.Name
"List employees that don't have a boss in the same department"
SELECT e1.Name FROM Employees e1 LEFT OUTER JOIN Employees e2
ON (e1.BossID = e2.EmployeeID)
WHERE e1.DepartmentID <> e2.DepartmentID
"List all departments along with the number of people there"
SELECT d.Name, COUNT(e.EmployeeID) FROM Department d
LEFT OUTER JOIN Employees e
ON (d.DepartmentID = e.DepartmentID)
GROUP BY d.Name
Curious question: why do you always use table aliases? To keep your query shorter?
When I don't need an alias I just use the full table name for readability:
SELECT
Department.Name,
COUNT(Employees.EmployeeID)
FROM Department
JOIN Employees
ON Employees.DepartmentID = Department.DepartmentID
GROUP BY Department.Name
HAVING COUNT(Employees.EmployeeID) < 3
Good question. I always find it easier to have the aliases because sometimes, table names are too long for me to remember. Also, tehre are times when we join the same table by itself and at that point, I use x1, x2 etc. In general, aliases always work while direct table names may not work for all cases. So i just keep it simple.
* 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.
I use to use full table names if only to avoid inconsistency with aliases in other code. Now I use aliases and ignore inconsistencies. Being a purist wasn't worth it.
You assume that all people have bosses. The top boss has no boss.
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.
Two assumptions to be clarified in the interview: 4th question above doesn't apply to the top boss (who can't have a greater salary than a boss he/she doesn't have, so no issue on the 1st question). Department table has an alternate key on department name.
I was following along (without peeking ahead) and I briefly thought "What about NULLs and empty joins?" But I figured, it is an idealized test. For example, what happens when a boss has a NULL department id? Would it be safe to say that they are in a different department than their underling? SQL says no.
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 first and fourth queries you don't need LEFT JOIN because the WHERE clause guarantees that only INNER JOIN rows will match. (Presumably neither question applies to the top person.)
On the third one you don't list all departments; the empty ones are filtered out. Needs a LEFT JOIN.
