Practice: DBMS mixed round and SQL drill
60 min · Advanced
Need help? Ask a tutor on WhatsApp (opens in a new tab)A technical round does not stay on one topic: a keys question leads to normalisation, then an index, then a query. This set drills that mix under time, so switching between theory and SQL feels normal on the day.
Rapid-fire DBMS round
The mixed round in the Interview tab has 28 questions that connect topics: design a schema, fix a table, write a query, explain a failure. Give yourself 60 to 90 seconds each and answer aloud in a few sentences; if you need longer, mark the topic for revision.
Interviewers chain topics: "Design the tables" leads to "Which normal form?" and then to "Which index?". Practise the chain, not single answers. Score yourself like this:
- 1 point for a correct, clear answer within about 90 seconds.
- 0.5 for correct but slow or vague; 0 for wrong.
- Write down every question below 1 point and the lesson it belongs to.
- Repeat the round after revising; aim for 22 or more out of 28.
Order of execution
Answer a "design the tables" question
Tap the steps in the order a strong answer follows.
Normalise two tables
Two tables, each with its FDs: find the key, name the highest normal form, decompose to BCNF and check the result. The program below confirms each step.
Example 1 · Checking both normalisation drills
from itertools import combinations
def closure(attrs, fds): result = set(attrs) changed = True while changed: changed = False for lhs, rhs in fds: if set(lhs) <= result and not set(rhs) <= result: result |= set(rhs) changed = True return result
def candidate_keys(attrs, fds): keys = [] for size in range(1, len(attrs) + 1): for subset in combinations(attrs, size): # A key determines every attribute, and no smaller key sits inside it. if closure(subset, fds) == set(attrs) and not any(set(k) <= set(subset) for k in keys): keys.append("".join(subset)) return keys
def highest_normal_form(attrs, fds): keys = candidate_keys(attrs, fds) prime = set("".join(keys)) # attributes that sit in some key is_2nf = is_3nf = is_bcnf = True for lhs, rhs in fds: for a in rhs: if a in lhs: # trivial dependency continue super_key = closure(lhs, fds) == set(attrs) if not super_key: is_bcnf = False if a not in prime: is_3nf = False if a not in prime and any(set(lhs) < set(k) for k in keys): is_2nf = False # part of a key decides a non-key attribute if is_bcnf: nf = "BCNF" elif is_3nf: nf = "3NF (not BCNF)" elif is_2nf: nf = "2NF (not 3NF)" else: nf = "1NF (not 2NF)" return f"keys {{{', '.join(keys)}}} -> {nf}"
def check(label, attrs, fds): print(f"{label}: {highest_normal_form(attrs, fds)}")
check("course_registration", "RNBHCTKG", [("R", "NB"), ("B", "H"), ("C", "TK"), ("RC", "G")])check(" student(R, N, B)", "RNB", [("R", "NB")])check(" branch(B, H)", "BH", [("B", "H")])check(" course(C, T, K)", "CTK", [("C", "TK")])check(" registration(R, C, G)", "RCG", [("RC", "G")])check("hostel_stay", "RMLWPF", [("R", "MP"), ("M", "L"), ("L", "W"), ("P", "F")])check(" student(R, M, P)", "RMP", [("R", "MP")])check(" room(M, L)", "ML", [("M", "L")])check(" block(L, W)", "LW", [("L", "W")])check(" fee_plan(P, F)", "PF", [("P", "F")])course_registration: keys {RC} -> 1NF (not 2NF)
student(R, N, B): keys {R} -> BCNF
branch(B, H): keys {B} -> BCNF
course(C, T, K): keys {C} -> BCNF
registration(R, C, G): keys {RC} -> BCNF
hostel_stay: keys {R} -> 2NF (not 3NF)
student(R, M, P): keys {R} -> BCNF
room(M, L): keys {M} -> BCNF
block(L, W): keys {L} -> BCNF
fee_plan(P, F): keys {P} -> BCNFWhat this code does
- course_registration has key RC and partial dependencies (R → N, B and C → T, K), so it is only in 1NF. hostel_stay has the single key R, so it is in 2NF, but M → L → W and P → F are transitive.
- Every decomposed table reaches BCNF, each original FD sits inside one table, and every table shares a key with another, so both decompositions are lossless and dependency-preserving.
Table 1: course_registration(R = roll_no, N = name, B = branch, H = hod, C = course_id, T = title, K = credits, G = grade) with R → N, B; B → H; C → T, K; RC → G. Table 2: hostel_stay(R = roll_no, M = room_no, L = block, W = warden, P = fee_plan, F = plan_fee) with R → M, P; M → L; L → W; P → F.
Write six interview SQL queries
The SQL drill uses a placement-cell schema: student, drive and application. Write each query on paper first and predict its rows; the example shows the data so you can check.
Example 2 · The placement-cell data for the SQL drill
Runs in: MySQL 8
CREATE TABLE student (roll_no VARCHAR(8) PRIMARY KEY, name VARCHAR(20), branch CHAR(3), cgpa DECIMAL(3,1));CREATE TABLE drive (drive_id INT PRIMARY KEY, company VARCHAR(20), ctc_lpa DECIMAL(4,1));CREATE TABLE application (roll_no VARCHAR(8), drive_id INT, status VARCHAR(10), PRIMARY KEY (roll_no, drive_id), FOREIGN KEY (roll_no) REFERENCES student(roll_no), FOREIGN KEY (drive_id) REFERENCES drive(drive_id));INSERT INTO student VALUES ('CS01', 'Priya', 'CSE', 9.1), ('CS02', 'Rahul', 'CSE', 7.4), ('EC01', 'Arun', 'ECE', 8.2), ('EC02', 'Meena', 'ECE', 6.9), ('ME01', 'Kiran', 'MEC', 8.8);INSERT INTO drive VALUES (1, 'Mintech', 3.6), (2, 'Zeno', 6.0), (3, 'Arcway', 32.0);INSERT INTO application VALUES ('CS01', 2, 'Selected'), ('CS01', 3, 'Rejected'), ('CS02', 1, 'Selected'), ('EC01', 1, 'Selected'), ('EC01', 2, 'Applied'), ('ME01', 1, 'Rejected');SELECT s.roll_no, s.name, s.branch, s.cgpa, d.company, a.statusFROM student sLEFT JOIN application a ON a.roll_no = s.roll_noLEFT JOIN drive d ON d.drive_id = a.drive_idORDER BY s.roll_no, d.company+---------+-------+--------+------+---------+----------+ | roll_no | name | branch | cgpa | company | status | +---------+-------+--------+------+---------+----------+ | CS01 | Priya | CSE | 9.1 | Arcway | Rejected | | CS01 | Priya | CSE | 9.1 | Zeno | Selected | | CS02 | Rahul | CSE | 7.4 | Mintech | Selected | | EC01 | Arun | ECE | 8.2 | Mintech | Selected | | EC01 | Arun | ECE | 8.2 | Zeno | Applied | | EC02 | Meena | ECE | 6.9 | NULL | NULL | | ME01 | Kiran | MEC | 8.8 | Mintech | Rejected | +---------+-------+--------+------+---------+----------+
What this code does
- The LEFT JOINs keep Meena (EC02), who has not applied anywhere, with NULLs in the drive columns.
- Use this table to predict the answers to the six Practice queries before running them.
The schema is student(roll_no, name, branch, cgpa), drive(drive_id, company, ctc_lpa) and application(roll_no, drive_id, status). The Practice tab has six queries to write, from a simple filter to a correlated subquery, and each solution shows its output for comparison. Revise: Classic SQL interview queries (opens in a new tab).
Predict the output
Predict the rows
Work out the result from the data before you pick.
Round 1 of 3
What does this query return?
Fix wrong queries
Query-fixing questions show SQL that runs but gives the wrong answer, or does not run at all. The example shows five classic faults with their fixes.
Example 3 · Five classic query faults, fixed
1. SELECT branch, COUNT(*) FROM student WHERE COUNT(*) > 1 GROUP BY branch;2. SELECT name FROM student WHERE roll_no NOT IN (SELECT roll_no FROM application); -- (imagine application.roll_no can contain NULL)3. SELECT s.name, a.status FROM student s JOIN application a ON a.roll_no = s.roll_no; -- meant to list every student, applied or not4. SELECT cgpa FROM student ORDER BY cgpa DESC LIMIT 1 OFFSET 1; -- meant: 2nd highest distinct cgpa, cgpa values may repeat5. SELECT branch, name, MAX(cgpa) FROM student GROUP BY branch;1. Error 1111: aggregate in WHERE -> GROUP BY branch HAVING COUNT(*) > 1 2. If the subquery returns any NULL, NOT IN returns no rows -> WHERE NOT EXISTS (SELECT 1 FROM application a WHERE a.roll_no = s.roll_no) 3. Inner join drops students with no application -> LEFT JOIN 4. Duplicates take two positions -> SELECT DISTINCT cgpa ... LIMIT 1 OFFSET 1 5. name is not grouped (rejected under ONLY_FULL_GROUP_BY) -> join MAX(cgpa) per branch back to student, or RANK() OVER (PARTITION BY branch ...)
What this code does
- Faults 2 and 3 do not raise errors; they silently return the wrong rows, which is why interviewers like them.
- When you fix a query, say what it returned before and why, not only the new SQL.
The usual faults are aggregates in WHERE, a missing DISTINCT, NOT IN with NULLs, inner joins that drop rows, and non-grouped columns in SELECT. For each broken query, say what it returns now, why, and the fix. The Practice tab has more to try.
Fix the error
Fix the query error
Read the real MySQL error, then pick the line that fixes it.
Round 1 of 2
Show each student's roll number, name and application status.
Error
ERROR 1052 (23000): Column 'roll_no' in field list is ambiguous
Self-scoring sheet and revision map
Scoring by part shows where to revise: a good total can hide one weak topic that an interviewer will find. Record each part's score from the sheet in the Summary tab.
If the SQL part is weak, revise the Classic SQL interview queries lesson, Joins practice (opens in a new tab) and Grouping practice (opens in a new tab). If theory is weak, reread the Summary tab of the matching lesson in this module, then repeat the mixed round the next day.
What's next
You finished the preview of DBMS
Python Programming has 21 sections, 110 modules and 609 lessons. An account opens every lesson and saves your progress.
Next step
Check your understanding
12 quick questions