Life Of Navin

The folks at JitBit recently came up with a very nice blogpost about their SQL hiring practices. You can read that blogpost here. The post sparked a debate on HN and other dev sites on whether ORMs are making people lose touch with SQL or simply abstracting the lower level database logic from the developer. The discussion from both camps is very interesting, with valid points being made to support either side. Personally, I feel ORMs, databases, programming languages and even programming paradigms are simply tools in a developer's arsenal. You use the right one for the job. The tools you choose for a simple PoC are completely different from the ones you choose for an enterprise project. If they're not, you have a serious problem on your hands. Unfortunately, too few developers understand this. Programmers need to be, by default, polyglot. I personally believe that the whole process of "Engineering" education is simply trying to instill that virtue in you. How successful the *system* really is (as always) open for debate.

Anyway, coming back to JitBit: They had a simple problem asked to their prospective future employees. In their own words:

The test's job is not to tell genius and rockstars from "normal" devs. The purpose is to save you time and quickly filter out DB-experienced guys from the ones that just claim to be.

Here's some solutions that me and Damodar came up with to the question in the blog. Can you spot the flaws in them (a few are obvious ;) )? Or come up with something that performs better? Let me know in the comments :)




Question: For the schema given above, answer the following questions:

1. List employees (names) who have a bigger salary than their boss:
   

   SELECT employee.name FROM employees AS employee WHERE employee.salary>(SELECT salary FROM employees WHERE employeeid=employee.bossid);

2. List employees who have the biggest salary in their departments:
   

   SELECT  name FROM employees AS employee WHERE employee.salary=(SELECT MAX(salary) FROM employees WHERE departmentid=employee.departmentid); 

3. List departments that have less than 3 people in it
   

   SELECT name FROM departments AS employee WHERE (SELECT COUNT(employeeid) FROM employees WHERE departmentid=employee.departmentid) < 3;

4. List employees that don't have a boss in the same department
   

   SELECT name FROM employees AS employee WHERE (SELECT departmentid FROM employees WHERE employeeid=employee.bossid) != employee.departmentid;

5. List all departments along with the total salary there
   

   SELECT name, salary FROM departments LEFT JOIN (SELECT departmentid , SUM(salary) AS salary FROM departments GROUP BY departmentid) AS employee ON departments.departmentid=employee.departmentid;

... Looking forward to being corrected by your alternative solutions :)

---
PS Speaking of PoCs, If you're a Java web developer, check out Spark. It's a web development framework inspired by Sinatra. Been using it quite a bit for the PoCs I've been working on recently. :)