100 SQL Interview Questions and Answers in 2024

With the help of Indexes, information retrieval from the database happens faster and with greater efficiency. Thus, indexes improve performance. There are three types of indexes:

• Clustered: Used for reordering tables and searching information with key values.
• Non-clustered: Used for maintaining the order of the tables.
• Unique: They ban fields from having duplicate values.

There can be many non-clustered indexes in a table, however, there can be only one clustered index.

As the name suggests, a synonym is used to give different names to the same object in the database. In the case of object-renaming or object schema-change, existing applications can continue to use older names because of synonyms. A synonym must only reference an object and not another synonym. Additionally, synonyms can also be used to reference objects in different databases or servers, by using 3 or 4 part object names. There can be many names for a single database object as long as all the names directly refer to the same database object.

You must ensure that you know answers to such SQL interview questions as answering them correctly will give you the much-needed confidence for the more difficult ones.

Below are some advantages of using Synonyms:

• Synonyms create a layer of abstraction for the specific object
• For objects, with complex 3 or 4 part names, residing on the same server, Synonyms can give a simpler alias
• Offers the flexibility to change object location without having to change the existing code
• When the name of an object is changed or dropped, Synonym offers backward compatibility for older applications
• Synonyms are also useful in front-end query tools such as Access linked tables and spreadsheets if there is a direct link to these tables

Yes, there are some disadvantages.

• Synonyms are only loosely linked to the referenced object and thus, can be deleted without warning when being used to reference a different database object
• Inside chaining cannot take place, meaning that the synonym of a synonym cannot be created
• One cannot create a table with the same Synonym name
• The checking for the object for which the Synonym is created happens at runtime and not at the time of creation. This means if there is an error, such as a spelling error, it will only show up at runtime creating a problem in accessing the object
• Synonyms cannot be referenced in DDL statements

For SQL interview questions that ask you to talk about the advantages or disadvantages of a certain component or tool, ensure that you list as many as you can. Also, you can make your answer to such an SQL interview question meaty by adding personal anecdotes about some of the advantages or disadvantages.

No. NULL values show an absence of characters, whereas zero is a numerical value. NULL values occur when a character is unavailable or not known. NULL values should also not be confused with blank space because a blank space is not supposed to have any data attached to it, whereas a NULL value shows a data record without any value assigned to it.

A Scalar subquery is when a query returns just one row and one column of data. A Correlated subquery occurs when a query cannot process without information from an outer query. In such cases, table aliases define the scope of the argument and the subquery is parameterized by an outer query. Thus, there is a correlation between the inner and outer queries. As a result, back and forth execution takes place where a single row of results from the outer query passes parameters to the inner query.

SQL interview questions like the one above try to ascertain the depth of your knowledge of SQL.

The function NVL (exp1, exp2) is a conversion function that changes exp1 into the target exp2 under the condition that exp1 is NULL. The data type of exp1 is the same as that of a return value. The function NVL2 (exp1, exp2, exp3), on the other hand, is a checking function, which determines whether exp1 is null or not. When exp1 is not null, exp2 is returned as the result. When exp1 is null, exp3 is returned as the result.

With the auto-increment command, one can generate unique numbers when new records are added to a table. This function is especially useful when one wants to automatically generate the primary key field values upon inserting new records. This command comes in handy on several platforms. The auto-increment command for the SQL servers is “identity”.

The main use of the recursive stored procedure is to make the code calls till the time certain boundary conditions are reached. This helps programmers enhance productivity by using the same code multiple times.

An SQL interview question like this one shows that even though some of the advanced concepts may be easy to understand, they may be difficult to recount when suddenly faced with the question. Thus, when you prepare for SQL interview questions, ensure to revise all types of concepts.

A ‘datawarehouse’ is a system used for analyzing and reporting data. It is very similar to a physical warehouse where inventory is stored and assessed before being sent to a customer. Here, data is stored, analyzed, and reported. A datawarehouse functions as a central repository of data integrated from different areas and sources and makes this data available for use.

DBMS is an abbreviation for Database Management System for creating and managing databases. There are two types of databases:

• Relational Database Management Systems (RDBMS) - Data is stored in tables.
• Non-Relational Database Management Systems - Mostly referred to as NoSQL, stores data in non-tabular format.

Structured Query Language is utilized for handling and modifying data in relational databases. With SQL, you can generate and alter databases, tables, and other related objects, alongside executing various data operations, including record insertion, updates, and deletions.

MySQL, on the other hand, is a specific relational database management system (RDBMS) that uses SQL as its primary language for managing data. MySQL is an open-source RDBMS that is widely used for web applications,

Below are the popular subsets used in SQL:

• DDL (Data Definition Language) - It is used to define and structure tables. Users can CREATE, ALTER, and DELETE the database tables.
• DCL (Data Control Language) - Administrators use it to give users privileges to GRANT or REVOKE permissions to the database.
• DML (Data Manipulation Language) - It allows users to either UPDATE, INSERT, RETRIEVE, or DELETE information from the database.

Joins is a statement used to join two or more rows based on their relationship. There are four types of Join statements:

• Left Join
• Right Join
• Inner Join
• Full Join

A primary key is used to identify unique rows or tables in a database. Primary keys must always contain unique values. Null or duplicate values are not considered primary keys.

A foreign key is used to link two or more tables together. Its values match with a primary key from a different table. Foreign keys are like references between tables.

A unique key ensures a table has a unique value not found or contained in other rows or columns. Unlike the primary key, the unique key may have multiple columns. You can create a unique key using the keyword "UNIQUE" when defining the table.

Below is how to create an employee table:

SELECT is a DML command used for fetching one or more tables. It queries for information which usually returns a set of results.

WHERE - filters the rows according to their criteria

ORDER BY - Sorts the tables/rows according to the ASC clause (ascending order) or DESC clause (descending order)

GROUP BY - groups data from different tables that have similar rows in the database

CHAR is a fixed-length string character, whereas VARCHAR is a variable-length string data structure. VARCHAR is preferred over CHAR because it is more space-efficient when storing strings with variable lengths.

One-to-one relationship - This relationship exists between two tables when a single row in one table corresponds to a single row in another table. This relationship is usually established using a foreign key constraint.

One-to-Many/Many-to-One - This relationship exists between two tables when a single row in one table corresponds to multiple rows in another table. This relationship is also established using a foreign key constraint.

Many-to-Many - This relationship exists between two tables when multiple rows in one table correspond to multiple rows in another table. This relationship is usually implemented using an intermediate table that contains foreign keys to the two tables being related.

The truncate command is used when you want to delete all rows and values from a table. It is a DDL type of command which is faster. While the DELETE command is used when you want to delete a specific row in a table. It is a DML command type and less efficient than the truncate statement.

A cursor is a temporary memory allocated by the server when performing any DML queries. They are used to store Database Tables. Basically a cursor in sql is an object in database code that allows processes to process rows one by one. While in other programming languages sets of data is processed individually through a loop, in SQL, data is processed in a set through a cursor.

Two types of cursors are Implicit cursors and Explicit cursors.

Implicit Cursors:
They are Default Cursors of SQL SERVER. Allocated when the user performs DML operations.

Explicit Cursors:
They are created by users in need. They are used for Fetching data from Tables in Row-By-Row Manner.

Normalization is a method of breaking down larger, complex data into smaller tables. It helps in filtering unnecessary, redundant data and leaves only unique values.

ETL is an acronym for Extract, Transform, and Load. It is a process where you extract data from different sources, transform the data quality, and finally load it into the database.

Local variables are used inside a function and can’t be reused by other functions, whereas global variables can be accessed and used throughout the program.

A subquery is a query that is found in another query. Usually referred to as an inner query, its output is typically used by another query.

ACID in SQL refers to a set of properties that guarantee the reliable and consistent processing of database transactions. It is an acronym where each letter stands for one of the properties:

Atomicity: Ensures that a transaction is either fully completed or not executed at all. If any part of a transaction fails, the entire transaction is rolled back, and the database remains unchanged.

Consistency: Guarantees that the database transitions from one consistent state to another upon the completion of a transaction. All data must adhere to predefined rules and constraints.

Isolation: Provides a degree of separation between concurrent transactions, ensuring that they do not interfere with one other. It helps maintain data integrity by controlling the visibility of changes made by one transaction to another.

Durability: Guarantees that after a transaction has been committed, the modifications made to the database become permanent, even if a system failure or crash occurs.

ACID properties are vital in maintaining data integrity and consistency in relational database management systems (RDBMS) and ensuring the robustness of transactions.

A stored procedure is a function that contains a group of query statements that can be reused. They are stored inside a named object in the database and can be executed anytime they are required.

Triggers are special stored procedures that run when there's an event in the database server, such as changing data in a table. A trigger is different from a regular stored procedure as it cannot be directly called like a regular stored procedure.

An Entity Relationship (ER) diagram is a visual representation of the relationship tables found in the database. It displays the table structures and primary and foreign keys.

Triggers in SQL are used to automatically enforce business rules or maintain data integrity by executing predefined actions in response to specific database events, such as INSERT, UPDATE, or DELETE. Common use cases include data validation, data auditing, and maintaining referential integrity or complex relationships between tables.

Sparse columns are columns that provide optimized storage for null values. They reduce space that is usually taken up by null values and can be defined by using CREATE or ALTER statements.

Check constraints are used for checking and ensuring that values in a table follow domain integrity. Users can apply Check constraints to single and multiple columns.

In SQL, collation refers to a set of rules that govern the proper ordering, comparison, and representation of characters in a particular character set or encoding. Collation influences how text data in a database is sorted, searched, and compared. It typically accounts for various linguistic considerations such as case sensitivity, accent sensitivity, and specific language-based conventions.

To write a SQL query that shows salespeople and customers who live in the same city, you need to have information about both salespeople and customers. Here's an example SQL query assuming you have two tables: salespeople and customers.

In this query, we're selecting the salesperson name, customer name, and city from two tables (salespeople and customers) using an INNER JOIN to connect them based on the condition that the city in the salespeople table equals the city in the customers table.

To find orders with an order amount between 1000 and 5000, you can use the following SQL query:

In this query, replace "orders" with the actual name of your orders table, and "order_amount" with the appropriate column name representing the order amount in your table. This query will return all rows where the order amount falls between 1000 and 5000, inclusive.

A filtered index is a non-clustered index that comes with optimized disk restore. It is created when a column has few values for queries. The purpose of a filtered index is to optimize query performance by reducing the size of the index and the number of index pages that need to be read. It helps in improving performance, storage reduction, and index maintenance.

A clause is one of the SQL query statements that filters or customizes data for a query. It allows users to limit the results by providing a conditional statement to the query. It is typically used when a large amount of data is in the database.

The following SQL query removes the duplicate values

A case function is a SQL logic that uses the if-then-else statements. It evaluates the conditions of a table and returns multiple result expressions.

A view is a virtual table containing values in one or multiple tables. Views restrict data by selecting only required values to make queries easy.

A schema in SQL is a collection of database objects, including tables, indexes, sequences, and other schema objects. It defines how data is organized in a relational database system. It is used to manage database objects and control access to them by different users.

These conditions are used for searching values except that the HAVING clause is used with the SELECT statement accompanied by the GROUP BY clause. The HAVING clause is used in combination with the GROUP BY clause to filter the data based on aggregate values, while the WHERE clause is used to filter the data based on individual values.

In SQL, a CTE (Common Table Expression) is a temporary result set, often used to simplify complex queries by breaking them into smaller, more manageable pieces. A CTE is created using the WITH clause and is available only within the context of the query that follows it.

Operators are special characters or words that perform specific operations. They are used with the WHERE clause to filter data in most cases.

CDC means change data capture. It records the recent activities made by the INSERT, DELETE, and UPDATE statements made to the tables. It is basically a process of identifying and capturing changes made to data in the database and returning those changes in real time. This capture of changes from transactions in a source database and transferring them to the target, all in real-time, keeps the system in sync. This allows for reliable data copying and zero-downtime cloud migrations.

“AUTO INCREMENT” is a clause used to generate unique values whenever a new record is created and inserted into a table. It means that every time a new row is inserted into the table, the database system automatically generates a new value for that column.

COALESCE is a function that takes a set of inputs and returns the first non-null values. It is used to handle null values in a query's result set.

Data integrity maintains security measures to the database by implementing rules and processes during the design phase. It helps with consistency and accuracy in the database.

Views can be classified into four categories:

Simple View - This is based on a single table and does not have a GROUP BY clause or other features.

Complex View - This is built from several tables and includes a GROUP BY clause and functions.

Inline View - This is constructed using a subquery in the FROM clause, creating a temporary table that streamlines complex queries.

Materialized View- This saves both the definition and the details. It builds data replicas by physically preserving them.

SQL injection is a flaw in a code that allows attackers to take control of back-end processes and access, retrieve, and delete sensitive data stored in databases. This strategy is widely utilized using data-driven apps to get access to sensitive data and execute administrative tasks on databases.

In SQL, the UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. The resulting set consists of unified records from both queries, and any duplicate rows are eliminated. The UNION operator requires that the SELECT statements being combined have the same number of columns and that the data types of the corresponding columns are compatible.

An INTERSECT operator combines two or more SELECT statements and returns only the values common from the SELECT statements. For example, we have table1 and table2; when we apply INTERSECT to the query statement, only the common values are returned from the SELECT statements.

BETWEEN operator is used for representing rows based on specific values. It then returns the total number of values found between two specified values. While IN operator is used to search for values within a given range of values. We apply the In operator if there is more than one value to define the range.

White box testing is a method for dealing with internal database structures where users can hide specification details. The methods involve the following:

• Eliminating internal errors
• Checks the consistency of the database
• Performs testing for functions such as triggers, views, and basic queries.

Black box testing is a method that tests the interface of the database. It verifies incoming data, mapping details, and data used for query functions. Here the tester provides the input and watches the output generated by the system.

The black box testing involves testing the database by treating it as a "black box," focusing on its external behavior and functionality, without any knowledge of its internal structure, design, or code. The tester provides input to the database through its interfaces (such as SQL queries, stored procedures, or API calls), and observes the output generated by the database in response to that input.

It enables us to find how the system behaves to expected and unexpected user actions, response time, reliability issues, etc. Learn more about Black Box Testing and White Box Testing.

A TABLESAMPLE is a SQL statement that extracts random data using the FROM condition from a TABLE. It is done when the user doesn’t need the entire dataset but only a specific table portion.

Database mirroring is a disaster recovery technique used in SQL Server to provide redundancy and failover capabilities for critical databases. It involves maintaining two copies of a database, known as the principal database and the mirror database, on two separate servers. Database mirroring provides a highly reliable and robust solution for critical databases, with the ability to maintain high availability, reduce downtime, and provide a quick recovery in case of a failure.

The database engine is the underlying software mechanism used for storing, processing, and securing data. It processes queries, grants access, and optimizes transactions in the database engine.

Procedural Language for SQL is an extension that allows users to write code in a procedural language. The code can be run in the same SQL server and has features such as high security, scalability, and flexibility.

The WITH TIES statement is used in SQL queries to include additional rows that have the same values as the last row in the result set. It is typically used in conjunction with the TOP or LIMIT statement and the ORDER BY clause to return additional rows beyond the specified limit.

The DISTINCT condition is accompanied by the SELECT statement to remove all duplicate records and return unique values. It is used to eliminate duplicate results returned from a SELECT statement.

The MERGE statement in SQL is used to perform a combination of INSERT, UPDATE, and DELETE operations on a target table based on the data present in a source table. It is also known as UPSERT operation, which means to update the existing record if it exists, otherwise insert a new record.

An ALIAS command is a name given to a table. It is used with the WHERE statement when users need to identify a particular table. They are often used to increase the readability of the column names. An alias exists only for the time the query exists and is created with the AS keyword.

Alias Syntax for column

SELECT column_name AS alias_name
FROM table_name;

Alias syntax for table

SELECT column_name(s)
FROM table_name AS alias_name;

This is how you UPDATE a table in SQL:

The REPLACE function is used to replace existing characters in all instances. The function searches the original string, identifies all instances of the substring to be replaced and then replaces them with the specified replacement substring.

UNION combines the result of two SELECT statements by removing duplicate records and returning a sorted result set.

UNION ALL also combines the result of two SELECT statements, but it does not remove duplicates and does not sort the result set. It is faster due to less processing overhead.

The STUFF() function deletes a string section and inserts another part into a string starting from a specified position.

Syntax:

STUFF (source_string, start, length, add_string)

Where:-

1. source_string: This is the original string to be modified.
2. start: It is the starting index from which the given length of characters are deleted, and a new sequence of characters will be added.
3. length: The number of characters to be deleted from the starting index in the main string.
4. add_string: The new set of characters (string) to be inserted in place of deleted characters from the starting index

RENAME changes the name of a column or table, while ALIAS gives an additional name to an existing object. RENAME is permanent, whereas ALIAS is a temporary name.

To optimize a slow-performing SQL query, consider the following steps:

• Examine the execution plan, identifying potential bottlenecks.
• Use efficient JOIN operations (e.g., INNER JOIN, OUTER JOIN) for quicker results.
• Optimize SELECT statement, retrieving only necessary columns.
• Use appropriate indexes on frequently searched columns.
• Implement pagination, limiting the returned records using OFFSET and FETCH or LIMIT.
• Filter data as early as possible with WHERE clauses to reduce the query's scope.
• Avoid using subqueries when possible, opting for JOINs or CTEs.
• Use aggregate functions and GROUP BY effectively.
• Optimize the database schema and normalize tables.

Index-Seek is a faster and more efficient way to search for records in a table. It is used when the query optimizer chooses to navigate the index's B-tree structure directly to find the specific records, rather than scanning the entire table/index. It is performed when an appropriate index exists and the query matches the index conditions (e.g., WHERE clause with indexed columns).

A deadlock occurs when two or more transactions are waiting indefinitely for each other to release resources such as locks on rows or tables. To prevent deadlocks:

• Access objects in a consistent order, reducing the chances of cyclic dependencies.
• Reduce lock time, ensuring minimal time between acquiring and releasing locks.
• Apply appropriate isolation levels to limit the locking scope
• Use transactions with a smaller scope and avoid running large, long-running transactions.
• Implement a deadlock detection and handling mechanism, such as timeouts or retry logic.

A LIKE operator checks if a value matches a given string. Here is an example:

A live lock is a situation where two or more processes are actively trying to make progress but are blocked and unable to proceed. In live lock, the processes are not blocked on resources but are actively trying to complete the tasks causing them to interfere with each other. It is difficult to detect and resolve Live locks because the system does not crash or give an error message.

COMMIT is used when a transaction happens, and the changes are permanently recorded in the database. Once you use COMMIT, you can't revert the changes unless you perform another transaction to reverse them.

A non-equi join is a method of joining two or more tables without an equal condition. Operators such as <>,!=,<,>,BETWEEN are used.
!=,<,>,Between. See below:

Equi joins two or more tables using the equal sign operator (=). See the example below:

COMMIT is a statement executed to save the changes made to a database. It ensures that the changes made within a transaction are permanent and cannot be rolled back. On the other hand, a ROLLBACK statement is executed to revert all the changes made on the current transaction to the previous state of the database.

GETDATE function returns the date and time of a location. While on the other hand, the SYSDATETIME function returns the date and time with a precision of 7 digits after the decimal point.

Temporary Table: A temporary table is created using CREATE TABLE statement with a prefix #, and it is stored in the tempdb system database. Temporary tables support indexing, statistics, and can have constraints. There are two types of temporary tables: local (visible only to the session that created it) and global (accessible to all sessions).

Table Variable: A table variable is declared using DECLARE statement with @ prefix, and it is also stored in the tempdb system database. Table variables don't require explicit dropping, have no statistics, limited constraints, and are scoped to the batch or stored procedure in which they are declared.

SET NOCOUNT function is a function that helps to stop the message that indicates how many rows are being affected while executing a T-SQL statement or stored procedure.

In SQL transactions, isolation refers to the degree to which a transaction is separated from other transactions taking place concurrently within a database management system (DBMS). It is one of the four key properties of database transactions - known as ACID (Atomicity, Consistency, Isolation, Durability). Isolation levels determine the extent to which changes made by one transaction are visible to other simultaneous transactions, and each level provides different performance and side effects.

The main advantage of using graph databases is their ability to efficiently handle interconnected data, making them suitable for use cases where relationships between entities are deep, complex, or frequently changing. They excel at tasks such as performing complex graph analysis, traversing hierarchical relationships, or finding patterns in connected data.

This will create a new table with the same structure as the old table but without the values from the previous table.

A covering index is a non-clustered index that includes all the columns required to satisfy a particular query, eliminating the need for an additional key lookup in the base table. It can significantly improve query performance by reducing I/O operations, especially when the included columns are frequently accessed or filtered in the query.

You should use a covering index when:

• The underlying table has a large number of columns, but only a subset is regularly queried.
• The query needs to access data in a specific order.
• JOIN or aggregation operations can be optimized using a covering index.

For SQL Server, you can use: