If you're preparing for an SQL interview in 2025, it's essential to master a blend of fundamental concepts and advanced query techniques. Here's a comprehensive list of the top 50 SQL interview questions with key explanations and practical query examples to help you succeed.
What is SQL?
SQL (Structured Query Language) is the standard programming language used to manage, manipulate, and query relational databases. It allows users to insert, update, delete, and retrieve data efficiently.
Differentiate between SQL and NoSQL databases.
SQL databases are relational, use structured schemas, and support ACID properties ensuring data integrity. NoSQL databases are non-relational, schema-flexible, and optimized for horizontal scaling and Big Data needs. SQL is ideal for complex queries and transactions, while NoSQL suits unstructured data and rapid development.
What are the different types of SQL commands?
DDL (Data Definition Language): CREATE, ALTER, DROP
DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
DCL (Data Control Language): GRANT, REVOKE
TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT
Explain the difference between WHERE and HAVING clauses.
WHERE filters rows before grouping, applicable to individual rows. HAVING filters grouped rows after aggregation.
Write a SQL query to find the second highest salary in a table.
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
What is a JOIN? Explain different types of JOINs.
A JOIN combines rows from two or more tables based on related columns.
INNER JOIN: Returns matching rows from both tables.
LEFT JOIN: All rows from left table + matching from right.
RIGHT JOIN: All rows from right table + matching from left.
FULL OUTER JOIN: All matching and non-matching rows from both tables.
How do you optimize slow-performing SQL queries?
Check execution plans, add appropriate indexes, avoid SELECT *, rewrite queries, and limit result sets.
What is a primary key? What is a foreign key?
Primary key uniquely identifies each row, cannot be NULL.
Foreign key enforces referential integrity between tables, linking to primary keys.
What are indexes? Explain clustered and non-clustered indexes.
Indexes speed up data retrieval.
Clustered index sorts and stores data rows physically.
Non-clustered index creates a separate structure with pointers to data rows.
Write a SQL query to fetch the top 5 records from a table.
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 5;
What is a subquery? Give an example.
A query nested inside another query.
Example:
SELECT name FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');
Explain the concept of normalization.
Organizing database to reduce redundancy by dividing tables and establishing relationships.
What is denormalization? When is it used?
Denormalization adds redundancy to improve read performance, used in data warehousing or OLAP.
Describe transactions and their properties (ACID).
Transactions are units of work ensuring:
Atomicity: All or none succeeds.
Consistency: Database moves from one valid state to another.
Isolation: Transactions don’t interfere.
Durability: Committed changes are permanent.
What is a stored procedure?
A saved collection of SQL statements executed as a single call, often with parameters.
How do you handle NULL values in SQL?
Using IS NULL or IS NOT NULL for comparisons; COALESCE to provide default values.
Explain the difference between UNION and UNION ALL.
UNION removes duplicates.
UNION ALL includes all results, duplicates included.
What are views? How are they useful?
Virtual tables created by queries for simplifying complex joins or limiting data exposure.
What is a trigger? Give use cases.
A procedure executed automatically on certain events, like auditing or enforcing business rules.
How do you perform aggregate functions in SQL?
Functions like COUNT, SUM, AVG, MAX, MIN operate on groups of rows.
What is data partitioning?
Splitting a database into smaller, manageable parts for performance and scalability.
How do you find duplicates in a table?
SELECT column_name, COUNT(*)
FROM table
GROUP BY column_name
HAVING COUNT(*) > 1;
What is the difference between DELETE and TRUNCATE?
DELETE is logged, can have WHERE clause, slower. TRUNCATE removes all rows, faster, no logging per row.
Explain window functions with examples.
Functions performing calculations across a set of query rows related to the current row, such as ROW_NUMBER(), RANK().
What is the difference between correlated and non-correlated subqueries?
Correlated subqueries depend on outer query row.
Non-correlated run independently.
How do you enforce data integrity?
By using constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL.
What are CTEs (Common Table Expressions)?
Temporary result sets defined within a query (WITH clause), improve readability and recursion.
Explain EXISTS and NOT EXISTS operators.
Check for presence or absence of rows in subqueries, efficient for existence tests.
How do SQL constraints work?
They enforce rules at table or column level to maintain data accuracy and integrity.
What is an execution plan? How do you use it?
A breakdown of how SQL engine executes a query. Used to optimize SQL performance by analyzing steps.
Describe how to handle errors in SQL.
Using TRY...CATCH blocks, error logging, transactions with rollback.
What are temporary tables?
Tables that store intermediate data during sessions, automatically dropped after session ends.
Explain the difference between CHAR and VARCHAR.
CHAR is fixed length, VARCHAR is variable length string type.
How do you perform pagination in SQL?
Using OFFSET and FETCH or LIMIT to fetch a subset of rows.
What is a composite key?
A key made from two or more columns to uniquely identify a row.
How do you convert data types in SQL?
Functions like CAST() or CONVERT() convert one data type to another.
Explain locking and isolation levels in SQL.
Controls concurrent access to data, isolation levels define visibility of changes between transactions.
How do you write recursive queries?
Using CTEs with WITH RECURSIVE syntax to process hierarchical data.
What are the advantages of using prepared statements?
Improved performance, prevention of SQL injection, reuse of execution plans.
How to debug SQL queries?
Check syntax, use EXPLAIN for execution plans, break complex queries into parts.
Differentiate between OLTP and OLAP databases.
OLTP handles transactional processing, OLAP is for analytical queries and reporting.
What is schema in SQL?
The structure that defines tables, views, and relationships within a database.
How do you implement many-to-many relationships in SQL?
Using a junction table that contains foreign keys referencing the related tables.
What is query optimization?
Techniques to enhance query performance like indexing, rewriting queries, and analyzing execution plans.
How do you handle large datasets in SQL?
Partitioning, indexing, using batch processing, and optimizing queries.
Explain the difference between CROSS JOIN and INNER JOIN.
CROSS JOIN returns Cartesian product.
INNER JOIN returns matching rows.
What is a materialized view?
A view that stores physically computed results for faster access but requires refresh.
How do you backup and restore a database?
Using database-specific tools or SQL commands like BACKUP and RESTORE.
Explain how indexing can degrade performance.
Too many indexes slow down write operations like INSERT, UPDATE, DELETE.
Can you write a query to find employees with no managers?
SELECT employee_name
FROM employees
WHERE manager_id IS NULL;
This comprehensive guide on top 50 SQL interview questions for 2025 covers basics to advanced concepts, practical queries, and performance optimization techniques to help interviewees excel.