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

121–130 of 227 posts

Re: Our SQL interview questions

#121

Post by jcampbell1 »

Tyrannosaurs wrote: post #14

Seems to me to be a solid test, though I might include a small amount of sample data to nudge them in the direction of some of the potential issues (empty departments and so on) - maybe I'm just kind like that. ;-) In interviews I've always been amazed how few people who claim to know SQL can use GROUP BY, HAVING and aggregate functions (or depending on the question self joins or sub queries that will allow them to a…

Removing duplicates is dependent on the underlying database because the behavior of deleting while selecting varies. I know MySQL doesn't allow it in most cases, so you need a temporary table. Identifying them is a fine question.

Re: Our SQL interview questions

#122

Post by jackmaney »

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'd recommend Head First SQL (http://www.headfirstlabs.com/books/hfsql/). I started on that book with no real coding knowledge whatsoever (except a long-forgotten Java class that I took circa 1996). Plugging away on that book a few hours a week for a few months taught me enough SQL to get my foot in the door to a new career.

Re: Our SQL interview questions

#123

Post by VLM »

I like the little schema, it flows right into more advanced discussion about how you'd deploy indexes based on the design and queries, how you'd expand the schema in normalized form into supporting an office building seating assignment for each employee, or even multi-sites for employees using a many-many table.

One thing I didn't get was one comment on the article that a guy could struggle thru this with phpmyadmin but not at the console. Maybe he was kidding or trolling. I recently install phpmyadmin to fool around and I can't imagine talking about using it, you'd have to click like fifty thousand times just to implement just a simple query and it would probably take 15 minutes, yet not reduce the cognitive load at all. How do you talk about GUIs in an interview? "Click on the icon of the fornicating centipedes, then on the cthulhu icon, then in the ribbon select the turtle crossing street sign" It makes talking about regex's seem humane in comparison.

Re: Our SQL interview questions

#124

Post by pluies »

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…

My own answers, with test data: https://gist.github.com/Pluies/5663135

Despite thinking I knew SQL reasonably well, I wouldn't have fared very well at all in an interview setting. :/ Took more time and googling than expected.

Re: Our SQL interview questions

#125

Post by jcampbell1 »

It would be really interesting to see solutions for MongoDB. These questions are designed to be easily solvable with SQL, but it would be interesting to see how this can be solved with a totally different technology.

Re: Our SQL interview questions

#126

Post by yread »

clubhi wrote: post #62
yread wrote: post #59

Much better than this question i got asked once: "Which is faster: select from a table or from a view?"

I don't see why that is a bad question. A reasonable answer is selecting from a table. Of course it depends on many factors. I often ask questions like this just to get the candidate to tell me why there is not an absolute answer.

Yeah that's the thing. I was talking about computed columns in tables, materialized (or indexed) views, about measuring stuff by experiment and about measuring stuff that actually matters but the interviewer seemed like he wanted to hear a simple answer

Re: Our SQL interview questions

#127

Post by VLM »

jcampbell1 wrote: post #121
Tyrannosaurs wrote: post #14

Seems to me to be a solid test, though I might include a small amount of sample data to nudge them in the direction of some of the potential issues (empty departments and so on) - maybe I'm just kind like that. ;-) In interviews I've always been amazed how few people who claim to know SQL can use GROUP BY, HAVING and aggregate functions (or depending on the question self joins or sub queries that will allow them to a…

Removing duplicates is dependent on the underlying database because the behavior of deleting while selecting varies. I know MySQL doesn't allow it in most cases, so you need a temporary table. Identifying them is a fine question.

You can get a list of dupes to eat without a temp table with something like:

select min(id), count(*) as dupecount from yer_table group by some_hash_identifier_or_whatever having dupecount > 1

And then just iterate thru delete from yer_table where the id = the min(id) as fetched above.

Or maybe your business logic is to keep the oldest record and zap the newest. Or based on some column data rather than simply age.

Now the really interesting discussion is how often this happens (like once-off, or every 10 seconds, or), and how scalable you need it to be. Are you talking about 100 records or 100e6 records. Also literal duplicates as in "two" works pretty well but not so good if there's 50 duplicates and you need to delete 49 of them. Of course for 49 duplicates you could select the identifier hash and the lone lowest ID and delete all entries with the same identifier hash where the id isn't the lowest id for that hash...

Re: Our SQL interview questions

#128

Post by kenko »

_pmf_ wrote: post #108
PeterisP wrote: post #82

Earlier quoted context omitted.

Maybe the previous person was an emacs guy? Seriously, what would be a valid reason to sort employees based on which specific editor they are habitually using?

> 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?

Re: Our SQL interview questions

#129

Post by genwin »

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…

I was once tasked with hiring some developers. HR used filtering software, so every candidate had C++ on their resume even though we didn't need that. Out of curiosity I asked each candidate a simple C++ question. Not one had a clue.

Re: Our SQL interview questions

#130

Post by chrislomax »

VLM wrote: post #116
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…

"Wow. Is this the state of our industry now?" Traditionally this happens when HR demands "10 years experience with windows server 2007" and you somehow snuck thru (or maybe worked at MS on the dev team, or were in a beta program or...) anyway by definition the only people making it thru the filter are going to have a weird/cool/interesting background or are going to be pathological liars who inevitably will only get…

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

Post reply on HN