The 50 questions interviewers ask most about DBMS and SQL, with short, clear answers and code where it helps. The first 25 are the most common; the rest go deeper. Free, no sign-up.
A DBMS (Database Management System) is software that lets you create, store, retrieve, update and secure data in a database. It sits between users or applications and the stored data, handling concurrency, transactions, access control, backup and recovery. Examples include MySQL, PostgreSQL, Oracle, SQL Server and MongoDB.
An RDBMS (Relational DBMS) is a DBMS that stores data in tables of rows and columns and links those tables through keys and relationships. A general DBMS may use hierarchical, network, document or file-based storage and may not enforce relations. An RDBMS supports SQL, integrity constraints, joins and ACID transactions. MySQL, PostgreSQL and Oracle are RDBMSs.
A primary key uniquely identifies each row in a table, while a foreign key is a column that references the primary key (or a unique key) of another table to link the two. A table can have only one primary key, which cannot be NULL. A foreign key can repeat and may be NULL, and it enforces referential integrity.
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);Both enforce uniqueness, but a table can have only one primary key and it can never contain NULL, whereas a table can have many unique keys. Unique constraints usually allow NULL values; how many depends on the database (SQL Server allows one, MySQL and PostgreSQL allow several). A primary key typically creates a clustered index by default in SQL Server and MySQL InnoDB.
A join combines rows from two or more tables based on a related column. The main types are INNER JOIN (only matching rows), LEFT JOIN (all left rows plus matches), RIGHT JOIN (all right rows plus matches), FULL OUTER JOIN (all rows from both sides) and CROSS JOIN (Cartesian product). A self join joins a table to itself.
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;INNER JOIN returns only rows that have a match in both tables, while LEFT JOIN returns all rows from the left table and fills the right-side columns with NULL where no match exists. Use LEFT JOIN when you must keep unmatched rows, for example customers with no orders. A common trick is LEFT JOIN ... WHERE right.id IS NULL to find rows with no match.
Normalization is the process of organizing tables to reduce data redundancy and avoid insert, update and delete anomalies. It splits large tables into smaller related ones connected by keys. The result is consistent data and smaller storage, at the cost of more joins. Common forms are 1NF, 2NF, 3NF and BCNF.
1NF requires atomic values and no repeating groups, so a column cannot hold a list like "phone1, phone2". 2NF requires 1NF plus no partial dependency, meaning every non-key column depends on the whole composite key. 3NF requires 2NF plus no transitive dependency, so non-key columns depend only on the key. For example, storing city and zip_code together with an employee violates 3NF because city depends on zip_code.
BCNF (Boyce-Codd Normal Form) is a stricter version of 3NF that requires every determinant in a functional dependency to be a candidate key. 3NF allows a dependency A -> B when B is a prime attribute even if A is not a key, while BCNF does not. Every BCNF table is in 3NF, but not the other way around. The difference appears only with overlapping candidate keys.
Denormalization is deliberately adding redundant data or merging tables to reduce joins and speed up reads. It is used in read-heavy systems such as reporting, analytics and data warehouses, where query speed matters more than storage. The trade-off is slower writes and a risk of inconsistent data, so the redundant copies must be kept in sync.
ACID stands for Atomicity, Consistency, Isolation and Durability, the guarantees of a reliable transaction. Atomicity means all steps succeed or none do. Consistency means a transaction moves the database from one valid state to another. Isolation means concurrent transactions do not interfere with each other. Durability means committed data survives crashes, usually through a write-ahead log.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;WHERE filters individual rows before grouping and cannot use aggregate functions, while HAVING filters groups after GROUP BY and can use aggregates like COUNT() or SUM(). The logical order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. Put conditions in WHERE whenever possible, since it reduces rows early and is faster.
SELECT dept_id, COUNT(*) AS cnt
FROM employees
WHERE salary > 30000
GROUP BY dept_id
HAVING COUNT(*) > 5;GROUP BY collapses rows that share the same values in the specified columns into one group so you can apply aggregate functions such as COUNT, SUM, AVG, MIN and MAX to each group. Every selected column must either appear in GROUP BY or be inside an aggregate function. It is often combined with HAVING to filter the groups.
DELETE removes selected rows (or all rows) one by one, supports a WHERE clause, fires triggers and can be rolled back. TRUNCATE removes all rows quickly, has no WHERE, resets identity counters and is DDL, though some databases such as PostgreSQL and SQL Server allow rolling it back inside a transaction. DROP deletes the whole table, including its structure, indexes and privileges.
An index is a separate data structure, usually a B-tree, that lets the database find rows without scanning the whole table. It speeds up SELECT, WHERE, JOIN and ORDER BY on the indexed columns. The cost is extra storage and slower INSERT, UPDATE and DELETE, because the index must be maintained. Index columns that are frequently filtered or joined.
CREATE INDEX idx_emp_email ON employees(email);A clustered index defines the physical order of the rows in the table, so a table can have only one, and the leaf level is the data itself. A non-clustered index is a separate structure whose leaf entries point to the rows (or to the clustered key), and a table can have many. Range queries on the clustered key are very fast. In MySQL InnoDB, the primary key is the clustered index.
Constraints are rules applied to columns or tables to keep data valid and consistent. The common ones are PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK and DEFAULT. The database rejects any insert or update that breaks a constraint.
CREATE TABLE employees (
id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE NOT NULL,
age INT CHECK (age >= 18),
status VARCHAR(10) DEFAULT 'active'
);A view is a saved SELECT query that behaves like a virtual table, with no data of its own unless it is materialized. Views simplify complex queries, hide sensitive columns for security and provide a stable interface when the underlying schema changes. Simple views can be updated through, but views with joins or aggregates usually cannot.
CREATE VIEW active_employees AS
SELECT id, name FROM employees WHERE status = 'active';A stored procedure is a precompiled set of SQL statements saved in the database and called by name, which can take parameters and perform actions like inserts and transactions. A function must return a value, can be used inside a SELECT, and is generally restricted from changing data. Procedures are called with CALL or EXEC. Procedures reduce network traffic and centralize business logic.
A trigger is a special stored routine that runs automatically when an INSERT, UPDATE or DELETE happens on a table, either BEFORE or AFTER the event. Triggers are used for auditing, validation and keeping derived data in sync. Overusing them makes behavior hidden and hard to debug and can hurt performance.
CREATE TRIGGER trg_audit
AFTER DELETE ON employees
FOR EACH ROW
INSERT INTO audit_log(emp_id, action) VALUES (OLD.id, 'deleted');A subquery is a query nested inside another query, wrapped in parentheses, and used in SELECT, FROM, WHERE or HAVING. It can be scalar (one value), row, or table (many rows). A correlated subquery references columns of the outer query and runs once per outer row, so it can be slower than an equivalent join.
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);UNION combines the results of two queries and removes duplicate rows, while UNION ALL keeps all rows including duplicates. Because UNION must sort or hash to find duplicates, UNION ALL is faster and should be used when duplicates are impossible or acceptable. Both require the same number of columns with compatible data types.
The common approach is a subquery that takes the maximum salary below the overall maximum. Alternatives are LIMIT with OFFSET, or the DENSE_RANK() window function, which handles ties correctly and generalizes to the Nth highest.
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) t WHERE rnk = 2;Group by the columns that define a duplicate and keep only the groups with a count greater than one using HAVING. To see the full duplicate rows, join this result back to the table or use ROW_NUMBER() and filter where the number is above one. The same window approach can be used to delete duplicates while keeping one row.
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;SQL databases are relational, store data in tables with a fixed schema and use SQL and ACID transactions, while NoSQL databases use flexible models such as document, key-value, column-family or graph. SQL suits structured data and complex joins; NoSQL often favors horizontal scaling and changing schemas. Many modern NoSQL systems like MongoDB also support transactions, so the line is less strict than it once was.
All three are window functions that number rows within an ordered partition. ROW_NUMBER() gives unique sequential numbers even for ties, RANK() gives tied rows the same rank and then skips numbers (1, 1, 3), and DENSE_RANK() gives tied rows the same rank without gaps (1, 1, 2). Use DENSE_RANK() for Nth highest values and ROW_NUMBER() for deduplication or pagination.
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;Window functions perform a calculation across a set of rows related to the current row, defined by OVER (PARTITION BY ... ORDER BY ...), without collapsing those rows. GROUP BY returns one row per group, while a window function keeps every row and adds the computed value as a column. They are used for running totals, rankings, moving averages and comparisons with previous rows.
SELECT name, dept_id, salary,
AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;LAG() returns a column value from a previous row and LEAD() returns it from a following row, based on the window ordering, without a self join. They are used to compare a row with its neighbour, such as day-over-day change, time gaps between events or detecting a status change. You can pass an offset and a default value for rows that have no neighbour.
SELECT order_date, amount,
amount - LAG(amount, 1, 0) OVER (ORDER BY order_date) AS change
FROM daily_sales;A CTE (Common Table Expression) is a named temporary result set defined with WITH that exists only for the duration of one statement. It makes complex queries more readable, can be referenced several times and, unlike a subquery, can be recursive. Performance is usually similar to a subquery, though some databases materialize the CTE and others inline it.
WITH high_earners AS (
SELECT * FROM employees WHERE salary > 80000
)
SELECT dept_id, COUNT(*) FROM high_earners GROUP BY dept_id;A recursive CTE is a CTE that references itself, made of an anchor query that returns the starting rows and a recursive member that is repeatedly joined to the previous result until no new rows appear. It is the standard way to traverse hierarchies such as org charts, category trees or bill of materials. Always ensure a termination condition to avoid infinite loops.
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 1 AS lvl
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, o.lvl + 1
FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;Use a self join: join the employees table to itself, matching each employee's manager_id to the manager's id, then compare the two salaries in the WHERE clause. Give each copy of the table a distinct alias so the database can tell them apart.
SELECT e.name
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;Use a window function: rank the rows with DENSE_RANK() or ROW_NUMBER() partitioned by the group, then filter on that rank in an outer query, since window functions cannot be used directly in WHERE. Choose ROW_NUMBER() for exactly N rows and DENSE_RANK() when ties should all be included.
SELECT * FROM (
SELECT name, dept_id, salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees
) t
WHERE rnk <= 3;Number the rows inside each duplicate group with ROW_NUMBER() and delete every row whose number is greater than one. This works in a CTE in SQL Server and with a join or subquery on the primary key in MySQL and PostgreSQL. Always run it as a SELECT first and inside a transaction so you can verify before committing.
WITH d AS (
SELECT id, ROW_NUMBER() OVER (
PARTITION BY email ORDER BY id) AS rn
FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM d WHERE rn > 1);COUNT(*) counts all rows, including those with NULL values. COUNT(column) counts only rows where that column is not NULL. COUNT(DISTINCT column) counts the unique non-NULL values. This difference is a classic source of wrong totals, so always know whether the column can contain nulls.
SELECT COUNT(*), COUNT(phone), COUNT(DISTINCT city)
FROM customers;NULL means unknown, so any comparison with it using =, <> or < evaluates to unknown, not true, and the row is filtered out. You must use IS NULL or IS NOT NULL. This also explains why NOT IN against a subquery that returns even one NULL returns no rows at all, and why NULL values are ignored by most aggregates.
SELECT * FROM users WHERE phone IS NULL;IN checks a value against a list or subquery result, EXISTS checks whether a correlated subquery returns any row, and a JOIN combines columns from both tables. Modern optimizers often turn all three into similar plans, so check the execution plan. EXISTS stops at the first match and is NULL-safe, which makes it preferable to NOT IN for anti-joins.
SELECT c.name FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);Isolation levels control how much one transaction can see of another's uncommitted or concurrent changes. The SQL standard defines four: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE, from weakest to strongest. Higher levels prevent more anomalies but reduce concurrency. The default is READ COMMITTED in PostgreSQL, Oracle and SQL Server, and REPEATABLE READ in MySQL InnoDB.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;A dirty read means reading data another transaction has changed but not yet committed. A non-repeatable read means reading the same row twice in one transaction and getting different values because another transaction committed an update. A phantom read means re-running a range query and seeing new or missing rows because another transaction inserted or deleted them. Stricter isolation levels progressively prevent each one.
A deadlock occurs when two or more transactions each hold a lock the other needs, so none can proceed. The database detects the cycle and aborts one transaction as the victim, which the application should retry. To prevent deadlocks, access tables and rows in a consistent order, keep transactions short, avoid user interaction inside them, and index the columns used in updates so fewer rows are locked.
Pessimistic locking assumes conflicts are likely and locks the rows up front, for example with SELECT ... FOR UPDATE, so others must wait. Optimistic locking assumes conflicts are rare, takes no lock and instead checks at update time that the row has not changed, usually with a version column. Optimistic suits read-heavy workloads; pessimistic suits high contention.
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 7 AND version = 3;A composite index covers multiple columns in a defined order. The database can use it only for queries that filter on a leftmost prefix of those columns (the leftmost prefix rule), so an index on (last_name, first_name) helps a search on last_name but not one on first_name alone. Put the most selective, equality-filtered columns first and range columns last.
CREATE INDEX idx_name ON employees(last_name, first_name);A covering index contains every column a query needs, so the database can answer it from the index alone without reading the table rows. This avoids extra lookups and can make queries much faster. It is created by adding the selected columns to the index, either as key columns or with INCLUDE in PostgreSQL and SQL Server. The trade-off is a larger index.
CREATE INDEX idx_cover ON orders(customer_id) INCLUDE (total);The optimizer skips an index when it estimates a full scan is cheaper, for example when the query returns a large share of the table. Common causes are applying a function to the column (WHERE YEAR(created_at) = 2025), implicit type conversion, a leading wildcard in LIKE '%abc', breaking the leftmost prefix of a composite index, and outdated statistics. Rewrite the condition so the column stands alone.
-- sargable version
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';Run EXPLAIN (or EXPLAIN ANALYZE to actually execute and time it) to see how the database runs the query. Look for full table scans on large tables, expensive sorts, nested loops over big row sets, and large gaps between estimated and actual row counts. Then add or adjust indexes, rewrite the query, update statistics, and compare the plan again.
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;The N+1 problem happens when code runs one query to fetch a list and then one extra query per item to fetch related data, producing N+1 round trips. It is common with ORMs and lazy loading, and it slows pages dramatically as data grows. Fix it by fetching the data in one query with a JOIN, using eager loading, or batching with WHERE id IN (...).
Partitioning splits one large table into smaller pieces, such as by date range or hash, inside a single database server, which speeds up queries through partition pruning and eases maintenance. Sharding spreads data across multiple independent database servers, each holding a subset, to scale writes and storage horizontally. Sharding adds complexity for cross-shard joins, transactions and rebalancing.
A regular view stores only the query and runs it each time it is accessed, so it is always current. A materialized view stores the query result physically, which makes reads fast but means the data can be stale until it is refreshed. Use materialized views for expensive aggregations in reporting; they are supported natively in PostgreSQL and Oracle.
CREATE MATERIALIZED VIEW sales_summary AS
SELECT region, SUM(amount) AS total FROM sales GROUP BY region;
REFRESH MATERIALIZED VIEW sales_summary;SQL injection is an attack where untrusted input is concatenated into a SQL string, letting an attacker change the query, for example by entering ' OR '1'='1. The main defense is parameterized queries (prepared statements), which keep data separate from code. Also use least-privilege database accounts, validate input, and never build queries by string concatenation.
Unsafe: "SELECT * FROM users WHERE name = '" + input + "'"
Safe: "SELECT * FROM users WHERE name = ?" (bind input)CHAR(n) is fixed length and pads shorter values with spaces, while VARCHAR(n) stores only the characters used plus a small length overhead. Use CHAR for values of constant length like country codes and VARCHAR otherwise. In general pick the smallest suitable type, use DECIMAL rather than FLOAT for money, and use proper date types instead of strings.
The CAP theorem states that a distributed data store can guarantee only two of three properties during a network partition: Consistency, Availability and Partition tolerance. Since partitions cannot be avoided in practice, systems effectively choose between consistency and availability when one occurs. Traditional single-node RDBMSs sidestep this, while distributed systems like Cassandra favor availability and others like HBase favor consistency.
Read the DBMS and SQL revision notes, then come back and test yourself with these questions.