Thursday 8 June 2017

Development : No Great Shakes at SQL

I was just handed a technical test, this was for a job interview, and I was a little... well I'll say surprised, but then shocked, by the reply, lets take a look...

I was handed these SQL statements, in several questions, and then told to summarise what I should see on any output of the various instructions....

create table customers (id INTEGER PRIMARY KEY, name Text, age INTEGER, weight REAL);
insert into customers values(73, "Brian", 42, 33);
insert into customers values(1, "Helen", 12, 12.5);
select * from customers;
SELECT name, CASE WHEN age > 18 THEN "adult" ELSE "minor" END "type" FROM customers;
create table orders (id INTEGER PRIMARY KEY, customer_id INTEGER, desc TEXT);
insert into orders values (0, 73, "Apples");
insert into orders values (1, 73, "Oranges");
insert into orders values (2, 1, "Bananas");
select * from orders;
SELECT customers.name, orders.desc FROM customers JOIN orders ON customers.id = orders.customer_id;

I drew up the two tables, their info, the results of the selects and the listing of each order by the last point.

The final question was then, "alter the final query so that only orders by customers named "Brian" are displayed.

My answer was this:

SELECT customers.name, orders.desc FROM customers JOIN orders ON customers.id = orders.customer_id where customers.id = (select id from customers where name = "Brian");

Now, I'm no great shakes at SQL, it's a tool to be used, not a mantra to adhere to, so I understood that I could also solve this problem thus:

SELECT customers.name, orders.desc FROM customers JOIN orders ON customers.id = orders.customer_id where customers.name = "Brian";

Both are valid results, and yes I admit mine results in another query (within the parenthesis) however, in my mind I wanted all parameters directly in a query to be the result of other queries, meaning I could place "Select customers where name is 'Brian'" into a function elsewhere for ease of maintenance, rather than have "Brian" hard coded into the query here.

I discussed this when myself and the technical reviewer went over my suggested solution, I explained "The query in the bracket should be stand along, either as a separate query or stored procedure".

He made a note on his clip board, and I thought nothing more about it.

Fast forward a fortnight, and this morning I heard back from this job, they said I was a very good candidate, but failed the technical test on one key point.  I looked at my notes, nothing looked that key.

I left it there, and went back to the recruiter but asked them for specific technical feedback.

The feedback I received, just an hour ago...

"Over thinks the solution to a problem" and they cited the above answer.

I am flabbergasted, it seems people really would rather my not give a valid answer, or perhaps don't want to discuss the decisions I passed through in order to reach my conclusion.

Either way, I think my solution is valid, my point about being able to move the latter query into its own location is valid, code maintenance is important to me, so being able to break down none-nonsensical ordering things like SQL statements is high on my radar, but to be told I am essentially "too verbose"... I'll be frank, it's pissed me off.

But hey, what do I know?... I'm no great shakes at SQL.

No comments:

Post a Comment