intro
Welcome to the second part of the blog series on SQL interview questions and answers. In these blogs, we’re continuing to dive deep into SQL interview questions and provide answers and advice. Have a read!
If you’ve been attentive when reading our blogs, you will certainly know that a while ago, we’ve began writing about SQL interview questions and answers that may save you if you find yourself on the receiving end of SQL interview questions.
In the first part of those series, we’ve covered the basics. In particular, we’ve covered the thought process behind answering various questions you may find yourself receiving in an SQL interview as well as walked you through the thought process of questions in an SQL interview.
Now, we’ll walk you through solutions to various database problems!
SQL Interview Questions about Problems & Solutions
When we’re talking about SQL interview questions and answers related to various database problems, it’s important to understand the basics, as well as what we can do if we consider ourselves to be an advanced data engineer.
Note: The sample queries below use MySQL syntax, but you can easily adapt them for PostgreSQL, SQL Server, Oracle, or any other SQL dialect.
Here’s what you need to know!
Question Topic #1: Efficiently Retrieving Records From a Table
The first problem you’re likely to encounter will likely have something to do with retrieving records (rows) from a table. An interviewer is likely to ask you something along the lines of “how do you retrieve only the necessary data given these circumstances: …?”
To answer this question, remember that we retrieve all records from a table with a SELECTquery like so:
1
SELECT * FROM `table_name`;
After that, consider what data you need to retrieve. Start by understanding that to minimize data retrieval, you’d need to specify columns like so:
1
SELECT `column_1`, ..., `column_n` FROM `table_name`;
Don’t forget about the WHERE clause as it allows you to further filter down data you need:
1
SELECT `column_1`, ..., `column_n` FROM `table_name` WHERE `column` = 'Value';
Finally, don’t forget about the LIMIT and OFFSET clauses either (or use the equivalent OFFSET ... FETCH mechanism in standard SQL). LIMIT limits the amount of rows that are returned and OFFSET allows your database to start counting from a specific row (that’s useful for pagination):
1
SELECT `column_1`, ..., `column_n` FROM `table_name` WHERE `column` = 'Value' LIMIT 5 OFFSET 1;
At the end of the day, remember that to efficiently retrieve records from a table, you need to combine a couple of things:
Complete those two things, and your queries will run faster as well as retrieve necessary data.
Question Topic #2: Finding Duplicates in a Set of Data
The next SQL interview questions and answers in this series you’re likely to encounter has to do with finding duplicates in a set of records: how do you do that?
Honestly, that’s pretty simple to accomplish. A query like so will do:
1
SELECT `username`, COUNT(*)
2
FROM `users`
3
GROUP BY `username`
4
HAVING COUNT(*) > 1;
This GROUP BY query with a HAVING cluse will find duplicate usernames in a set of data (i.e., in a table).
For this query to return desired results, replace the column names with columns necessitated by your use case.
Question Topic #3: SQL Joins
The next SQL interview question in the list would probably have something to do with SQL JOIN operations or the like. You’re likely to face questions like how to retrieve employee names along with the titles of their departments?
The answer to this question would be as follows:
1
SELECT e.employee_name, d.department_name
2
FROM employees e
3
JOIN departments d
4
ON e.department_id = d.department_id;
Such an SQL query would:
Question Topic #4: Aggregate Functions
After SQL joins, you’re likely to face SQL interview questions and answers facilitating aggregate functions in database management systems. Since we’re talking about problems and solutions to them, an interviewer is likely to ask you to craft a query using an aggregate function or two as they provide us with a way to exploit the functionality provided by the database to assist a use case to solve a problem. For that, you’d have an answer as follows:
1
SELECT `productid`, SUM(quantity_sold) AS `total_sold`
2
FROM `products`
3
GROUP BY `productid`;
This query would make use of an aggregate function called SUM that would sum up the values in a column. There are various other aggregate functions, such as MIN and MAX, and the like. The point here is to craft a query that would make use of one or more of the aggregate functions available in your database management system of choice. Make sure the query aligns with a provided use case.
Question Topic #5: How to Optimize a Database for Large Data Sets?
This question is very broad, but regardless, it may come up. To answer such a question, consider the database management system required for the job (i.e. MySQL, SQL Server, PostgreSQL, etc.), then remember the optimization techniques regarding that particular database. Look into configuration files (postgres.conf, my.cnf and the like), then turn to the documentation.
In the DbVisualizer blog, you can find a variety of blogs covering this topic: answering this question in a “one way or the highway” fashion is not really possible because you need to consider the type of database management system in use. Understanding the basics and the SQL EXPLAIN clause will be a good start though.
Question Topic #6: Data Types
Before optimizing, keep in mind that we’re optimize a database to work best regarding our specific scenario: with that in mind, perhaps inadvertedly, but you’re inevitably going to make a decision on data types.
If you’re completing a code test and need to select a couple of data types, keep in mind that you need to select the data type closest to the data you intend to store in that column, then decide on the size of the column (INT and SMALLINT are different things as they allow you to store numbers up to a certain size e.g. up to 128, etc.), and finally, then decide on the length of that column.
Starting from MySQL 8.0, data type length has been deprecated (removed), so you’d only need to decide what data type you’re going to use and then make a decision on its size.
Make a decision based on:
Adhere to these things and you should be good to go!
Question Topic #7: NULL Values
Another thing to keep in mind would be related to NULL values and associated functions. This likely wouldn’t be a question per se, but if you’ve been working with any database management systems for a while, you’ve likely noticed that NULL values are sometimes treated differently than others.
MySQL’s partitioning functionality is a good example: NULL values will always be stored in the lowest possible partition. Other database management systems may have similar quirks and features.
Aside from NULL values, keep in mind that these questions are derived from use cases that hiring managers (or DBAs, developers, depending who you find yourself talking to) found themselves to be around at the time.
With that in mind, keep yourself updated in the database world by reading blogs, books on your database management system of choice, and watching videos if you desire to do so.
Summary
In the second part of SQL interview questions and answers, we’ve walked you through a couple of solutions to database problems. In this part, we’ve efficiently focused on database problems and SQL interview questions derived from those problems.
Keep in mind that each interview necessitating the use of SQL interview questions and answers is different and it’s up to you to take what works for you from the blogs we write and advice we give.
Regardless, the first part covered the basics. The second part covered answers and walkthroughs through specific problems that you may encounter, and upcoming parts will deliver even more clarity and advice.
For now, keep yourself occupied by reading our blog, and until next time!
