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

151–160 of 227 posts

Re: Our SQL interview questions

#151

Post by twistedpair »

codegeek wrote: post #44

"List employees (names) who have a bigger salary than their boss" SELECT e1.Name FROM Employees e1 LEFT OUTER JOIN Employees e2 ON (e1.BossID = e2.EmployeeID) WHERE e1.Salary > e2.Salary "List departments that have less than 3 people in it" SELECT d.Name, COUNT(e.EmployeeID) FROM Department d LEFT OUTER JOIN Employees e ON (d.DepartmentID = e.DepartmentID) GROUP BY d.Name HAVING COUNT(e.EmployeeID) "List all departme…

You assume that all people have bosses. The top boss has no boss. You also assume that all department names are unique.

Nitpicking, yes, but these questions certainly allow for a deeper discussion with the interviewer.

Re: Our SQL interview questions

#152

Post by versk »

VLM wrote: post #150
chrislomax wrote: post #130

Earlier quoted context omitted.

Without seeing any real data, wouldn't that all be a WHERE clause? I wouldn't imagine the skill was an aggregate of the applicants. I've just skimmed over this though, I'm not having a go

Ah I was trying to imply two sets of filtering and probably have them reversed, where HR is filtering on lack of honesty which only implies lack of skill or at least average skill, whereas the secondary filtration (the having clause) would imply the test for skill.

Don't you need a group by clause in order to have a having clause?

Re: Our SQL interview questions

#153

Post by zhodge »

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…

I'm an aspiring junior developer, and I found Stanford's (online) "Introduction to Databases" course to be excellent. I particularly enjoyed the relational algebra section where we wrote relational algebra queries by hand.

From the newbie's perspective, sometimes it can feel as though beneficial resources on the basics of subjects like relational algebra, SQL, and relational databases are somewhat sparse.

Unfortunately, I did not have enough time to finish the class completely (it required, as it should, a significant amount of time dedicated each week), but I'll definitely be re-enrolling next semester as long as it is offered online again.

Re: Our SQL interview questions

#154

Post by [deleted] »

VLM wrote: post #150
chrislomax wrote: post #130

Earlier quoted context omitted.

Without seeing any real data, wouldn't that all be a WHERE clause? I wouldn't imagine the skill was an aggregate of the applicants. I've just skimmed over this though, I'm not having a go

Ah I was trying to imply two sets of filtering and probably have them reversed, where HR is filtering on lack of honesty which only implies lack of skill or at least average skill, whereas the secondary filtration (the having clause) would imply the test for skill.

[deleted]

Re: Our SQL interview questions

#155

Post by jfb »

LekkoscPiwa wrote: post #25
jacques_chester wrote: post #22

Earlier quoted context omitted.

These aren't "details memorised from a manual". These are fundamental primitives of SQL queries. You simply cannot express most serious questions without them. If someone came to me and I asked them to write fizzbuzz, an answer of the form "I would google if-thens and modulo and print statements" would be a pretty obvious no-hire.

I would imagine questions that are good to start with: how you as a developer usually start a design of a database? How do you plan it? I got this question once, I thought it was really good. On the spot I could tell the engineer who asked me that is experienced. Can't answer that after reading sql book two days earlier. In contrast to the questions from the post.

If my datastore is a SQL database, I don't want you modeling anything if you can't answer these questions.

Re: Our SQL interview questions

#156

Post by numbsafari »

I like this set of questions and the simplicity of it. I think I would only add one or two simple questions about INSERT, UPDATE and DELETE statements.

Writing a bad SELECT doesn't tend to have the same ramifications as an incorrect DELETE or UPDATE statement.

Re: Our SQL interview questions

#157

Post by twistedpair »

slc wrote: post #63

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.

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

#158

Post by xanadohnt »

nahname wrote: post #111
xanadohnt wrote: post #84

If the position you're filling is directly dependent on more-than-average SQL experience - creating a DB driver, an ORM, for ex. - then SQL-specific questions are applicable. But, by and large, this type of specific-knowledge testing is not very useful. I want to see a developer's general abilities at problem solving and the source code to back it up. If you have solved complex problems in C# - and can prove it - the…

Even the worst developers I have worked with could figure out SQL queries like this in an afternoon. If they study before the test, congratulations, we just hired a dud.

You made my point much more succinctly than I. Mainly, this type of candidate testing tells you very little (positively or negatively) whether they're strong developers. Consider an OS developer - the person may have never EVER been exposed to SQL beyond "select * from table" but is probably superior to any candidate that may ace this little SQL test. Mrs. OS developer can learn any amount of SQL syntax (and deep understanding of it as @joshyeager points out) within weeks of being hired.

Re: Our SQL interview questions

#159

Post by stonemetal »

T-hawk wrote: post #139
edw519 wrote: post #33

I took a similar test, on-line while being watched. 4 sets of 10 multiple choice questions: SQL, unix commands, vi, & HTML. Make your 10 choices, click submit, get your score. It was kinda silly, but what the heck... It was ridiculously easy and I got all 40 right without much thinking, as many people here would also, I imagine. Then I asked, "Why bother with this after reading my resume?" They answered, "We have to…

It's the state of the bottom of our industry. Those 52 programmers aren't a representative set of the general population. Those 52 programmers are the ones that can't catch on for any jobs so they keep applying over and over. It's a sampling bias. And by the way, this happens for most industries, not just technology. McDonald's has the same problem. Their majority of applicants fail at tasks like having the literacy…

After looking for a new job recently, I realized the converse is also true. The worse a company is as a place to work the more often they need to hire. Therefore the pool of available jobs mostly consists of jobs no one wants. Be picky in who you hire and be picky in where you apply.

Re: Our SQL interview questions

#160

Post by gcr »

kenko wrote: post #128
_pmf_ wrote: post #108

Earlier quoted context omitted.

> Seriously, what would be a valid reason to sort employees based on which specific editor they are habitually using? It's not for filtering out really good candidates, it's for filtering out abysmally bad candidates. Show me the dumbest Vi user and the dumbest Eclipse user you can find and you might learn something.

And people who've never used vi are ipso facto abysmally bad?

Ignoring ed, vi is the only sane editor that you can depend on to be installed on every system you work with. Even if you know emacs, it's common to at least be familior with enough vi basics to fix a broken system (hjkl, i, :q, :w)
Post reply on HN