Interview Q&A / SQL & DBMS

1. What are the ACID properties of a transaction?

   ACID stands for Atomicity, Consistency, Isolation and Durability. A transaction either fully happens or not at all, leaves the data valid according to its rules, behaves as if it ran without interference from other transactions, and stays saved once committed, even if the server crashes.

   BeginnerIn-depth3 min read
2. What is the difference between a primary key, a unique key and a foreign key?

   A primary key uniquely identifies each row and cannot be NULL; a table has only one. A unique key also prevents duplicates, but a table can have many and they usually allow NULL. A foreign key is a column that must match a primary or unique key in another table, which keeps related data consistent.

   BeginnerIn-depth3 min read
3. What is the difference between INNER JOIN and LEFT JOIN?

   INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns every row from the left table, and fills the right table's columns with NULL when there is no match.

   BeginnerIn-depth2 min read
4. What is the difference between WHERE and HAVING?

   WHERE filters individual rows before they are grouped, so it cannot use aggregate functions like SUM or COUNT. HAVING filters groups after GROUP BY has combined the rows, so it is where conditions on aggregates go.

   BeginnerIn-depth3 min read
5. Write an SQL query to find the second highest salary.

   Find the highest salary that is less than the maximum, with SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). For the Nth highest, use DENSE_RANK() so that duplicate salaries share a rank, or ORDER BY DISTINCT salary with LIMIT and OFFSET.

   BeginnerIn-depthCoding3 min read
6. What is normalization? Explain 1NF, 2NF and 3NF.

   Normalization organises tables so each fact is stored once. 1NF means single values per cell, 2NF removes columns that depend on only part of a composite key, and 3NF removes columns that depend on other non-key columns.

   IntermediateIn-depth2 min read
7. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

   All three number rows in a given order. ROW_NUMBER always gives unique numbers 1, 2, 3, even for ties. RANK gives tied rows the same number and then skips ahead (1, 2, 2, 4). DENSE_RANK gives tied rows the same number without skipping (1, 2, 2, 3).

   IntermediateIn-depth4 min read
8. What is the difference between a clustered and a non-clustered index?

   A clustered index decides the physical order in which table rows are stored, so the table itself is the index and there can be only one. A non-clustered index is a separate structure holding the indexed values plus a pointer back to the row, and a table can have many.

   AdvancedIn-depth4 min read