Loading...
Loading...
Loading...
Core CS and SQL interview guide
Keys, normalisation, transactions, ACID, indexing and concurrency control. Every answer is short enough to say aloud, and every question links to the lesson that teaches it.
60 questionsLast updated
A database is an organised collection of related data. A DBMS is the software that stores it and controls access, providing a query language, constraints, transactions, concurrency control, recovery and security. MySQL and PostgreSQL are examples.
Lesson: DBMS vs file system and types of keysFiles lead to redundancy and inconsistency, lost updates under concurrent access, rules hidden in code, half-done updates after crashes and coarse security. A DBMS solves these with normalised tables, transactions, locks, constraints, recovery logs and fine-grained permissions.
Follow-up: Is a DBMS always better than files? No. For a tiny single-user tool, a config file or CSV is simpler. A DBMS pays off when many users share data that must stay correct, searchable and secure.
Common mistake: Saying "a DBMS is faster than files". Speed is not the main reason. The wins are less redundancy, integrity rules, concurrency control, crash recovery and security.
Lesson: DBMS vs file system and types of keysStoring the same fact in several places. It wastes space, and worse, the copies can drift apart when only one is updated, which is inconsistency.
Lesson: DBMS vs file system and types of keysThe DBA designs and maintains schemas, manages users and permissions, plans backups and recovery, monitors performance and tunes indexes and configuration.
Lesson: DBMS vs file system and types of keysThe external level has user-specific views, the conceptual level describes all tables and relationships, and the internal level describes physical storage such as files and indexes. Mappings between the levels give data independence.
Follow-up: Give a real example of each level. External: a view that shows a clerk only the student name and fee. Conceptual: the student and fee tables with their keys. Internal: the data files and the index on roll_no.
Common mistake: Mixing the three levels with application layers (UI, logic, database). The levels describe three views of the data inside the DBMS.
Lesson: DBMS vs file system and types of keysThe ability to change the schema at one level without changing the level above. Physical independence: change storage without changing tables. Logical independence: change tables without changing views and applications.
Common mistake: Defining it as "data does not depend on the program". Say what can change (storage, or the tables) without touching what (the tables, or the views and programs).
Lesson: DBMS vs file system and types of keysDDL defines structure (CREATE, ALTER, DROP), DML works with data (SELECT, INSERT, UPDATE, DELETE), DCL controls access (GRANT, REVOKE) and TCL controls transactions (COMMIT, ROLLBACK, SAVEPOINT).
Lesson: DBMS vs file system and types of keysA set of one or more columns used to identify rows uniquely or to link tables. Keys enforce uniqueness and relationships.
Lesson: DBMS vs file system and types of keysA minimal super key: unique, and removing any column makes it non-unique. A table can have several, such as roll_no, Aadhaar number and email.
Follow-up: Can a table have more than one candidate key? Yes. A student table can have roll_no and college email, each unique. One becomes the primary key and the other becomes an alternate key.
Common mistake: Saying "a candidate key is any unique column set". That is a super key. A candidate key must also be minimal.
Lesson: DBMS vs file system and types of keysThe candidate key chosen to identify rows. It must be unique and NOT NULL, and a table has only one, although it may span several columns.
Follow-up: Can a primary key be composite, or contain NULL? Composite, yes, like (roll_no, subject_code). NULL, never: every primary key column is NOT NULL.
Common mistake: Saying "a primary key is just a unique column". It is unique AND NOT NULL, and a table has only one.
Lesson: DBMS vs file system and types of keysA candidate key that was not chosen as the primary key. It is usually enforced with a UNIQUE constraint.
Lesson: DBMS vs file system and types of keysA column, or set of columns, in one table that refers to the primary or a unique key of another table. It ensures every reference points to a row that exists.
Lesson: DBMS vs file system and types of keysThe rule that a foreign key value must match an existing parent key or be NULL. The DBMS rejects inserts and updates that break it and controls deletes with ON DELETE rules.
Common mistake: Saying "referential integrity means the data is correct". It means each foreign key value matches an existing parent key, or is NULL.
Lesson: DBMS vs file system and types of keysA table has one primary key but can have many unique keys. The primary key cannot be NULL, while a unique key can, and in MySQL several rows may have NULL in a unique column. InnoDB also stores rows in primary-key order.
Follow-up: How many NULLs can a UNIQUE column hold in MySQL? Many. MySQL treats each NULL as different, so several rows can have NULL. SQL Server allows only one.
Common mistake: Claiming a unique key cannot be NULL. Only the primary key forbids NULL; a unique key allows it.
Lesson: DBMS vs file system and types of keysYes, unless the column is declared NOT NULL. NULL means "no related row", for example a student not yet allotted a hostel room.
Follow-up: How would you forbid NULL in a foreign key? Declare the column NOT NULL. Then every row must point to a parent row, for example every application must have a student.
Lesson: DBMS vs file system and types of keysYes, a self-referencing foreign key. For example, employee.manager_id refers to employee.emp_id.
Lesson: DBMS vs file system and types of keysIt depends on the ON DELETE rule: RESTRICT or NO ACTION rejects the delete, CASCADE deletes the child rows too, and SET NULL sets the foreign key to NULL.
Lesson: DBMS vs file system and types of keysA natural key comes from real data, such as PAN or email. A surrogate key is an artificial id, such as an auto-increment number, used because natural keys can change, be long or be sensitive.
Follow-up: When would you still choose a natural key? For a short, stable, meaningful code that never changes, like a country code or a subject code. If values can change or are long, use a surrogate id.
Lesson: DBMS vs file system and types of keysEvery subset containing A is a super key, which is 2³ = 8.
Lesson: DBMS vs file system and types of keysFrom the meaning of the data: find columns or combinations that can never repeat, then keep only the minimal ones. With functional dependencies, a set is a candidate key if its closure contains every attribute and no smaller subset's does.
Lesson: DBMS vs file system and types of keysAn entity-relationship diagram is a high-level picture of the data: the entities a system stores, their attributes and the relationships between them. It is drawn before creating tables.
Follow-up: Why draw it before creating tables? A picture is cheap to change and a live schema is not. The diagram lets everyone agree on entities, relationships and rules before any SQL is written.
Lesson: ER diagramsAn entity is one real thing, such as the student Priya. An entity set is the collection of all entities of that type, such as all students, and becomes a table.
Lesson: ER diagramsSimple, composite, single-valued, multivalued, derived and key attributes. For example, roll_no is a simple key, name (first, last) is composite, phones are multivalued and age is derived.
Lesson: ER diagramsAn attribute made of smaller parts, such as address (street, city, pin). It is stored as one column per part.
Follow-up: When would you keep a composite attribute as one column? When you never search or sort by its parts, such as a free-text remark. If you filter by city or pin code, store the parts as separate columns.
Lesson: ER diagramsAn attribute with several values per entity, such as a student's skills. It goes into a separate table with the owner's key and the value, both forming the primary key.
Follow-up: Why not keep the values in one comma-separated column? You cannot index, search or constrain a single value, and changing one value means rewriting the whole text. A separate table has one row per value.
Common mistake: Suggesting columns phone1, phone2, phone3. That is a repeating group: it breaks 1NF and cannot hold a fourth phone number. Use a separate table.
Lesson: ER diagramsA relationship is an association between entities, such as Priya applies to the Infosys drive. A relationship set is all such associations of one type.
Lesson: ER diagramsThe number of entity sets it connects: unary (recursive, such as employee manages employee), binary (two, the most common) or ternary (three, such as judge scores team in event).
Lesson: ER diagrams1:1, a person and their passport. 1:N, a company and its drives. M:N, students and the courses they register for.
Lesson: ER diagramsParticipation says whether every entity must take part in a relationship. Total participation (double line) means all must, such as every drive belonging to a company; partial means some may not, such as students who never apply.
Follow-up: How does total participation show up in tables? As a NOT NULL foreign key. If every drive must belong to a company, drive.company_id is NOT NULL.
Lesson: ER diagramsAn entity that cannot be uniquely identified by its own attributes and depends on an owner entity, such as instalments of a fee or rounds of a drive. Its key is the owner's key plus its partial key.
Follow-up: What is the primary key of a weak entity's table? The owner's primary key plus the partial key, for example (drive_id, round_no) for round.
Common mistake: Saying a weak entity is "less important" or has few attributes. It is weak because it cannot be identified without its owner.
Lesson: ER diagramsBecause it cannot exist or be identified without its owner, every weak entity must be linked to an owner through the identifying relationship.
Lesson: ER diagramsPut the primary key of the "one" side as a foreign key in the "many" side's table. If the many side has total participation, make that column NOT NULL.
Follow-up: What if the N side is optional? Then the foreign key may be NULL (partial participation). If every child must have a parent, make it NOT NULL.
Lesson: ER diagramsPut a foreign key with a UNIQUE constraint on one side, preferably the side with total participation to avoid NULLs. If both sides are total and always used together, they can be merged into one table.
Follow-up: Which side gets the foreign key? The side with total participation, plus a UNIQUE constraint so two rows cannot share the same partner.
Lesson: ER diagramsCreate a table with the owner's primary key as a foreign key plus the partial key and other attributes; the primary key is (owner key, partial key), usually with ON DELETE CASCADE.
Lesson: ER diagramsGeneralisation combines entities with common attributes into a higher-level entity, such as Car and Bike into Vehicle. Specialisation is the reverse, splitting an entity into subtypes such as Employee into Manager and Engineer; both form an ISA hierarchy.
Lesson: ER diagramsOne table per subtype that includes the parent's attributes, one parent table plus subtype tables that share the parent's key, or a single table with a type column and nullable subtype columns. The choice depends on overlap and query patterns.
Lesson: ER diagramsTreating a relationship as a higher-level entity so it can take part in another relationship. For example, the relationship student works_on project can itself be monitored by a guide.
Lesson: ER diagramsWhen the fact needs all three entities at once, such as a judge giving a score to a team in a particular event. Three binary relationships cannot record which judge scored which team in which event.
Lesson: ER diagramsThe ER model is a conceptual design tool with entities and relationships drawn as diagrams. The relational model is the implementation with tables, keys and constraints, and ER diagrams are mapped into it.
Lesson: ER diagramsA recursive M:N relationship on User called follows, mapped to a table follow(follower_id, followee_id) with both as foreign keys to user and together as the primary key.
Lesson: ER diagramsNormalisation is organising tables so that each fact is stored once, by splitting tables according to functional dependencies. It removes redundancy and the insert, update and delete anomalies that come with it.
Follow-up: Does normalisation make queries faster? Not always. It removes redundancy and anomalies, but reads then need joins. That is why reporting tables are sometimes denormalised on purpose.
Common mistake: Defining normalisation as "splitting a table into smaller tables". Splitting is the method; the goal is to store each fact once so updates, inserts and deletes stay safe.
Lesson: Normalisation: functional dependencies, 1NF and 2NFWhen one fact is stored in many rows, changing it requires updating all of them; missing any leaves the data inconsistent. For example, a faculty name repeated for every student in a course.
Follow-up: Give a one-line example. The faculty of CS301 is stored in 40 enrolment rows; changing it in 39 of them leaves the table with two different faculties.
Lesson: Normalisation: functional dependencies, 1NF and 2NFBeing unable to store a fact without another unrelated fact, such as not being able to add a new course until a student enrols, because the key needs a roll number.
Lesson: Normalisation: functional dependencies, 1NF and 2NFLosing a fact unintentionally when deleting another, such as deleting the last enrolment of a course and losing the course's details.
Lesson: Normalisation: functional dependencies, 1NF and 2NFOne where the right side is part of the left side, such as {roll_no, name} → name. It always holds and gives no information.
Lesson: Normalisation: functional dependencies, 1NF and 2NFReflexivity (if Y ⊆ X then X → Y), augmentation (if X → Y then XZ → YZ) and transitivity (if X → Y and Y → Z then X → Z). Union, decomposition and pseudo-transitivity follow from them.
Lesson: Normalisation: functional dependencies, 1NF and 2NFX is a super key if X⁺ contains all attributes, and a candidate key if no proper subset of X also has that property. Start from attributes that appear on no right side, since they must be in every key.
Lesson: Normalisation: functional dependencies, 1NF and 2NFF⁺ is the set of all FDs that can be derived from F using Armstrong's axioms. It is usually too large to list, which is why attribute closure is used instead.
Lesson: Normalisation: functional dependencies, 1NF and 2NFA simplified equivalent set of FDs with single attributes on the right, no extra attributes on the left and no redundant FDs. It is used as the starting point for 3NF synthesis.
Lesson: Normalisation: functional dependencies, 1NF and 2NFA table is in first normal form when every cell holds a single atomic value and there are no repeating groups such as phone1, phone2, phone3.
Follow-up: How do you fix a phone1, phone2, phone3 layout? Move the phones to student_phone(roll_no, phone) with both columns as the key: one row per phone, any number of phones.
Common mistake: Saying "1NF means the table has a primary key". 1NF is about atomic cells and no repeating groups of columns.
Lesson: Normalisation: functional dependencies, 1NF and 2NFMove the values into a separate table with one row per value, keyed by the original key plus the value, such as student_phone(roll_no, phone).
Lesson: Normalisation: functional dependencies, 1NF and 2NFA table is in second normal form when it is in 1NF and no non-prime attribute depends on only part of a candidate key, that is, there are no partial dependencies.
Follow-up: Give an example of a table in 1NF but not 2NF. marks(roll_no, subject_code, student_name, score) with key (roll_no, subject_code): student_name depends on roll_no alone.
Common mistake: Stating 2NF without the 1NF condition or without the word "non-prime". The rule: in 1NF and no non-prime attribute depends on part of a candidate key.
Lesson: Normalisation: functional dependencies, 1NF and 2NFA non-prime attribute that depends on a proper subset of a composite candidate key. In marks(roll_no, subject_code, student_name), student_name depends only on roll_no.
Follow-up: Why does a partial dependency cause update anomalies? The student's name repeats in every marks row for that student, so changing it means updating many rows, and missing one leaves two names.
Lesson: Normalisation: functional dependencies, 1NF and 2NFFor each partial dependency, create a table with the part of the key and the attributes that depend on it, and keep a table with the full key and attributes that need all of it.
Follow-up: How do you check that the decomposition lost nothing? Join the pieces on the common key and compare with the original: the same rows must come back. That is a lossless-join decomposition.
Lesson: Normalisation: functional dependencies, 1NF and 2NFA decomposition where joining the pieces gives back exactly the original rows, with no missing and no extra rows. It holds when the common attributes form a key of at least one piece.
Follow-up: When is a two-table split lossless? When the common attributes form a key of at least one of the two tables.
Lesson: Normalisation: functional dependencies, 1NF and 2NFNo. 2NF removes partial dependencies, but transitive dependencies such as dept_id → dept_name in an employee table still cause redundancy, which 3NF removes.
Lesson: Normalisation: functional dependencies, 1NF and 2NFAB and BC. {A, B}⁺ gives C, and {B, C}⁺ gives A through C → A; no single attribute determines everything.
Lesson: Normalisation: functional dependencies, 1NF and 2NFData can show that an FD does not hold, if two rows have the same X and different Y. It cannot prove an FD holds; that comes from business rules.
Lesson: Normalisation: functional dependencies, 1NF and 2NFWith key (order_id, product_id): product_name depends on product_id and customer_name on order_id, both partial. Split into product(product_id, product_name), orders(order_id, customer_name) and order_item(order_id, product_id, quantity).
Common mistake: Splitting the table before naming the FDs. Write order_id → customer_name, product_id → product_name and (order_id, product_id) → quantity first.
Lesson: Normalisation: functional dependencies, 1NF and 2NFA table is in third normal form when it is in 2NF and has no transitive dependencies. Formally, for every non-trivial FD X → A, either X is a super key or A is a prime attribute.
Follow-up: Which two conditions allow an FD in 3NF? For X → A, either X is a super key or A is a prime attribute. If neither holds, the FD is a transitive dependency.
Common mistake: Saying "3NF means no dependencies between columns". Dependencies on a key are fine; only non-key to non-key dependencies are the problem.
Lesson: Normalisation: 3NF and BCNFEach lesson has quizzes, practice and more interview questions on the same topic.
Open the first lesson