SQL

SQL Interview Questions – SQL Queries

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:

  1. Creating – such SQL queries insert data into our database;
  2. Reading – such SQL queries select data from our database;
  3. Updating – such SQL queries update data inside of our database instances;
  4. 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:

  1. Index data.
  2. Demonstrate how JOIN procedures work.
  3. Normalize or denormalize a table in a database instance.
  4. Create, read, update, or delete data as quickly as possible.
  5. Filter data according to a specific criteria.

Indexing Data

SQL interview questions dealing with indexes in database management systems are related to:

  1. The types of indexes.
  2. What indexes do.
  3. 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:

  1. What helps a SELECT query, hinders UPDATEs, INSERTs, or DELETEs and vice versa.
  2. 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.)
  3. Scan through as little data as you can to optimize SELECTs, INSERT or UPDATE in bulk to optimize INSERTs or UPDATEs, and switch DELETE to TRUNCATE to increase the speed of DELETE SQL 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:

  1. First Normal Form (1NF) – each table can only contain one value.
  2. 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.
  3. 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.
  4. Fourth Normal Form (4NF) – a table is in the third normal form AND there are no dependencies with multiple values.
  5. 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 SELECT data, you only SELECT what’s necessary (e.g. use SELECT column instead of SELECT *)
  • If you UPDATE data, use a WHERE clause and avoid indexes.
  • If you INSERT data, 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 to INSERT without specifying an INSERT query will be enough for now – to make your SQL queries even faster, switch INSERT to something that reads from a text file while ignoring all of the overhead posed by INSERT queries: MySQL has LOAD DATA INFILE, other database management systems may come with similar options.)
  • If you DELETE data, optimize DELETEs just like you’d optimize UPDATEs with one caveat – know that TRUNCATE TABLE x will 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?

  1. Observing query performance in a DBMS is a crucial task any DBA should turn to once their query performance starts to nosedive.
  2. 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.
  3. 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!

Dbvis download link img
About the author
LukasVileikisPhoto
Lukas Vileikis
Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL DDL: The Definitive Guide on Data Definition Language

author Antonello Zanini tags DDL SQL 7 min 2024-02-15
title

SQL CONTAINS Function: SQL Server Guide With Examples

author Antonello Zanini tags SQL 8 min 2024-01-18
title

SQL DISTINCT: A Comprehensive Guide

author Bonnie tags DISTINCT POSTGRESQL SQL 5 MINS 2024-01-11
title

The SQL BETWEEN Clause Explained

author Lukas Vileikis tags BETWEEN SQL 6 MINS 2023-12-28
title

SQL vs NoSQL Databases: Which is Better?

author Ochuko Onojakpor tags NOSQL SQL 16 MINS 2023-11-20
title

SQL Cheat Sheet: A Comprehensive Guide to SQL Commands and Queries

author Bonnie tags CHEAT SHEET SQL 18 MINS 2023-11-15
title

A Beginners Guide to SQL Window Functions

author Ochuko Onojakpor tags FUNCTIONS SQL 6 MINS 2023-08-18
title

How to Pronounce SQL: SQL or Sequel?

author TheTable tags SQL 3 MINS 2023-05-23
title

Parameterized Queries in SQL – A Guide

author Lukas Vileikis tags SECURITY SQL 5 MINS 2023-05-12

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗