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

181–190 of 227 posts

Re: Our SQL interview questions

#181

Post by stakecounter »

dalore wrote: post #32
tomp wrote: post #15

Earlier quoted context omitted.

How would you solve the first one using Django ORM?

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.

Re: Our SQL interview questions

#182

Post by dragonwriter »

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…

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

If the full name is a real conceptual primary key, you shouldn't have introduced an autoincrement key. If the uniqueness of the fullname is a business rule but not a real conceptual restriction (a distinction which can be hard to make, to be sure), then it makes sense to create the autoincrement key -- and it is the only real primary key. (That is, the autoincrement key represents the concept of identity which isn't present in any of the other data.)

> On the other hand lets say you implement just a prikey. Now you can have multiple rows with the same data, because you never set up a UNIQUE INDEX.

No, you can't, because the "prikey", as you call it, is data, and has meaning -- specifically, it represents identity -- so rows which differ in it do not have "the same data".

Re: Our SQL interview questions

#183

Post by genwin »

versk wrote: post #152
VLM wrote: post #150

Earlier quoted context omitted.

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?

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

Re: Our SQL interview questions

#184

Post by metaphorm »

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.

this is a good question. my experience with the django ORM is that in general it resolves any call to an ORM method into SQL and nothing else, but it is not guaranteed to generate the most optimal SQL.

It is however, a somewhat common practice of django devs to to do some post-processing on a queryset in Python. totally acceptable for small querysets with complicated logic, but, yeah, obviously unacceptable for large performance critical queries.

Re: Our SQL interview questions

#185

Post by slc »

twistedpair wrote: post #157
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

In your example, for each row of the result set

* "em.departmentId" will contain one of the distinct values from the "departmentId" column

* "salary" will contain the maximum value of the "salary" column of the table rows whose "departmentId" equals "em.departmentId" of the given result set row.

* "em.EmployeeID" will contain the value of the "EmployeeID" column of one the table rows, whose "departmentID" equals "em.departmentId" of the given result set row, but it is UNDEFINED which one. It IS NOT quaranteed to be the one whose "salary" column equals "MAX(salary)".

See here for examples of how to achieve what is actually needed: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-colum...

As I said, tricky, and, judging from the difficulty level of the other questions, I suspect that the authors of the article have fallen for it themselves.

Re: Our SQL interview questions

#186

Post by danielweber »

mgkimsal wrote: post #138
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…

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.

Re: Our SQL interview questions

#187

Post by dspillett »

ohwp wrote: post #70
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…

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)

I always use table aliases as a matter of habit:

* I always specify columns as table.column, not just column as it makes things explicit where the could be ambiguity if a less experienced coder is looking (I know that column reference in a correlated sub-query refers to the most local instance of that table, but having the table name there explicitly states that referring to that was my intention and not an accident). Having short aliases saves typing in this instance (though not too short/arbitrary - the object names should still be meaningful in the context of the query: a, b, c, d, ... would generally be bad aliases)

* If the query gets more complex and needs to join objects in that have columns of the same names as those in existing objects (especially if you add another reference to an object already in use in this query), you've already got the aliases there for the first instance reducing the chance you'll get one wrong when adding them in for both instances of the same name.

Re: Our SQL interview questions

#188

Post by genwin »

ohwp wrote: post #70
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…

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)

I use to use full table names if only to avoid inconsistency with aliases in other code. Now I use aliases and ignore inconsistencies. Being a purist wasn't worth it.

Re: Our SQL interview questions

#189

Post by twistedpair »

slc wrote: post #185
twistedpair wrote: post #157

Earlier quoted context omitted.

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

In your example, for each row of the result set * "em.departmentId" will contain one of the distinct values from the "departmentId" column * "salary" will contain the maximum value of the "salary" column of the table rows whose "departmentId" equals "em.departmentId" of the given result set row. * "em.EmployeeID" will contain the value of the "EmployeeID" column of one the table rows, whose "departmentID" equals "em.…

Thanks for the clarification. 5/6 and dunce hat for me :)

Re: Our SQL interview questions

#190

Post by socialist_coder »

VLM wrote: post #163
versk wrote: post #152

Earlier quoted context omitted.

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

This is yet another of those things that gets the pgsql folks all wound up about mysql. mysql is generally permissive best effort rather than restrictive follow the spec, so HAVING is allowed to reference stuff not in a GROUP BY or an aggregate (like MAX or COUNT). As you imply, this is not allowed by the SQL standard so philosophically I would Strongly Expect pgsql to error out unlike mysql. I donno what ms-sql does…

That's messed up. "Having" should only be used for filters on the output aggregate functions, and "where" should be used for filters on the input row data. If mysql lets you use "having" when you mean "where", that is unfortunate.

example:

    select count(1) cnt, department
    from sales
    where department_id in (1, 2, 3, 4, 5)
    group by department
    having count(1) >= 100;
So, it filters out all the input rows to only those department ids, and then it filters out the aggregate output rows to only those with a count() of 100 or more.

This is how Oracle and MS-SQL server work.

Post reply on HN