intro
SQL query-related SQL interview questions are some of the most frequent interview questions for junior and senior engineers alike.
Preface
Database management systems are built on the premise of queries. For relational database management systems, those queries are SQL queries. SQL queries, as obvious as they may be, are still a very frequent point of contention between junior and senior DBAs: many know their outcomes, but not everyone knows how to optimize each of them and what to do if one of them fails.
Today we’re walking you through everything you may be asked about SQL queries in your next SQL interview.
The Basics
We’ll start from the basics. A SQL query is any query interacting with a relational database management system. Quite simple, yeah? We think so too. SQL queries can be split into one of four types – they either Create, Read, Update, or Delete data. That’s the meaning of the CRUD acronym you may come across:
- Creating – such SQL queries insert data into our database;
- Reading – such SQL queries select data from our database;
- Updating – such SQL queries update data inside of our database instances;
- Deleting – such SQL queries delete data from our database instances.
Here everything’s pretty simple – if you come across questions that ask what is CRUD or what its acronyms stand for, you have your answer above.
Your SQL interview is not very likely to end with the interviewer asking what SQL queries are and what their types are – you will likely be asked to craft SQL queries that:
- Index data.
- Demonstrate how JOIN procedures work.
- Normalize or denormalize a table in a database instance.
- Create, read, update, or delete data as quickly as possible.
- Filter data according to a specific criteria.
Indexing Data
SQL interview questions dealing with indexes in database management systems are related to:
- The types of indexes.
- What indexes do.
- How to optimize queries to use certain types of indexes.
The types of indexes available in your DBMS directly depend on what database management system you find yourself using – for MySQL, the index types are B-Tree indexes, R-Tree or spatial indexes, descending indexes, composite indexes, hash indexes, or full-text indexes.
The type of index to choose depends on what you want to achieve – B-Tree indexes are best for most use cases (i.e. use a B-Tree index whenever you want to index a column for speed.) R-Tree indexes are suitable for geospatial data, composite indexes are just multiple indexes on the same column, hash indexes are very fast but in most cases (in MySQL Server) only work with specific storage engines (MEMORY.)
Full-text indexes provide us will search capabilities in other cases only available within full-text search engines. Such features include multiple search modes (Boolean, natural language mode, query expansion mode, etc.), and a couple of features unique to themselves (wildcards and the like.)
All indexes perform the same task – they store data for it to be more easily accessed. Think of them as an index in a book – if you know that a specific problem is discussed on the page 57, you turn to the page 57 and find the solution.
Query Optimization
The big next part of SQL query interview questions is related to SQL query optimization. You’re very likely to hear questions similar to “how to optimize an INSERT|SELECT|UPDATE|DELETE query?”
To answer such a question, remember these concepts:
- What helps a
SELECTquery, hindersUPDATEs,INSERTs, orDELETEs and vice versa. - To properly optimize a query, you need to optimize your database instance based on the settings provided by your server in question. In other words, look into mongod.conf, postgres.conf, or my.cnf/my.ini (depending on the OS you use.)
- Scan through as little data as you can to optimize
SELECTs,INSERTorUPDATEin bulk to optimizeINSERTs orUPDATEs, and switchDELETEtoTRUNCATEto increase the speed ofDELETESQL queries.
These are the basics of SQL query optimization – answer optimization questions according to this criteria, and you’re good to go.
JOINs, Normalization, and Denormalization
The next likely interview questions will likely be related to JOINs – to properly answer such questions, keep in mind that a JOIN clause is used to combine records from multiple tables and that there are multiple types of JOINs available in any DBMS:
[INNER] JOIN– such a join procedure returns data that have the same values in both tables.FULL JOIN– combines rows from the left and right table to come up with a result set.LEFT JOIN– returns all records from the left table as well as matching records from the right table.RIGHT JOIN– the same as a left join, just the other way around.
As far as joins go, that’s it, really. Normalization isn’t rocket science either. All you need to know is that normalization gets rid of unnecessary data in our database instances to improve performance and that there are five normalization types, them being as follows:
- First Normal Form (1NF) – each table can only contain one value.
- Second Normal Form (2NF) – a table is in the first normal form AND it has a primary key that’s not dependant on any other key.
- Third Normal Form (3NF) – a table is in the second normal form AND it has no non-primary attributes that are dependant on one another.
- Fourth Normal Form (4NF) – a table is in the third normal form AND there are no dependencies with multiple values.
- Boyce-Codd Normal Form (BCNF) – a table is in the third normal form, and for every A -> B dependency, A is the super key (an attribute that uniquely identifies a row.)
And what’s denormalization? You’ve guessed it! It’s the opposite of normalization. No, really – add redundant data to a table to denormalize it, that’s it! Normalization gets rid of redundant data, denormalization adds it. Not exactly rocket science, is it?
Filtering Data and Other Things
The rest of the SQL interview questions related to SQL queries will likely be related to data filtering – an interviewer may ask you how do you filter unnecessary data when selecting, updating, inserting, or deleting data from a table?
The answer is as follows:
- If you
SELECTdata, you onlySELECTwhat’s necessary (e.g. useSELECT columninstead ofSELECT *) - If you
UPDATEdata, use aWHEREclause and avoid indexes. - If you
INSERTdata, use bulk inserting mechanisms (this part really deserves a book in and of itself, but keeping in mind that you can use multiple data sets toINSERTwithout specifying anINSERTquery will be enough for now – to make your SQL queries even faster, switchINSERTto something that reads from a text file while ignoring all of the overhead posed byINSERTqueries: MySQL hasLOAD DATA INFILE, other database management systems may come with similar options.) - If you
DELETEdata, optimizeDELETEs just like you’d optimizeUPDATEs with one caveat – know thatTRUNCATE TABLE xwill always be faster than deleting all rows at once.
Other things you should keep in mind are related to the fact that database management systems come with configuration abilities unique to themselves. That’s why you find files like my.cnf related to MySQL Server, postgres.conf related to PostgreSQL, or mongod for MongoDB. To optimize your databases properly and answer the rest of SQL interview questions related to SQL queries, make sure to brush up on the internal functionality of the database management system of your choice and its configuration abilities.
Database Clients and SQL Query Management
Last but not least, you’re also likely to receive questions regarding SQL query management – for that, an answer like “I would observe the queries running in my database instance and terminate them if necessary” will do, but if you really want to impress your interviewer, answer like this:
“I will observe the functionality of my SQL queries by observing their performance in my DBMS, checking the error log, and using a SQL client like DbVisualizer.”
An answer like this will guarantee you get the job. Why?
- Observing query performance in a DBMS is a crucial task any DBA should turn to once their query performance starts to nosedive.
- An error log is the obvious place to look for any errors – big or small – so an interviewer will notice your knowledge in this area too.
- A SQL client will act as a virtual DBA that monitors your SQL query performance and general database availability, capacity, performance, and security and will always inform you once something goes wrong. Good SQL clients will come with a free trial, extensive support for the most popular data sources, and features helping you optimize your database performance in a couple of clicks. Try one of them today and see why DbVisualizer is the most popular choice for the best database engineers in the world.
Summary
SQL interview questions related to SQL queries are some of the most popular database interview questions for junior and senior database administrators alike. In this blog, we’ve answered some of the most burning questions related to them – you now know how to crush your next SQL interview and impress the interviewer with your knowledge in the database world.
To impress the interviewer even further, give DbVisualizer a try today, and until next time!
