SQL test for job interview

A place to discuss the implementation and style of computer programs.

Moderators: phlip, Moderators General, Prelates

User avatar
Worker Bee
Posts: 24
Joined: Sun Dec 21, 2008 5:53 pm UTC
Location: Greater London

SQL test for job interview

Postby Worker Bee » Thu Mar 11, 2010 2:52 pm UTC

Hi all, hope all's well - I've been asked to put together an SQL test for candidates for a database reporting job, and have come up with the below:

http://img169.imageshack.us/img169/3043/sql.jpg

Just wondering if anyone would be so kind as to have a look and let me know what they think? I'm a bit worried once the candidates have all done the test it will turn out to have been far too easy / hard! The time for the test is supposed to be 30 minutes.

Many thanks.

User avatar
Xanthir
My HERO!!!
Posts: 5413
Joined: Tue Feb 20, 2007 12:49 am UTC
Location: The Googleplex
Contact:

Re: SQL test for job interview

Postby Xanthir » Thu Mar 11, 2010 3:29 pm UTC

Actually, I think that's a reasonably competent test. It tests joins, basic logic, and data manipulation. For somebody who knows their stuff the whole test should take less than 5 minutes, but if they need to experiment a bit or look up some concepts it could take longer.

Are you allowing them to look up things during the test? There are some bits that I might need to look up for the exact usage, or at least experiment with until I get it right (it's been a while since I've done a SUM, frex).

Unrelated: Argh I hate that column naming pattern. The name of the table is already right there for you, and if a column with the same name appears in multiple tables used together you can just qualify them with the table name.
(defun fibs (n &optional (a 1) (b 1)) (take n (unfold '+ a b)))

User avatar
Berengal
Superabacus Mystic of the First Rank
Posts: 2707
Joined: Thu May 24, 2007 5:51 am UTC
Location: Bergen, Norway
Contact:

Re: SQL test for job interview

Postby Berengal » Thu Mar 11, 2010 3:31 pm UTC

Time taken: 7 minutes.
Skill level: Entirely self-taught in all things SQL as a matter of simple survival. Knows some relational calculus for the math of it, but is less interested in the practical aspects. Databases are mostly "just there," and would not be missed if they suddenly were replaced with something else as long as the APIs are equally good or better.
Difficulty: Piece of cake. Most time spent reading the questions or typing answers, very little actual thinking.

I hope you don't mind me posting the answer here, but I'll remove it if you're scared of people googling their way out of it. I post them so you can verify that I got it right, as my conclusion will obviously be incorrect if I didn't. Keep in mind that I typed this straight into the forum textarea, so no tools to help.

Code: Select all

-- 1
select
  r.res_code as "Restaurant Code",
  r.res_name as "Restaurant Name"
from
  restaurant r
  left join bookings b
    on (r.res_code = b.bok_resc and b.bok_status = 'F')
where
      r.res_status = 'Y'
  and b.bok_resc is null
;

-- 2
select
  tv_channel as "Channel Code",
  -1 * (100 - sum(tv_perc)) as "Percentage disrepancy"
from
  tv
group by
  tv_channel
having
  sum(tv_perc) <> 100
;

-- 3
The subclauses in the where are negative exclusive, i.e. when one is false the other must be true, so when you or them the result will always be true, regardless of the value in the surname field. (This assumes there are no null surnames)
It is practically impossible to teach good programming to students who are motivated by money: As potential programmers they are mentally mutilated beyond hope of regeneration.

User avatar
JBJ
Posts: 1263
Joined: Fri Dec 12, 2008 6:20 pm UTC
Location: a point or extent in space

Re: SQL test for job interview

Postby JBJ » Thu Mar 11, 2010 3:42 pm UTC

I agree it's on the easy side. I'm also gainfully employed as a DBA, so I'm a little biased.

There are often several ways of getting the same results. Some are more elegant while some are a total cludge (and I've seen some cludge in my time. I'm sure I'm guilty of a few cludges myself). Are you going to be evaluating whether it works, or whether it was done to best practices?

Edit: Relevant Daily WTF today
Last edited by JBJ on Thu Mar 11, 2010 3:50 pm UTC, edited 1 time in total.
So, you sacked the cocky khaki Kicky Sack sock plucker?
The second cocky khaki Kicky Sack sock plucker I've sacked since the sixth sitting sheet slitter got sick.

User avatar
Worker Bee
Posts: 24
Joined: Sun Dec 21, 2008 5:53 pm UTC
Location: Greater London

Re: SQL test for job interview

Postby Worker Bee » Thu Mar 11, 2010 3:47 pm UTC

Thanks both - I was expecting to get replies asking me not to post my work queries on the forum!

To answer your question Xanthir, I was not planning on providing a manual, although I will have to have a think about that as I suppose you do get to use one in the job. Really useful feedback by the way, so thanks again.

Thanks for taking the time to try the questions Berengal - the 2nd and 3rd answers are spot on (although the answer I've set for the third one is 'OR should be AND', so a bit less descriptive than yours!), although the first would not do what is required (unless I've misunderstood my own question) - I was expecting something along the lines of one of these bits of code, although other ways are of course possible:

Code: Select all

SELECT
res_code AS "Restaurant Code",
res_name AS "Restaurant Name"

FROM restaurant

WHERE res_status = 'Y'

AND NOT EXISTS (
 SELECT 1 FROM bookings
 WHERE bok_resc = res_code
AND   bok_status = 'F'
)

Code: Select all

SELECT
res_code AS "Restaurant Code",
res_name AS "Restaurant Name"

FROM restaurant

WHERE res_status = 'Y'

AND res_code NOT IN (
 SELECT bok_resc
 FROM bookings
 WHERE bok_status = 'F'
)

User avatar
Worker Bee
Posts: 24
Joined: Sun Dec 21, 2008 5:53 pm UTC
Location: Greater London

Re: SQL test for job interview

Postby Worker Bee » Thu Mar 11, 2010 3:50 pm UTC

Sorry JBJ, just saw your reply - I was worried about the difficulty level as if any of the things I dealt with at work were at this level I'd be sitting around with my feet up! I'll certainly be looking at the way the code's written, as there is a large amount of existing messy (and in some rare cases nonsensical) code being used at the moment, so if it looks like somebody's going to be adding to that then that would definitely be a bad thing!

User avatar
Berengal
Superabacus Mystic of the First Rank
Posts: 2707
Joined: Thu May 24, 2007 5:51 am UTC
Location: Bergen, Norway
Contact:

Re: SQL test for job interview

Postby Berengal » Thu Mar 11, 2010 3:55 pm UTC

Worker Bee wrote:the first would not do what is required (unless I've misunderstood my own question)
I still think my version does the same. I must now promptly go check. Can't have being wrong on the internet, now can we?
It is practically impossible to teach good programming to students who are motivated by money: As potential programmers they are mentally mutilated beyond hope of regeneration.

User avatar
Xanthir
My HERO!!!
Posts: 5413
Joined: Tue Feb 20, 2007 12:49 am UTC
Location: The Googleplex
Contact:

Re: SQL test for job interview

Postby Xanthir » Thu Mar 11, 2010 3:57 pm UTC

I think easy questions that still require actual knowledge of the language at hand are useful. You won't get a true sense of the person's skills until they start working - the interview test is rather just used to weed out idiots and people who can't actually program. It's perfectly acceptable to ask a question that is super-easy for someone actually working with that technology, as it's an immediate indicator that, hey, this person actually works with that technology.

If you're in a country where it's difficult to fire people, I might give different advice, but at least here in the US it's pretty easy to keep someone on a probationary basis for a month or three and let them go if things don't work out.
(defun fibs (n &optional (a 1) (b 1)) (take n (unfold '+ a b)))

User avatar
JBJ
Posts: 1263
Joined: Fri Dec 12, 2008 6:20 pm UTC
Location: a point or extent in space

Re: SQL test for job interview

Postby JBJ » Thu Mar 11, 2010 4:02 pm UTC

Actually, Berengal's solution for #1 is spot on.

The left outer join returns all rows in the restaurant table and the rows from the status table where future bookings occur.
All the rows in the restaurant table that don't have future bookings will have a null value for BOK_RESC.
The only thing missing from Berengal's solution is that a singular restaurant may be listed multiple times (if they had no future bookings, but multiple past or cancelled) A DISTINCT operator is the only thing missing, but a unique listing wasn't specified. Edit: Nevermind, distinct isn't needed either. He took care of that with the second criteria on the join.

That's why I asked if you were going on whether it worked, or whether it was elegant. Using a subquery works, but starts becoming inefficient on larger data sets.
So, you sacked the cocky khaki Kicky Sack sock plucker?
The second cocky khaki Kicky Sack sock plucker I've sacked since the sixth sitting sheet slitter got sick.

User avatar
psykx
Posts: 408
Joined: Sat Feb 23, 2008 11:24 pm UTC
Location: England
Contact:

Re: SQL test for job interview

Postby psykx » Thu Mar 11, 2010 4:24 pm UTC

Personally I'd have one set of tables for the questions, there is no real need to have two, and having just one set would allow a shorter time to be taken in the interview. Depending on the skill level required I'd also ask them to design the tables and discuss why they have chosen what they did, I'd also be looking for an answer to your question that doesn't require use of a sub query as they are very time consuming and when writing large query they should be avoided for the most part (however there are exceptions).
Berengal wrote:Only if they're killer robots. Legos are happy robots. Besides, even if they were killer robots it wouldn't stop me. You can't stop science and all that.

User avatar
Worker Bee
Posts: 24
Joined: Sun Dec 21, 2008 5:53 pm UTC
Location: Greater London

Re: SQL test for job interview

Postby Worker Bee » Thu Mar 11, 2010 4:49 pm UTC

How embarrassing, sorry Berengal, I have just tested your code and it does indeed work perfectly! I am officially quite foolish :oops:

UMOP
Posts: 54
Joined: Sat Feb 06, 2010 1:08 am UTC

Re: SQL test for job interview

Postby UMOP » Thu Mar 11, 2010 4:52 pm UTC

Ew, don't save images that have text as jpg. Other than that, the test looks fairly simple; in fact, it's almost too simple. I haven't worked with SQL for more than few hours total, and I could answer the questions fairly easily. I'm not sure how much weeding out this test would do for you, but maybe I'm just good with SQL and don't know it yet.

User avatar
Worker Bee
Posts: 24
Joined: Sun Dec 21, 2008 5:53 pm UTC
Location: Greater London

Re: SQL test for job interview

Postby Worker Bee » Thu Mar 11, 2010 4:56 pm UTC

Sorry for the text jpg UMOP! Well, the test was written to replace the old one which was far too simple, so imagine what that one was like (I actually had to check the questions weren't trick questions when I got it)!

Peter Galbavy
Posts: 76
Joined: Wed Dec 23, 2009 11:11 am UTC
Location: London, UK
Contact:

Re: SQL test for job interview

Postby Peter Galbavy » Thu Mar 11, 2010 5:17 pm UTC

Personally I would introduce the tables using their CREATE TABLE ( ... ) structures. Also I would suggest some basic prodding to test knowledge of GROUP BY and HAVING, at least.

User avatar
JBJ
Posts: 1263
Joined: Fri Dec 12, 2008 6:20 pm UTC
Location: a point or extent in space

Re: SQL test for job interview

Postby JBJ » Thu Mar 11, 2010 5:46 pm UTC

Just out of curiosity, is the report writing going to be done in SQL Reporting Services, Crystal Reports, or some other reporting tool? Aside from just knowing the appropriate SQL to pull the right data, each of those applications have their own little quirks when it comes to presenting the report. Any thoughts of providing some questions that focus on the report writing application?

You could have someone who is well versed in all aspects of SQL, including querying, table creation, indexing, security, backups, etc... but could fail miserably when putting together an SRS report.
So, you sacked the cocky khaki Kicky Sack sock plucker?
The second cocky khaki Kicky Sack sock plucker I've sacked since the sixth sitting sheet slitter got sick.

User avatar
Worker Bee
Posts: 24
Joined: Sun Dec 21, 2008 5:53 pm UTC
Location: Greater London

Re: SQL test for job interview

Postby Worker Bee » Fri Mar 12, 2010 9:37 am UTC

The main reporting part of the role is through Business Objects, which certainly is quirky as you say! But the job description's gone out as having experience of that as desirable, but SQL as essential - I suppose the idea is that it would be a nightmare if the data got screwed up (well, there are of course backups made every day, but still, could be terrible at a busy time of year or if it just got messed up bit-by-bit over time) due to the person not knowing SQL, but if they need a while getting the hang of the functionality in the reporting tool (plus presentation of reports etc), it's not the end of the world. Plus, as Xanthir mentioned, the probation period's extremely long!


Return to “Coding”

Who is online

Users browsing this forum: No registered users and 6 guests