13 ms·
Our SQL interview questions
- brixon 13y agoMy 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 HTML Forms. I want to see if all they ever did was ASP.NET WYSIWYG web development or if they actually know something about the internet. These two questions can be done in a phone screen. The faster that you can weed out people the cheaper the hiring process is.
- jasonkester 13y agoCareful using that terminology. You'll get false negatives on people who still think in terms of *= syntax, and will tell you that the words "left", "outer", and "join" are all redundant and probably don't belong in SQL in the first place. I used to be one of those guys, but I'm much less grumpy about it these days so I'd still pass your test. I have a sad suspicion that I'm on the progressive end of the spectrum when it comes to guys who deeply understand SQL.
- ajuc 13y agoYep, I've done a lot of PL/SQL programming, and we've always used (+)= syntax and joins with all the tables after commas and all the joining conditions after WHERE. Now I work on different project and we use join syntax, but I could easily imagine people that do joins all day, and not know JOIN .. ON .. syntax.
- jacques_chester 13y agoI came to Oracle after it adopted the ANSI syntax, so that's what I use. So my experience is the opposite of yours -- when I see the (+) I need to look up the syntax to remember if it's left or right outer.
- jfb 13y agoAnd then I ask, hey, where's my BOOLEAN? And then I drink.
- jacques_chester 13y agoOh god. And the lack of a serial/autoincrement/identity type. So. Many. Effing. Triggers. And 32-character identifiers. sigh
- tmzt 13y agoIt'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.
- jacques_chester 13y agoI don't. I prefer for the common case to be correctly and automatically handled for me.
- brixon 13y agoTrue, I use it for a conversation starter. You can tell the split second after you ask the question if you will get any kind of reasonable answer. I personally have used the *= syntax more than the LEFT syntax, but that concept has helped filter people since I am not allowed to "test" people.
- zerr 13y agoGood point - that they don't have to get it exactly right. For me, it would be enough if the candidate is just aware that there are different types of Joins for different scenarios.
- Ecio78 13y agoInteresting, so I think I'll pass your first screening even though I'm not a (web) developer but (most of all) a system/network engineer :D
- tmzt 13y agoObviously 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.]
- free 13y agoThey seem to be quite simple. For what profiles are you asking these questions?. I have used very similar questions for dev ops role.
- jacques_chester 13y agoThey're good questions because they go past the two simplest query types: select from ... where ...; and select from ... join ... where; Those are the types 99% of programmers who use SQL for simple CRUD apps know. But they come up short for asking more useful business questions. Eyeballing the list, it tests subqueries, GROUP BY, HAVING, OUTER JOIN, IN/NOT IN and SUM. Fairly useful primitives for general query writing. I'd try to add a question that relies on UNION, INTERSECT or EXCEPT.
- j-kidd 13y agoFor interview question, the keyword I like best would be EXISTS. Sadly, many developers write SQL for years without knowing its existence. If I am not mistaken, the propel ORM of symfony doesn't even have native support for it. As shown by codegeek, the 6 questions here can be answered without needing sub-query. Maybe we can add something like "List employees who are not working alone in their department"
- codeka 13y agoYou'd be surprised how many people claim knowledge of relational databases but who could not answer those questions.
- arb99 13y agoYeah they seem quite obvious really. Serious question: is this really the types of questions asked for a dev job interview? (was still interesting to see.) >List all departments along with the number of people there (tricky - people often do an "inner join" leaving out empty departments) inner join seems the non obvious way to do it really IMO. select departments.name as "department name", (select sum(salary) from employees where employees.departmentid = departments.departmentid) as "department total salary" from departments
- pikewood 13y agoOne initial step I use is to give a description of the problem, then have them actually design the table structure themselves. I then have a printed copy of the structure along with some sample data ready, so that I can refer to specific values (and also to help them visualize the contents).
- gfosco 13y agoThis is a great way to start... A defined schema and very clear and simple requirements, I could dash these off really quickly, and then we could discuss optimization. So much better than asking me to define a join. I miss SQL. http://stackoverflow.com/search?tab=votes&q=user%3a353988%20%5bsql%5d http://stackoverflow.com/search?tab=votes&q=user%3a35398...
- 300bps 13y agoThis is much more useful than the typical question I've received at some companies - "On a scale of 1 to 10, how would you rate your SQL knowledge?"
- saosebastiao 13y agoEverybody answers 8.
- mgkimsal 13y agoI've had this and have answered something like: "Compared to most other devs I work with, 7 or sometimes 8. Compared to people who do SQL for a living, possibly a 4, or a 5 if I'm feeling cocky. It all depends on what the '10' really represents - the best of the best, or the best of the people I'd be working with." Well, something like that. That last bit - I've said it more tactfully in the past.
- jfb 13y agoThis is the only way to answer this question.
- mgkimsal 13y agoThanks :) Said wrongly, it implies that the company has crap people working at it, and that's generally not a way to get hired. But most companies know they're not getting the 'best of the best' when they hire - they're getting the best they can afford in their geographic area during a certain time frame.
- brown9-2 13y agoThis seems like a great sign of the overall condition of the company, or at least their hiring practices. Has there ever been a great company or great interviewer who would seriously ask this question?
- packetslave 13y agoWhen I was interviewing, I was told to think carefully about my answers to the "rate yourself 1-10 on your Python/Java knowledge" pre-screen questions. If you answered 10, you just might find Guido or Josh Bloch on your interview panel.
- deleted 13y ago[deleted]
- pramodliv1 13y agoNice set of questions. I would also use some sample real world data to check if my queries scale well instead of inserting 10 rows and running all queries on it. http://stackoverflow.com/questions/57068/good-databases-with-sample-data http://stackoverflow.com/questions/57068/good-databases-with...
- acjohnson55 13y agoI could nail those with the Django ORM, but I'd struggle to write syntactically correct SQL, not having done it in a while. But it says that your test machine has MS-SQL; with the machine in front of me, I could probably puzzle it out the join quirks with a couple minutes of trial and error.
- Tyrannosaurs 13y agoI think if you don't give someone a machine you should be pretty forgiving about exact syntax. Someone who can put together a statement that's broadly right with small errors normally means someone who is rusty (or nervous) but knows their stuff rather than someone who is guessing and a couple of follow up questions will usually confirm that. General rule for me: don't ask anything that a decent IDE or 10 seconds with a manual / help file / Google would have prevented (unless you've given them a decent ID or similar in which case it's fair game).
- tomp 13y agoHow would you solve the first one using Django ORM?
- megaman821 13y agoEmployee.objects.filter(boss__salary__lte=F('salary')).values_list('names', flat=True)
- dalore 13y agoEmployee.objects.filter(boss__salary__lte=F('salary')) Find me employee objects which have a boss salary less than or equal to the salary.
- PeterisP 13y agoOut 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.
- Tyrannosaurs 13y agoSeems 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 achieve some of the same things). My normal question is to present them a table with a level of duplication ask them to write something in SQL that identifies the duplications and something that removes them which covers much of the same syntax.
- jcampbell1 13y agoRemoving 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.
- VLM 13y agoYou 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...
- Tyrannosaurs 13y agoI give a basic single table schema with sample data so they have all the information they need to work with.
- codeka 13y agoI find it kind of interesting that the first comment on the article says they wouldn't be able to answer the questions by they use an ORM. I'm not really a fan of ORMs, personally, because I don't think it's useful to try and "map" a relational model onto an object-oriented one.
- lucian1900 13y agoThere are some really good SQL libraries out there that make it easy to compose SQL and some of them also include an ORM built on top of the basic abstraction. SQLAlchemy is a great example of this, I always get precisely the SQL query I would've written myself, except it's syntactically correct, easy to compose and has a chance of being portable.
- mhd 13y agoA sample SQL file with dummy data would be much appreciated, I think. I guess some of us would like to try their luck. At least this should be quicker in an interview situation than the usual "normalize this database" or "given this situation, define a database schema".
- arb99 13y agoI made the tables quick to go through them. It could do with more data really but its got a few rows. https://gist.github.com/abkr/5662615 https://gist.github.com/abkr/5662615
- petepete 13y agoI had the same idea, but in an SQL Fiddle http://sqlfiddle.com/#!1/778bb/5 http://sqlfiddle.com/#!1/778bb/5
- mhd 13y agoDidn't even know that there was such a thing as SQL Fiddle. That's going to come in handy, beyond this quiz, thanks.
- lostsock 13y agoIn the same vain here are some very quickly put together answers if anyone is interested. I haven't tested these so they may not work. If you see any errors please let me know :) https://gist.github.com/dritterman/5662750 https://gist.github.com/dritterman/5662750
- LekkoscPiwa 13y ago<Blunt> I'm not a SQL Developer, but anytime I get questions for which Google has answers to be found in 5 minutes or less, I'm quite hesitant to work there. I respect interviews that go along the lines of: "What would you do if..." after giving a detailed description of their environment. But then again I'm a tools/OSes admin, so maybe it makes more sense for my job description. But anytime guys are too focused on third option you can give to the 'ls' command and don't ask real world questions related to potential or existing issues they have/had, I'm not really interested. I can use google, you know, if that answers your question. I like questions where the interviewer can see years of experience not the amount of detail memorized from a manual. 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, it's really good. Can't answer that after reading sql book two days earlier. In contrast to his question. </Blunt>
- jacques_chester 13y agoThese 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.
- LekkoscPiwa 13y agoI 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.
- jacques_chester 13y agoIn a business setting, the conceptual/relational, logical and physical design of a database happens far less often than querying such databases. Indeed, one of the reasons normalisation is such a Big Deal to relational bigots like me is that it makes SQL's querying tools much more useful and versatile.
- arry 13y agoWhich 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.
- jacques_chester 13y agoA little theory goes a long way in SQL. Pick books focusing to SQL, not on databases overall. Most database textbooks (Date, Ramakrishnan & Gehrke) cover a lot of ground, rather than just the language.
- poxrud 13y agoI enjoyed SQL Antipatterns by Bill Karwin. Very easy to read and offers some practical approaches to common issues.
- qohen 13y agoBill 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-back http://www.slideshare.net/billkarwin/sql-antipatterns-strike...
- mkoble11 13y agoBen Forta's Teach Yourself SQL in 10 minutes http://www.forta.com/books/0672336073/ http://www.forta.com/books/0672336073/
- jackmaney 13y agoI'd recommend Head First SQL (http://www.headfirstlabs.com/books/hfsql/ 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.
- megaman821 13y agoIf you can only answer these using an ORM, then you really shouldn't put knowledge of SQL on your resume. I am not really into giving programming tests to interviewees, but if you claim to have experience with something you should be able to answer simple questions about it.
- jfb 13y agoI wouldn't treat this sort of thing as dispositive, and if I were doing hard-core SQL development, I'd dismiss it entirely and start the interview with much hairier wizardry; but for a generic, gonna write some queries but mostly live outside the database kind of role, the five minutes or so this sort of test takes at the beginning of the interview gives me a strong indicator of how to assess what the candidate actually knows, rather than what is represented on their resume. It is a guide for the actual meat of the interview.
- edw519 13y agoI 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 do this. We've interviewed 52 programmers with resumes similar to yours and no one else got them all right. In fact, the highest score before you was 32." Wow. Is this the state of our industry now?
- oneandoneis2 13y agoYou've heard of the FizzBuzz test, right?
- jebblue 13y agoI heard about it in the last year or so here on HN, otherwise I wouldn't have known what a FizzBuzz was. I've been coding for 20 years professionally and 30 for fun. I've never coded a Fibonacci either. I think colleges need to teach how to code a microcontroller to do something, build a multi-platform application, build a database application, set up a CI server, etc.
- Bognar 13y agoWe use FizzBuzz as a very basic first filter in our hiring process. It saves us a lot of interview time considering some 60% of candidates fail (or give ridiculously over-complicated implementations). Resumes are nothing but an exercise in creative writing, it seems.
- twistedpair 13y agoFibonacci? That's a recent invention, right?
- metaphorm 13y agonot sure what you mean? coding a simple function for evaluating the fibonacci sequence is a reasonable alternative to FizzBuzz that allows for some slightly more sophisticated requirements: like "code a recursive function that evaluates the first N members of the fib sequence. use memoization in your implementation and show how this runs in O(n) time complexity."
- codegeek 13y ago"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) < 3 "List all departments along with the total salary there" SELECT d.Name, SUM(e.Salary) FROM Department d INNER JOIN Employees e ON (d.DepartmentID = e.DepartmentID) GROUP BY d.Name "List employees that don't have a boss in the same department" SELECT e1.Name FROM Employees e1 LEFT OUTER JOIN Employees e2 ON (e1.BossID = e2.EmployeeID) WHERE e1.DepartmentID <> e2.DepartmentID "List all departments along with the number of people there" SELECT d.Name, COUNT(e.EmployeeID) FROM Department d LEFT OUTER JOIN Employees e ON (d.DepartmentID = e.DepartmentID) GROUP BY d.Name
- shawabawa3 13y ago> people often do an "inner join" leaving out empty departments Empty departments have less than 3 people
- codegeek 13y agocorrect. Edited.
- 3pt14159 13y agoI was following along (without peeking ahead) and I briefly thought "What about NULLs and empty joins?" But I figured, it is an idealized test. For example, what happens when a boss has a NULL department id? Would it be safe to say that they are in a different department than their underling? SQL says no. Besides that, I think this is a great test. Personally, I start off a bit slower so I don't embarrass people that don't know SQL.
- ohwp 13y agoCurious 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) < 3
- yread 13y agoMuch better than this question i got asked once: "Which is faster: select from a table or from a view?"
- clubhi 13y agoI 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.
- chris_wot 13y agoUnless, of course, the view is a materialized view.
- jfb 13y agoIf a candidate can talk intelligently about materialized views, I think we're past the "explain HAVING" stage of the interview.
- yread 13y agoYeah 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
- slc 13y agoThe 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.
- twistedpair 13y agoCall 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
- brown9-2 13y agoIn an interview, it would be wise to mention the special cases that might exist and how you would alter your answer if you had to taken them into account, rather than waiting to be told of the special cases.
- slc 13y agoIn 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-column-group-row.html 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.
- twistedpair 13y agoThanks for the clarification. 5/6 and dunce hat for me :)
- geekymartian 13y agowait, they have somebody doing only SQL ?
- brixon 13y agoThere is someone that works indirectly for me and that is all they do. A lot of data warehouse and DTL stuff, so SQL is their life.
- bluedino 13y agoI 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 silly like "Which of the following is a DDL command?", and many were SELECT statements with a syntax error that you had to pick out, and probably the one question that made sense was about the difference between WHERE and HAVING.
- Gravityloss 13y agoIf their recruiting is so incompetent, maybe the company is clueless otherwise as well?
- stiff 13y agoSomewhat surprisingly most web developers I know know very little SQL, having picked it up exclusively by tinkering to get things done any way whatsoever, even if clumsy or slow. In fact SQL might look deceptively simple at times, at one point I read an ANSI SQL book so I already had some formal education in SQL when I started doing webdev, but I only really learnt ANSI SQL at the university in the databases course, and then I still had to do more learning about many details of my DB server of choice (postgres), including things like spatial queries and indexes, full-text search etc., you can get huge speed ups and infrastructure simplifications by putting those kinds of things directly in the DB. Ask people about difference between LEFT JOIN and RIGHT JOIN, or using the schema from the article, to select all attributes of employees with the highest salary in their department in pure SQL and you will see how much or how little people know, in fact many webdevs don't even understand JOINs at all!
- jcampbell1 13y agoWhat is the preferred way to aggregate with nulls? SELECT Departments.name, SUM(COALESCE(salary,0)) FROM Departments LEFT JOIN employees USING departmentID GROUP BY 1 The above is how I would solve the last one, but I often feel like I abuse COALESCE.
- zrail 13y agoAggregates generally do the right thing with null without the coalesce.
- deleted 13y ago[deleted]
- jcampbell1 13y agoThanks. 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, 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 empty department). You need to do something because the total salary of an empty department is known to be zero.
- deleted 13y ago[deleted]
- dragonwriter 13y ago> Aggregates generally do the right thing with null without the coalesce. Aggregates generally do the most-likely-to-be-right thing with NULL values if there is at least one non-null input to the aggregate. The thing is, if you depend on this, you'll run into real data situations where all the inputs are NULL, the result is NULL, and that's not what you expected. If you are aggregating over an expression that can be NULL, and you always want a non-NULL answer, you probably need to use coalesce or something similar so that you don't have non-NULL inputs to the aggregate.
- xanadohnt 13y agoIf 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 - then you certainly as hell can solve complex problems in Go despite not having any experience there yet. Sure, if I'm trying to fill a Go position and someone has proof they're an excellent developer _and_ it's in Go then they'll get top consideration. Being able to write SQL queries from memory has little correlation to a candidate's level of ability. Personally I consider myself a fairly strong developer and it hasn't been only until the last year that I can now write pretty complex joins from memory. And I've been developing for 20 years. Only because of a recent project and the volume of queries I had to write did my method change from using a graphical query writer to simply memorizing the syntax I need. Indeed, this very type of adaptation is something I look for in candidates.
- jfb 13y agoIf you can't handle these sorts of queries in a forgiving interview format, then by definition you are not a strong developer in SQL. That is not to say that you are not a strong developer in general; or that you couldn't handle a job where you had to interact with a SQL datastore; merely that the interviewer is not going to be able to talk SQL with you.
- joshyeager 13y agoSince SQL is a declarative language, you have to use it very differently than procedural or functional languages. I've met many people who don't understand the difference and just write procedural code in their SQL queries (cursors, etc). The output may be correct, but performance on anything bigger than a toy dataset is terrible. If your team builds massively parallel systems in Erlang, you need to make sure a candidate understands at least the basics of its process model and message passing. If your team build high-performance web apps, you need to make sure a candidate understands at least the basics of HTTP and the difference between client and server. For SQL, the same is true: they need to understand at least the basics of the relational model and declarative programming.
- aidos 13y agoThese 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 could excuse someone not knowing about left joins. If it were for a position that had any sort of focus on db work I would pass on the candidate (caveat, when hiring juniors I look for desire to learn above most everything else). Obviously I'm getting old. "Back in my day" a basic understanding of SQL was just part of the job. Didn't matter what you worked on - you should be able to work with relational database. I'm concerned that the attitude of "I don't need to know that - my ORM does that for me" has become the default outlook. Over the last few years I've had to convince developers several times that the complex aggregation they're writing in their script would be easiest solved by using SQL. Unfortunately, increasingly it seems that newer developers aren't even aware that these tools are available - or how to use them. If nothing else, relational algebra is a wonderful and elegant subject that is worth learning. Darn kids, get off my lawn! :)
- wmil 13y ago> That's a basic question and if that's considered "tricky" you've got a real problem on your hands. If they aren't warned then it's reasonable to assume that every department has employees. Otherwise why would it exist?
- aidos 13y agoFair point. Experience has taught me to internally question whether or not each relationship should use a left join or not. The fact that it says "List all departments" made me think it should be a left join. In fact, to me that screams "left join". Really, in a philosophical sort of way, it's the use of the query that determines which join to use. They want ALL departments, you give them ALL departments. Using a left join you protect yourself in the future - with a performance hit. As you say, if there was a guarantee that the data didn't contain empty departments you'd use an inner join. Most importantly, a reasonable developer should know to ask the question - if not of the examiner, at least to themselves. Just making an assumption is not the right approach.
- lucb1e 13y agoLiked them; not too hard but also not too easy. I'd have succeeded on the interview if I had been given the chance to test them (and if I wasn't too nervous about it I guess). Never had an interview with technical questions like this before; are you commonly given a chance to test them? My database and answers dump (Warning: spoilers!) http://pastebin.com/HGBpemHn http://pastebin.com/HGBpemHn
- nahname 13y agoAnyone else bothered when primary keys are not given the name 'ID'?
- jfb 13y agoI'm bothered by primary keys full stop.
- nahname 13y agoWhat's wrong with identity?
- jfb 13y agoDesignating a candidate key as primary is a nasty SQL implementation wart.
- VLM 13y agoA 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 you use the full name as the "primary key"? Problem: What if the business logic of what a distinct user is changes from unique "full name" to unique "full name" and "telephone number". Whoops now all your foreign keys need messing with, its just a bad scene. Ditto schema changes like you finally change from ascii to utf8 or something, now all your foreign keys need changing (well thats maybe a bad example unless your ascii datatype enforces 7 bits or you're running into byte length vs character length limits...) Or you change the length, which changes the truncation perhaps, which changes your foreign keys. Also you can't just use a rule like all foreign keys are BIGINT now some are CHAR(20) some are FLOAT who knows. 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. Generally speaking if you KNOW absolutely KNOW that your schema will never change, you should probably optimize it to not have multiple keys aka a primary key and unique indexes, or data definition will never change. Very few people can guarantee it so they're better off in the real world with imaginary prikeys. You can read a lot more about this in "SQL antipatterns" I think chapter 4 or so, but always keep in mind that beyond noob level of being able to define the overall issue, short term snapshots will occasionally (but not always) conflict with longer term thinking.
- david927 13y agoThese are nice, but you need simpler questions. Hear me out: Take away two questions (the first four are enough anyway) and add two to start out with that are much simpler. You will be shocked how many people will fall out at that level. Years ago, when I first started hiring, a friend told me about this and I didn't believe him, but I tried it anyway. I was astounded how many people were completely bluffing. It helps expedite the whole process.
- VLM 13y agoI 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.
- tmzt 13y agoI'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.)
- jcampbell1 13y agoIt 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.
- SkippyZA 13y agoI have just been on the job market looking for a senior PHP position. There were so many companies that requested tests from me. Either in the form of online tests, or tasks which I had to complete and return. While I do understand the need for them (having had to hire other developers), some of the requests were quite outrageous. 1 particular company basically wanted an entire application to be developed in an evening, and I was giving strict instructions to focus on security and not allowed to use external libraries. After submitting this elaborate task, I was still criticized on using PDO (which is standard with PHP...). IMHO, sometimes the lengths employees go through to find a developer are so ridiculous that they actually drive away people.
- pessimizer 13y agoCoincidentally, this is also the list of questions that I need to ask anyone who is recommending a particular NoSQL solution.
- btilly 13y agoThe question List all departments along with the number of people there has an answer using a correlated subquery, rather than a join. I have a relevant story about that. About 9 years ago now, another developer escalated a bug to me. Every time they ran a complicated auto-generated query, they got logged out of Oracle. No way! I tried it. Happened to me. Began trying to narrow it down. Ran out of connections. Got a DBA to unwedge the machine. Began again. Ran out of connections again. Got the same DBA to unwedge the machine. Received a lecture about not opening so many connections, replied that I was tracking down a bug and had no choice. Showed him the bug. He was astounded. Not long after I finished tracking it down and sent them the fix. Showed it to the DBA who verified that it had been reported already, and was fixed in the next release. The bug was that any time there was a correlated subquery with no records, Oracle logged you out. My guess is that something, somewhere, followed a null pointer. The obvious solution was to move to a left join. If I remember correctly, the way it was being autogenerated made that hard. My solution was to have a correlated subquery which was a left join on DUAL so that there was always a record.
- krsunny 13y agoRelevant story? You're hired!
- numbsafari 13y agoI 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.
- hgezim 13y agoHere's a SQLFiddle to try the questions out :) http://sqlfiddle.com/#!2/9a84e http://sqlfiddle.com/#!2/9a84e
- _pmf_ 13y agoThat's perfect!
- jrockway 13y agoPlural table names? <irritated hipster sigh>
- EGreg 13y agoIt's an interesting debate. While I also feel that developers should know the underlying SQL, however all that stuff like joins, indexes etc. are actually very hard to scale beyond one machine. MySQL cluster does attempt to do it automatically, but even it has limits, and places most stuff in memory. In short, if I was looking for developers to do sharding, I would actually prefer to AVOID queries with joins, non-pk lookups etc. Having said that, I have discovered a heuristic over the years: 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. The CAP theorem shows that you can't get it all, and most likely you want to use one of those data stores instead of a relational one. For regular sites that won't have millions of users constantly using it, though, a relational db is fine.
- acdha 13y ago“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 because it's designed for a different problem. CAP says nothing whatsoever about whether you want a relational or non-relational database, merely what tradeoffs you'll have to make to satisfy your business needs. Using an ORM doesn't factor into this discussion at all other than for providing a convenient place to implement whatever system you devise to meet those needs.
- EGreg 13y agoAs 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 stored procedure. 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.
- wambotron 13y agoThis is the perfect interview test. It's easy enough that a candidate can roll through it relatively quickly, but deep enough to prove they have the experience they claim to have. Kudos on having a smart interview test!
- kamaal 13y agoThere is a very simple way of testing SQL knowledge. And you don't need any of this online tests or white board programming stuff. Build your self a small sqlite database. Nothing much, but sufficient enough to test the candidates ability write queries. Give him a manual. No internet connection and now give him problems(a few select queries, joins, inserts and may be a few tests here and there to test how good the guy is in schema design). If the guy can write queries after reading the documentation, then hire him. If he can't write queries, I mean practically on the computer and show you results he is not of much use. Even if he can answer all your white board answers. This is applicable to any programming interview. If a person can read documentation well and find his way to write a program to solve a problem such a person makes a good hire.
- lkrubner 13y agoThis comment was very good: "This is exactly why reliance upon ORMs has had a huge negative impact on engineering. Most of these are easily solved with Group By, Having, and/or other aggregate functions, but the ORMs have created this veil of complexity." If ORMs really simplified the underlying complexity, so I didn't have to think about it, then ORMs might be worth it, but I have never worked on a large project where, at some point, I was wholly free of the underlying technology. If its a project that I work on for a year or more, there is always some moment when I need to drop down to SQL.