Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Our SQL interview questions

jitbit.com

Post reply on HN
Print view View original

221–227 of 227 posts

Re: Our SQL interview questions

#221

Post by Nick_C »

wmil wrote: post #102
aidos wrote: post #95

These questions are very simple, though I guess they cover a few of the core concepts. Basic selects, joins, joining the same table twice, left joins and group by. I'm most worried by the comment "(tricky - people often do an "inner join" leaving out empty departments)" . That's a basic question and if that's considered "tricky" you've got a real problem on your hands. Maybe if you're hiring for a junior position you…

> That's a basic question and if that's considered "tricky" you've got a real problem on your hands. If they aren't warned then it's reasonable to assume that every department has employees. Otherwise why would it exist?

I can think of two business reasons straight off: 1) a department changed its name, or 2) the department used to have employees but is now defunct, such as the Typing Pool department.

My point is, to try to be aware of the assumptions that you are otherwise unconsciously putting in to your program's business logic.

Re: Our SQL interview questions

#222

Post by ohwp »

jfb wrote: post #99
ohwp wrote: post #70

Earlier quoted context omitted.

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)

Too much typing.

Get a better IDE ;)

Re: Our SQL interview questions

#224

Post by tetha »

chrislomax wrote: post #162
specialist wrote: post #96

Earlier quoted context omitted.

We also set up an online test to screen candidates. Because HR and the recruiters kept sending us such terrible candidates. I just can't understand how people don't score 100%. Alas, not many do. It's saved us devs a HUGE amount of time.

I dislike the whole interview process, I'm only young so the interviewing process is still very new to me. We find that people lie heavily on CVs to the point where it is annoying. We had someone once who had 5 years CSS experience yet found it hard to center some text. I think an online test would help our cause some but I don't know how simple to make some of the questions. How long would you expect the candidate t…

It's dunning-kruger and lying in full swing, I feel.

Some people go like "Oh I worked 5 years in that tool, I must be magnificient in this tool. I shall list myself as highly professional in this area!" and do so.

Me... I rather go like "Hmm... Well I worked with C for 3-4 years, but I'm rusty as hell... and I didn't touch many libraries much, I just worked on tiny microcontrollers and implemented a highly multithreaded programming language execution environment. Guess I'm gonna call me there." Though afterwards I just roll over people in an interview.

Re: Our SQL interview questions

#225

Post by dalore »

PeterisP wrote: post #88
dalore wrote: post #32

Earlier quoted context omitted.

Employee.objects.filter(boss__salary__lte=F('salary')) Find me employee objects which have a boss salary less than or equal to the salary.

Out of curiosity, would the ORM map to the same SQL query? Or would it request all employee-boss pairs and filter them outside of the DB? There's a huge performance difference involved.

It will only be one query. The django orm won't do any filtering outside the DB that is up to you.

Re: Our SQL interview questions

#226

Post by siddboots »

jacques_chester wrote: post #216
pessimizer wrote: post #145

Earlier quoted context omitted.

What if you're not looking for an experienced data modeller, but someone who can write SQL? Also: If you can read the SQL book two days earlier and answer these questions in a reasonable amount of time - sounds like an insta-hire to me. There's certainly nothing so hard about SQL that you couldn't do that - but some people who spend a lot of time working with databases seem to find the climb insurmountable.

When I learnt SQL, the part that took me the longest to square away was the distinction between WHERE and HAVING. It was thoroughly confusing at the time. Once you grasp that conceptually, SQL generally executes left-to-right, it's easier.

I avoid `having` wherever possible these days. There are data analysts in my team that I know would have to think twice about it if they encountered it in my code, and to be honest, it's a bit of an wart in the SQL grammar anyway.

In (almost) all cases, a subquery with an extra `where` clause will have equal performance to a `having` clause in the main query.

See the final example in: http://en.wikipedia.org/wiki/Having_(SQL)

Re: Our SQL interview questions

#227

Post by bediger4000 »

danielweber wrote: post #186
mgkimsal wrote: post #138

Earlier quoted context omitted.

Took a similar test at a recruiting company in ... 2006 (IIRC). Mostly on PHP. And the test was wrong. IIRC, I got 24 out of 25. They were ecstatic - "wow, no one in this office ever got such a high score - you were almost perfect" "I was," I said. "One of those questions is wrong". "Oh, no, it couldn't be - we have a team of experts who create these to the highest standards, blah blah blah.". I asked again to go bac…

"One of your questions is wrong" is an excellent filter. People should be thrilled to get that information. I corrected two questions on a multiple-choice test that a finance firm gave me. They flushed me out after I handed in my personality test, so maybe it was for the best.

In the early 2003 time frame, I ended up taking a bunch of multiple choice tests about Java, C and C++. Every single one of them had things wrong. As near as I can tell, the auto-capitalization feature of Microsoft Word caused most of these problems. For example 'boolean' and 'Boolean' denote very different types in Java. In one test, they meant 'boolean' but had 'Boolean' written out.
Post reply on HN