Matt Gale

11 Oct 2019

SQL In The Real World: Setting Expectations For Your First Job

I’ve noticed an influx of fresh grads from bootcamps and universities who are nervous about SQL “in the real world”. New developers fear they don’t know or understand SQL well enough to get through an interview, let alone perform well in the job. To a newcomer, SQL feels vast and illogical compared to other programming they’re used to. They know SQL is important, but it isn’t emphasized in coursework or training to a point where they feel confident in being evaluated.

I understand how you feel and I’m here to help. I’ve interviewed dozens of developers fresh out of school over the years. I’ve worked as an enterprise backend developer for my entire career and I know the profile I want fresh developers coming into my team to have on day one. I want to set some reasonable expectations for you for what you should know walking into your first set of interviews, and your first days on the job.

No one expects you to know everything. And an entry level interview shouldn’t try blow your mind with all the things that you don’t know. You’ll be evaluated on practical, applicable knowledge. Here are the 5 questions I ask to the juniors who land in my lap with a bit of explanation as to why I value them.

Given a sample schema, write a basic SQL query.

Show me you can use a JOIN and a WHERE clause- being able to write a straightforward SQL query using SELECT, FROM, and WHERE to get a look at the data. I typically ask you to join one table to another, using maybe one or more join types- so you should know the differences between them:

  • join (aka inner join)
  • left join (aka left outer join)
  • right join (aka right outer join)
  • full join (aka full outer join)
  • cross join

If you’re looking for primers on these, there are some great, well travelled articles and diagrams on join types from Jeff Atwood or this nice explanation from Julia Evans, read up on them and practise a few problems on HackerRank and LeetCode. You want to be at a point where if you saw that a left join was something you wanted to do, you’d understand the syntax and the usage to make that happen.

Bonus points! What are indexes and how do they work? I want to be clear that this is only bonus. Not knowing about indexes is not something I would ever eliminate a candidate for, but they will definitely win you respect in an interview if you understand their role and can apply them. Mentioning query performance and index usage when formulating a queries in interview questions definitely shows you’re thinking of one of the biggest practical aspects of databases.

Model me a “many to many” relationship.

I would never tell you to jump through a hoop if I wasn’t so positive you’d be asked this question. It’s a popular question for a reason- it demonstrates to me that you can think relationally, and have a little bit of design sense when it comes to crafting and making changes to a DB schema. This is a very common problem, and there is a common solution all developers working with a relational database should know.

What is the difference between = NULL and IS NULL?

This is one of those general usability aspects of SQL that all developers should have in their tool belt. Now that I’ve drawn your attention to it, learn it and have it at your disposal to know that it needs to be applied.

What’s a prepared statement and why do we use them?

I don’t expect an extremely technical answer here in terms of what happens at the database level, but one term I would expect to hear from you is “SQL injection”. It’s important to know that we shouldn’t just do string substitution for parameters in queries or bare string queries against a database- we need to protect ourselves from malicious input.

That’s it! Hopefully you feel a little less daunted.

All-in-all, don’t live and die by the coding interview prep sites- you can spend weeks on HackerRank and LeetCode going through problems of increasing difficulty and it can turn into a rabbit hole of “how much is enough”. Practising will make you stronger and I advise it, but also remember that in the workplace, schemas are not clean and contrived like they are on these practise sites- they’re flawed and imperfect. Designing and dealing with these beasts will be its own adventure that you won’t need to tackle under time pressure or with another developer evaluating you over your shoulder.

My advice is to get comfortable with the basics- when you have these, as an interviewer and coworker, I can work with you through most problems and we can have a discussion about them, which helps me understand you and vice versa to get to the end goal- you shipping code.