105 SQL Interview Questions [2024 Prep Guide]

In recent years, Structured Query Language (SQL) has become central to businesses that employ data science methods. SQL is a programming language used to input and extract information from databases. Given that SQL is so integral to the data science process, demand for data scientists proficient in SQL is growing. According to Indeed, the average salary for an SQL specialist is $96,000. And job prospects are strong—one study showed that the demand for SQL professionals grew by almost 50% in the last year. 

But in order to land a job working with SQL, you need to ace your interview first. So we’ve compiled a list of the most commonly asked SQL interview questions, along with the answers. 

General SQL Questions and Answers

Introduce yourself.

Tech recruiters often begin by asking candidates to introduce themselves. The purpose of this question is to ease into the interview, and to understand your interest in working for their company. 

Start by talking about your current role to contextualize your most recent. Then, give a brief account of your past experience and educational qualifications. Mention any SQL work you’ve done before, and any relevant classes or bootcamps that you’ve taken. 

Don’t want to get too long-winded with this answer. Keep your answers brief, and tailor them to the company and role that you’re applying for. 

What do you know about SQL? 

Recruiters aren’t asking for an exhaustive list of every SQL topic that you’re familiar with. Rather, they’re trying to discern whether you have a general understanding of the field, and whether you have the practical experience to boot. 

Start with a general overview of what SQL is, and how it’s used in business contexts. Then, detail some of your learning journey, whether that’s been in college or an online course that covers SQL. Finally, tell them about your relevant projects and how you employed theoretical foundations. 

Why did you opt for SQL?

Recruiters want to know why you’re passionate about SQL. Use your storytelling skills for this answer. Describe what got you interested in the field, and how you learned the relevant skills. Also explain why SQL is so important in business contexts, and how it can be used to solve important business problems. 

What is the most challenging project you encountered on your learning journey?

Interviewers ask this question to learn how you encounter challenges, your problem-solving methodologies, and your ability to work under duress. 

Start by describing the situation. Was this a personal project or a work one? What were you trying to achieve and how many people were you working with? 

After setting the scene, describe your personal role in the project—your responsibilities, and the tools and technologies you used. 

Then describe the hurdle that you faced. Detail how you recognized that there was a problem, and note what kind of bottlenecks it created in the larger project. 

End by detailing the actions you took to solve the problem. Include both the technical and soft skills that you employed. And note the positive learning experiences that you picked up along the way. 

Situational questions based on your resume. 

There are a few questions that can crop up based on what you’ve mentioned in your resume. Let’s take a look at a couple of examples. 

What was your biggest achievement at your previous job?

Answer this question in the same way that you would when detailing your most challenging project. Start by explaining the project and your role in it. Don’t forget to mention the performance indicators that you were given for the project. Then, describe how you outdid those performance indicators. 

Why is there a gap in your resume?

This question might seem intimidating, but doesn’t need to be. Be honest about why you needed to take some time off. Also, tell them how you got back to the workplace and upskilled while you were away. 

Basic SQL Technical Interview Questions

What is SQL?

SQL stands for Structured Query Language. It is used to store, retrieve, and manipulate the data in a database. 

What are the five basic SQL commands?

The five most basic SQL commands are ALTER, UPDATE, DELETE, INSERT, and CREATE. 

How are SQL commands classified?

The SQL commands are classified into: 

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transaction Control Language (TCL)
• Data Query Language (DQL)

What is DBMS?

A database management system (DBMS) is a software system used to oversee the operations of a database. It helps manage the structure of the database and manipulate the data stored within it.

Define the term database.

A database is a repository of structured data stored in a computer system. 

What is RDBMS? Is it different from DBMS?

An RDBMS (relational database management system) is used to store, query, and manage the data in a relational database. 

An RDBMS is used to manage a relational database, which means that data can be stored only in the form of tables. This makes relational databases a more advanced and structured alternative to traditional DBMS systems. 

Define tables and fields.

When you store data that is structured in the form of rows and columns, it is known as a table. The columns that form the table are known as fields. 

How do you create a table in SQL?

Use the following command: 

CREATE TABLE

How do you delete a table in SQL?

The following command deletes a table in SQL: 

DROP TABLE

What are constraints in SQL?

Constraints are the rules that are set for the data that can be placed inside a table. It ensures that the data in a table is relevant and accurate. Here are the constraints that you can place on data in SQL: 

NOT NULL: The data in this column cannot include any null values. 

UNIQUE: Every entry in this column needs to be unique. 

PRIMARY KEY: These are columns that have data that are unique for each record in the column. 

CHECK: This constraint checks every entry in a column for a specific condition. 

FOREIGN KEY: These are columns with attributes that refer to the primary key of a different table. 

DEFAULT: This constraint sets a default value for when no value is entered. 

CREATE INDEX: This is used to create indexes in tables so that data can be retrieved quickly. 

What is a unique constraint?

A unique constraint implies that every value in a column should be different from every other value in that column. In other words, every value should be unique. 

How do you change a table name in SQL?

The RENAME command is used to change table names in SQL. 

Let’s say you have a table named Customers and want to change the name to New_Customers. You’d type the following command: 

Rename Customers to New_Customers

How do you delete a row in SQL?

You can use the DELETE SQL query. 

Assume that you have a table named “Cars” which has a column for serial numbers with the name “sno.” If you want to delete the row with serial number 6, then you’d use the following SQL query: 

DELETE FROM Cars
WHERE sno=6

How do you create a database in SQL?

Use the CREATE DATABASE query. If you want to create a database named StudentInfo, then type the query as follows. 

CREATE DATABASE StudentInfo

What is normalization in SQL?

Normalization is the process of eliminating redundant entries from a database. This enhances the referential integrity of the data. It also helps reduce update, deletion, and insertion anomalies. 

Define a primary key.

In a relational database, a primary key is a key for which every record has a unique value. For example, if you created a database with customers’ information, their phone number could serve as a primary key because no two people are given the same phone number. 

The database administrator determines which column needs to be chosen as the primary key based on the available data. 

Define a foreign key. 

A foreign key is a column, or multiple columns, whose data is linked in a relational database. 

Say that you have two tables—one with customer data and one with order data. You could create a foreign key relationship between a customer ID column and an order column because these two are logically linked. This maintains the referential integrity of the table and ensures that there are no redundant or repeated values. 

What is a “join” in SQL?

A join is a way to combine rows that are in two different columns by using a field that they have in common. 

What is the difference between a self join and a cross join?

A join SQL query combines data that is in two different rows in the same table or in rows that are in different tables. 

A self join involves joining a table with itself, which means that the rows are replicated. A cross join implies joining every row in two tables. Read more about joins here. 

Define an index, along with its different types.

An index is a table that has two columns. The first column is a duplicate of the primary key of a table. The second column has pointers to where the key value of each record is stored. 

The following are the types of indexes in SQL: 

• Primary index (dense index and sparse index)
• Secondary index
• Clustering index 
• Multi-level index
• B-tree index 

Read more about indexes here. 

How do you insert a date in SQL?

You can use the DATE command, which inserts the date in the YYYY-MM-DD format. 

Alternatively, you can use the DATETIME format, which inserts the time along with the date in the YYYY-MM-DD HH:MI:SS format. 

Define data integrity. 

The data integrity of a database refers to the accuracy and consistency of the data. It is a key feature of the design and implementation of any system used to store and retrieve data. 

What is PL/SQL?

PL/SQL is short for Procedural Language Extensions to the Structured Query Language. It gives software developers a way to combine SQL with procedural statements like the ones used in general-purpose programming languages. 

What is MySQL?

MySQL is an open-source relational database management system software. It was created by Oracle and based on SQL. 

What are the different types of relationships in SQL?

There are three main types of relationships between data values in SQL. They are: 

• One-to-one relationship: This implies that a record in the first table is related to only one record in the second table. 
• One-to-many relationship: In a one-to-many relationship, a record in the first table can be related to one or more records in the second table. But the records in the second table can be related only to one record in the first table. 
• Many-to-many relationship: In this kind of relationship, multiple records in the first table are related to multiple records in the second table. 

Read more about relationships in SQL here. 

What is an alias in SQL?

An alias is an alternative name that is given to a table. It is only available for the duration of a specific SQL query. Aliases are created to make columns easier to refer to or to make their names more readable. 

Define query

A query is how you can request information that is stored in a database.

What is a subquery?

A subquery is a query that is nested within another query in SQL. Read more about subqueries here. 

What do you mean by denormalization?

Denormalization is a method of optimizing databases that involves adding redundant data to the tables in the database. Read more about denormalization here.

List the various forms of normalization.

The following are the different kinds of normalization that can be carried out on a database in SQL: 

• First Normal Form (1 NF)
• Second Normal Form (2 NF)
• Third Normal Form (3 NF)
• Fourth Normal Form aka Boyce Codd Normal Form (BCNF or 4 NF)
• Fifth Normal Form (5 NF)
• Sixth Normal Form (6 NF)

What is ETL in SQL?

ETL is short for extract, transform and load. Each of these is a basic operation in database management combined into one ETL tool to simplify the process. Extraction involves getting data out of a database; the transform operation modifies that data; load places that data into another database. 

How do you install MySQL?

SQL is free to install and use. Follow the instructions laid out here to start using MySQL. 

Is there a difference between DELETE and TRUNCATE statements?

Here are the key differences:

• DELETE is a DML (Data Manipulation Language) command whereas TRUNCATE is a DDL (Data Definition Language) command. 
• The DELETE command deletes rows in a table based on a specific set of conditions. TRUNCATE doesn’t use any conditions and removes all the rows in a table. 

What do you mean by aggregate and scaler functions?

Aggregate functions are applied to multiple values and return a single value. Think of how the average of a set of numbers is calculated. That’s an example of an aggregate function. 

Scalar functions, on the other hand, return a single output from one input value. A function that converts a string from uppercase to lowercase would be considered a scaler function. 

What is OLTP?

Online Transaction Processing (OLTP) are software systems that are able to handle transaction-based applications. Software used to book a hotel or send money to a friend are examples of OLTP systems. Read more about OLTP here. 

What is OLAP?

Online Analytical Processing (OLAP) is a term used to describe software that extracts insights from raw data in a consistent, interactive manner. OLAP systems are often used in finance, sales, marketing, and accounting departments. 

OLTP vs. OLAP: What’s the difference?

OLTP is a software application that facilitates transactions that need to be carried out in a system. OLAP is a family of applications that can be used to mine data, analyze it, and produce business intelligence. Read more about OLT and OLAP here. 

Define collation. 

Collation refers to the rules based on which the characters stored in an SQL database are encoded. This determines how the information is represented in the memory of a computer, and how data is retrieved or compared. Read more about collation here. 

What is a schema in SQL?

A schema is a logical representation of the different objects in the database. Think of it as a mapping of all the tables, functions, stored procedures, and indexes that are part of a database. Creating schemas allows you to give different users different levels of access and easily share the logical structure of the database with multiple users. Read more about schema in SQL here. 

What is a unique key in SQL?

A unique key is a constraint requiring every value in a particular column to be different from every other value in that single column. 

What does the SELECT statement do?

SELECT is the most common data manipulation language command used in SQL. It retrieves one or more rows in a table based on a certain set of conditions described by the user. 

What is a clause in SQL?

Clauses are functions built into SQL that refer to particular types of data. Here are examples of clauses in SQL: 

• WHERE: Specify the conditions based on which a query returns data
• OR: Used when there are multiple conditions involved and data is returned if any one of those conditions is satisfied
• AND: Sets at least two conditions, both of which need to be satisfied for a value to be returned by a query 

Discuss standard clauses used with SELECT query in SQL?

These are the clauses used with a SELECT query in SQL: 

• WHERE: Used to select items based on a specific set of conditions 
• GROUP BY: Used to group rows whose values match that of the result set
• ORDER BY: Arranges the resulting set in descending or ascending order
• TOP: Sets a limit on the number of records returned by a query
• HAVING: Serves the same function as the WHERE clause except that it can be used with aggregate functions 

Read more about clauses used with the SELECT query here. 

Explain UNION, MINUS, and INTERSECT commands. 

These commands are commonly applied to result sets in relational databases. Here’s what each of these commands does. 

UNION

The UNION command combines the output of two select queries into a single result, meaning that it unifies the result set from two tables, making it a binary set operator. There are two conditions that need to be fulfilled for the UNION command to work: 

• The two SELECT statements in question should have the same number of fields listed in the same order. 
• The data types of the fields should either be the same or compatible with each other. 

INTERSECT

INTERSECT is also a binary set operator like the UNION command, except that it outputs the rows that are in common between the results of two SELECT queries. This command always displays only distinct rows. Any duplicates that are present are ignored. 

MINUS

The MINUS command is the opposite of the INTERSECT command. When you apply MINUS to the results of two SELECT queries, it returns the rows that are in the first selection but not in the second one. 

The conditions that need to be fulfilled by the UNION command also apply to MINUS. That is, the two tables need to have the same number of fields in the same order and the data types need to be compatible. 

What is a cursor and how do you use it?

A cursor is an object that makes it possible to traverse the rows that have been produced by a query. 

These are the steps that you need to follow to use a cursor: 

1. Declare the cursor object along with the name of the cursor. 
2. Open the cursor by executing a SELECT query. 
3. Fetch one of the rows into one or multiple variables. 
4. Close the cursor and deallocate it. 

Read more about cursors in SQL here. 

Define entities and relationships. 

An entity is a real-world object, which means that it has certain attributes or properties. For example, a database could have entities like employees and department names. A relationship shows how different entities are related to each other. 

Read more about SQL entities and relationships here. 

What is pattern matching in SQL?

This is the process of identifying particular strings in a database using rules defined with regular expressions or wildcard characters. Read more about pattern matching here. 

Advanced SQL Interview Questions

What is SQL injection?

This is a code injection technique used to access unauthorized data stored in a database. An attacker exploiting an SQL injection vulnerability will often be able to view data belonging to other users in a system and in some cases even be able to modify that data. Read more about SQL injection here. 

What is a trigger in SQL?

A trigger is a specialized procedure in SQL which runs automatically when a particular event occurs or a certain condition has been met. 

For example, let’s say you want to know which of the orders coming into your store need to be fulfilled within a day. In that case, you could create a trigger that looks for one-day delivery orders and transfers their information into a new table. That way you have easy access to that data in a separate table. 

Read more about triggers in SQL. 

What is PostgreSQL?

PostgreSQL is an open-source relational database system with advanced features for enterprise use cases. It offers support for most of the popular programming languages, including Python, C, Javascript, Java, and Ruby. Read more about PostgreSQL.

How to insert multiple rows in SQL?

You can use the INSERT statement to insert multiple rows into a table in SQL. Read more about inserting rows in SQL.

How to find the nth highest salary in SQL?

There are multiple ways that you can go about finding the nth highest salary in a table that stores employee salaries. 

Below is an example of how you can use the TOP clause in SQL to write a query that returns the nth highest salary, without using a subquery. 

How do you copy a table in SQL?

You can use the SELECT INTO command to copy the contents of one table and place it into another. 

Let’s assume that you have two tables, T1 and T2, and you want to copy the contents of the former into the latter. To do this, you would type out the following query: 

SELECT * INTO T2 FROM T1; 

How to add a new column in SQL?

You can use the ALTER TABLE command to add a new column in SQL. 

Let’s say you have a table titled “Employees” and you want to add a new column titled “EmployeeID.” The following syntax is what you would use to add a new column in the “Employee” table: 

ALTER TABLE Employees
ADD EmployeeID data_type column_constraints;

Here, data_type refers to the data type you want to set for the column. That’s followed by any constraints that you want to set on the values in the column. 

Define a live lock. 

A live lock is when a table requires access to an exclusive lock, but is denied access because there are multiple overlapping shared locks. Read more about live locks. 

Define COMMIT and give an example?

COMMIT is a command used to permanently store the changes that have been made to a table. 

Let’s say you have a table that stores employee data and want to delete the data of an employee who has left the company. The syntax for that would be: 

DELETE from Employees where EmployeeName = ‘X’

If you want to save this change that you’ve made, you would use the COMMIT command, which has the following simple syntax: 

COMMIT;

What is the difference between NVL and NVL2 functions?

An NVL function requires only two parameters, whereas NVL2 functions take three parameters. 

What are the different types of SQL sandbox?

A sandbox is where you can test scripts. It gives you a safe place to try out scripts and observe the effects that they have on the database that you’re working in. 

There are three different types of sandboxes in SQL. They are: 

External access sandbox: You can access the file system and use classes, but you don’t get access to memory allocations or manipulated threads. 

Safe access sandbox: You can use commands, triggers, functions, and other basic features but you can’t access the memory or create files. 

Unsafe access sandbox: You can access the memory and have the ability to manipulate threads. 

Difference between CHAR and VARCHAR2 datatype in SQL?

CHAR is used to declare a string that has a fixed length. VARCHAR2 specifies a character string that can be of variable length. 

Difference between COALESCE & ISNULL?

The COALESCE command returns the very first non-null value that is present in the arguments that are provided to it. ISNULL provides a specific replacement value when there is a NULL value present in a table. Read more about COALESCE and NULL.

What is a deadlock?

A deadlock is a situation in which two SQL processes contend for access to resources that have been locked by the other. As a result of this, both processes cannot continue because they’re waiting for the other one to release access to the resource that it requires. SQL intervenes in this situation and kills off one process so that the deadlock doesn’t continue forever. 

Read more about deadlocks in SQL. 

What is a UNION operator?

The UNION operator is used in SQL to combine the results obtained from multiple queries into a single result set. 

What are the nonstandard string types?

The following are the nonstandard string types in SQL: 

• TEXT
• MEDIUMTEXT
• LONGTEXT
• TINYTEXT

Compare SQL vs. PL/SQL. 

The following are the differences between SQL and PL/SQL. 

• SQL is a structured query language used to access the data in a database. PL/SQL is a procedural language that enhances the way in which SQL commands can be used to interact with databases. 
• SQL queries execute one operation at a time whereas groups of commands can be executed in blocks in PL/SQL. 
• PL/SQL supports data types and constraints on variables. SQL does not support data variables. 
• SQL transacts directly with the database through commands. PL/SQL does not interact directly with the database. 

Compare MySQL vs. SQL Server. 

The following are the differences between MySQL and SQL Server: 

• MySQL was developed by Oracle whereas an SQL Server is a Microsoft product. 
• MySQL supports languages like Haskey and Perl. SQL Server has support for more popular modern languages like Java, Ruby, R, and C++. 
• MySQL doesn’t require a large amount of space in the memory to create and run databases. SQL Server requires large volumes of operational storage space. 
• MySQL is an open-source database system and is available for free use. SQL Server is a paid software. 
• MySQL blocks access to the database when it is carrying out backups. This is not the case with SQL Server. 
• You can manipulate files in the database while SQL Server is running. This is not possible with MySQL. 

What are the differences between NoSQL and SQL?

Here are the differences between NoSQL and SQL: 

• SQL is a database management software that supports the storage and analysis of structured data. NoSQL allows you to store and retrieve structured data but also offers support for unstructured and polymorphic data. 
• SQL was created in the year 1970 to deal with problems relating to file storage. NoSQL came about in the early 2000s as a way to allow databases to handle semi-structured and unstructured data. 
• SQL is vertically scalable, which means that you can add resources so that the existing hardware and software can handle greater capacities. NoSQL is horizontally scalable and lets you add larger nodes to smaller nodes to enhance its capacity. 
• There is no support for storing data hierarchically in SQL whereas NoSQL is especially suited to handling hierarchical data. 
• SQL doesn’t support the distribution of data, which means that one installation can only run on one system. Features like partition and repetition mean that NoSQL allows data distribution. 

What are the differences between MongoDB and SQL?

Here are the major differences between MongoDB and SQL: 

• SQL databases are relational in nature whereas MongoDB has non-relational databases. 
• SQL uses the commands and statements in the SQL query language. MongoDB uses JSON as its query language. 
• SQL is vertically scalable, whereas MongoDB is horizontally scalable. 
• SQL focuses on the properties of ACID: atomicity, consistency, isolation, and durability. MongoDB emphasizes CAP properties: consistency, availability, and partition tolerance. 

What are the differences between SQL and Transact Structured Query Language (TSQL)?

Here are the key differences between SQL and TSQL: 

• SQL was developed by IBM whereas TSQL was developed by Microsoft. 
• SQL is used to execute one query at a time to insert, update, or delete the data in a database. TSQL makes it possible to carry out transactional activities in a database using various programmatic techniques. 
• SQL is focused on manipulating the data in a database. TSQL is used to build applications and implement business logic within them. 

SQL Interview Questions for Developers

How do you find duplicate records in SQL?

You can use the GROUP BY command to find the duplicate records in a row in SQL as shown below. 

Read more about duplicate records. 

How do you delete duplicate rows in SQL?

Much like identifying duplicate rows, you can use the GROUP BY command to delete duplicate rows in SQL. To do this, delete the duplicate rows that surface when you identify the duplicates using GROUP BY. Read more about deleting rows in SQL. 

What is CASE in SQL?

The CASE command is how you use if/then logic in SQL. 

The CASE command is followed by a WHEN statement, which corresponds to the ‘if’ in if/then logic. The WHEN clause sets a condition for the query. It then moves to the THEN part of the query, which defines what occurs if the condition is satisfied. 

Read more about the use of CASE statements in SQL. 

How do you find the 2nd highest salary in a table in SQL?

You can use the following query:

What is the command used to create an index in SQL?

The CREATE INDEX command can be used to create an index in SQL. Read more about creating indexes in SQL. 

How to change the column data type in SQL?

The ALTER TABLE statement can be used to change the data type of a column in SQL. 

Assume you have a table named CarData and a column called CarNames. If you wanted to change the data type of that column from char to varchar, then you’d use the following query: 

ALTER TABLE CarData

ADD CarNames varchar (100); 

How to change column names in SQL?

You can use the ALTER TABLE command to change the name of a column in SQL. The syntax for the command is as shown below: 

What is a “view” in SQL?

A view is a table that holds data from one or multiple tables in an SQL database, but doesn’t actually physically exist in the memory. In that sense, a view can be thought of as a virtual table in the database. 

Views are made up using predefined queries to gather data that exists in the database. Much like any other table, views need to be given unique names. They don’t take up any space in the memory since they don’t have a physical existence there. 

Read more about SQL views.

How do you drop a column in SQL?

Use the ALTER TABLE command. 

Let’s say you have a table named Employees and want to drop the Salary column. You would type a query out as follows: 

ALTER TABLE Employees

DROP Salary; 

How do you join rows from two tables in SQL?

You can use the JOIN statement. There are four types of joins that you can use to achieve that: 

• INNER JOIN 
• LEFT JOIN 
• RIGHT JOIN 
• FULL JOIN 

The syntax for an inner join is as shown below: 

Read more about joining tables. 

How to use BETWEEN in SQL?

The BETWEEN statement in SQL is used to select values that fall within a particular range. These values can be text, numbers, or dates. Read more about the BETWEEN command. 

SQL Interview Questions for Data Analysts

Define normalization. 

Normalization is the process of restructuring the data in a database so that it adheres to a certain set of rules, which are known as normal forms. This is done to improve data integrity and reduce the amount of data redundancy. 

Data redundancy is undesirable in a database for a few reasons, including: 

• It is tougher to maintain a database when there are many redundant records in tables. 
• Inconsistencies begin to emerge in the data. 
• Updating, inserting, and deleting anomalies becomes more arduous when there is a high degree of data redundancy. 
• Redundant data occupies space in the memory and increases the storage requirements of a database. 

Normalization is undertaken so that all of this can be avoided. There are three normal forms that are used to normalize databases. Let’s find out what criteria each normal form requires. 

First Normal Form

• The rows and columns in the database are not ordered. 
• Duplicate data is present in the database. 
• The records in the intersections of rows and columns are always unique. 
• There are no hidden values in any columns. 

Second Normal Form

• The table must adhere to the criteria for the first normal form. 
• All non-key tables should have a dependency on the PRIMARY KEY. 
• All partial dependencies are transferred into a separate table. 

Third Normal Form

• The table adheres to the criteria for the second normal form. 
• Columns that are not the primary key columns shouldn’t have any dependencies on other non-primary key columns. 
• There are no instances of transitive functional dependencies in the table. 

What does it mean to denormalize a database? 

Denormalization is the process of enhancing the performance of a database that has already undergone the normalization process. Read more about denormalization.

How do you identify duplicate email IDs in an SQL table?

You can use the following query to identify whether there are duplicate email IDs in an SQL table: 

Read more about identifying duplicate entries. 

What is the difference between clustered and non-clustered indexes?

Here are the differences between clustered and non-clustered indexes in SQL: 

• The main data in a clustered index is the index itself. Non-clustered indexes have only a copy of the data. 
• Clustered indexes are able to store data on the disk, which non-clustered indexes cannot do. 
• The records in a clustered index are physically reordered so that they match the index. In non-clustered indexes, the order within the index isn’t the same as that on the physical disk. 
• Clustered indexes are faster and require less memory. Non-clustered indexes are slower and have higher memory requirements. 

What is the difference between an inner join and an outer join?

An inner join focuses on the data that two tables have in common. It is essentially the intersection of the two datasets. 

An outer join also includes the data that two tables have in common. But along with that, it also returns rows for which there is no match in the other table. 

Read more about inner and outer joins.

What is the difference between B-Tree and hash indexing in SQL?

Balanced trees, or B-trees, are used to make comparisons between columns that use the =, >, >=, <, <= operators or the BETWEEN clause. Hash indexes are used for comparisons using only the operators = or ⇔. 

Read more about B-trees and hash indexing.

What is the difference between UNION and UNION ALL?

The difference between the UNION and UNION ALL commands lies in the way they treat duplicate records. 

When you use the UNION command, it combines the data in two tables, but also eliminates duplicate data. UNION ALL doesn’t remove duplicate values and combines all of the data in the two tables. 

What do you mean by collation?

Collation refers to the rules based on which characters are encoded in an SQL database. Read more about SQL collation.

What do you mean by ACID?

ACID is an acronym that stands for atomicity, consistency, isolation, and durability. Let’s take a look at what each of these terms mean: 

Atomicity

Atomicity refers to the way all of the different operations within a command are treated. It implies that every operation is executed when it can be. If any of the operations cannot be completed, then the entire operation is rolled back. 

Consistency

The consistency property of SQL means that any transaction that needs to occur leaves the database only if it has been completed fully. If there is an error in the way the transaction occurs, then all of the changes that it affects are rolled back. Any system errors that crop up during execution will also result in the changes being negated. 

Isolation

Every transaction that occurs in SQL is independent and isolated. Any transaction that requires access to the result of another transaction can occur only after the latter has been executed to completion. This implies that you cannot perform one operation that requires data from multiple transactions parallelly. 

Durability

Durability refers to the way changes to the database are treated after a transaction has been completed. If a transaction has been fully executed, then any changes that it made to the database are permanent. This is true regardless of whether there are any system errors that occurred or abnormal behaviors in the system afterward. This property protects against data loss in the system. 

How and when do you use the GROUP BY function?

The GROUP BY command quite simply groups rows in a table that have the same values. In effect, it summarizes the data that is in the database. Read more about the GROUP BY command. 

What are the most common aggregate functions in SQL?

The most common aggregate functions that are used in SQL are: 

• COUNT – Counts the number of rows in a specific table or a view
• AVG – Calculates the average value from a given set of values
• MAX – Identifies the maximum value in a given set of values 
• MIN – Identifies the minimum value in a given set of values
• SUM – Outputs the sum of a given set of values

Assume that you have a table with columns titled idnumber and employeename. The table has 100 million rows. How would you sample a random row without affecting the performance of the database?

The following query can be used to sample a random row from the given columns: 

select * from table order by rand()

Related Read: What Is Data Analytics? and What Does a Data Analyst Do?

SQL Server Interview Questions

What is an SQL server?

An SQL server is a relational database management system created by Microsoft. 

What is CTE in SQL Server?

CTE (common table expressions) is a temporary name given to the result of a query which you can then reference in an INSERT, DELETE, UPDATE, or SELECT statement. 

There are two kinds of CTEs in SQL servers. They are: 

Non-recursive CTEs: The subroutines in these CTEs are not repeated during processing. 

Recursive CTEs: These CTEs use looped procedures in their execution. 

How do you change an SQL server password?

Use the following query to change the password in an SQL server: 

Read more about changing the password of an SQL server. 

How do you uninstall an SQL server?

Make sure that you’ve backed up the SQL server that you’re deleting before continuing with this process. Once that’s done, follow these steps: 

1. Go to Settings in the start menu and select Apps. 
2. Enter ‘sql’ in the search box. 
3. Select the option which says Microsoft SQL Server along with the version number listed. 
4. Click Uninstall. 
5. Select the Remove option in the popup. 
6. You will now see a page with the Select Instance dropdown. Here you can select the SQL server that you want to remove. Click Next. 
7. You will now see the Select Features page and can select the features that you want to remove, along with that instance. 
8. You will now be able to review the items that you’re removing on the Ready to Remove page. 
9. Hit Remove to initiate the uninstallation process. 

How to Prepare for an SQL Interview

Use the following pointers when preparing for an SQL interview. 

Theoretical Foundations

Prepare for your interview by learning the basic theory behind databases, relational database management systems, and SQL as a technology. In your interview, you might be asked about things like triggers, constraints, cursors, and basic SQL commands. 

Writing Queries

To extract specific data from a database, you need to be proficient at writing complex queries. You will usually be given one or two tables and asked to write various queries to retrieve information from them. So you should learn how to use aggregate functions, joins, comparison operators, and subqueries. 

Writing Code

Whenever you write code, make sure that you talk through your process so that interviewers can understand how you go about solving problems. Although solving the problem is the main goal, make sure that you always use the right syntax so that interviewers aren’t confused about what you’re doing. 

Communicate Effectively

Whether it’s a question about yourself or a technical SQL question, make sure that you communicate confidently and keep your answers to the point. Recruiters usually don’t have much time with each candidate and like when they can get the information they want quickly. 

SQL Interview FAQs

Since you’re here…
Switching to a career in data analytics is possible, no matter your background. We’ve helped over 10,000 students make it happen. Check out our free data analytics curriculum to gauge your interest, or go all-in with our Data Analytics Bootcamp.