Interview Q&A / SQL & DBMS
SQL & DBMS interview questions
Joins, keys, normalisation, indexes and transactions for placement and backend interviews.
Prefer rapid revision? Practise SQL & DBMS short interview answers →
- 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 - 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 - 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 - 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 - 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 - 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 - 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 - 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 - How do the LIKE wildcards % and _ work in SQL?
In LIKE, % matches any number of characters including none, and _ matches exactly one character. Use NOT LIKE to exclude, and ESCAPE to search for a literal % or _.
BeginnerQuick answer1 min read - How do you fetch only the first N rows in MySQL, SQL Server and Oracle?
MySQL and PostgreSQL use LIMIT n, SQL Server uses SELECT TOP n, and the SQL standard (Oracle 12c and later, PostgreSQL, SQL Server) uses FETCH FIRST n ROWS ONLY. Always pair it with ORDER BY.
BeginnerQuick answer1 min read - How does GROUP BY work with more than one column?
GROUP BY with several columns makes one group for each unique combination of those column values, and aggregates are computed inside each combination.
BeginnerQuick answer1 min read - How is a window function different from GROUP BY?
GROUP BY collapses rows into one row per group. A window function computes an aggregate or rank over a set of related rows but keeps every original row in the output.
BeginnerQuick answer1 min read - In what order does SQL actually execute the clauses of a SELECT query?
The logical order is FROM and JOINs, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then LIMIT. It differs from the written order, which is why a SELECT alias works in ORDER BY but not in WHERE.
BeginnerQuick answer1 min read - What are insertion, update and deletion anomalies?
They are problems in a badly designed table where redundant data forces you to add dummy values, edit many rows for one change, or lose unrelated facts when deleting a row. Normalization removes them.
BeginnerQuick answer1 min read - What are the advantages and disadvantages of stored procedures?
Advantages are less network traffic, precompiled plans, central business rules and access control. Disadvantages are harder version control, testing and debugging, database-specific syntax and logic split between app and database.
BeginnerQuick answer1 min read - What are the disadvantages of having too many indexes?
Every index uses disk and memory, and must be updated on each INSERT, UPDATE and DELETE, which slows writes. Extra indexes can also confuse the optimizer and slow maintenance such as bulk loads and backups.
BeginnerQuick answer1 min read - What do COMMIT, ROLLBACK and SAVEPOINT do?
COMMIT makes a transaction's changes permanent. ROLLBACK undoes everything since the transaction began. SAVEPOINT marks a point inside a transaction so you can roll back only part of it.
BeginnerQuick answer1 min read - What do the NOT NULL, DEFAULT and CHECK constraints do?
NOT NULL forbids missing values, DEFAULT supplies a value when none is given on insert, and CHECK rejects rows whose values fail a condition. Together they keep bad data out at the database level.
BeginnerQuick answer1 min read - What does a table need to be in First Normal Form, and what breaks it?
A table is in 1NF when every column holds a single atomic value, there are no repeating groups, and each row is unique. Storing a comma-separated list of values in one cell breaks 1NF.
BeginnerQuick answer1 min read - What does SELECT DISTINCT do when you list more than one column?
DISTINCT removes duplicate rows, not duplicate values in one column. Two rows are duplicates only if every selected column is equal, and NULLs are treated as equal to each other.
BeginnerQuick answer1 min read - What happens when a subquery used with = returns more than one row?
The database raises an error such as "subquery returns more than 1 row", because = expects a single value. Use IN, ANY or ALL for multiple values, or make the subquery return one row with an aggregate or LIMIT.
BeginnerQuick answer1 min read - What is a composite key and when do you need one?
A composite key is a key made of two or more columns that together identify a row. It is needed when no single column is unique, such as a student and subject pair in a marks table.
BeginnerQuick answer1 min read - What is a CROSS JOIN and when would you use it?
A CROSS JOIN pairs every row of one table with every row of the other, giving rows1 x rows2 results. It is useful for generating combinations, such as every size with every colour.
BeginnerQuick answer1 min read - What is a derived table (subquery in FROM)?
A derived table is a subquery in the FROM clause that acts like a temporary table for that one statement. It must have an alias, and it is useful for filtering or aggregating before joining.
BeginnerQuick answer1 min read - What is a functional dependency in DBMS?
A functional dependency X -> Y means that if two rows have the same X value they must have the same Y value, so X determines Y. Normalization is built on finding and organizing these dependencies.
BeginnerQuick answer1 min read - What is a self join and when do you need one?
A self join joins a table to itself using two different aliases. It is used when rows relate to other rows in the same table, such as an employee and their manager.
BeginnerQuick answer1 min read - What is an index in a database and how does it speed up queries?
An index is a separate sorted structure, usually a B-tree, that maps column values to row locations. It lets the database jump to matching rows instead of scanning the whole table, at the cost of extra storage and slower writes.
BeginnerQuick answer1 min read - What is autocommit and how does it affect transactions?
In autocommit mode every SQL statement is its own transaction and is committed as soon as it succeeds. To group statements into one atomic unit, start an explicit transaction with BEGIN or turn autocommit off.
BeginnerQuick answer1 min read - What is SQL injection and how do you prevent it?
SQL injection is when user input is concatenated into a query and changes its meaning. Prevent it with parameterized queries or prepared statements, input validation and least-privilege database accounts.
BeginnerQuick answer1 min read - What is the difference between a super key, a candidate key and a primary key?
A super key is any set of columns that uniquely identifies a row. A candidate key is a minimal super key with no removable column. The primary key is the one candidate key you choose to identify rows.
BeginnerQuick answer1 min read