Rapid Revision · Technical
Every fresher round asks DBMS, and most of it is ten questions recycled. Here they are with answers you can say in one breath.
If you read nothing else tonight, read these 6 lines.
12 cards, each one idea: what it is, a worked example, and the trap to dodge.
A DBMS is any software that stores and manages data. An RDBMS stores data in related tables with rows and columns, enforces keys and integrity constraints, and supports SQL. MySQL, PostgreSQL and Oracle are RDBMSs; a file-based store like a simple key-value system is only a DBMS.
Candidate key: any minimal set of columns that uniquely identifies a row. Primary key: the chosen candidate key, unique and not null. Alternate keys: the remaining candidates. Foreign key: a column referencing another table's primary key. Composite key: a key made of multiple columns.
Trap: Probe: can a primary key be null? Never. Can a foreign key? Yes, unless constrained.
Normalization splits tables to remove redundancy and anomalies. 1NF: atomic values, no repeating groups. 2NF: 1NF plus no partial dependency on part of a composite key. 3NF: 2NF plus no transitive dependency (non-key depending on non-key). BCNF: every determinant is a candidate key.
Student(course, instructor) where instructor depends on course but course is not a key: fails 3NF/BCNF, split it.
Trap: Expect: why would you DENORMALIZE? Read speed; joins are expensive at scale.
Atomicity: a transaction happens fully or not at all. Consistency: it moves the database between valid states. Isolation: concurrent transactions do not see each other's half-done work. Durability: once committed, it survives crashes.
Bank transfer: debit and credit commit together or both roll back.
INNER JOIN returns only matching rows. LEFT JOIN keeps all left rows, filling nulls when no match; RIGHT JOIN mirrors it. FULL OUTER JOIN keeps both sides. CROSS JOIN pairs every row with every row. Self join joins a table to itself with aliases.
Employees with managers: self join employees ON e.manager_id = m.id.
Trap: Row-count questions: LEFT JOIN never returns fewer rows than the left table has (with 1:1 matches).
An index is a sorted lookup structure, usually a B+ tree, that lets the database find rows without scanning the table. A clustered index defines the physical order of rows, so a table can have only one; non-clustered indexes are separate structures pointing at rows, and you can have many.
Trap: Trade-off probe: indexes speed up SELECT but slow INSERT, UPDATE, DELETE and cost space.
A transaction is a unit of work with ACID guarantees. Isolation levels trade correctness for speed: read uncommitted (dirty reads possible), read committed, repeatable read (stops non-repeatable reads), serializable (full isolation, slowest). Problems to name: dirty read, non-repeatable read, phantom read.
Trap: Match the anomaly to the level that stops it; phantoms need serializable (or MVCC tricks).
SQL databases have fixed schemas, relations, joins and strong ACID; they scale vertically first. NoSQL (document, key-value, column, graph) is schema-flexible, built for horizontal scale, and often relaxes consistency for availability. Choose SQL for transactional integrity, NoSQL for huge, flexible, fast-changing data.
Payments ledger: SQL. Product catalog with varied attributes: document store.
DELETE removes selected rows, is logged and can be rolled back. TRUNCATE removes ALL rows quickly with minimal logging and usually cannot be filtered. DROP removes the table structure itself. WHERE filters rows before GROUP BY; HAVING filters the grouped results.
SELECT dept, COUNT(*) FROM emp GROUP BY dept HAVING COUNT(*) > 5;
Trap: HAVING without GROUP BY, or aggregate conditions inside WHERE, are the screener traps.
A view is a saved query that acts like a virtual table. A stored procedure is precompiled SQL logic you call by name. A trigger runs automatically on an event like INSERT or UPDATE. Views simplify and secure access; procedures centralize logic; triggers enforce rules.
Trap: Probe: can you always update through a view? Only simple single-table views without aggregates.
The most-asked SQL screener. Know two shapes: LIMIT/OFFSET on a sorted DISTINCT list, and the subquery counting distinct higher salaries. Also be ready for find-duplicates: GROUP BY the column and keep HAVING COUNT(*) > 1.
2nd highest: SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp);
Trap: Duplicates break naive LIMIT solutions; say DISTINCT out loud before the interviewer does.
Entities become tables, attributes become columns, and the entity's key becomes the primary key. 1:N relationships put a foreign key on the N side. M:N relationships need a junction table holding both foreign keys. Weak entities include the owner's key in their primary key.
Students and courses (M:N): enrollment(student_id, course_id) as the junction table.
A recap is not practice. These are the creators we rate for real depth on DBMS; full credit to each.
Each recap takes 3 minutes; the full set covers everything the first round tests. And when the test is cleared, your resume takes the next screen.
Original content by OptiResume; facts and formulas are common knowledge, the wording is ours. Go-deeper links go to creators we rate; we are not affiliated with them.