SQL Interview Questions and Answers: A Complete Guide for 2026

SQL remains one of the most sought-after skills in data management and backend development. Whether you’re preparing for your first interview or leveling up your expertise, this comprehensive guide covers 30+ essential SQL interview questions across all difficulty levels. These questions reflect real-world scenarios and the topics most commonly asked by leading technology companies.

Beginner Level SQL Interview Questions

1. What is SQL and what are its main applications?

SQL (Structured Query Language) is a standardized language used to manage and manipulate data stored in relational databases. Its main applications include:

• Retrieving data from databases using SELECT statements
• Inserting, updating, and deleting records
• Creating and modifying database structures
• Enforcing data security and access control
• Ensuring data consistency and integrity
• Generating reports and analytics

2. What is a table and what are rows and columns?

A table is the fundamental structure in a relational database that stores data in an organized format. A table consists of:

• Rows (Records): Horizontal entries that represent individual data instances. Each row contains one complete record.
• Columns (Fields): Vertical entries that represent specific attributes or properties. Each column has a defined data type and contains values for a particular attribute across all rows.

3. What is a primary key?

A primary key is a column or combination of columns that uniquely identifies each record in a table. Key characteristics include:

• Must contain unique values with no duplicates
• Cannot contain NULL values
• Only one primary key is allowed per table
• Ensures data integrity and enables efficient data retrieval

4. What is a foreign key?

A foreign key is a column or set of columns in one table that refers to the primary key of another table. It establishes relationships between tables and ensures referential integrity by enforcing that values in the foreign key column must match values in the referenced table’s primary key.

5. What are the different types of SQL commands?

SQL commands are categorized into several groups:

• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE – used for database structure
• DML (Data Manipulation Language): INSERT, UPDATE, DELETE, SELECT – used for data operations
• DQL (Data Query Language): SELECT – used for retrieving data
• DCL (Data Control Language): GRANT, REVOKE – used for access permissions

6. What is the difference between DELETE and TRUNCATE commands?

While both remove data, they differ significantly:

• DELETE: Removes rows one by one, can use WHERE clause, slower, logs each deletion, can be rolled back in a transaction
• TRUNCATE: Removes all rows at once, cannot use WHERE clause, faster, minimal logging, cannot be rolled back in all databases

7. What is the DROP command?

DROP removes an entire table structure along with its data and indexes from the database. Unlike DELETE or TRUNCATE, DROP removes the table definition itself, freeing up the storage space. It cannot be rolled back in many database systems without a backup.

8. What are constraints in SQL?

Constraints are rules enforced on columns to maintain data integrity:

• PRIMARY KEY: Uniquely identifies each record
• FOREIGN KEY: Ensures referential integrity
• UNIQUE: Ensures all values in a column are unique
• NOT NULL: Ensures a column always contains a value
• CHECK: Verifies that values satisfy a specified condition
• DEFAULT: Assigns a default value when none is specified
• INDEX: Provides faster data retrieval

9. What is NULL in SQL?

NULL represents the absence of a value or unknown data in a field. It is different from zero or an empty string. NULL values require special handling using IS NULL or IS NOT NULL operators in queries, as standard comparison operators do not work with NULL.

10. What is the SELECT statement?

The SELECT statement is the most fundamental SQL command used to retrieve data from a database. The typical order of SQL clauses in a SELECT statement is:

SELECT – FROM – JOIN – ON – WHERE – GROUP BY – HAVING – ORDER BY – LIMIT

Intermediate Level SQL Interview Questions

11. What is a JOIN in SQL?

A JOIN combines data from multiple tables based on a related column. JOINs enable querying across tables and are essential for working with normalized databases. Different types of JOINs include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

12. What are the different types of JOINs?

SQL supports multiple JOIN types:

• INNER JOIN: Returns only rows with matching values in both tables
• LEFT JOIN: Returns all rows from the left table and matched rows from the right table
• RIGHT JOIN: Returns all rows from the right table and matched rows from the left table
• FULL OUTER JOIN: Returns all rows from both tables
• CROSS JOIN: Returns the Cartesian product of two tables, combining each row from the first table with every row from the second table
• NATURAL JOIN: Joins tables based on columns with the same name and data type

13. What is a CROSS JOIN?

A CROSS JOIN produces a Cartesian product of two tables. It combines each row from the first table with every row in the second table. If Table A has 5 rows and Table B has 3 rows, the result contains 15 rows (5 × 3). CROSS JOINs are useful for generating combinations but should be used cautiously as they can produce large result sets.

14. What is the difference between NATURAL JOIN and CROSS JOIN?

These are fundamentally different:

• NATURAL JOIN: Focuses on columns with the same names and data types in both tables, performing an inner join on those matching columns
• CROSS JOIN: Creates a Cartesian product by combining every row from one table with every row from another, without any join condition

15. What is a subquery?

A subquery (or inner query) is a query nested inside another SQL query. The subquery provides data to the main query. Subqueries can be used in SELECT, FROM, WHERE, and HAVING clauses. They can return a single value, multiple values, or an entire table.

16. What is a correlated subquery?

A correlated subquery is a subquery that references columns from the outer query. Unlike regular subqueries that execute once, correlated subqueries execute once for each row processed by the outer query. They are useful for row-by-row comparisons but can be slower on large datasets.

17. What is the difference between UNION and UNION ALL?

Both combine results from multiple queries, but differ in handling duplicates:

• UNION: Combines results and removes duplicate rows
• UNION ALL: Combines results and keeps all rows, including duplicates

UNION ALL is faster since it doesn’t need to check for and remove duplicates.

18. What are aggregate functions in SQL?

Aggregate functions perform calculations on sets of values and return a single result:

• COUNT(): Counts the total number of records
• SUM(): Calculates the sum of numeric values
• AVG(): Calculates the average of numeric values
• MIN(): Finds the minimum value
• MAX(): Finds the maximum value
• FIRST(): Fetches the first element in a collection

19. What is the GROUP BY clause?

The GROUP BY clause groups rows that have the same values in specified columns. It is typically used with aggregate functions to generate summary reports. GROUP BY must include all non-aggregated columns in the SELECT list.

20. What is the HAVING clause?

The HAVING clause establishes qualifying conditions for grouped data. It filters results after the GROUP BY operation, similar to how WHERE filters before grouping. HAVING is essential for filtering aggregated data, whereas WHERE filters individual rows.

21. What is a view in SQL?

A view is a virtual table that displays data from one or more base tables without storing data independently. Views simplify complex queries, provide security by restricting column access, and enhance maintainability by encapsulating query logic. Views can be queried like regular tables.

22. What is normalization in SQL?

Normalization is the database design process that organizes data to reduce redundancy and improve integrity. It involves breaking down larger tables into smaller, related tables and establishing relationships between them. Normalization helps prevent data anomalies and ensures efficient storage.

23. What is denormalization?

Denormalization is the process of intentionally introducing redundancy into a database to improve query performance. While normalization optimizes for storage and data integrity, denormalization optimizes for read performance by reducing the number of JOINs needed. It trades storage space and update complexity for faster queries.

24. What is an index in SQL?

An index is a database structure that speeds up data retrieval operations. Indexes create a sorted lookup table that points to actual data, reducing the need for full table scans. While indexes significantly improve read performance, they slow down write operations (INSERT, UPDATE, DELETE) and consume additional storage space.

25. What is a composite primary key?

A composite primary key consists of multiple columns that together uniquely identify each record in a table. Unlike a simple primary key with a single column, a composite key requires all constituent columns to create a unique identifier. This is useful when no single column can uniquely identify records.

Advanced Level SQL Interview Questions

26. What are ACID properties in SQL transactions?

ACID properties ensure reliable database transactions:

• Atomicity: All operations succeed or none do. A transaction is indivisible—it either completes fully or rolls back entirely
• Consistency: The database remains in a valid state before and after each transaction
• Isolation: Concurrent transactions do not interfere with each other. Changes in one transaction are not visible to others until committed
• Durability: Once committed, changes persist permanently even after system failures

27. What is a transaction in SQL?

A transaction is a sequence of SQL statements executed as a single logical unit of work. Transactions begin with the first executable SQL statement and end with a COMMIT (to save changes) or ROLLBACK (to undo changes). Transactions ensure data consistency and maintain ACID properties.

28. What is a deadlock and how can you prevent it?

A deadlock occurs when two or more transactions block each other, waiting indefinitely for resources held by the other. Prevention strategies include:

• Acquiring locks in the same order across transactions
• Keeping transactions short to minimize lock duration
• Using lock timeout strategies
• Minimizing the scope of transactions
• Avoiding nested transactions when possible

29. How does indexing affect query performance?

Indexes significantly impact performance:

• Positive effects: Speed up data retrieval by reducing full table scans, improve WHERE clause filtering, enhance JOIN performance
• Negative effects: Slow down INSERT, UPDATE, and DELETE operations as indexes must be updated, consume additional disk storage

Effective indexing requires balancing read and write operation frequencies for your specific workload.

30. What is an execution plan and how do you use it?

An execution plan shows how the database engine will execute a query, displaying the steps and methods used. Key insights from execution plans include:

• 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 assess query performance and identify bottlenecks

Use EXPLAIN ANALYZE commands to view execution plans and optimize slow queries.

31. How do you optimize SQL queries?

Query optimization strategies include:

• Selecting specific columns instead of using SELECT * to reduce data transfer
• Using proper indexes on frequently queried columns
• Avoiding complex or unnecessary JOINs
• Leveraging CTEs (Common Table Expressions) for better readability and sometimes better performance
• Studying and analyzing execution plans
• Using LIMIT to restrict result sets when possible
• Avoiding functions on indexed columns in WHERE clauses

32. What is a stored procedure?

A stored procedure is a precompiled set of SQL statements stored in the database that can be reused. Stored procedures improve performance by reducing network traffic, enhance security by encapsulating database logic, and simplify complex operations. They can accept parameters, return results, and contain control flow logic.

33. What is a trigger?

A trigger is a special type of stored procedure that automatically executes in response to specific database events such as INSERT, UPDATE, or DELETE operations. Triggers are useful for:

• Enforcing complex business rules
• Maintaining audit trails
• Ensuring referential integrity
• Automatically updating related data
• Validating data before insertion or modification

34. What is the difference between IN and EXISTS in SQL?

Both can be used for filtering, but they differ:

• IN: Checks if a value exists in a list or subquery result. Returns all matching values from the subquery
• EXISTS: Checks if a subquery returns any rows without retrieving the actual values. More efficient for large datasets as it stops checking once a match is found

EXISTS is generally faster for checking existence, while IN is better for small result sets.

35. What is a Common Table Expression (CTE)?

A CTE (Common Table Expression), defined using the WITH clause, is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs improve readability by breaking complex queries into smaller logical components and enable recursive queries. They are helpful for hierarchical data and complex analytical queries.

Scenario-Based SQL Interview Questions

36. You need to find the second highest salary in an employee table. How would you approach this?

To find the second highest salary, use subqueries or window functions:

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Alternatively, use the RANK() window function to handle ties appropriately and retrieve the second-ranked salary.

37. How would you remove duplicate records from a table?

To remove duplicates, use a CTE with ROW_NUMBER():

WITH duplicate_rows AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) AS rn
  FROM table_name
)
DELETE FROM duplicate_rows
WHERE rn > 1;

This approach identifies duplicates based on specified columns and deletes all but the first occurrence of each unique combination.

38. A retail company like Amazon needs to find customers who made purchases in consecutive months. How would you solve this?

Use window functions and CTEs to identify consecutive months:

WITH monthly_purchases AS (
  SELECT DISTINCT customer_id, DATE_TRUNC('month', purchase_date) AS month
  FROM orders
  ORDER BY customer_id, month
)
SELECT customer_id, month
FROM monthly_purchases
WHERE LEAD(month) OVER (PARTITION BY customer_id ORDER BY month) = month + INTERVAL '1 month';

This groups purchases by month per customer and identifies those with consecutive monthly activity using the LEAD() window function.

39. How would you identify data inconsistencies across related tables?

Use outer JOINs to find orphaned records (foreign keys without matching primary keys):

SELECT t1.id, t1.foreign_key_id
FROM table1 t1
LEFT JOIN table2 t2 ON t1.foreign_key_id = t2.id
WHERE t2.id IS NULL;

This query returns all records from table1 that don't have corresponding records in table2, revealing referential integrity violations.

40. A SaaS company needs to calculate the month-over-month growth rate for users. How would you structure this query?

Use window functions to calculate growth rates:

SELECT 
  month,
  user_count,
  LAG(user_count) OVER (ORDER BY month) AS previous_month,
  ((user_count - LAG(user_count) OVER (ORDER BY month)) * 100.0) / LAG(user_count) OVER (ORDER BY month) AS growth_rate
FROM monthly_user_counts
ORDER BY month;

This calculates the percentage change from one month to the next using the LAG() window function to access previous row values.

Conclusion

These 40 SQL interview questions cover the essential knowledge areas that technology companies evaluate during technical interviews. Success in SQL interviews requires understanding not just the syntax, but also the practical implications of different approaches. Focus on learning the fundamentals thoroughly, practice writing optimized queries, and understand how to troubleshoot performance issues using execution plans. With consistent practice and a clear understanding of database concepts, you'll be well-prepared for SQL interviews across all experience levels.