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

201–210 of 227 posts

Re: Our SQL interview questions

#201

Post by PeterisP »

gcr wrote: post #160
kenko wrote: post #128

Earlier quoted context omitted.

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)

The original interview was claimed to be for a developer position. If you're a developer in most companies, you never have to handle a random broken system, as usually any and all deployment or production maintenance is separated. DevOps is still an exception, not the rule.

You manage your workstation and your dev/test environments or VMs at most - they have the exact editor setup you like. The only interaction between your computers and "foreign" systems is the code version control system. Your editor, no matter how rare or exotic, is guaranteed to be installed on every system you work with - if you work on your systems, not manage systems of other people.

Even the OS doesn't need to match. You can easily code for Linux deployments on a MacOS or Win machine, and never touch any Linux computers. Heck, you even can code for Windows deployments on Linux machines, though sometimes testing that may be a mess and requires a VM - but you certainly can do that.

Re: Our SQL interview questions

#202

Post by mgkimsal »

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.

Yeah, you'd think it would be, but it's also indicative of someone who might be 'too big for their britches', or some other such nonesense. Given how many fakers I've encountered doing crap work for people (not just in software, but any service), I'm sadly inclined to realize that when I say "one of your questions is wrong", I'm very likely to be dismissed.

Re: Our SQL interview questions

#203

Post by icedog »

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.

I was wondering if the author realized this. Using Microsoft SQL Server, the answer would be to use a window function like row_number or dense_rank and then select where row = 1.

Re: Our SQL interview questions

#204

Post by freework »

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.

Potentially huge performance difference.

For the 99% use case, the performance hit fo the ORM is not significant enough to matter. Most projects have many tables, but only one table that actually needs to have any speed optimizations. That one table can go in NoSQL and the rest can be handled by a ORM.

Re: Our SQL interview questions

#205

Post by acdha »

EGreg wrote: post #195
acdha wrote: post #178

Earlier quoted context omitted.

“that if you are using an ORM, you probably don't want a relational database. You should learn something like Riak and let it handle the distribution and provide all the partitioning and availability for you” These are not the same concept: a relational database makes sense when your application relies on relations between records. If you need to do lots of joins across many records, Riak is going to perform horribly…

As I said, it's a heuristic. If you find that you are telling your developers to use your ORM, then you probably should have gone with a NoSQL database like Riak. You can still do joins, etc. but it's in the context of things like map-reduce, and it makes sure that you can scale despite the joins. MySQL way: SELECT * FROM a JOIN b ON x WHERE y NoSQL way: 1) SELECT * FROM a WHERE x 2) Perform join in app layer or stor…

You're still conflating different parts of the stack: an ORM has nothing to do with CAP.

> You can still do joins, etc. but it's in the context of things like map-reduce, and it makes sure that you can scale despite the joins.

Either of your examples are commonly implemented in SQL databases, too: this is a routine MySQL optimization to avoid subselects and, amusingly, one which an ORM makes significantly easier to implement:

SELECT * FROM a WHERE x; SELECT * FROM b WHERE pk IN (…list of IDs from first query…);

Again, the SQL vs. NoSQL question is about your data model and access patterns, not whether you use an ORM or magical thinking about CAP. The line between the two has become quite blurry since there are things like MySQL-backed key-value stores or Postgres extensions which allow it to handle document-store workloads without losing performance or giving up the ability to do flexible queries. This isn't a question of religion: it's just looking at your business, assessing how well you know the access patterns (SQL systems are generally more flexible) and performance requirements and picking the best solution. Anyone claiming to have a right answer for everyone is wrong.

> Like it or not, when you scale you will lose one of the CAP, and NoSQL databases do the hard task of delivering an eventually consistent data store to you and letting you express yourself in the RIGHT context, which is not SQL.

You've now gone from wrong to very dangerously wrong: there is no scale which is immune to CAP and NoSQL has no magic for avoiding this. Eventual consistency is only appropriate for some problems and, as above, can be implemented on either system. No matter what storage system you choose you're still going to have to make careful decisions about business priorities and test carefully.

Re: Our SQL interview questions

#206

Post by jfb »

VLM wrote: post #176
nahname wrote: post #113

Earlier quoted context omitted.

What's wrong with identity?

A concrete example is some folks like the abstraction of a row as your primary "thing" and some folks like the abstraction that the data defines a row and rows don't really exist just the data. Consider the hated multiple primary key situation where you've got a autoincrementing prikey and a "real" key where you make an unique index off "full name" or something. So which is the real conceptual primary key? Shouldn't…

ON UPDATE CASCADE is in the standard.

Re: Our SQL interview questions

#207

Post by megaman821 »

stakecounter wrote: post #181
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.

It does not apply to the first question, but how does the ORM handle joined columns from other tables? Is it embedded somewhere in the returned object? The best way I could think to handle this using an ORM would be for the object to contain a set of arbitrary key/value pairs to contain joined columns but it seems like a hack.

It is fetched dynamically e.g. employee.boss.department. That will usually cause two more queries to the database. You can use select_related and prefetch_related so that these objects will be loaded into the ORM in one or two queries.

Re: Our SQL interview questions

#208

Post by Gravityloss »

bluedino wrote: post #72

I recently took an SQL skill assessment test from one of the big 'testing' sites. My first problem with the test was that it was a mix of Oracle and MS SQL, when my resume said 'MySQL'. And there were questions such as 'What is the MS SQL equivalent to the Oracle keyword xxxx?' Luckily I've used it enough to not bomb that portion. To be expected with a recruiter... Anyway, some of the other questions were pretty sill…

If their recruiting is so incompetent, maybe the company is clueless otherwise as well?

Re: Our SQL interview questions

#209

Post by [deleted] »

jcampbell1 wrote: post #106
zrail wrote: post #83

Earlier quoted context omitted.

Aggregates generally do the right thing with null without the coalesce.

Thanks. It appears I need to stop overusing coalesce. I was told that sql NULL means "A value that is not yet known", which nicely explains why 1+NULL, 1 NULL, 1 = NULL is always NULL. Now I know that AVG(test_scores) produces the average of the known values automatically. - - - I just did a test, and it appears the COALESCE is needed in this case. Running an aggregate where all values are null, results in NULL (the…

[deleted]

Re: Our SQL interview questions

#210

Post by tmzt »

VLM wrote: post #123

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…

I've switched to Chive DB (chive-project.com) precisely to get away from the inanities of the PHPMyAdmin interface, it's much easier to just enter the SQL. (Working on a Chromebook so not using a native application for this.)
Post reply on HN