Complete SQL Interview Questions and Answers
Q: What is SQL?
A: SQL stands for Structured Query Language. It is used to communicate with and manipulate
databases.
Q: What are the types of SQL commands?
A: 1. DDL (CREATE, ALTER, DROP)
2. DML (INSERT, UPDATE, DELETE)
3. DCL (GRANT, REVOKE)
4. TCL (COMMIT, ROLLBACK, SAVEPOINT)
5. DQL (SELECT)
Q: What is a primary key?
A: A primary key uniquely identifies each record in a table and does not allow NULLs.
Q: What is a foreign key?
A: A foreign key is a column that creates a relationship between two tables by referencing the
primary key of another table.
Q: Difference between WHERE and HAVING?
A: WHERE filters rows before grouping. HAVING filters groups after GROUP BY.
Q: What is normalization?
A: Normalization is the process of organizing data to reduce redundancy and improve integrity.
Common forms: 1NF, 2NF, 3NF.
Q: What is denormalization?
A: Denormalization is the process of introducing redundancy to improve read performance.
Q: Types of joins in SQL?
A: 1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. FULL OUTER JOIN
5. CROSS JOIN
6. SELF JOIN
7. NATURAL JOIN
Q: What is a subquery?
A: A subquery is a query nested within another SQL query. It can be correlated or non-correlated.
Q: What is a correlated subquery?
A: A correlated subquery uses values from the outer query and is evaluated once for each row in the
outer query.
Q: Difference between DELETE, TRUNCATE and DROP?
A: DELETE: Removes rows, can be rolled back.
TRUNCATE: Removes all rows, can't be rolled back.
DROP: Deletes the table structure.
Q: What are indexes?
A: Indexes are used to speed up data retrieval. Types: Unique, Composite, Clustered,
Non-clustered.
Q: What is a view?
A: A view is a virtual table based on a SQL SELECT query. It does not store data physically.
Q: What is a stored procedure?
A: A stored procedure is a set of SQL statements saved in the database that can be executed
repeatedly.
Q: What is a trigger?
A: A trigger is a set of SQL statements automatically executed when a specific database event
occurs (INSERT, UPDATE, DELETE).
Q: What is a transaction?
A: A transaction is a sequence of operations performed as a single unit. It must be ACID compliant.
Q: What are ACID properties?
A: ACID stands for Atomicity, Consistency, Isolation, Durability. It ensures reliable processing of
database transactions.
Q: What is a CTE (Common Table Expression)?
A: A CTE is a temporary result set defined within the execution scope of a single SQL statement
using WITH clause.
Q: What is window function?
A: Window functions perform calculations across a set of rows related to the current row without
collapsing the result set.
Q: Explain ROW_NUMBER(), RANK(), DENSE_RANK().
A: ROW_NUMBER(): Unique number to each row.
RANK(): Same rank to ties, gaps allowed.
DENSE_RANK(): Same rank to ties, no gaps.
Q: What is the difference between UNION and UNION ALL?
A: UNION removes duplicates. UNION ALL keeps all records, including duplicates.
Q: Explain EXISTS vs IN vs JOIN.
A: EXISTS: Returns true if subquery returns rows.
IN: Checks if a value exists in a list.
JOIN: Combines rows from multiple tables.
Q: What is the difference between clustered and non-clustered index?
A: Clustered index sorts actual table data. Non-clustered index creates a separate structure with
pointers to data.
Q: How do you optimize SQL queries?
A: 1. Use proper indexing
2. Avoid SELECT *
3. Use joins instead of subqueries
4. Use EXISTS instead of IN when possible
5. Use WHERE clause to filter early.
Q: What is sharding?
A: Sharding is a database architecture pattern that distributes data across multiple servers to
improve performance and scalability.