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

211–220 of 227 posts

Re: Our SQL interview questions

#211

Post by tmzt »

brixon wrote: post #2

My first weed out question is asking them to describe a Left Outer Join. They don't have to get it exactly right, I just want to see if they ever did anything more than a two table inner join. For a Web Developer the first weed out question is to tell me the difference between a GET and an POST. Here all I really want then to know is that a GET is what generally see in the URL and a POST is commonly what you see in H…

Obviously a GET has variables and a POST doesn't (that's why they are called "GET variables")

[I've encountered this belief more than once working with PHP developers. I would hope that the answer was closer to something demonstrating knowledge of HTTP as a protocol.]

Re: Our SQL interview questions

#212

Post by tmzt »

jacques_chester wrote: post #77
jfb wrote: post #69

Earlier quoted context omitted.

And then I ask, hey, where's my BOOLEAN? And then I drink.

Oh god. And the lack of a serial/autoincrement/identity type. So. Many. Effing. Triggers. And 32-character identifiers. sigh

It's so much better to use a database that only allows one autoincrementing value per record, or one TIMESTAMP and then only allows you to have either a create timestamp or an update timestamp without writing a trigger.

I prefer the way Oracle does it, you may have to do more work but it's more explicit and flexible that way.

Re: Our SQL interview questions

#213

Post by [deleted] »

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…

[deleted]

Re: Our SQL interview questions

#214

Post by jacques_chester »

tmzt wrote: post #212
jacques_chester wrote: post #77

Earlier quoted context omitted.

Oh god. And the lack of a serial/autoincrement/identity type. So. Many. Effing. Triggers. And 32-character identifiers. sigh

It's so much better to use a database that only allows one autoincrementing value per record, or one TIMESTAMP and then only allows you to have either a create timestamp or an update timestamp without writing a trigger. I prefer the way Oracle does it, you may have to do more work but it's more explicit and flexible that way.

I don't. I prefer for the common case to be correctly and automatically handled for me.

Re: Our SQL interview questions

#215

Post by jacques_chester »

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…

> We need a name for this effect so that we can just quote it whenever this topic comes up, like Dunning-Kruger.

Sounds a lot like the Market for Lemons:

http://en.wikipedia.org/wiki/The_Market_for_Lemons

Re: Our SQL interview questions

#216

Post by jacques_chester »

pessimizer wrote: post #145
LekkoscPiwa wrote: post #25

Earlier quoted context omitted.

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.

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.

Re: Our SQL interview questions

#217

Post by qohen »

poxrud wrote: post #57
arry wrote: post #27

Which book would you recommend for learning this stuff? I'm not very interested in 800-p. gorillas; there surely must be something short, not too theoretical, and to the point.

I enjoyed SQL Antipatterns by Bill Karwin. Very easy to read and offers some practical approaches to common issues.

Bill Karwin's "SQL Anti-Patterns Strike Back" presentation on Slideshare.com presentation is worth checking out -- it's 250 slides long, covers 4 kinds of anti-patterns (in queries, DB creation and the design of both DBs and applications). And he goes through actual code examples.

Check it out here:

http://www.slideshare.net/billkarwin/sql-antipatterns-strike...

Re: Our SQL interview questions

#218

Post by jmelloy »

genwin wrote: post #183
versk wrote: post #152

Earlier quoted context omitted.

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

In SQL Server a HAVING without GROUP BY is a way to filter out duplicates.

It seems to error out.

  select name, count(*)
  from queries
  having count(*) > 1
Column 'queries.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Re: Our SQL interview questions

#219

Post by genwin »

jmelloy wrote: post #218
genwin wrote: post #183

Earlier quoted context omitted.

In SQL Server a HAVING without GROUP BY is a way to filter out duplicates.

It seems to error out. select name, count(*) from queries having count(*) > 1 Column 'queries.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Like this. There is more than one way to do it:

  -- List employees who have the biggest salary
  -- in their departments
  select
      Name
  from
      Employees e1
  where
      exists
      (
          select
              1
          from
              Employees e2
          where
              e2.DepartmentID = e1.DepartmentID
          having
              max(e2.Salary) = e1.Salary
      )

Re: Our SQL interview questions

#220

Post by SatvikBeri »

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.

I would definitely be thrilled. I wonder if intentionally putting an incorrect assumption in order to see if someone brings it up would be a good indicator. The idea wouldn't be to penalize someone who didn't notice the assumption, but to reward anyone who noticed the mistake and brought it up.
Post reply on HN