SQL Interview Q&A

Interview Answer: Normalization is the process of organizing data to reduce redundancy and improve data integrity. 1NF removes repeating groups, 2NF removes partial dependency, and 3NF removes transitive dependency. The main advantage is reduced data duplication and better consistency, while the disadvantage is that too many joins can impact query performance.

Interview Answer: Denormalization is the process of combining tables to reduce joins and improve query performance. It introduces some data redundancy but makes data retrieval much faster. I mainly use it in reporting or data warehouse scenarios where read performance is more important than storage.

Interview Answer: Window functions perform calculations across a set of rows without grouping the data. ROW_NUMBER() gives unique row numbers, RANK() skips ranks after ties, DENSE_RANK() doesn't skip ranks, LAG() and LEAD() access previous and next rows, and SUM() OVER() calculates running totals. They're commonly used in analytics and reporting.

Interview Answer: SQL supports INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and SELF JOIN. INNER JOIN returns matching records, LEFT and RIGHT return all records from one side, FULL returns all records from both tables, CROSS creates a Cartesian product, and SELF JOIN joins a table with itself.

Interview Answer: A CTE is a temporary result set created using the WITH clause. It improves query readability, simplifies complex SQL, and avoids repeating the same subquery multiple times. I often use CTEs for multi-step transformations and recursive queries.

Interview Answer: An index improves query performance by allowing SQL to find data quickly instead of scanning the entire table. A Clustered Index stores data physically in sorted order, so only one can exist per table. A Non-Clustered Index stores pointers to the data and multiple indexes can be created on a table.

Interview Answer: A stored procedure is a precompiled collection of SQL statements stored in the database. It improves performance because the execution plan is reused and reduces network traffic. It also provides better security and makes complex business logic easier to maintain.

Interview Answer: WHERE filters individual rows before grouping, whereas HAVING filters grouped or aggregated data after GROUP BY. WHERE cannot use aggregate functions, but HAVING can. WHERE executes earlier than HAVING, making it generally faster.

Interview Answer: The logical execution order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. SQL first identifies the source table, filters rows, groups data, filters groups, selects required columns, and finally sorts the result. Understanding this order helps in writing optimized queries.

Interview Answer: A Primary Key uniquely identifies each row and cannot contain NULL values. A Foreign Key creates a relationship between two tables. A Unique Key also ensures uniqueness but allows one NULL value in most databases. A Composite Key is formed by combining two or more columns to uniquely identify a record.

Interview Answer: DELETE removes selected rows and can be rolled back. TRUNCATE removes all rows quickly without logging individual row deletions and is a DDL command. DROP deletes the entire table structure along with its data. TRUNCATE is faster than DELETE because it deallocates data pages instead of deleting rows one by one.

Interview Answer: I optimize SQL queries by creating proper indexes, selecting only required columns instead of using SELECT *, filtering data early with the WHERE clause, and avoiding unnecessary subqueries or functions on indexed columns. I also analyze execution plans, use joins efficiently, and partition large tables when needed. These techniques improve query performance and reduce execution time.