Top 100 SQL Interview Questions and Answers for Every Skill Level

Q: What are the key topics I should focus on for advanced SQL interview questions?

For advanced roles, master query performance tuning, indexing strategies, window functions, recursive queries, CTEs, stored procedures, and concurrency control. Be prepared to explain execution plans, manage large-scale databases, and handle transactional integrity.

Q: What are the best tools or platforms for practicing SQL interview questions and answers?

Recommended tools include: dbForge Edge for advanced query-building and debugging, LeetCode & HackerRank for SQL coding challenges, Mode Analytics for SQL in a data analysis setting, and PostgreSQL/MySQL Workbench for hands-on database management and query testing.

Q: How can I effectively explain my approach to solving SQL problems during an interview?

Think out loud. Explain your logic step by step: Clarify requirements and edge cases, describe how you’d structure the query, justify your approach by mentioning efficiency, indexing, or normalization, and suggest optimizations to improve performance if needed.

Q: Are there any real-world SQL scenarios I should study to prepare for interviews?

Yes! Study cases like: E-commerce (querying order histories, customer segmentation, inventory tracking), Finance (fraud detection, risk analysis, transactional integrity), Healthcare (managing patient records, ensuring compliance with data regulations), and Social Media (ranking posts, tracking user engagement, analyzing trends). For best results, practice with real datasets and analyze case studies from your industry of interest.

By Dereck Mushingairi

March 31, 2025

0

45

Imagine spending weeks grinding through SQL tutorials, practicing syntax, and refining your queries in your spare time. But when the interviewer throws a complex JOIN + subquery at you, your brain suddenly hits a 404.

It happens to the best of us.

However, most candidates crash and burn—not because they lack knowledge, but because they prepare like students instead of engineers. They memorize syntax, pray for predictable questions, and freeze the moment they’re asked to think, not recite.

The real-world SQL problems—the ones that decide who gets hired—aren’t in your tutorial exercises. Hiring managers don’t want walking documentation. They want problem solvers. People who can break down queries under pressure, optimize sluggish reports, and design databases that don’t break under heavy demand.

This guide brings you interview-worthy SQL challenges for every level—freshers, intermediate, and advanced users. And to truly prepare, it’s also essential to utilize every resource available. Tools like dbForge Edge can help to sharpen your SQL skills. Let’s dive in!

Basic SQL interview questions for freshers

SQL is the backbone of modern data, and with 72% of developers using SQL daily, hiring managers expect candidates to go beyond syntax. This section provides the top SQL interview questions every fresher must master, from writing efficient queries to structuring data for real-world applications. Let’s get started.

1. What is SQL, and why is it essential for data management?

SQL is the industry-standard language for managing and querying relational databases. It enables businesses to store, retrieve, and analyze structured data quickly and precisely, forming the backbone of data-driven decision-making. From tracking financial transactions in banking to managing customer data in e-commerce, SQL ensures that organizations can efficiently process and extract insights from vast datasets.

Beyond its core functionality, SQL stands out for several key advantages:

Universal compatibility – SQL works across databases like MySQL, PostgreSQL, and SQL Server, making it a go-to tool for developers and analysts, no matter the platform.
Declarative and efficient – Instead of writing complex procedures, users simply specify what data they need, and SQL determines how to retrieve it.
Efficient scalability – SQL handles millions of transactions in banking, billions of records in social media, and real-time analytics in AI-driven financial models.

2. What role does SQL play in managing and querying databases?

SQL is a structured language used to manage, access, and manipulate data in relational databases. It plays two key roles:

Database management – Allows users to define schemas, enforce data integrity, and control user permissions.
Data retrieval & manipulation – SQL enables querying large datasets efficiently, applying filters, and performing aggregations.

Core SQL operations

Operation	SQL command	Example
Creating databases	CREATE DATABASE	CREATE DATABASE company_db;
Defining tables	CREATE TABLE	CREATE TABLE Employees (ID INT, Name VARCHAR(50));
Modifying tables	ALTER TABLE	ALTER TABLE Employees ADD COLUMN Salary DECIMAL(10, 2);
Updating records	UPDATE	UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 1;
Deleting entries	DELETE	DELETE FROM Employees WHERE EmployeeID = 5;

What are the use cases of these operations? With SQL:

Online stores can track orders
Banks can verify transactions
Streaming platforms can suggest your favorite shows

SQL powers the data-driven experiences we use daily, making it one of the most critical skills in tech today.

3. What are the different types of SQL commands?

SQL commands are grouped into five categories, each serving a distinct purpose.

Categories of SQL commands

Category	Purpose	Example commands
DDL (Data Definition Language)	Defines the structure of a database.	CREATE TABLE Employees (ID INT, Name VARCHAR(50)); ALTER TABLE Employees ADD COLUMN Salary DECIMAL(10, 2); DROP TABLE Employees;
DML (Data Manipulation Language)	Handles data within tables.	INSERT INTO Employees (ID, Name) VALUES (1, ‘Alice’); UPDATE Employees SET Salary = 55000 WHERE ID = 1; DELETE FROM Employees WHERE ID = 1;
DCL (Data Control Language)	Manages user permissions.	GRANT SELECT ON Employees TO User1; REVOKE DELETE ON Employees FROM User1;
TCL (Transaction Control Language)	Ensures data consistency.	BEGIN TRANSACTION; ROLLBACK; (Reverts changes if something goes wrong) COMMIT; (Finalizes changes)
DQL (Data Query Language)	Fetches data.	SELECT * FROM Employees WHERE Department = ‘Sales’;

Understanding these command types is essential for maintaining an efficient, secure, and well-structured database.

4. What are primary keys, foreign keys, and unique keys? How do they ensure data integrity?

SQL databases maintain accuracy and consistency through keys, which define table relationships.

Database key types and their functions

Key type	Purpose	Example
Primary key	Uniquely identifies each row in a table; does not allow NULL values; a table can have one primary key only.	EmployeeID in the Employees table
Foreign key	Establishes a relationship between tables by referencing a primary key (or unique key) in another table; can allow NULL values for optional relationships.	CustomerID in Orders referencing CustomerID in Customers
Unique key	Ensures column values are unique but allows NULL values; a table can have multiple unique keys.	Email in a Users table

Why do these keys matter?

Ensure entity integrity (e.g., no two employees have the same EmployeeID).
Ensure referential integrity (e.g., an order must be linked to a valid customer).
Ensure data uniqueness for efficient lookups and indexing, improving performance.

5. What are common SQL data types, and how are they used?

SQL databases categorize data into distinct types for efficient storage and retrieval.

Common data types in databases

Data type	Purpose	Example
INT	Stores whole numbers	Age INT;
VARCHAR(n)	Stores variable-length text	Name VARCHAR(50);
DATE	Stores dates	BirthDate DATE;
DECIMAL(p,s)	Stores precise decimal numbers	Price DECIMAL(10,2);
BOOLEAN	Stores TRUE/FALSE values	IsActive BOOLEAN;

Choosing the right data type minimizes storage space and improves query efficiency.

6. Can you provide simple SQL queries using SELECT, WHERE, and ORDER BY?

Below are some fundamental SQL queries every user should know.

-- Retrieve all records from the Employees table 
SELECT * FROM Employees; 
 
-- Fetch specific columns 
SELECT FirstName, LastName FROM Employees; 
 
-- Filter results using WHERE 
SELECT FirstName, LastName FROM Employees WHERE Department = 'Finance'; 
 
-- Order results by Salary in descending order 
SELECT FirstName, LastName, Salary FROM Employees ORDER BY Salary DESC;

Key takeaways:

SELECT fetches data from tables.
WHERE applies filters to narrow down results.
ORDER BY sorts data in ascending (ASC) or descending (DESC) order.

Mastering these statements allows freshers to write more complex SQL queries with confidence.

7. What is the difference between SQL and MySQL?

Think of SQL as a universal language, like English, and MySQL as one of the many dialects that speak it.

SQL is the standardized query language (not a programming language) to query and manipulate data across databases.
MySQL is an RDBMS (Relational Database Management System) that implements SQL to store, retrieve, and manage structured data.
Other database systems like PostgreSQL, SQL Server, and Oracle also use SQL, but with slight variations in syntax and features.

8. What is a SQL table, and how is it structured?

A table is a structured collection of related data stored in rows and columns. Each row represents a record, and each column represents a field with a defined data type. Below is an example.

CREATE TABLE Employees ( 
    EmployeeID INT PRIMARY KEY, 
    Name VARCHAR(50), 
    Department VARCHAR(50), 
    Salary DECIMAL(10,2) 
);

9. What is the difference between CHAR and VARCHAR?

CHAR and VARCHAR are both used to store text, but they differ in storage and performance. The table below outlines their key differences.

CHAR vs. VARCHAR

Feature	CHAR	VARCHAR
Storage	Fixed-length	Variable-length
Performance	Faster for fixed-size data	More efficient for variable-size text
Example	CHAR(10) always stores 10 characters	VARCHAR(10) stores up to 10 characters

Use CHAR when all values have the same length, and VARCHAR for flexible-length text fields.

10. How do you retrieve all records from a table?

To retrieve every record from a table, use SELECT *:

SELECT * FROM Employees;

Best practice: Avoid SELECT * in production queries—it retrieves unnecessary data, slowing performance. Instead, only select the columns you need.

SELECT Name, Salary FROM Employees;

11. How do you retrieve specific columns from a table?

List the required column names in the SELECT statement to fetch only specific columns instead of all data.

SELECT Name, Department FROM Employees;

This fetches only Name and Department columns.

12. What is the difference between WHERE and HAVING?

WHERE is used to filter rows before aggregation while HAVING is used to filter results after aggregation (GROUP BY). Note that WHERE cannot reference aggregate functions (e.g., COUNT), but HAVING can. Below is an example to illustrate the difference.

SELECT Department, COUNT(*) AS EmployeeCount FROM Employees WHERE Salary > 50000 -- Filters individual rows before aggregation GROUP BY Department HAVING COUNT(*) > 10; -- Filters aggregated results

13. How do you use the DISTINCT keyword?

The DISTINCT keyword removes duplicate values from a column, returning unique records.

SELECT DISTINCT Department FROM Employees;

This returns unique departments, eliminating duplicates.

14. What is the purpose of the LIMIT clause?

Ever scrolled through Google search results and noticed only the first 10 results show up? That’s LIMIT in action—it restricts query results to improve performance.

SELECT * FROM Employees LIMIT 5;

Pro tip: SQL Server doesn’t use LIMIT; instead, it uses:

SELECT TOP 5 * FROM Employees;

15. What is an alias in SQL?

An alias temporarily renames a column or table to improve readability in query results.

SELECT Name AS Employee_Name FROM Employees;

Here, Name is displayed as Employee_Name.

16. How do you sort query results?

To sort records, use the ORDER BY clause, which arranges results in ascending (ASC) or descending (DESC) order.

SELECT Name, Salary FROM Employees ORDER BY Salary DESC;

This sorts results by salary in descending order.

17. What are SQL aggregate functions?

SQL aggregate functions perform calculations on multiple rows:

SUM() – Adds up values
AVG() – Calculates the average
COUNT() – Counts rows
MIN() & MAX() – Finds the lowest and highest values

Example: using AVG()in a query

SELECT AVG(Salary) FROM Employees;

18. What is the difference between COUNT(*) and COUNT(column_name)?

COUNT(*) counts all rows (including NULL), while COUNT(column_name) counts only non-null values. Here is an example.

SELECT COUNT(*) FROM Employees; -- Counts all employees 
SELECT COUNT(Department) FROM Employees; -- Counts employees with a department

19. What is NULL in SQL?

NULL represents a missing or unknown value in a database. It is different from zero or an empty string. To find records with NULL values, use IS NULL.

SELECT * FROM Employees WHERE Salary IS NULL;

20. How do you replace NULL values with a default value?

Use the COALESCE() function to replace NULL values with a default value.

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

This replaces NULL salaries with 0.

21. What is a DEFAULT constraint?

A DEFAULT constraint assigns a predefined value to a column when no value is provided during insertion.

CREATE TABLE Employees ( 
    ID INT, 
    Name VARCHAR(50), 
    Salary DECIMAL(10,2) DEFAULT 50000 
);

If a salary isn’t provided, 50000 is assigned.

22. How do you count unique values in a column?

Use the COUNT(DISTINCT column_name) function to count unique values in a column. This ensures that only distinct values are counted, eliminating duplicates.

SELECT COUNT(DISTINCT Department) FROM Employees;

This counts unique departments.

23. What is a CHECK constraint?

A CHECK constraint enforces a condition on a column, ensuring that only valid values are inserted. It helps maintain data integrity by restricting values based on predefined rules at the row level.

CREATE TABLE Employees ( 
    ID INT, 
    Salary DECIMAL(10,2) CHECK (Salary >= 30000) 
);

This prevents inserting salaries below 30,000.

Limitations of CHECK constraints

A CHECK constraint only validates individual rows and cannot enforce conditions that involve multiple rows. For example, it cannot be used to ensure that no two employees exceed a department’s headcount limit. To enforce such multi-row validations, use triggers, unique constraints, or stored procedures instead.

24. How do you retrieve the second highest salary?

Use ORDER BY, LIMIT, and OFFSET to retrieve the second highest salary. The OFFSET keyword skips rows, allowing retrieval of a specific ranking.

SELECT Salary FROM Employees ORDER BY Salary DESC LIMIT 1 OFFSET 1;

This skips the highest salary and retrieves the second highest.

25. How do you find duplicate records in a table?

Use GROUP BY and HAVING:

SELECT Name, COUNT(*) FROM Employees GROUP BY Name HAVING COUNT(*) > 1;

This provides lists of duplicate names.

26. What is a case statement in SQL?

CASE is used for conditional logic:

SELECT Name,  
    CASE  
        WHEN Salary > 50000 THEN 'High Salary'  
        ELSE 'Low Salary'  
    END AS SalaryCategory 
FROM Employees;

27. How do you remove duplicate rows in SQL?

To remove duplicate rows from a table while keeping only one occurrence, use the ROW_NUMBER() function (for SQL Server, PostgreSQL, MySQL 8+).

WITH CTE AS ( 
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Name, Department, Salary ORDER BY EmployeeID) AS rn 
    FROM Employees 
) 
DELETE FROM Employees WHERE EmployeeID IN (SELECT EmployeeID FROM CTE WHERE rn > 1);

For older MySQL versions, use a self-join.

DELETE e1 FROM Employees e1 
JOIN Employees e2 
ON e1.Name = e2.Name AND e1.Department = e2.Department AND e1.Salary = e2.Salary 
WHERE e1.EmployeeID > e2.EmployeeID;

Create a temporary table with distinct values if your SQL dialect lacks window functions.

CREATE TABLE Employees_Temp AS SELECT DISTINCT * FROM Employees; 
DROP TABLE Employees; 
ALTER TABLE Employees_Temp RENAME TO Employees;

Key takeaways:

DISTINCT only filters query results but does not remove stored duplicates.
Use ROW_NUMBER() for modern SQL, JOIN for older MySQL, and temporary tables as a last resort.
Always test with SELECT before running DELETE.

No matter your role—developer, analyst, or data engineer—learning these SQL basics for interviews and real-world use is essential.

Intermediate SQL interview questions

At this level, you need to know how to make data work efficiently at scale. Imagine running an e-commerce platform where thousands of transactions happen every second. Without optimized queries, indexing strategies, and performance tuning, reports take minutes instead of milliseconds, and users abandon slow-loading applications.

Companies hiring for data engineering, backend development, and database administration expect you to go beyond basic CRUD operations. You must think critically, structure queries for maximum efficiency, and enforce data integrity without sacrificing performance.

These intermediate SQL interview questions will test your ability to handle real-world database challenges—where performance, scalability, and security all matter.

28. What are SQL Joins, and why are they important?

Joins allow data retrieval from multiple related tables, a core principle of relational databases. Instead of duplicating data across tables, joins let you structure databases efficiently while enabling complex queries to extract meaningful relationships.

29. How does an INNER JOIN work? When should you use it?

An INNER JOIN retrieves only matching rows from both tables, ensuring you only work with related data. It’s the most common type of join, used in:

HR systems: Fetching employees and their assigned departments.
E-commerce databases: Linking orders to customer details.
Financial transactions: Combining account data with transaction logs.

However, INNER JOINs can degrade performance on large datasets if proper indexing is not applied. To optimize:

Ensure indexes exist on both join columns (e.g., Employees.DepartmentID and Departments.DepartmentID).
Prefer HASH JOIN or MERGE JOIN over NESTED LOOPS in large datasets.
Use EXISTS instead of JOIN when checking for existence only (not retrieving data).

Example query

-- Faster for existence check:  
SELECT 1 FROM Employees e WHERE EXISTS (SELECT 1 FROM Departments d WHERE d.DepartmentID = e.DepartmentID);

30. How is a LEFT JOIN different?

A LEFT JOIN returns all records from the left table, even if there’s no match in the right table. If no match exists, NULL values are inserted for right-table columns. It is useful when fetching optional relationships, like listing all employees, even if some don’t belong to a department.

31. When should you use a FULL JOIN?

A FULL OUTER JOIN returns all rows from both tables, filling in missing values with NULL. It is useful for data reconciliation, such as merging historical data from two sources. However, avoid FULL OUTER JOIN on large datasets—it forces a full scan of both tables. Instead, use UNION ALL with LEFT and RIGHT JOINs for better performance.

-- Alternative to FULL OUTER JOIN 
SELECT * FROM Employees e LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID 
UNION ALL 
SELECT * FROM Employees e RIGHT JOIN Departments d ON e.DepartmentID = d.Department

32. Why is normalization critical in real-world databases?

Without normalization, databases become inefficient, inconsistent, and hard to maintain. Imagine a customer database where the same user appears in multiple tables with slightly different email addresses—updating their information becomes a nightmare!

Normalization solves this problem by:

Eliminating redundant data, ensuring updates happen in one place.
Preventing inconsistencies, so customer details are always accurate.
Enhancing storage efficiency, reducing database size.

Example: without normalization (bad design)

Customers (CustomerID, Name, Email, OrderHistory, Address)

Example: with normalization (proper design)

Customers (CustomerID, Name, Email)   
Orders (OrderID, CustomerID, OrderDate)   
Addresses (AddressID, CustomerID, Address)

33. Can you explain the first three normal forms (1NF, 2NF, 3NF)?

The first three normal forms (1NF, 2NF, and 3NF) are fundamental rules for structuring relational databases to minimize redundancy and ensure data integrity. Here’s how each one works:

1NF (First Normal Form): No duplicate rows or repeating groups within a column.
2NF (Second Normal Form): No partial dependencies—each column must depend on the entire primary key, not just part of it.
3NF (Third Normal Form): No transitive dependencies—non-key columns shouldn’t rely on other non-key columns.

Each step removes redundancy and strengthens data relationships.

34. What is Boyce-Codd Normal Form (BCNF), and when is it necessary?

BCNF is a stricter version of 3NF. It ensures that every determinant is a candidate key. It is required when functional dependencies create data integrity risks, even after achieving 3NF.

35. How do indexes improve query speed?

Think of an index as the table of contents in a book—it lets you find information instantly instead of flipping through every page. Without indexes, databases must scan every row, making queries painfully slow.

Use indexes in the following cases:

Search queries (WHERE email = ‘[email protected]‘).
Sorting operations (ORDER BY Salary DESC).
JOIN conditions (ON EmployeeID = Department.EmployeeID).

Pro Tip: Use SQL Server tools like execution plan analyzers and index advisors to identify inefficient queries and optimize indexing for better performance.

36. What are the different types of indexes?

Different types of indexes serve different purposes, balancing performance and storage efficiency. Below is an overview of the main index types.

Index Type	Description
Clustered index	Determines physical order of rows, speeds up retrieval but affects insert performance.
Non-clustered Index	Separate structure that points to actual data, allowing multiple indexes per table.
Unique index	Ensures no duplicate values in a column.
Full-text index	Optimized for text search queries (e.g., searching in large documents).

37. When should you avoid indexing?

Avoid indexing in these cases:

High-churn tables (frequent INSERT/UPDATE/DELETE operations): Indexes slow down modifications because they must be updated with every change. If necessary, use filtered indexes to minimize impact:

CREATE INDEX idx_active_users ON Users (LastLogin) WHERE IsActive = 1;

Small tables: Indexing offers little benefit since full table scans are already fast. However, if a small table is frequently used in JOIN queries, an index might still be useful.
Columns with low uniqueness: Indexing is ineffective for columns with very few distinct values, such as boolean flags (is_active) or status fields with limited options (pending/completed), because the database still scans most of the table.

38. What’s the difference between a PRIMARY KEY and a UNIQUE constraint?

Here is how the compare and contrast:

PRIMARY KEY: Uniquely identifies rows and cannot be NULL. Each table can have only one primary key.
UNIQUE: Also prevents duplicates but allows NULL values (except in some RDBMSs). A table can have multiple UNIQUE constraints.

39. How do FOREIGN KEYS enforce referential integrity?

A FOREIGN KEY ensures that values in one table must exist in another, preventing orphaned records. It maintains data consistency across related tables.

CREATE TABLE Orders ( 
  OrderID INT PRIMARY KEY, 
  CustomerID INT, 
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) 
);

This prevents orders from being assigned to nonexistent customers.

40. What is a CHECK constraint?

A CHECK constraint enforces custom conditions on data entry.

CREATE TABLE Employees ( 
    ID INT, 
    Salary DECIMAL(10,2) CHECK (Salary >= 30000) 
);

Here, salaries cannot be below 30,000, enforcing business rules at the database level.

41. DELETE vs. TRUNCATE vs. DROP – Which One Should You Use?

Choosing the wrong command can be costly! Here’s a quick decision guide:

Command	Effect	Rollback possible?	Best use case
DELETE	Removes specific rows	✅ Yes	When deleting only some records
TRUNCATE	Removes all rows instantly; resets auto-increment counters for SQL Server RDBMS	❌ No	Resetting a table before fresh imports
DROP	Deletes the table & structure	❌ No	Permanently removing a table

Performance tip:

DELETE: Logs each row deletion, supports ROLLBACK, but slower on large tables.
TRUNCATE: Deallocates pages, but still logs the action. Cannot be used if the table has FOREIGN KEYS.
DROP: Removes the table structure, making recovery impossible.

42. What is a subquery, and how does it differ from a JOIN?

A subquery is a query inside another query that returns intermediate results. Unlike JOINs, which combine multiple tables, subqueries provide calculated or filtered results for further processing.

Example: find employees earning above the company average

SELECT Name, Salary  
FROM Employees  
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

43. What are the pros and cons of using correlated subqueries?

Correlated subqueries run once per row in the outer query, allowing row-wise comparisons but impacting performance. Here’s a closer look at the pros and cons.

Pros	Cons
Allows row-by-row comparison	Can be slow for large datasets
Enables dependent filtering	Often replaced with JOINs for efficiency

44. What are SQL transaction isolation levels?

SQL isolation levels define how transactions interact, balancing consistency and concurrency.

Isolation level	Effect
READ UNCOMMITTED	Allows dirty reads (uncommitted changes visible).
READ COMMITTED	Only sees committed data, preventing dirty reads.
REPEATABLE READ	Prevents changes to rows that are being read.
SERIALIZABLE	Fully isolates transactions, ensuring absolute consistency but reducing concurrency.

Higher isolation levels improve data integrity but can slow performance due to increased locking.

45. What is an execution plan, and why is it important?

An execution plan is the strategy SQL Server (or another RDBMS) uses to execute a query efficiently. It reveals how indexes, joins, and scans are used, helping identify performance bottlenecks. Here’s how to analyze a query’s execution.

EXPLAIN ANALYZE SELECT * FROM Orders WHERE CustomerID = 101;

Key benefits:

Identifies full table scans (which slow queries).
Shows which indexes are used.
Helps rewrite queries for better efficiency.

46. What is query optimization, and why does it matter?

Query optimization is rewriting SQL queries to improve efficiency and reduce execution time.

Best practices:

Use indexes – Ensure indexed columns are used in queries.
**Avoid SELECT *** – Fetch only the required columns.
Filter early – Apply WHERE conditions before joins.
Use EXISTS instead of IN for subqueries.

47. How do you identify and resolve slow queries?

To diagnose slow queries:

Use EXPLAIN ANALYZE – Check execution plans for inefficiencies.
Monitor index usage – A missing index can slow down lookups.
Refactor subqueries – Convert them into efficient JOINs where possible.
Partition large tables – Reduces scan times on large datasets.
Monitor database locks – Long-running queries can block others.

Optimizing slow queries can improve database performance exponentially.

48. What’s the difference between views and materialized views?

Views and materialized views both simplify data access, but they differ in storage and performance. The table below provides a comparison of their key differences.

Feature	Views	Materialized views
Storage	No physical storage	Stores precomputed results
Performance	Slower (runs on-demand)	Faster (cached data)
Use case	Dynamic, real-time data retrieval	Frequently queried data, optimized performance
Refresh Mechanism	Always current, no refresh needed	Require manual or scheduled refreshes (depending on the RDBMS)

49. What is a covering index, and why is it useful?

A covering index contains all columns used in a query, eliminating the need for extra table lookups and speeding up performance. Below is an example for creating a covering Index on the Orders table.

CREATE INDEX idx_cover ON Orders (CustomerID, OrderDate, TotalAmount);

Now, this query runs faster.

SELECT CustomerID, OrderDate, TotalAmount FROM Orders WHERE CustomerID = 101;

The database engine retrieves everything from the index alone, skipping table scans.

50. What is database partitioning, and when should it be used?

Partitioning splits large tables into smaller, manageable segments, reducing query scan time.

Example: partitioning sales data by year

PARTITION BY RANGE (OrderDate) 
( 
    PARTITION p1 VALUES LESS THAN ('2022-01-01'), 
    PARTITION p2 VALUES LESS THAN ('2023-01-01') 
);

Use cases:

Massive datasets (millions of rows).
Faster queries (reduces scanned data).
Distributes data efficiently across storage.

51. What is the difference between pessimistic and optimistic Locking?

Pessimistic and optimistic locking handle data concurrency differently, balancing conflict prevention and performance. The table below shows how they compare.

Locking type	How it works	Best use case
Pessimistic locking	Locks a row before updating, preventing conflicts	High-contention databases
Optimistic locking	Allows multiple users to read, checks for conflicts at update	Web applications, distributed systems

52. What is deadlock, and how do you prevent it?

A deadlock occurs when two transactions block each other, waiting indefinitely.

Here are the prevention strategies:

Order transactions consistently—always update tables in the same order.
Use shorter transactions—commit frequently to reduce lock time.
Set timeout values (SET LOCK_TIMEOUT).

53. What is snapshot Isolation?

Snapshot Isolation prevents dirty reads by giving each transaction a frozen database snapshot at the start.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

Use case: Prevents dirty reads without locking.

54. What is a Common Table Expression (CTE), and why use it?

A CTE is a temporary result set that makes queries more manageable to read and maintain.

Example: simplifying queries with a CTE

WITH SalesByYear AS (   
    SELECT YEAR(OrderDate) AS Year, SUM(TotalAmount) AS TotalSales   
    FROM Orders   
    GROUP BY YEAR(OrderDate)   
)   
SELECT * FROM SalesByYear WHERE Year = 2023;

Here’s why CTEs are used:

Cleaner than nested subqueries.
Can be referenced multiple times in the same query.

55. What are window functions, and how do they work?

Window functions operate on a subset of rows while preserving individual row details, unlike GROUP BY, which collapses results into a single row per group.

Example: ranking employees by salary

SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank FROM Employees;

Common use cases:

Ranking results dynamically (RANK(), DENSE_RANK(), ROW_NUMBER()).
Calculating moving averages (e.g., AVG(Salary) OVER (PARTITION BY Department ORDER BY HireDate)).
Computing running totals (e.g., SUM(Sales) OVER (ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)).

Performance considerations:

ORDER BY in window functions can be expensive on large datasets.
Missing indexes can slow down execution, especially when partitioning.
PARTITION BY can improve performance by processing smaller row groups instead of scanning the entire table.

Best practice: Use indexes on partitioned columns and limit result sets where possible to optimize performance.

56. What is an indexed view?

An indexed view is a materialized view that stores precomputed query results, improving performance.

Example: Creating an Index on a Materialized View

CREATE UNIQUE CLUSTERED INDEX idx_view ON SalesSummary(CustomerID);

Why use indexed views? Here are the main reasons:

Speeds up complex aggregations.
Improves read performance for repeated queries.

57. How do you optimize bulk inserts in SQL?

Use BULK INSERT instead of INSERT for large data loads.

BULK INSERT Orders   
FROM 'C:\data\orders.csv'   
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

Performance tips:

Use batch commits (e.g., commit every 1000 rows) to speed up insertions.
Disable indexes during bulk loads (rebuild afterward for faster writes).

58. What is a recursive CTE, and how is it used?

A Recursive CTE (Common Table Expression) allows queries to process hierarchical data (e.g., organizational charts, category trees) without needing loops or stored procedures.

Example: fetching an employee hierarchy where each employee reports to a manager

WITH EmployeeHierarchy AS (   
    SELECT EmployeeID, Name, ManagerID, 1 AS Level   
    FROM Employees   
    WHERE ManagerID IS NULL  -- Start with top-level managers   
 
    UNION ALL   
 
    SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1   
    FROM Employees e   
    JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID   
)   
SELECT * FROM EmployeeHierarchy ORDER BY Level;

Why use recursive CTEs? They provide the following:

Elegant way to handle tree structures.
Easier to read and maintain than complex joins.
Ideal for org charts, product categories, etc.

Please note: Recursive CTEs can risk infinite loops if the data has cycles.

59. What are LATERAL JOINs, and why are they useful?

A LATERAL JOIN allows subqueries to reference columns from the outer query, enabling row-wise comparisons.

Example: get the latest order for each customer

SELECT c.CustomerName, o.* 
FROM Customers c   
JOIN LATERAL (   
    SELECT * FROM Orders   
    WHERE Orders.CustomerID = c.CustomerID   
    ORDER BY OrderDate DESC   
    LIMIT 1   
) o ON true;

Why use LATERAL JOINs?

Filters rows dynamically (unlike normal joins).
Improves efficiency in retrieving top-ranked or most recent records.

60. What is the difference between stored procedures and functions?

Stored procedures and functions optimize SQL operations, but they differ in execution, flexibility, and use cases. The table below shows how they compare.

Feature	Stored procedure	Function
Return value	Can return multiple result sets	Must return a single value or table
Use in queries	Cannot be used in SELECT	Can be used in SELECT
Side effects	Can modify database state (INSERT, UPDATE, DELETE)	Cannot modify database state
Performance	Optimized for batch processing	Optimized for computed values

When to use?

Use stored procedures for bulk data updates and complex transactions.
Use Functions for computed columns, aggregations.

61. How does SQL Server handle concurrency?

Concurrency allows multiple users to access and modify data simultaneously without conflicts. SQL Server achieves this through locking, blocking, and isolation levels.

Concurrency control methods:

Pessimistic locking – Transactions lock rows to prevent changes by others.
Optimistic locking – Multiple transactions work on data and check for conflicts at commit.
Row versioning (snapshot Isolation) – Creates a copy of the data to avoid locking.

62. What is a dead tuple in PostgreSQL?

A dead tuple is a stale row version left behind after updates/deletes in PostgreSQL’s MVCC (Multiversion Concurrency Control) system. However, too many dead tuples slow down reads and waste storage.

Here’s how to fix:

VACUUM: Removes dead tuples to free space.
AUTOVACUUM: Automatically maintains performance.

63. What is the purpose of the MERGE statement?

MERGE inserts, updates, or deletes records based on a condition, avoiding multiple INSERT or UPDATE statements.

Example: updating employee salaries, inserting new employees if not found

MERGE INTO Employees AS Target   
USING SalaryUpdates AS Source   
ON Target.EmployeeID = Source.EmployeeID   
WHEN MATCHED THEN   
    UPDATE SET Target.Salary = Source.NewSalary   
WHEN NOT MATCHED THEN   
    INSERT (EmployeeID, Name, Salary)   
    VALUES (Source.EmployeeID, Source.Name, Source.NewSalary);

Reasons for using MERGE:

Reduces query complexity.
Optimized for data warehousing.

64. What is the difference between TRUNCATE and DELETE?

TRUNCATE and DELETE remove data from a table, but they differ in speed, rollback ability, and impact on identity columns. The table below highlights key differences.

Operation	TRUNCATE	DELETE
Removes rows	Yes (all rows)	Yes (specific rows)
Can be rolled back?	No (unless inside a transaction)	Yes
Resets identity column?	Yes (SQL Server); No (MySQL/PostgreSQL unless explicitly reset)	No
Performance	Faster	Slower (row-by-row deletion)

Best practice:

Use TRUNCATE for clearing entire tables quickly.
Use DELETE when you need selective row removal.

65. What is a CROSS APPLY, and how is it different from OUTER APPLY?

CROSS APPLY and OUTER APPLY are used to join table-valued functions or subqueries, but they differ in how they handle unmatched rows. Below is how they compare.

Feature	CROSS APPLY	OUTER APPLY
Behavior	Works like an INNER JOIN (only matching rows)	Works like a LEFT JOIN (includes unmatched rows with NULLs)
Use case	Filters dynamically using subqueries	Retains all left table rows, even if there’s no match

Example: Retrieving the Latest Order with CROSS APPLY

SELECT c.CustomerName, o.* 
FROM Customers c   
CROSS APPLY (   
    SELECT TOP 1 * FROM Orders WHERE Orders.CustomerID = c.CustomerID ORDER BY OrderDate DESC   
) o;

Why use APPLY?

Efficient for returning the latest record per group.
Useful for dynamic filtering inside subqueries.

66. What is the difference between a heap table and a clustered index table?

Heap tables and clustered index tables organize data differently, affecting how quickly it can be retrieved. Below is how they compare.

Table type	Storage method	Best use case
Heap table	Unordered data (no clustered index)	Staging tables, temporary storage
Clustered index table	Rows physically sorted by primary key	High-performance transactional queries

Heap tables are rare—most production databases use clustered indexes for speed.

67. How do you optimize Query Performance in Large Databases?

To speed up queries in large databases, you need efficient indexing, smart query design, and proper resource management. Here are key strategies to improve performance:

Partitioning – Divide large tables into smaller parts.
Indexing – Use covering indexes, filtered indexes, and full-text search.
Query optimization – Avoid SELECT *, use EXPLAIN ANALYZE to inspect execution plans.
Connection pooling – Reduces overhead for frequent queries.
Caching – Store precomputed results when possible.

Pro tip:

Batch process updates instead of modifying millions of rows at once.
Use parallel execution for complex queries.

Advanced SQL interview questions for experienced

At this stage of your SQL career, expertise isn’t just about writing queries—it’s about architecting high-performance databases, handling massive data volumes, and ensuring reliability under stringent requirements. The following advanced SQL questions for interviews will challenge your understanding of everything from recursive queries and window functions to clustering, index optimization, and beyond.

68. How do CTEs, recursive CTEs, and subqueries differ in terms of performance, readability, and execution in SQL?

CTEs, recursive CTEs, and subqueries handle data retrieval differently. Below is a quick comparison.

Feature	CTE	Recursive CTE	Subquery
Execution	Runs once, can be reused	Calls itself iteratively until a stopping condition is met	Runs every time it’s referenced
Performance	More efficient for reuse	Can be slow for deep recursion	Slower if used multiple times in a query
Readability	Improves query structure	Complex but needed for hierarchy	Hard to read when deeply nested
Best for	Complex queries, reusing results, improving maintainability	Hierarchical data like org charts or category trees	One-time calculations or filtering within a query

69. Can you explain SQL window functions and give examples of ROW_NUMBER, RANK, LEAD, and LAG?

Window functions enable complex analytics without collapsing result sets, making them crucial for ranking, running totals, and period-over-period analysis.

ROW_NUMBER(): Assigns a unique row number (useful for pagination).

SELECT  
    EmployeeID,  
    Name,  
    Salary,  
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum 
FROM Employees;

Use case: Fetching top 10 highest-paid employees.

SELECT * FROM ( 
    SELECT EmployeeID, Name, Salary,  
          ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum 
    FROM Employees 
) AS RankedEmployees 
WHERE RowNum <= 10;

RANK(): Assigns ranks with gaps in case of ties (useful for leaderboard rankings).

SELECT EmployeeID, Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank FROM Employees;

How it works:

Employees with the same salary get the same rank.
The next rank number skips places (i.e., Ranks: 1, 2, 2, 4 instead of 1, 2, 2, 3).

DENSE_RANK(): Similar to RANK but without gaps in ranking (ideal for consistent ranking across groups).

SELECT EmployeeID, Name, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank FROM Employees;

Key difference:

Employees with the same salary get the same rank, but ranks remain consecutive (i.e., Ranks: 1, 2, 2, 3 instead of 1, 2, 2, 4).

LEAD()/LAG(): Fetch previous or next row values, useful for trend analysis.

SELECT  
    EmployeeID,  
    Name,  
    Salary,  
    HireDate, 
    LAG(Salary) OVER (ORDER BY HireDate) AS PrevSalary,  
    LEAD(Salary) OVER (ORDER BY HireDate) AS NextSalary 
FROM Employees;

Use case:

LAG() fetches the previous row’s value (compares to earlier records).
LEAD() fetches the next row’s value (compares to upcoming records).

This approach helps in salary trend analysis—seeing how an employee’s salary compares to the previous and next hire’s salary.

70. What are the ACID properties in SQL transactions, and why are they significant?

ACID properties ensure database transactions remain reliable, consistent, and crash-resistant.
For example, in a banking system transaction:

Atomicity: A transfer from account A to account B must either fully complete or roll back if an error occurs.
Consistency: The database must remain valid before and after the transaction.
Isolation: Concurrent transfers shouldn’t interfere (e.g., double deductions).
Durability: Once committed, a transaction remains even if the system crashes.

71. What is the difference between UNION and UNION ALL, and when would you use each?

Both UNION and UNION ALL combine results from multiple queries, but they handle duplicates differently.

UNION removes duplicates, sorting the result set, which may slow performance if deduplication is costly.
UNION ALL keeps all rows, including duplicates, and is usually faster because it skips sorting.

When to use each:

Use UNION when you need unique results.
Use UNION ALL when duplicates are acceptable, and performance is a priority.

Example: UNION vs. UNION ALL in Action

-- UNION (removes duplicates) 
SELECT City FROM Customers 
UNION 
SELECT City FROM Suppliers; 
 
-- UNION ALL (keeps duplicates) 
SELECT City FROM Customers 
UNION ALL 
SELECT City FROM Suppliers;

72. What are triggers, and how can they be utilized effectively?

Triggers are automatically invoked procedures that respond to INSERT, UPDATE, or DELETE events. They’re valuable for auditing changes, enforcing business rules, or maintaining log tables.

Example: creating an AFTER UPDATE trigger for auditing

CREATE TRIGGER trg_AuditOrder 
ON Orders 
AFTER UPDATE 
AS 
BEGIN 
    INSERT INTO OrdersAudit(OrderID, ModifiedDate) 
    SELECT OrderID, GETDATE() 
    FROM inserted; 
END;

This trigger logs order modifications in an audit table.

73. How do you approach query optimization at an advanced level?

Here are the advanced query optimization tips:

Use indexed columns in JOINs to improve execution plans.
Use WHERE conditions early to filter results before applying aggregations.
Use EXISTS instead of COUNT(*) for presence checks.
Avoid SELECT * unless dynamically generating column names.
Use Indexed Views only for read-heavy, aggregation-heavy workloads (e.g., BI dashboards).

Additional Tip: Analyze query performance with EXPLAIN ANALYZE.

-- Checking query plan 
EXPLAIN ANALYZE 
SELECT CustomerName, OrderTotal 
FROM Orders 
WHERE OrderDate > '2023-01-01';

74. How does Snapshot Isolation differ from other Isolation Levels?

Under Snapshot Isolation, each transaction sees data as of the start time, preventing dirty reads without extensive locks. Other isolation levels (e.g., SERIALIZABLE) rely more on locks, which may reduce concurrency but ensure strict data integrity.

Example (SQL Server)

ALTER DATABASE MyDB 
SET ALLOW_SNAPSHOT_ISOLATION ON; 
 
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; 
BEGIN TRAN; 
-- Transaction sees snapshot of data 
COMMIT;

75. What is the MERGE statement, and when is it appropriate to use it?

The MERGE statement combines INSERT, UPDATE, and DELETE into a single operation, making it useful for upsert scenarios, especially in ETL or data warehousing.

MERGE Employees AS Target 
USING EmpUpdates AS Source 
ON Target.EmpID = Source.EmpID 
WHEN MATCHED THEN 
    UPDATE SET Target.Salary = Source.NewSalary 
WHEN NOT MATCHED THEN 
    INSERT (EmpID, Name, Salary) 
    VALUES (Source.EmpID, Source.Name, Source.NewSalary);

This synchronizes the Employees table with new updates in one operation.

Important considerations:

In SQL Server, MERGE can cause deadlocks, race conditions, and unexpected behaviors due to its complex locking mechanism.
In MySQL, use INSERT … ON DUPLICATE KEY UPDATE instead.
In PostgreSQL, use UPSERT (INSERT … ON CONFLICT DO UPDATE) for safer upsert handling.

Best practice: Carefully evaluate whether MERGE is the right choice, especially in high-concurrency environments.

76. How do you handle hierarchical data in SQL, and what are some common pitfalls?

Recursive CTEs and Nested Set Models are common ways to manage hierarchical data in SQL. CTEs make it easy to navigate relationships, while Nested Sets improve query performance. However, challenges include handling deep hierarchies, keeping parent-child relationships consistent, and avoiding performance issues with large datasets. Proper indexing and query optimization can help.

77. What are lateral joins, and how do they differ from standard Joins?

A lateral join allows subqueries in the FROM clause to reference columns from preceding tables, providing per-row computations that traditional joins don’t support.

Example (PostgreSQL)

SELECT c.CustomerName, recent.* 
FROM Customers c 
JOIN LATERAL ( 
    SELECT * FROM Orders o 
    WHERE o.CustomerID = c.CustomerID 
    ORDER BY o.OrderDate DESC 
    LIMIT 1 
) recent ON true;

This query grabs each customer’s most recent order.

78. What is an indexed view, and how can it improve performance?

An indexed view (materialized view) stores aggregated or joined data physically. It’s extremely fast for read-heavy queries but requires extra storage and maintenance overhead.

Example (SQL Server)

CREATE VIEW SalesSummary WITH SCHEMABINDING AS 
SELECT CustomerID, SUM(TotalAmount) AS TotalSpent 
FROM dbo.Orders 
GROUP BY CustomerID; 
 
CREATE UNIQUE CLUSTERED INDEX ix_SalesSummary 
ON dbo.SalesSummary(CustomerID);

79. How do you design and manage partitioned tables for large datasets?

Partitioning splits a table by a chosen key (commonly date), improving query efficiency and maintenance for massive datasets.

Example (SQL Server)

CREATE PARTITION FUNCTION MyDateRangePFN (datetime) 
AS RANGE LEFT FOR VALUES ('2022-01-01', '2023-01-01'); 
 
CREATE PARTITION SCHEME MyPartitionScheme 
AS PARTITION MyDateRangePFN 
TO ([PRIMARY], [FileGroup2022], [FileGroup2023]);

Partitioned data in distinct filegroups can boost performance and simplify archiving.

80. How do pessimistic and optimistic locking differ, and which scenarios suit each?

Pessimistic and optimistic locking manage concurrent data access in different ways, each suited for specific scenarios. Here’s how they compare and when to use them:

Pessimistic locking: Locks rows before updates—best for high contention or critical transactions.
Optimistic locking: Allows concurrent reads, checks for conflicts at commit—ideal for web-scale apps where collisions are rare.

81. How can you prevent or resolve deadlocks?

Deadlocks occur when transactions block each other, preventing progress. Here are key strategies to prevent or resolve them:

Consistent object order: Always update tables in a fixed sequence.
Short transactions: Commit quickly to reduce lock durations.
App-level retries: Automatically retry if a transaction is the deadlock victim.
Monitoring: Tools like sys.dm_tran_locks (SQL Server) help identify hotspots.

82. What is a cursor, and what are its performance implications?

A cursor iterates row-by-row, suitable for complex logic but typically slower than set-based methods. Modern techniques like window functions, CTEs, or array-based processing often replace cursors to enhance performance.

Example: iterating through rows with a SQL Cursor

DECLARE cur CURSOR FOR 
    SELECT EmployeeID FROM Employees; 
 
OPEN cur; 
FETCH NEXT FROM cur INTO @EmpID; 
WHILE @@FETCH_STATUS = 0 
BEGIN 
    -- Process row 
    FETCH NEXT FROM cur INTO @EmpID; 
END;
CLOSE cur; 
DEALLOCATE cur;

Use sparingly due to overhead.

83. How does MVCC improve concurrency in databases like PostgreSQL and Oracle?

MVCC maintains multiple versions of rows so readers can see a snapshot of data from the start of their transaction. Writers don’t block readers, significantly boosting concurrency. Unneeded row versions are cleaned up (e.g., via VACUUM in PostgreSQL).

84. How do you implement high availability and disaster recovery for SQL databases?

To keep SQL databases running smoothly and recover quickly from failures, use these key strategies:

Failover clustering or availability groups (SQL Server).
Log shipping or replication for near real-time copies.
Regular backups stored offsite.

Goal: Minimize downtime (RTO) and data loss (RPO) during failures.

85. What are table hints, and when should you use them?

Table hints (NOLOCK, FORCESEEK, etc.) override the query optimizer’s choices. Use them cautiously for troubleshooting or special cases. Overuse can lead to inconsistent reads or suboptimal plans if the schema evolves.

Example (SQL Server)

SELECT *  
FROM Orders WITH (NOLOCK) 
WHERE CustomerID = 101;

NOLOCK reads data without shared locks, risking “dirty reads.”

86. Can you explain parameter sniffing and how to handle it?

Parameter Sniffing occurs when the optimizer bases the plan on the first-run parameters, which might not suit subsequent calls. Solutions include using local variables, OPTION (RECOMPILE), or plan guides to handle skewed parameters.

Example: Avoiding Parameter Sniffing with Local Variables

CREATE PROCEDURE GetOrdersByDate @OrderDate DATETIME 
AS 
BEGIN 
    -- Using local variables to avoid parameter sniffing 
    DECLARE @LocalDate DATETIME = @OrderDate; 
 
    SELECT * FROM Orders WHERE OrderDate = @LocalDate; 
END;

87. Which advanced SQL features or patterns do you often use for big data solutions?

SQL handles big data more efficiently with the following approaches:

Sharding/Horizontal Partitioning for massive scale.
Columnstore Indexes in SQL Server for analytical queries.
Parallel Query Execution in MPP systems (e.g., Azure Synapse, AWS Redshift).
External Tables (PolyBase) to query Hadoop or cloud data.

88. What is CROSS APPLY vs. OUTER APPLY, and how do they differ from standard JOINs?

CROSS APPLY and OUTER APPLY work like JOINs but with more flexibility for subqueries and table-valued functions. Here’s how they differ:

CROSS APPLY: Similar to an INNER JOIN; returns rows only if the subquery returns results.
OUTER APPLY: Similar to a LEFT JOIN; returns all rows from the left table, with NULL for unmatched subqueries.

Example (SQL Server)

SELECT c.CustomerName, lastOrder.* 
FROM Customers c 
CROSS APPLY ( 
    SELECT TOP 1 * FROM Orders o 
    WHERE o.CustomerID = c.CustomerID 
    ORDER BY OrderDate DESC 
) lastOrder;

This query fetches the latest order for each customer.

89. Why is a set-based approach usually preferred over row-by-row processing?

Set-based operations use the optimizer to process batches in parallel, generally faster and more scalable. Row-by-row (cursor-like) methods can be simpler to code but cripple performance under large data loads.

90. What are temporal tables, and why are they useful?

Temporal tables keep a history of row changes automatically. They’re invaluable for auditing, compliance, and time-travel queries.

Example (SQL Server)

CREATE TABLE EmployeesHistory 
( 
    EmployeeID INT NOT NULL PRIMARY KEY, 
    Name VARCHAR(100), 
    Salary DECIMAL(10,2), 
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START, 
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END 
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistoryArchive));

Records changes automatically over time.

91. How does full-text search work, and when should you use it?

Full-text search tokenizes text for advanced matching (e.g., stemming, synonyms, phrase searches). It’s essential for document-based queries or searching large text fields efficiently.

Example (SQL Server)

SELECT * 
FROM Articles 
WHERE CONTAINS(Content, '"machine learning" OR "data mining"');

Searches for specific keywords or phrases.

Please note: It requires a full-text index (e.g., CREATE FULLTEXT INDEX ON Articles(Content)).

92. How is JSON data handled in modern SQL databases, and what are the pitfalls?

Databases like SQL Server and PostgreSQL offer JSON functions to parse and query semi-structured data.

Example (PostgreSQL)

SELECT 
   info->>'city' AS City 
FROM Customers 
WHERE (info->>'country') = 'USA';

Pitfalls: Harder to enforce constraints, indexing JSON fields requires specialized strategies to maintain performance.

93. How do you use PARTITION BY in window aggregates for advanced analytics?

PARTITION BY breaks data into logical subsets for calculating group-based aggregates within a single query.

Example query

SELECT  
    Department, 
    EmployeeName, 
    Salary, 
    AVG(Salary) OVER (PARTITION BY Department) AS DeptAvg 
FROM Employees;

Unlike a standard GROUP BY, each row includes the department average salary, without collapsing rows.

94. Why is parameterizing queries better than using ad-hoc SQL?

Parameterized queries improve security, boost performance, and simplify maintenance. Here’s why they are better than ad-hoc SQL:

Security: Minimizes SQL injection risks.
Performance: Encourages plan reuse, reducing parse/compile overhead.
Maintainability: Easier to track and debug parameterized queries in stored procedures or prepared statements.

95. What is In-Memory OLTP, and what scenarios benefit most?

In-Memory OLTP (Hekaton) uses memory-optimized structures for extreme transaction throughput, removing latching and lock overhead. It’s ideal for stock trading platforms, high-frequency e-commerce operations, or any system with stringent latency demands.

96. How do advanced OVER clauses help with analytics beyond simple RANK() or ROW_NUMBER()?

They handle sliding window calculations, running totals, and period-over-period comparisons without using self-joins or complex subqueries.

Example query

SELECT  
    OrderID, 
    OrderDate, 
    SUM(TotalAmount) OVER (ORDER BY OrderDate  
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal 
FROM Orders;

Computes a running total chronologically.

97. Beyond standard isolation levels, what advanced concurrency strategies can mitigate contention?

Advanced concurrency strategies help reduce contention and keep databases running smoothly. Here are some effective approaches:

Row Versioning: Minimizes blocking by letting readers see older row versions.
Optimistic Concurrency: Checks data versions at commit to handle collisions gracefully.
Reduced Lock Granularity: Choosing row locks instead of page locks.
Sharding/Distributed SQL: Splits workload across nodes to avoid hotspots in a single database.

98. How does adaptive query processing improve performance in modern SQL databases?

Adaptive Query Processing (AQP) dynamically adjusts execution plans at runtime to optimize performance based on actual data distribution and resource availability. It includes features like:

Adaptive Joins: Switches between nested loops, hash joins, or merge joins based on row estimates.
Interleaved Execution: Defers final execution plan decisions until subqueries return actual statistics.
Memory Grant Feedback: Adjusts memory allocation dynamically to avoid over- or under-utilization.

Used in databases like SQL Server, PostgreSQL, and Oracle to enhance real-time query optimization.

99. What are graph databases, and how do they integrate with SQL?

Graph databases model relationships as nodes and edges, making them ideal for network-based data structures. Some relational databases (e.g., SQL Server, PostgreSQL) provide graph extensions, enabling queries like the following.

MATCH (p:Person)-[:FRIEND_OF]->(f:Person) 
WHERE p.Name = 'Alice' 
RETURN f.Name;

Used for social networks, fraud detection, and recommendation engines.

100. How do you troubleshoot and optimize high CPU usage in SQL queries?

High CPU usage often indicates inefficient queries or poor indexing. Key optimization strategies include:

Identifying bottlenecks using sys.dm_exec_requests, EXPLAIN ANALYZE, or Query Store.
Optimizing expensive functions (e.g., reducing scalar UDFs, replacing row-by-row operations with set-based logic).
Reducing unnecessary sorting and aggregations via proper indexing and avoiding complex joins.
Parallel query tuning, adjusting MAXDOP and COST THRESHOLD FOR PARALLELISM settings in SQL Server.

These techniques ensure database performance remains stable under heavy loads.

Tips for SQL interview preparation

SQL job interview questions demand more than syntax memorization. Employers expect you to write efficient queries, optimize performance, and structure databases for scalability. Whether you’re preparing for a data analyst, software engineer, or database administrator role, your ability to apply SQL in real-world scenarios will determine your success.

These expert-level strategies will set you apart from average candidates and help you ace any interview questions on SQL.

1. Stop reading—start writing queries

SQL is a practical skill. Interviewers don’t care how much theory you’ve read; they want to see how fast and accurately you can write and optimize queries under pressure.

Set up an actual database (MySQL, PostgreSQL, SQL Server).
Download open-source datasets from Kaggle.
Use SQL playgrounds like Mode Analytics, SQLFiddle, or db<>fiddle.
Work with database management tools like dbForge Edge, DBeaver, or MySQL Workbench for hands-on experience.

Example: can you retrieve the top 5 customers by total purchases without using GROUP BY?

SELECT CustomerID,  
      SUM(TotalAmount) OVER (PARTITION BY CustomerID) AS TotalSpent 
FROM Orders 
ORDER BY TotalSpent DESC 
LIMIT 5;

Mastering window functions like this will set you apart.

2. Master the SQL fundamentals—they always show up

No matter how advanced your role, SQL fundamentals always come up in interviews. Get these core concepts locked down:

DDL vs. DML vs. DCL vs. TCL commands
Primary keys, foreign keys, and unique constraints
Joins (INNER, LEFT, RIGHT, FULL) and when to use them
GROUP BY vs. HAVING vs. WHERE—don’t mix them up!

Example: can you spot the difference between WHERE and HAVING?

-- Incorrect: WHERE filters after aggregation (won't work) 
SELECT Department, COUNT(*)  
FROM Employees  
WHERE COUNT(*) > 10 
GROUP BY Department; 
 
-- Correct: HAVING filters after aggregation 
SELECT Department, COUNT(*)  
FROM Employees  
GROUP BY Department 
HAVING COUNT(*) > 10;

Many candidates fail interview questions on SQL because they misuse WHERE and HAVING. Don’t be one of them.

3. Optimize queries—speed wins interviews

A correct SQL query isn’t enough. If it runs slow, it’s useless. Performance optimization separates strong candidates from weak ones.

Avoid SELECT * (fetch only what you need)
Use indexes wisely—but don’t overuse them.
Analyze execution plans before running queries in production.
Filter early—apply WHERE conditions before joining tables.

Example: why is this query slow?

SELECT * FROM Orders WHERE YEAR(OrderDate) = 2023;

Problem: YEAR(OrderDate) is non-sargable, which is why it prevents index usage.

Optimized query

SELECT * FROM Orders WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01';

This minor tweak makes queries up to 100x faster.

4. Know when to use Joins vs. Subqueries

Interview questions for SQL will test your ability to join tables efficiently. If you hesitate between JOIN vs. subquery, you’re in trouble. Use:

INNER JOIN for strict relationships.
LEFT JOIN when data may be missing.
EXISTS for performance-friendly subqueries.

Example: finding employees who placed orders using JOIN vs. EXISTS

-- Using JOIN (returns duplicate employees if multiple orders exist) 
SELECT e.Name  
FROM Employees e 
JOIN Orders o ON e.EmployeeID = o.EmployeeID; 
 
-- Using EXISTS (more efficient for checking existence) 
SELECT Name  
FROM Employees e 
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.EmployeeID = e.EmployeeID);

JOIN retrieves data, EXISTS checks for existence—knowing when to use each improves query efficiency.

5. Index like a pro—or watch your queries crawl

Indexing can make or break query performance. Use them smartly to improve lookup speed, but avoid indexing everything:

Use indexes for searching indexed columns (WHERE email = ‘[email protected]’).
Sort large tables efficiently (ORDER BY created_at DESC).
Check if an index is missing before running slow queries:

EXPLAIN ANALYZE SELECT * FROM Employees WHERE LastName = 'Smith';

Don’t index:

Small tables (scans are often faster than maintaining an index).
Boolean flags (is_active = 1 for 99% of rows).
Frequently updated fields (index maintenance slows updates).

Poor indexing = SQL bottlenecks. Don’t let that be your downfall.

6. Learn window functions—they show up in every interview

SQL window functions power advanced analytics without collapsing result sets. Master these:

RANK() – Rank employees by salary.
LEAD() – Fetch next row value.
LAG() – Compare current vs. previous row.

Example: rank employees by salary.

SELECT EmployeeID, Salary,  
      RANK() OVER (ORDER BY Salary DESC) AS Rank 
FROM Employees;

Window functions are a must-know for data analytics and reporting roles.

7. Solve real-world SQL problems—textbook examples won’t cut it

SQL in the real world is messy. Interviewers won’t ask you to fetch “all employees with a salary over 50K”—they’ll ask complex business questions. For example:

Clean messy data (ETL queries).
Analyze marketing funnel drop-offs using SQL.
Optimize an e-commerce product search query for speed.

Where to practice?

LeetCode SQL challenges
Mode Analytics SQL exercises
Kaggle datasets (work with real data)

If you don’t solve real SQL problems, you won’t pass interviews.

8. Master system design—advanced SQL roles require it

For senior roles, expect system design and scalability questions. Know these:

Sharding – Splitting massive tables across databases.
Partitioning – Storing data efficiently across multiple disks.
Data warehousing – Understanding OLAP vs. OLTP trade-offs.

Example: If you had 1 billion user logins, would you:
(a) Store them all in one table?
(b) Partition them by month for faster queries?

Partitioning makes queries 100x faster.

Pressure-rest your skills with mock SQL interviews

Practicing SQL questions and answers alone won’t cut it. Simulate real interview conditions:

Use Pramp or Interviewing.io for live mock interviews.
Time yourself on LeetCode SQL problems.
Have a friend quiz you with SQL take-home problems.

How dbForge Edge can help you prepare for SQL interviews

dbForge Edge is an all-in-one multidatabase SQL development solution that helps you practice queries, optimize performance, and troubleshoot errors—just like you would in a real job. Whether you’re preparing for technical screenings, live coding rounds, or system design discussions, dbForge Edge equips you with the tools to tackle most top SQL interview questions.

Key features that give you an edge in SQL interviews

1. Query Profiler

Interviewers don’t just want correct queries; they want efficient ones. The Query Profiler in dbForge Edge helps you:

Analyze execution plans to find performance bottlenecks.
Compare query versions to identify the best-performing one.
Reduce load times by eliminating costly table scans and inefficient joins.

Example: Need to speed up a slow query? Run EXPLAIN ANALYZE in dbForge Edge’s Query Profiler to see where it lags—just like database engineers do in real jobs.

2. SQL Debugger

In a high-stakes interview, a single syntax or logic error can ruin your chances. The built-in SQL Debugger in dbForge Edge helps you:

Step through stored procedures, functions, and scripts to catch logical errors.
Set breakpoints to analyze variables and execution flow.
Debug without modifying production data—just like in a real-world SQL environment.

3. Data Generator

Struggling to find realistic test data for SQL practice? dbForge Edge’s Data Generator can:

Create thousands of test records instantly (no more relying on sample databases).
Simulate complex business scenarios for JOINs, aggregations, and subqueries.
Practice ETL transformations with structured and unstructured data.

Interviewers want to see how you handle real-world datasets, not just textbook queries.

4. Database Designer

Some of the best SQL interview questions go beyond writing queries—they test how well you design databases for performance and scalability. The Database Designer in dbForge Edge helps you:

Visually design and modify database schemas using an intuitive drag-and-drop editor.
Automatically generate optimized SQL scripts for table structures.
Learn normalization techniques to avoid redundancy and improve efficiency.

If an interviewer asks you to “design a database for an e-commerce platform,” dbForge Edge will have prepared you for it.

5. Schema & Data Comparison

Many companies give SQL take-home assessments. dbForge Edge’s Schema & Data Comparison tool ensures you:

Compare and sync database schemas to understand structural changes.
Validate test case results by comparing expected vs. actual data outputs.
Spot differences in execution results before submitting your assignment.

Never lose an interview because of an unnoticed data mismatch.

Conclusion

From global enterprises to disruptive startups, businesses depend on SQL to power decisions, optimize performance, and drive innovation. Professionals who can write efficient queries, troubleshoot databases, and fine-tune performance don’t just land jobs—they lead the future of data.

Now, take action with the top SQL interview questions and answers from this guide:

Sharpen your skills daily—real-world datasets are your best training ground.
Power up with dbForge Edge—professional tools make all the difference.
Go beyond the basics—optimize queries, master indexing, and tackle performance tuning.

SQL mastery isn’t just a skill—it’s a competitive advantage. Own it, apply it, and accelerate your career in the data-driven economy.

FAQ

What should I expect in a technical SQL interview?

Expect a mix of theoretical and hands-on coding interview questions in SQL. You may be asked to write queries, optimize performance, explain normalization, or troubleshoot a database issue. Many companies use live coding platforms or take-home assessments to test your ability to solve real-world SQL challenges.

What is the best way to practice SQL queries for an interview?

Hands-on experience is crucial. Use SQL practice platforms like LeetCode, HackerRank, Mode Analytics, or dbForge Edge. Work with real datasets, replicate interview-style problems, and practice optimizing queries.

What are the key topics I should focus on for advanced SQL interview questions?

For advanced roles, you should master query performance tuning, indexing strategies, window functions, recursive queries, CTEs, stored procedures, and concurrency control. Be prepared to explain execution plans, manage large-scale databases, and handle transactional integrity.

What are the best tools or platforms for practicing SQL interview questions and answers?

dbForge Edge – Advanced query-building and debugging tools.
LeetCode & HackerRank – SQL coding challenges based on real-world problems.
Mode Analytics – Great for practicing SQL in a data analysis setting.
SSMS, MySQL Workbench, Oracle SQL Developer, pgAdmin – Hands-on database management and query testing platforms.

How can I effectively explain my approach to solving SQL problems during an interview?

Think out loud. Explain your logic step by step:

Clarify the requirements and edge cases.
Describe how you’d structure the query.
Justify your approach, mentioning efficiency, indexing, or normalization.
Optimize—explain how you’d improve performance if needed.

Are there any real-world SQL scenarios I should study to prepare for interviews?

Yes! Study cases like:

E-commerce: Querying order histories, customer segmentation, inventory tracking.
Finance: Fraud detection, risk analysis, transactional integrity.
Healthcare: Managing patient records, ensuring compliance with data regulations.
Social Media: Ranking posts, tracking user engagement, analyzing trends.

For best results, practice with real datasets and analyze case studies from your industry of interest.

Top 100 SQL Interview Questions and Answers for Every Skill Level

Table of contents

Basic SQL interview questions for freshers

1. What is SQL, and why is it essential for data management?

2. What role does SQL play in managing and querying databases?

Core SQL operations

3. What are the different types of SQL commands?

Categories of SQL commands

4. What are primary keys, foreign keys, and unique keys? How do they ensure data integrity?

Database key types and their functions

5. What are common SQL data types, and how are they used?

Common data types in databases

6. Can you provide simple SQL queries using SELECT, WHERE, and ORDER BY?

7. What is the difference between SQL and MySQL?

8. What is a SQL table, and how is it structured?

9. What is the difference between CHAR and VARCHAR?

10. How do you retrieve all records from a table?

11. How do you retrieve specific columns from a table?

12. What is the difference between WHERE and HAVING?

13. How do you use the DISTINCT keyword?

14. What is the purpose of the LIMIT clause?

15. What is an alias in SQL?

16. How do you sort query results?

17. What are SQL aggregate functions?

Example: using AVG()in a query

18. What is the difference between COUNT(*) and COUNT(column_name)?

19. What is NULL in SQL?

20. How do you replace NULL values with a default value?

21. What is a DEFAULT constraint?

22. How do you count unique values in a column?

23. What is a CHECK constraint?

Limitations of CHECK constraints

24. How do you retrieve the second highest salary?

25. How do you find duplicate records in a table?

26. What is a case statement in SQL?

27. How do you remove duplicate rows in SQL?

Intermediate SQL interview questions

28. What are SQL Joins, and why are they important?

29. How does an INNER JOIN work? When should you use it?

30. How is a LEFT JOIN different?

31. When should you use a FULL JOIN?

32. Why is normalization critical in real-world databases?

33. Can you explain the first three normal forms (1NF, 2NF, 3NF)?

34. What is Boyce-Codd Normal Form (BCNF), and when is it necessary?

35. How do indexes improve query speed?

36. What are the different types of indexes?

37. When should you avoid indexing?

38. What’s the difference between a PRIMARY KEY and a UNIQUE constraint?

39. How do FOREIGN KEYS enforce referential integrity?

40. What is a CHECK constraint?

41. DELETE vs. TRUNCATE vs. DROP – Which One Should You Use?

42. What is a subquery, and how does it differ from a JOIN?

43. What are the pros and cons of using correlated subqueries?

44. What are SQL transaction isolation levels?

45. What is an execution plan, and why is it important?

46. What is query optimization, and why does it matter?

47. How do you identify and resolve slow queries?

48. What’s the difference between views and materialized views?

49. What is a covering index, and why is it useful?

50. What is database partitioning, and when should it be used?

51. What is the difference between pessimistic and optimistic Locking?

52. What is deadlock, and how do you prevent it?

53. What is snapshot Isolation?

54. What is a Common Table Expression (CTE), and why use it?

55. What are window functions, and how do they work?

56. What is an indexed view?

Example: Creating an Index on a Materialized View

57. How do you optimize bulk inserts in SQL?

58. What is a recursive CTE, and how is it used?

Example: fetching an employee hierarchy where each employee reports to a manager

59. What are LATERAL JOINs, and why are they useful?

Example: get the latest order for each customer

60. What is the difference between stored procedures and functions?

61. How does SQL Server handle concurrency?

62. What is a dead tuple in PostgreSQL?

63. What is the purpose of the MERGE statement?

Example: updating employee salaries, inserting new employees if not found

64. What is the difference between TRUNCATE and DELETE?

65. What is a CROSS APPLY, and how is it different from OUTER APPLY?

Example: Retrieving the Latest Order with CROSS APPLY