SQL Interview Questions and Answers 2025

Top SQL Interview Questions List for 2025

SQL is a common language for Structured Query Language, which is based on general English, whereas MySQL is a database management system.

Structured Query Language is the main part of the relational database, which can be utilized for gaining access to the database and also plays a major role in the management of the database. On the other hand, MySQL is a Relational Database Management System like SQL Server, Informix, and others.

Set 1 - SQL Interview Questions and Answers

1) State the different subsets of SQL.

There are basically three subsets of SQL:

Data Definition Language, or DDL, which lets the candidate carry out different functions and perform tasks on the Database, is one of the most important subsets of SQL. These operations and functions include ALTER, CREATE and DELETE.

Data Manipulating Language, or DML, lets the candidates manipulate and gain access to the data in the Database. The following is really helpful for the candidate in inserting, deleting, and improving data from the Database. The following also helps the candidate retrieve the data from the Database.

Data Control Language, or DCL, proves to be beneficial for the candidate. The following lets the candidate to gain control over the Database and also helps him to access the following. For instance, Grant and Revoke access permissions are some of the Data Control Languages.

2) What is meant by DBMS, and how can it be classified?

DBMS stands for Database Management System, and it is a software application that connects with the candidate as well as the applications. The following also connects with the Database and applications to acquire and examine the data in the Database.

The Database Management System lets the candidate connect with the Database. The information that is stored in the Database can be improved, acquired, and deleted and can be of any type. The types of data can be digits, strings, pictures, and others.

Database Management Systems can be classified into two different categories. They are:

RDMS, or the Relational Database Management System in which the necessary information is stored in the form of relations or tables, is the first category. MySQL is the one of such Database Management Systems.

The other Database Management System is Non-Relational Database Management System. In the following form, there is nothing related to tables, relations, and attributes. One of such Database Management System is Mongo.

3) How are the table and field defined in SQL?

When we use the term "table," it means that there is a cluster or cluster of data in a proper and well-settled manner that is arranged in the form of rows and columns. One of such tables is Student Information.

Basically, we can say that a field is the number of columns in a table. Some examples of the field are Stu Id, Stu Name, and Stu Marks.

4) In SQL, what is meant by joins?

In SQL, we generally use the JOIN clause to conjunct rows from two tables or more at times. The following conjunction is based on a related column between the following.

The following can also be utilized to amalgamate two tables. This can also be utilized for acquiring information from there. SQL comprises of 4 different kinds of joins. They are Inner Join, Right Join, Left Join and Full Join.

5) How can we distinguish between CHAR and VARCHAR 2 data type in SQL?

CHAR as well as VARCHAR 2 are both used for the character's data type. But there lies a difference. We can say that VARCHAR2 is basically utilized for character strings of different lengths. On the other hand, CHAR is used for strings having the same length.

We can better understand the following through illustrations. For example, CHAR (10), which can be used to store only 10 characters, On the other hand, VARCHAR (10) will have the capability to store characters irrespective of their length.

For example, VARCHAR 10 will be able to store an even number of characters lower than ten, which includes 6, 8, 4, and 2.

This is one of the most frequently asked SQL Interview Questions in the interview.

6) How can you define Primary Key?

A Primary Key is basically a column that verifies each row in a table. It can also be a set or cluster of columns for identifying the number of rows in a table. The specialty of a primary key is that it can uniquely identify a single row in a table that to in a different and unique manner.

The primary key doesn’t allow null values in the table. For instance, Stu_Id is the primary key in the table containing information about students.

7) How can you define constraints?

Constraints are those elements of the table which can be used to classify the data limit type of the table. The data limit of the table can be specified while building or changing the statement of the table. We can understand the constraints of some samples. The samples of constraints are:

NOT NULL

DEFAULT

CHECK

PRIMARY KEY

UNIQUE

FOREIGN KEY

8) On what basis you can distinguish the statement of DELETE and TRUNCATE?

There are certain specifications by which we can distinguish the statements of DELETE and TRUNCATE. The specifications are:

DELETE Statement:

The command of DELETE is used for deleting a particular row in the table.

The command of DELETE allows the user to revert information back to its initial state after the use of the following statement or command.

The statement of DELETE is a DML (Data Managing Language) command.

The following command works slower than the command of TRUNCATE.

TRUNCATE Statement:

The command of TRUNCATE is used for deleting or erasing all the rows of the table in one go.

The command of TRUNCATE doesn’t allow the user to revert the data back to its initial state once it is deleted.

The following is a DDL (Data Definition Language) command.

The following command is basically faster than the command of DELETE.

9) How can you define the constraint: Unique Key?

The Unique Key classifies a particular row in the table in a different and unique way. The following key allows the user to insert multiple values that can be inserted according to the number of tables. The Unique key also allows the user to insert null values in a table.

10) How can you define the constraint: Foreign Key?

When the constraint of Foreign Key is used in the child table, it references the parent key in the main or primary table.

The foreign key is used to maintain referential integrity. The following is done by creating a link in two tables containing data.

The foreign key constraint allows the user to avoid actions that would otherwise destroy the connection between the parent and child tables. 

 

Set 2 - SQL Interview Questions and Answers

11) How can you explain data integrity?

We can term data integrity by saying that it maintains the consistency of the data which is stored in the Database. The following is also used to maintain the accuracy of the data in a database.

Integrity constraints are often changed when entering them into the Database. These constraints are edited according to the business rules before they are fed into the Database.

12) On what basis can you distinguish the clustered index and non-clustered index in SQL?

We can distinguish the clustered from the non-clustered index on the following basis:

A clustered index can be utilized for recovering information from the Database in an easy way. The works listed below are faster than the non-clustered index. Non clustered index is generally slower than the clustered index.

Clustered indexing basically changes the way in which the storage of records is maintained in a database in a changed manner. The clustered index does this because the following classifies the rows by the columns which are ready to be the clustered index.

The non-clustered index, on the other hand, does not alter the way information is stored. It instead builds another object or element in a table which points out the original rows of the table when searched.

There can be only one clustered index in a single table. On the other hand, there can be more than one table in a non-clustered index.

13) What are the different types of joins?

Joins are basically used to recover data from a database in between the tables. Generally, there are four types of joins:

Inner Join: In MySQL, the use of an inner join is considered the most common join. The following is used to recover the rows from numerous tables that satisfy and take care of the condition of joining.

Left join: In MySQL, the use of the left join is done to return all the rows from the tables that are on the left side. There is another criterion that it should satisfy. That is the compatibility of the data in the right column with the data in the left column.

Right join: In MySQL, the use of the right join is done to return all the rows from the tables that are on the right side. There is another criterion that it should satisfy. That is the compatibility of the data in the left column with the data in the right column.

Full join: In MySQL, the use of full joins is done to return all the records at that time when a match is found between two or more tables. Hence, the following returns all the rows from the table on the left-hand side along with the tables on the right-hand side.

14) How can you define Denormalization?

Denormalization refers to approaches for accessing information in lower forms in a database, beginning with higher forms of data. The next component, which ensures consistency and dependability in a table, benefits the managers by improving the performance of the entire scenario.

The consistent data then gets added up in a table. This is accomplished by incorporating the problems obtained through a harmonious shortlisting of data from various tables into the database. 

15) How can you define entities and relationships?

Entities:

An entity can be defined as an object, or a place, or a person in the actual world that has the ability to be stored in the Database. The data showing only one type of entity can be stored in tables.

For instance, the Database of a bank has a customer table to store its data as a cluster of qualities, which are basically the columns in the table for every single consumer.

Relationships:

Relationships are the connections or relationships that exist between entities that correlate with one another. It is established when the entities depend upon each other.

For instance, the name of the consumer has a relationship with the account number of the same consumer along with its contact information. The contact information might be present on the same table. Relationships can also be established between different tables, such as the relationship between customers and accounts.

16) How can the index be defined?

An index is basically a process of performance tuning that lets the user recover the information or records from the table with great speed. The specialty of the index is that it builds an entry for a particular value, which in turn allows it to recover the data with more speed.

17) What are the various kinds of the index, and how can you explain them?

There are three different types of indices. They are classified as either unique indices, clustered indices, or non-clustered indices.They can be classified in the following ways:

Unique index

The following index doesn’t let the particular field to create unreal values within it. A field with a real value can be called a uniquely indexed field. The unique index can function automatically if the primary key is particularly defined.

Clustered index

The following index rearranges the basic, or physical, arrangement of the table along with the research, which depends upon the key values of the table. There can be only one clustered index in one table.

Non-clustered index:

The following index doesn’t contribute to changing the general or the physical arrangement of the tables. The following index is also used to maintain the data in a logical manner. Each table can have more than one non-clustered index.

18) How can normalization be defined, and what benefits can it provide?

Normalization can be called as the method of arranging data in a proper way to prevent the use of unreal information in a table along with the redundancy. There are a lot of benefits that the user can enjoy with normalization. Some benefits of the following are:

• Proper arrangements of the Database
• More tables consisting of smaller rows
• Making data access efficient
• More acceptances towards the queries
• Accessing information fast
• Easy implementation of security measures
• Ensures flexibility in modifications
• Eradicating unreal data and decreasing the redundancy
• Compact database storage
• Maintaining data consistency

This is one of the most crucial SQL Interview questions which can be essential for the candidate.

19) What can be the basic difference between DROP and TRUNCATE commands?

The command of DROP is used to remove a table completely, and the following can be reverted from the Database.

On the other hand, the command of TRUNCATE removes all the rows in the table.

This is one of the basic SQL Interview questions that can be asked.

20) What are the different kinds of normalization and explain the following?

There are a lot of successive levels of normalization. The following successive levels of normalization are known as normal forms. Each consecutive normal form is dependent on its preceding one. The first three normal forms are mostly sufficient for SQL practices. The types of normalization are:

1NF or the first normal form: This normalization doesn’t repeat groups within the rows.

2NF or the second normal form: This normalization states that each and every non-key value of the column has to function according to the whole primary key.

3NF or the third normal form: This normalization depends on the main or the primary key without depending on the non-key supporting value.

 

Set 3 - SQL Interview Questions and Answers

There are more SQL Interview questions which the candidates are asked.

 

1) What is the use of ACID in a database?

In a database, ACID stands for Atomicity, Consistency, Isolation and Durability. The following element is used to make sure that the transactions made on the basis of information are reliably transmitted to the Database.

Atomicity: The following term refers to the complete or incomplete transactions referring to a single logical operation of data. This means, when any part of the transaction fails or remains incomplete, it negatively affects the entire transaction. This also doesn’t allow the state of the Database to change in any way.

Consistency: The following term makes sure that the information must fit into and satisfy all the rules and conditions of the validation. In other words, we can say that the transaction never moves out of the Database or never gets erased without finishing its state.

Isolation: The main objective of the following term is that it can control concurrency. 

Durability: The following term ensures that after a transaction is completed, there is no way that it can be reverted back. Power loss and other errors of this sort cannot stop it.

This is one of the most important SQL interview questions that are likely to be asked during an interview.

2) How can Trigger in SQL be defined?

The following are specific procedures that are conserved and are used to implement automatically in place or else implement the following after the improvement and upgrading of data. The following also allows the user to deploy a batch containing code when any kind of issue, like an insert or update, is implemented against a particular table.

3) A lot of operators are available in SQL. What are the various operators in SQL?

These kinds of SQL interview questions confuse the candidate at times. So, coming to the question, there are three different kinds of operators available in SQL. They are:

• Arithmetical operators
• Logical operators
• Comparison operators

4) Are the terms zero or blank space the same as that of NULL values?

No, absolutely not. We can never say that the terms zero or blank space as same as that of NULL values. The element of NULL value depicts a particular value which is not known or not present in a particular data. On the other hand, terms like zero value or blank spaces depict numbers and characters in the following, respectively. 

5) How can you distinguish between natural join and cross join?

The natural join basically focuses on all the columns of the table that have the same data types as well as the same names in both the tables. The cross join builds the cross product or the Cartesian product of two different tables.

6) How can you define subquery in SQL?

The term subquery is an issue within another issue where the same problem or issue is used to recover necessary data again from the Database.

There are two parts of a subquery. The two parts involve the outer query and the inner query. The inner query is basically called as a subquery, and the outer query is known as the main query in the Database. The implementation of subqueries is always done first, and the outcome of the following is always passed on to the main query.

The use of comparison operators like <,> or = can be done in a subquery. These kinds of SQL Interview Questions are a bit difficult to understand and answer accordingly.

There are a lot of SQL Interview Questions apart from this, which the candidates must practice qualifying for the interview.

 

 

For more information on SQL training and certification, please contact us at Click Here or chat with our course expert. If you’re looking for other training and certifications to enhance your career, you can explore all courses offered by us and request a trial. You can also subscribe to our newsletters to gain insights.

SQL Interview Questions for Experienced Professionals

1) On what basis can you distinguish the clustered index and non-clustered index in SQL?

We can distinguish the clustered from the non-clustered index on the following basis:

A clustered index can be utilized for easily recovering information from the Database. The works listed below are faster than the non-clustered index. Non clustered index is generally slower than a clustered index.

Clustered indexing changes how the storage of records is maintained in a database in a changed manner. The clustered index does this because the following classifies the rows by the columns which are ready to be the clustered index.

The non-clustered index, on the other hand, does not alter the way information is stored. It instead builds another object or element in a table which points out the original rows of the table when searched.

There can be only one clustered index in a single table. On the other hand, there can be more than one table in a non-clustered index.

Example:

-- Creating a Clustered Index

CREATE CLUSTERED INDEX idx_employee_id ON Employees(EmployeeID);

 

-- Creating a Non-Clustered Index

CREATE NONCLUSTERED INDEX idx_employee_name ON Employees(Name);

When to Use?

Use clustered indexes for primary keys and frequently sorted columns.

Use non-clustered indexes for faster search and filtering.

 

2) What are Window Functions in SQL?

Window functions perform calculations across a set of rows related to the current row, without affecting the row grouping.

Example: Running total of employee salaries

SELECT Name, Salary,  

SUM(Salary) OVER (PARTITION BY Department ORDER BY EmployeeID) AS RunningTotal  

FROM Employees;

Key Functions:

ROW_NUMBER() – Assigns a unique number to each row.

RANK() – Similar to ROW_NUMBER() but assigns the same rank for duplicates.

DENSE_RANK() – No gaps in ranking for duplicates.

3) How do you handle NULL values in SQL?

NULL values can impact calculations and filtering. Use these methods:

COALESCE(): Returns the first non-null value.

ISNULL() (SQL Server-specific): Replaces NULL with a default value.

Example: Handling NULL in a Salary Column

SELECT Name, COALESCE(Salary, 0) AS Salary FROM Employees;

 

4) How do you prevent SQL injection attacks?

- Use Prepared Statements: Avoids direct insertion of user input.

- Use Stored Procedures: Encapsulates logic to reduce SQL injection risks.

- Limit Database Permissions: Restrict users from executing dangerous commands.

Example: Safe Query Using Prepared Statements (MySQL)

PREPARE stmt FROM 'SELECT * FROM Users WHERE Email = ?';  

SET @email = 'user@example.com';  

EXECUTE stmt USING @email;

Never concatenate user inputs directly into queries.

 

5) What is ACID in SQL Transactions?

ACID properties ensure reliability in database transactions:

 

Atomicity: Transactions are all-or-nothing.

Consistency: Ensures database integrity before and after a transaction.

Isolation: Transactions operate independently.

Durability: Changes persist even after system failures.

Example: Implementing ACID Transactions

BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;

UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

COMMIT;  -- Ensures transaction is complete

If an issue occurs, ROLLBACK; cancels the transaction.

 

6) What is the difference between UNION and UNION ALL?

UNION removes duplicate rows.

UNION ALL keeps all rows, including duplicates.

Example:

SELECT Name FROM Employees_A  

UNION  

SELECT Name FROM Employees_B;

Use UNION ALL when duplicates are needed for performance improvement.

 

7) What is a Self Join?

A self join is when a table is joined with itself to compare rows.

Example: Find employees who report to the same manager

SELECT e1.Name AS Employee, e2.Name AS Manager  

FROM Employees e1  

JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

Self joins are useful for hierarchical data structures.

 

8) What is a Materialized View? How is it different from a Regular View?

A materialized view stores query results physically, improving performance, whereas a regular view is just a virtual representation.

Example: Creating a Materialized View (PostgreSQL)

CREATE MATERIALIZED VIEW HighSalary AS  

SELECT * FROM Employees WHERE Salary > 80000;

Materialized views are useful for precomputed reports.

 

9) How do you find duplicate records in a table?

SELECT Name, COUNT(*) AS Count  

FROM Employees  

GROUP BY Name  

HAVING COUNT(*) > 1;

This groups by the column and filters where the count is greater than 1.

 

10) What is the difference between a Primary Key and a Unique Key?

Feature: Uniqueness, NULL Allowed?, Number per Table

Primary Key: Ensures unique values, No NULL values, Only one

Unique Key: Ensures unique values, Allows NULL values, Multiple

Example:

CREATE TABLE Employees (

    EmployeeID INT PRIMARY KEY,

    Email VARCHAR(255) UNIQUE

);

Primary keys ensure row identity, while unique keys enforce constraints.

 

11) What are the different isolation levels in SQL?

Isolation levels define the degree of concurrency control in transactions to prevent issues like dirty reads, non-repeatable reads, and phantom reads. SQL provides five main isolation levels: 

Read Uncommitted – Transactions can read uncommitted changes from other transactions. This improves performance but risks dirty reads.

Read Committed – A transaction reads only committed data, avoiding dirty reads but allowing non-repeatable reads.

Repeatable Read – Prevents dirty and non-repeatable reads by ensuring rows read by a transaction cannot be modified by others until the transaction is complete.

Serializable – The highest level of isolation. It locks the entire range of rows, preventing all anomalies but significantly reducing concurrency.

Snapshot Isolation – Uses versioning instead of locks to ensure consistent reads without blocking concurrent operations.

The choice of isolation level depends on balancing data consistency and system performance.

 

12) How does a clustered index work, and how is it different from a non-clustered index?

A clustered index determines the physical order of rows in a table. Since table rows are stored in this order, there can only be one clustered index per table.

How It Works:

The leaf nodes of the B-tree structure store actual data rows (not pointers).

Improves range-based searches and sorting operations.

When inserting, updating, or deleting, SQL Server may need to rearrange rows, which can lead to fragmentation.

A non-clustered index, on the other hand:

Stores index keys with pointers to the actual data.

Allows multiple indexes per table.

Faster for lookups when searching for specific values but less efficient for range queries.

Using a combination of both index types optimizes performance for different types of queries.

 

13) What are SQL Server Reporting Services (SSRS)?

SQL Server Reporting Services (SSRS) is a server-based business intelligence tool that enables organizations to:

Generate, manage, and schedule interactive and paginated reports.

Deliver reports in multiple formats (Excel, PDF, HTML, Word).

Use data visualization tools like charts, graphs, and KPIs.

Provide role-based security and subscription-based delivery.

Integrate with SQL Server Analysis Services (SSAS) for multidimensional reporting.

SSRS is widely used in enterprise reporting, financial analysis, and BI dashboards.

 

14) What are Common Table Expressions (CTEs)?

A Common Table Expression (CTE) is a temporary named result set that simplifies complex queries by:

Improving query readability.

Supporting recursive queries.

Allowing modular query building.

Example:

WITH EmployeeCTE AS (

   SELECT EmployeeID, Name, Department, Salary 

   FROM Employees 

   WHERE Salary > 70000

)

SELECT * FROM EmployeeCTE;

CTEs provide an alternative to subqueries and derived tables, making queries easier to debug and maintain.

 

15) Explain the MERGE statement in SQL.

The MERGE statement performs INSERT, UPDATE, or DELETE operations in a single command, often used in data warehousing and synchronization scenarios.

Example: Synchronizing Employee Records

MERGE INTO Employees AS Target  

USING NewEmployees AS Source  

ON Target.EmployeeID = Source.EmployeeID  

WHEN MATCHED THEN  

   UPDATE SET Target.Salary = Source.Salary  

WHEN NOT MATCHED THEN  

   INSERT (EmployeeID, Name, Salary) VALUES (Source.EmployeeID, Source.Name, Source.Salary)

WHEN NOT MATCHED BY SOURCE THEN  

   DELETE;

The MERGE statement is efficient for bulk operations but should be used carefully to avoid performance issues due to excessive locking.

 

16) How do you use a window function in SQL?

Window functions perform aggregations over a defined row set without collapsing rows.

Example: Assigning Row Numbers to Employees by Department

SELECT EmployeeID, Name, Department, 

ROW_NUMBER() OVER(PARTITION BY Department ORDER BY Salary DESC) AS Rank

FROM Employees;

Common Window Functions:

RANK() – Assigns ranking with gaps.

DENSE_RANK() – Assigns ranking without gaps.

LEAD() & LAG() – Access next or previous row values.

SUM() OVER() – Calculates running totals.

Window functions are essential for analytical queries without impacting row structure.

 

17) What is a pivot table, and how do you create one in SQL?

A pivot table restructures row-based data into columns, commonly used for reporting and data analysis.

 

Example: Pivoting Sales Data

SELECT * FROM (

   SELECT Department, Year, Revenue FROM Sales

) src  

PIVOT (

   SUM(Revenue) FOR Year IN ([2022], [2023], [2024])

) AS PivotTable;

Pivot tables allow better data representation and are commonly used in financial and sales reporting.

 

18) How do you handle transactions in distributed databases?

Distributed transactions involve multiple databases or servers, requiring a Two-Phase Commit (2PC) protocol for consistency.

2PC Process:

Prepare Phase: Each participating database prepares the transaction and reports readiness.

Commit Phase: The transaction is either committed on all nodes or rolled back entirely.

Tools Used:

Microsoft Distributed Transaction Coordinator (MSDTC)

XA Transactions (Oracle, MySQL)

Distributed transactions introduce latency and failure risks, making eventual consistency models (e.g., CAP theorem, BASE) a preferred alternative in modern distributed systems.

 

19) What is the difference between OLTP and OLAP systems?

OLTP (Online Transaction Processing) – Optimized for fast, transactional queries (e.g., banking, e-commerce).

OLAP (Online Analytical Processing) – Designed for complex queries, reporting, and business intelligence.

Feature OLTP OLAP

Query Type Read/Write Read-heavy

Data Model Normalized Denormalized

Speed Milliseconds Seconds/minutes

Use Case Banking, CRM Reporting, BI

OLAP systems use dimensional modeling (e.g., star schema, snowflake schema) for efficient analysis.

 

20) What is the difference between database sharding and partitioning?

Partitioning – Splitting data within a single database for performance optimization.

Sharding – Splitting data across multiple databases to improve scalability and handle massive workloads.

Feature Partitioning Sharding

Data Scope Single DB Multiple DBs

Performance Query Optimization Scalability

Complexity Moderate High

Use Case OLAP, archiving Global-scale apps (e.g., Facebook, Amazon)

Sharding is essential for distributed systems handling high traffic loads.

 

21) What is a live lock, and how does it differ from a deadlock?

A live lock occurs when transactions keep changing their state but never progress, whereas a deadlock happens when two transactions block each other indefinitely.

Live Lock:

- Transactions continuously retry without making progress.

- Often caused by over-aggressive transaction retries.

- Example: Two processes lowering their priority to allow the other to proceed, but both continue lowering indefinitely.

Deadlock:

- Two transactions wait on resources locked by the other, leading to a permanent block.

- Resolved by deadlock detection mechanisms that terminate one transaction.

Prevention Techniques:

- Use lock timeouts to break live locks.

- Apply deadlock detection algorithms in SQL Server.

- Optimize indexing and query execution to reduce lock contention.

 

22) What is the purpose of the SQL EXCEPT operator?

The EXCEPT operator in SQL returns rows that are present in the first query but not in the second query. It is useful for comparing data differences between tables.

How It Works:

- Returns only distinct rows from the first dataset.

- Eliminates duplicate results automatically.

- Similar to MINUS in Oracle.

Example:

SELECT EmployeeID FROM Employees  

EXCEPT  

SELECT EmployeeID FROM Contractors;

This query retrieves employees who are not in the contractors list.

The EXCEPT operator is handy for data auditing, validation, and debugging in SQL.

 

23) How do you implement dynamic SQL, and what are its advantages and risks?

Dynamic SQL enables the construction of SQL queries at runtime, allowing flexibility in query execution. It is commonly used for search filters, report generation, and metadata-driven applications.

Implementation Methods:

Using EXECUTE:
DECLARE @sqlQuery NVARCHAR(MAX) = 'SELECT * FROM Employees WHERE Department = ''IT''';  

EXEC (@sqlQuery);

Using sp_executesql (Recommended for security & performance):
DECLARE @sqlQuery NVARCHAR(MAX) = 'SELECT * FROM Employees WHERE Department = @Dept';  

EXEC sp_executesql @sqlQuery, N'@Dept NVARCHAR(50)', @Dept = 'IT';

Advantages:

Allows flexible and customizable queries.

Useful in stored procedures for handling variable query structures.

Risks:

SQL injection vulnerability (if parameters are not sanitized).

Performance issues due to lack of query plan reuse.

Using sp_executesql with parameterized queries is the safest approach.

 

24) What is the difference between horizontal and vertical partitioning?

Partitioning improves database performance by dividing large datasets into smaller, more manageable parts.

Horizontal Partitioning (Sharding):

Splits rows into multiple tables based on a key (e.g., region, time).

Improves query performance by distributing load across servers.

Example: Partitioning user data based on geographic location.

Vertical Partitioning:

Divides columns into separate tables based on access patterns.

Used to separate frequently accessed columns from rarely used ones.

Example: Keeping personal details in one table and financial data in another.

Horizontal partitioning is preferred for scalability, while vertical partitioning is useful for schema optimization.

 

25) What is database mirroring, and how does it work?

Database mirroring is a high-availability solution that replicates a database in real time from a primary server (Principal) to a backup server (Mirror).

Modes of Mirroring:

High-safety mode (Synchronous) – Ensures data consistency but may introduce latency.

High-performance mode (Asynchronous) – Improves speed but may lead to data loss if the primary fails.

Automatic failover – Requires a witness server to monitor availability and switch roles if necessary.

Benefits:

Provides real-time data redundancy for disaster recovery.

Ensures minimal downtime during failures.

Improves read scalability by using mirrored databases for reporting.

Database mirroring is commonly used in mission-critical applications where high availability is essential.

 

26) What is the purpose of table partitioning?

Table partitioning helps in managing large datasets by dividing a table into smaller, more efficient chunks.

Types of Partitioning:

Range Partitioning: Divides data based on a column range (e.g., ORDER_DATE).

List Partitioning: Groups data based on predefined values (e.g., product categories).

Hash Partitioning: Uses a hash function to distribute data evenly.

Advantages:

Improves query performance by reducing scan times.

Enhances data management for archiving and backups.

Optimizes index maintenance for large datasets.

Partitioning is crucial for big data environments and data warehouses.

 

27) What is the purpose of the EXPLAIN PLAN in SQL?

The EXPLAIN PLAN statement helps analyze and optimize SQL queries by displaying the execution path chosen by the database engine.

Key Insights from EXPLAIN PLAN:

Index usage – Determines if queries are leveraging indexes efficiently.

Join methods – Identifies nested loops, hash joins, or merge joins used in execution.

Cost estimation – Helps in query performance tuning.

Example:

EXPLAIN ANALYZE SELECT * FROM Orders WHERE CustomerID = 1001;

Using EXPLAIN PLAN allows developers to optimize queries and indexes for better database performance.

 

28) What is the purpose of the WITH (NOLOCK) hint in SQL Server?

The WITH (NOLOCK) hint is used to allow reading uncommitted data (dirty reads) from a table. It helps reduce blocking in high-concurrency environments but comes with potential risks.

How It Works:

Bypasses shared locks and does not wait for transactions to commit.

Allows queries to read uncommitted modifications (dirty reads).

Used in reporting queries where real-time accuracy is not critical.

Example:

SELECT * FROM Orders WITH (NOLOCK);

 

Pros:

Improves query performance by reducing blocking.

Useful for read-heavy applications where minor inconsistencies are acceptable.

Cons:

Risk of reading stale or incorrect data due to in-flight transactions.

Can lead to phantom reads and non-repeatable reads.

WITH (NOLOCK) should be used cautiously—only when slight data inconsistencies are acceptable.

 

29) How do you handle transactions in distributed databases?

Managing transactions in distributed databases requires ensuring consistency, reliability, and fault tolerance across multiple nodes.

Approaches to Distributed Transactions:

Two-Phase Commit (2PC):

Ensures all participants in a transaction either commit or rollback.

Uses a coordinator to manage commit requests.

Example: Financial transactions across multiple banks.

Eventual Consistency Models (BASE):

Used in NoSQL and cloud-based databases.

Accepts temporary inconsistencies but ensures data convergence over time.

Example: Amazon DynamoDB, Apache Cassandra.

Saga Pattern:

Breaks a transaction into a sequence of smaller transactions.

If one step fails, compensating transactions rollback changes.

Common in microservices architectures.

Challenges:

Network latency and distributed failures.

Maintaining ACID properties across multiple databases.

Ensuring data consistency in a globally distributed system.

Distributed transactions require careful design to balance performance, consistency, and availability.

 

30) What is the difference between database sharding and partitioning?

Both sharding and partitioning help manage large datasets, but they serve different purposes.

Database Partitioning:

Divides a single database into logical sections (partitions) within the same server.

Improves query performance by limiting data scans.

Example: Splitting a sales table by year (range partitioning).

Database Sharding:

Distributes data across multiple servers (shards) to improve scalability.

Each shard operates as an independent database.

Example: Splitting user data across multiple servers based on region.

Key Differences:

- Feature

- Scope

- Goal

- Complexity

- Use Case

Sharding

- Across multiple databases

- Scalability & distribution

- More complex with routing logic

- High-traffic applications like social media

Partitioning

- Within one database

- Performance optimization

- Easier to implement

- Data warehousing

Sharding is ideal for global-scale applications, while partitioning is best for database performance optimization.

Scenario-Based SQL Interview Questions

1) Scenario: Fetch the third-highest salary without using LIMIT

Solution:

SELECT Salary FROM Employees e1 

WHERE 2 = (SELECT COUNT(DISTINCT Salary) FROM Employees e2 WHERE e2.Salary > e1.Salary);

This counts distinct salaries and fetches the third highest.

 

 

2) Scenario: Retrieve Continuous Absent Employee Records

Problem: 

Track employees who were absent for three or more consecutive days.

SELECT EmployeeID, AbsentDate  

FROM (  

    SELECT EmployeeID, AbsentDate,  

    LEAD(AbsentDate, 1) OVER (PARTITION BY EmployeeID ORDER BY AbsentDate) AS NextDay,  

    LEAD(AbsentDate, 2) OVER (PARTITION BY EmployeeID ORDER BY AbsentDate) AS Next2Day  

    FROM Attendance  

) temp  

WHERE NextDay = AbsentDate + INTERVAL 1 DAY  

AND Next2Day = AbsentDate + INTERVAL 2 DAY;

Uses LEAD() for consecutive date checks.

 

3) Scenario: Find Customers Who Made Consecutive Purchases

SELECT CustomerID, PurchaseDate  

FROM (  

    SELECT CustomerID, PurchaseDate,  

    LAG(PurchaseDate) OVER (PARTITION BY CustomerID ORDER BY PurchaseDate) AS PrevDate  

    FROM Orders  

) t  

WHERE DATEDIFF(PurchaseDate, PrevDate) = 1;

Detects customers making purchases on consecutive days.

 

4) Scenario: Find Employees Without a Manager

SELECT Name FROM Employees WHERE ManagerID IS NULL;

Useful for identifying top-level executives.

 

5) Scenario: Get Product Sales in the Last 3 Months

SELECT ProductName, SUM(Sales)  

FROM Orders  

WHERE OrderDate >= DATEADD(MONTH, -3, GETDATE())  

GROUP BY ProductName;

Helps analyze recent sales trends.

The Bottom Line

A thorough understanding of these SQL concepts demonstrates a commitment to data proficiency. We trust this resource will empower you to approach your 2025 SQL interviews with confidence and expertise.

There are a lot of SQL Interview Questions apart from this, which the candidates must practice qualifying for the interview.

Deepen your Full Stack skills. Explore our Full Stack Developer Master Program and learn how to build your career. For more information on SQL training and certification, please contact us at Click Here or chat with our course expert. If you’re looking for other training and certifications, you can explore all courses offered by us and request a trial. You can also subscribe to our newsletters to gain insights.