Top 50 SQL Interview Questions (2025)

1. What is SQL?

SQL (Structured Query Language) is a standardized programming language designed to manage and manipulate relational databases. It allows you to query, insert, update, and delete data, as well as create and modify schema objects like tables and views.

2. Differentiate between SQL and NoSQL databases.

SQL databases are relational, table-based, and use structured query language with fixed schemas, ideal for complex queries and transactions. NoSQL databases are non-relational, can be document, key-value, graph, or column-oriented, and are schema-flexible, designed for scalability and handling unstructured data.

3. What are the different types of SQL commands?

⦁ DDL (Data Definition Language): CREATE, ALTER, DROP (define and modify structure)

⦁ DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE (data operations)

⦁ DCL (Data Control Language): GRANT, REVOKE (permission control)

⦁ TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT (transaction management)

4. Explain the difference between WHERE and HAVING clauses.

⦁ WHERE filters rows before grouping (used with SELECT, UPDATE).

⦁ HAVING filters groups after aggregation (used with GROUP BY), e.g., filtering aggregated results like sums or counts.

5. Write a SQL query to find the second highest salary in a table.

Using a subquery:

SELECT MAX(salary) FROM employees

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

Or using DENSE_RANK():

SELECT salary FROM (

SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as
        rnk

FROM employees) t

WHERE rnk = 2;

6. What is a JOIN? Explain different types of JOINs.

A JOIN combines rows from two or more tables based on a related column:

⦁ INNER JOIN: returns matching rows from both tables.

⦁ LEFT JOIN (LEFT OUTER JOIN): all rows from the left table, matched rows from right.

⦁ RIGHT JOIN (RIGHT OUTER JOIN): all rows from right table, matched rows from left.

⦁ FULL JOIN (FULL OUTER JOIN): all rows when there’s a match in either table.

⦁ CROSS JOIN: Cartesian product of both tables.

7. How do you optimize slow-performing SQL queries?

⦁ Use indexes appropriately to speed up lookups.

⦁ Avoid SELECT *; only select necessary columns.

⦁ Use joins carefully; filter early with WHERE clauses.

⦁ Analyze execution plans to identify bottlenecks.

⦁ Avoid unnecessary subqueries; use EXISTS or JOINs.

⦁ Limit result sets with pagination if dealing with large datasets.

8. What is a primary key? What is a foreign key?

⦁ Primary Key: A unique identifier for records in a table; it cannot be NULL.

⦁ Foreign Key: A field that creates a link between two tables by referring to the primary key in another table, enforcing referential integrity.

9. What are indexes? Explain clustered and non-clustered indexes.

⦁ Indexes speed up data retrieval by providing quick lookups.

⦁ Clustered Index: Sorts and stores the actual data rows in the table based on the key; a table can have only one clustered index.

⦁ Non-Clustered Index: Creates a separate structure that points to the data rows; tables can have multiple non-clustered indexes.

10. Write a SQL query to fetch the top 5 records from a table.

In SQL Server and PostgreSQL:

SELECT * FROM table_name

ORDER BY some_column DESC

LIMIT 5;

In SQL Server (older syntax):

SELECT TOP 5 * FROM table_name

ORDER BY some_column DESC;

11. What is a subquery? Give an example.

A subquery is a query nested inside another query (SELECT, INSERT, UPDATE, DELETE). It helps filter or calculate values dynamically.

Example:

SELECT name FROM employees

WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

12. Explain the concept of normalization.

Normalization is organizing data to minimize redundancy by dividing tables and defining relationships using keys. It improves data integrity and reduces update anomalies. Common normal forms: 1NF, 2NF, 3NF.

13. What is denormalization? When is it used?

Denormalization is combining tables to reduce joins, improving read performance at the cost of redundancy. Used in data warehousing or OLAP scenarios requiring fast query responses.

14. Describe transactions and their properties (ACID).

A transaction is a set of SQL operations treated as a single unit. ACID properties:

⦁ Atomicity: all or nothing execution

⦁ Consistency: database moves from one valid state to another

⦁ Isolation: concurrent transactions don’t interfere

⦁ Durability: changes persist after commit

15. What is a stored procedure?

A stored procedure is a precompiled SQL program stored in the database, which can accept parameters and perform complex operations efficiently, improving performance and reusability.

16. How do you handle NULL values in SQL?

Use IS NULL or IS NOT NULL to check NULLs. Functions like COALESCE() or IFNULL() replace NULLs with specified values in queries.

17. Explain the difference between UNION and UNION ALL.

⦁ UNION combines results of two queries and removes duplicates.

⦁ UNION ALL combines results including duplicates, faster than UNION.

18. What are views? How are they useful?

A view is a virtual table based on a SELECT query. It simplifies complex queries, provides security by restricting access, and allows data abstraction.

19. What is a trigger? Give use cases.

Triggers are special procedures that automatically execute in response to certain events on a table (e.g., INSERT, UPDATE). Use cases: auditing changes, enforcing business rules, cascading changes.

20. How do you perform aggregate functions in SQL?

Aggregate functions process multiple rows to return a single value, e.g., COUNT(), SUM(), AVG(), MIN(), and MAX(). Often used with GROUP BY to group results.

21. What is data partitioning?

Splitting large tables into smaller, manageable pieces (partitions) based on a key like date or region, improving query performance and maintenance.

22. How do you find duplicates in a table?

Use GROUP BY with HAVING:

SELECT column, COUNT(*)
FROM table_name
GROUP BY column
HAVING COUNT(*) > 1;

23. What is the difference between DELETE and TRUNCATE?

⦁ DELETE removes rows one by one, can have WHERE clause, logs each row, slower.

⦁ TRUNCATE removes all rows instantly, no WHERE, resets identity, faster but less flexible.

24. Explain window functions with examples.

Window functions perform calculations across sets of rows related to the current row without collapsing results.

Example:

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS
        salary_rank

FROM employees;

25. What is the difference between correlated and non-correlated subqueries?

⦁ Correlated subqueries depend on the outer query and execute for each row.

⦁ Non-correlated subqueries run independently once.

26. How do you enforce data integrity?

Using constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL), triggers, and transactions.

27. What are CTEs (Common Table Expressions)?

Temporary named result sets within SQL statements to improve query readability and recursion:

WITH cte AS (SELECT * FROM employees WHERE salary > 5000)
SELECT * FROM cte;

28. Explain EXISTS and NOT EXISTS operators.

⦁ EXISTS returns TRUE if a subquery returns any rows.

⦁ NOT EXISTS returns TRUE if subquery returns no rows.

29. How do SQL constraints work?

Constraints enforce rules at the database level to ensure data validity and integrity during insert/update/delete operations.

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

A detailed roadmap of how SQL Server executes a query. Used to analyze and optimize query performance by revealing bottlenecks.

31. Describe how to handle errors in SQL.

Use TRY...CATCH blocks (in SQL Server) or exception handling constructs provided by the database to catch and manage runtime errors, ensuring graceful failure or rollback.

32. What are temporary tables?

Temporary tables store intermediate results temporarily during a session or procedure, usually with names prefixed by # (local) or ## (global) in SQL Server.

33. Explain the difference between CHAR and VARCHAR.

⦁ CHAR is fixed-length and pads unused spaces, faster for fixed-size data.

⦁ VARCHAR is variable-length, saves space for variable data but may be slightly slower.

34. How do you perform pagination in SQL?

Use LIMIT and OFFSET (MySQL/PostgreSQL):

SELECT * FROM table_name ORDER BY id LIMIT 10 OFFSET 20;

Or in SQL Server:`

SELECT * FROM table_name ORDER BY id OFFSET 20 ROWS FETCH NEXT 10
          ROWS ONLY;

35. What is a composite key?

A primary key made up of two or more columns that uniquely identify a record.

36. How do you convert data types in SQL?

Using CAST() or CONVERT() functions, e.g.,

SELECT CAST(column_name AS INT) FROM table_name;

37. Explain locking and isolation levels in SQL.

Locks control concurrent access to data. Isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) define visibility of changes between concurrent transactions, balancing consistency and performance.

38. How do you write recursive queries?

Using Recursive CTEs with WITH clause:

WITH RECURSIVE cte AS (

SELECT id, parent_id FROM table WHERE parent_id IS
          NULL

UNION ALL

SELECT t.id, t.parent_id FROM table t INNER JOIN cte ON t.parent_id =
          cte.id

)

SELECT * FROM cte;

39. What are the advantages of using prepared statements?

Improved performance (query plan reuse), security (prevents SQL injection), and ease of use with parameterized inputs.

40. How to debug SQL queries?

Analyze execution plans, check syntax errors, use descriptive aliases, test subqueries separately, and monitor performance metrics.

41. Differentiate between OLTP and OLAP databases.

⦁ OLTP (Online Transaction Processing) is optimized for transactional tasks—fast inserts, updates, and deletes with many users.

⦁ OLAP (Online Analytical Processing) is optimized for complex queries and data analysis, often dealing with large historical datasets.

42. What is schema in SQL?

A schema is a logical container that holds database objects like tables, views, and procedures, helping organize and manage database permissions.

43. How do you implement many-to-many relationships in SQL?

By creating a junction (or associative) table with foreign keys referencing the two related tables.

44. What is query optimization?

The process of improving query execution efficiency by rewriting queries, indexing, and analyzing execution plans to reduce resource consumption.

45. How do you handle large datasets in SQL?

Use partitioning, indexing, batch processing, query optimization, and sometimes materialized views or data archiving to manage performance.

46. Explain the difference between CROSS JOIN and INNER JOIN.

⦁ CROSS JOIN returns the Cartesian product (all combinations) of two tables.

⦁ INNER JOIN returns only matching rows based on join conditions.

47. What is a materialized view?

A stored physical copy of the result set of a query, which improves performance for complex queries by avoiding re-computation every time.

48. How do you backup and restore a database?

Use built-in commands/tools like BACKUP DATABASE and RESTORE DATABASE in SQL Server, or mysqldump in MySQL, often automating with scripts for regular backups.

49. Explain how indexing can degrade performance.

Too many indexes slow down write operations (INSERT, UPDATE, DELETE) because indexes must also be updated; large indexes can consume extra storage and memory.

50. Can you write a query to find employees with no managers?

Example:

SELECT * FROM employees e

WHERE NOT EXISTS (SELECT 1 FROM employees m WHERE m.id = e.manager_id);

Read more about Python Interview Questions 2025 here

Top 50 SQL Interview Questions (2025)