News for hackers. Stuff that matters.
Re:Our SQL interview questions by slc on Tuesday May 28, 2013 @09:27AM (#5779842)
The second question is actually trickier than one might think.

The obvious answer - something like

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

is wrong.

Re:Our SQL interview questions by twistedpair on Tuesday May 28, 2013 @12:26PM (#5780941)
Call me foolish, but what about the following makes it undesirable? The question didn't ask about a null case of a department with no employees.

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

Re:Our SQL interview questions by slc on Tuesday May 28, 2013 @01:36PM (#5781333)
In your example, for each row of the result set

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

Re:Our SQL interview questions by twistedpair on Tuesday May 28, 2013 @01:48PM (#5781420)
Thanks for the clarification. 5/6 and dunce hat for me :)
Re:Our SQL interview questions by brown9-2 on Tuesday May 28, 2013 @01:12PM (#5781200)
In an interview, it would be wise to mention the special cases that might exist and how you would alter your answer if you had to taken them into account, rather than waiting to be told of the special cases.
Re:Our SQL interview questions by icedog on Tuesday May 28, 2013 @04:15PM (#5782443)
I was wondering if the author realized this. Using Microsoft SQL Server, the answer would be to use a window function like row_number or dense_rank and then select where row = 1.