intro
In this blog about SQL interview questions, we’re looking into how to deal with advanced scenarios involving your data.
Welcome back. This is the third part of blogs on SQL interview questions and answers. In this blog, we’re going to look into what you can do and how to answer questions in relation to advanced scenarios involving your database.
Why Ask About Advanced Use Cases?
Depending on the role you’re interviewing for, advanced use cases involving a database management system may or may not be out of the question. Hiring managers may want to know your acumen around advanced use cases involving a database because:
What Questions to Expect and Why?
Now towards the questions themselves. What questions to expect heavily depends on the role you’re applying to.
Those of you applying to Senior developer roles may have a set of questions related to applications, while DBAs may expect more database and infrastructure-focused probes. On the other hand, DevOps engineers can expect different questions altogether.
Here’s what’s likely to happen!
Question Topic #1: Architecture and Backups
You saw this one coming, didn’t you? Architecture and backup-related questions are likely to be at the top of the list. Basics are out of the question: you should know your way around normalization, partitioning, and indexing as-is.
However, you may be hit with questions like can you explain the differences and trade-offs between monolithic and microservices architectures? Which one would you choose for a large-scale application and why? How do you perform a backup on a big data application bearing 15 billion rows so that it can be recovered without huge impact on those using the application?
These kinds of questions help hiring managers to understand your thinking and logic when it comes to database architecture, your understanding of monolithic and microservice-based architectures, and backing up data. In this regard, keep in mind that a monolithic architecture refers to all of the application being built as a single unit. In such an approach, all components run together.
A classic example of a microservices-based architecture, though, would be something like Docker. In such an architecture, each service can be independently scaled, and even if one service would fail, it wouldn’t bring down the entire application. So, a monolithic architecture comes with simplicity, but flexibility limitations, while a microservice-based architecture comes with scalability and more resilience, but with more deployment overhead and complexity to manage.
As far as backups on big data are concerned, you would need to consider the exact use case, but in general, in a relational database management system, you would back up big data sets by backing up raw data because it doesn’t come with overhead (that can’t be said about INSERT statements).
The same goes for recovery: if you recover raw data, there’s little overhead so even if the users of your application would be impacted (let’s be honest, if you’re recovering billions of rows, someone will feel the impact), everything would be over quickly.
Question Topic #2: Queries
This one is closely related to architecture and backups. Most hiring managers and developers checking your work will likely assume that you already know your way around SQL query optimization, but at the same time, you would be asked about query refactoring, rewriting, eliminating unnecessary parts from them, reducing the use of functions in WHERE and other kinds of clauses, and optimizing some parts of complex queries.
When answering complex questions about SQL queries, keep in mind that solutions to problems don’t necessarily have to be complex. In fact, some of the questions that you will receive can be solved in the easiest of ways.
To refactor a query, simplify complex expressions by:
Also, keep in mind to:
Question Topic #3: Transactions and Isolation Levels
Another topic that’s closely related to queries would be related to transactions. Think about it, every query should be a part of a transaction, right?
When considering such questions, keep in mind that you would be likely pressed on rollbacks, deadlocks, isolation levels and other considerations, and to answer them, keep in mind that:
Question Topic #4: SQL Clients
You’re unlikely to handle complex topics alone. The terminal, your colleagues, and phpMyAdmin will surely help, but if you are interviewing for a mid-level+ position, be advised that phpMyAdmin is ill-equipped to handle all scenarios. You can hack something together, but doing that every time you need to solve an issue would be overkill.
To obtain answers to most of your issues, you would also turn to SQL clients like DbVisualizer. Take a look at the screenshots before the third question topic: having bigger queries to deal with, would you like to format them manually? Having 100+ columns in a DBMS, would you like to click on each and every one of them to find out what data types they’re based on?
Another thing that SQL clients like DbVisualizer will help you with is to stop worrying about the database management system you’re using: with support for 50+ data sources, you will surely find your database management system in the list as well. DbVisualizer comes with:
DbVisualizer comes with many other features unique to itself, but we’ll leave them up to you to explore in full; do that and come back for the last, fourth, episode of SQL interview questions and answers later on.
Summary
In the third part of SQL interview questions and answers, we’ve looked into a bunch of advanced scenarios that may be involved in your next interview for a role. We’ve touched upon database architecture, backups for billions of rows, simplifying queries and operations, transactions and isolation levels, and SQL clients like DbVisualizer.
You would inevitably need to add or modify the steps within those outlined looking at your use case. But we hope that by providing you some information about these steps, we’ve put you on the right track in terms of passing your upcoming SQL interview.
Do keep in touch with us through our blog or through X/Twitter, read books pertaining to your specific DBMS of choice, and until next time.
