In interviews of many IT jobs like programmer, quality assurance engineer, software engineer, data analyst and database administrator, an employer may ask questions related to Structured Query Language (SQL). While you never know what questions the interviewer will ask, preparing for some common SQL questions can help you feel more confident during your interview.
In this article, we list several SQL interview questions and their sample answers to help you with your next SQL interview.
Related: How to Prepare for a Job Interview
SQL interview questions with sample answers
Here are some common SQL interview questions with sample responses. Discover the purpose of each question and what skills and qualities they test:
1. How will you explain what SQL is to someone without a technical background?
If you are preparing for a general IT job like a programmer or quality assurance engineer, you may face many SQL questions. A question like this may be an introduction to a series of SQL questions. Also, such questions help an employer understand your basic knowledge of this database programming language and how comfortable you are explaining SQL details to non-technical users.
Example: “SQL or Structured Query Language is an American National Standards Institute (ANSI) database coding language. The language works as a communication tool that creates, extracts and manages data in a database. It can update, insert, delete and add data. Using different commands, SQL instructs the database to perform a particular task.”
2. Write an SQL query that fetches two field names, “CustomerName” and “Address” from the table “Customers”?
An employer may ask this question to see your experience and expertise in SQL. In this question, you have to formulate a syntax during the interview. So, you should be comfortable writing new SQL queries. The answer you give may serve as tangible proof of your knowledge to the employer.
Example: “The Select command will pull the two field names. The SQL query is: SELECT CustomerName, Address FROM Customers;”
3. Differentiate between DELETE and TRUNCATE command.
A question that asks you to differentiate two or more things lets you show your knowledge in both. For such questions, briefly describe both and explain how they differ.
Example: “DELETE is a data manipulation language (DML) command used for removing a complete row from the table. It removes only rows that a user specifies in the SQL query.
The Delete command allows a user to delete specific records based on the WHERE clause. TRUNCATE is a data definition language (DDL) used for removing complete data from the table without altering the table structure. It does not allow the use of the WHERE clause. Also, in the TRUNCATE command, a user cannot roll back the deleted data after execution.”
4. What are UNIQUE, NOT NULL and DEFAULT constraints?
In SQL, you use constraints to specify some rules to process the data and limit the type of data that goes into a table. When answering this question, make sure you give examples along with the definition of these three constraints. Use the STAR method to answer this question. STAR stands for situation, task, action and result. It helps you prepare responses using real-life examples.
Example: “DEFAULT constraint is used for setting default values for a column. When no value is added or specified, all new records will have the default value. The UNIQUE constraint makes sure that all values in a column are different. In SQL, by default, all columns can hold NULL values. The NOT NULL constraint makes sure that a column does not accept NULL values. This means that the specified field will always contain a value.
In my experience of using these three constraints, I created an inventory database that included all the current inventory information of the company, including the product details, quantity in stock and price per unit. The product detail column required uniqueness, as no two product details could be the same. I used the UNIQUE constraint. Also, the price per unit could not be a NULL value, so I ensured this column was NOT NULL. This ensured that no two products were given the same code, and all products had a price.”
5. What are tables, fields and records in SQL?
A basic understanding of tables and fields is essential for SQL programming. When you show that you know these basics, it can help your interviewer understand that you have experience in SQL. The first part of the answer should include the definition. Then, include a specific example of how you will use each of them.
Example: “A table is an organised group of data which includes rows and columns. Rows are horizontal and columns are vertical. An SQL table can have any number of rows and they are called records. It has a specified number of columns called fields. For example, an SQL table may be called customer. The fields contain information like customer name, age, gender and height. The records are the value you add in each column.”
6. Define foreign key.
A foreign key can help you link two pieces of information in separate databases. Knowledge of choosing and using the correct foreign key showcases your logical reasoning and problem-solving abilities.
Example: “A foreign key is a unique field of one table that points to the primary key of another table. It helps to establish a link between two tables.”
7. What are the different clauses and their purpose?
An SQL clause helps a user to limit the queried result based on some specified conditions. This answer requires a simple list. You can showcase your SQL knowledge by briefly explaining each clause.
Example: “SQL uses four clauses, namely ORDER BY, WHERE, HAVING and GROUP BY. ORDER BY is used for sorting the results in ascending or descending order. The WHERE clause is used for specifying search conditions while fetching data from a table, while HAVING is used for specifying search conditions in the GROUP BY clause. Finally, GROUP BY is used for aggregating identical data in a group. It is always used with the SELECT command and placed after the WHERE clause. It is also placed before the ORDER BY clause.”
8. What is a database and a DBMS?
Another basic question that tests your SQL knowledge is the difference between a database and a database management system (DBMS). These two are the most widely used terms in SQL and have a strong relationship with one another. Knowledge of these two is the bare minimum requirement to understand the purpose of SQL language.
Example: “A database is a collection of data. SQL database is a collection of tables that stores structured data. In comparison, a DBMS is a system that monitors, controls and maintains a database. It serves as an interface between the application software or end-user.”
9. How will you prepare a database for migration to a new cloud-based server?
When organisations grow, they may need to expand and move databases. This question helps you showcase your technical expertise and experience in the SQL migration process.
Example: “For migration to a cloud-based server, I will take the database through a deduplication process and remove unwanted or extra data. I will then ensure data quality before commencing the migration process. Also, I will cross-verify that the new server meets all the technical requirements of a SQL database.”
10. What is normalisation and how does it impact databases?
Normalisation is an important data quality and performance tool in SQL. This question helps you showcase your experience in maintaining databases. The first part of the answer should include the definition and its impact on the database. Then, include a specific example of how you used it to increase your data quality and performance.
Example: “Normalisation is the process of removing data redundancy and organising data using different fields. It improves the database efficiency by reducing its size and dependencies. In my previous job role, I used normalisation to improve SQL performance and accelerate the data access speed.”
11. Name some aggregate functions used in SQL.
Aggregate functions perform a calculation on one or more values in a table and return a single value.
Knowledge of aggregate functions is helpful for job roles like data analyst and data scientists. In these job roles,
you have to crunch numbers to get insightful information.
Example: “There are different SQL aggregate functions, such as MIN, MAX, FIRST, LAST, SUM, AVG and COUNT.
Each of these functions performs a different task. For instance, the MIN function returns the smallest value from the records, while the MAX function returns the largest value from the record. Likewise, FIRST returns the first value and LAST returns the last value. The SUM function is used to return the sum of column values, while AVG returns the average of specified column values. As the term suggests, the COUNT function returns the number or count of all the table rows in a particular table."