This page needs a newer browser to run its tools. You can still read everything here.

Database Management Systems (DBMS) Module 16

GATE and interview readiness: questions

Questions on GATE and interview readiness: short warm-ups, then what each harder question tests, the first step of a good answer and its follow-ups.

8 questions: 3 warm-ups and 5 with model answers · Levels: Fresher to senior

Warm-up questions

Short questions with short answers, each answered in full.

Fresher

Under negative marking of one third, when does answering an MCQ you are unsure of raise your expected score?

Answered in the lesson GATE databases: syllabus map and strategy
Fresher

In the DA databases syllabus, does "normalization" mean normal forms?

Answered in the lesson GATE databases: syllabus map and strategy
Fresher

What is the difference between UNION and UNION ALL?

Answered in the lesson DBMS interview questions explained properly

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Fresher

In timed database sets you leave every 2-mark MCQ blank unless you are sure of it. Your log shows that when you narrow such a question to two options, you pick the right one 55 % of the time. Should you change the habit? Estimate what it is worth over the 8 such questions a typical set gives you.
What it tests

Expected marks under negative marking, turning a measured accuracy into a decision, and judging how far one estimate can be trusted.

First step

Write down the value of one answer: a 2-mark MCQ earns 2 marks with probability p and costs 2/3 of a mark otherwise, so it is worth 2(4p − 1)/3 on average. Put in p = 0.55 and compare the result with the 0 that a blank scores.
Follow-ups
- Your two-option accuracy turns out to be 30 %. Does the advice change?
- How would you measure that accuracy honestly?
- Does the same reasoning apply to MSQs, which have no negative marking and no partial marks?
From the lesson GATE databases: syllabus map and strategy
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; nothing renews unless you choose to. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher

Six weeks before the exam, your mistake log shows that most of the database marks you lose come from normal forms and serializability questions, while you almost never miss an SQL output question. Plan the remaining database revision with spaced reviews and timed sets, and defend how you split the time.
What it tests

Turning a mistake log into a plan, choosing practice testing and spacing over rereading, and pacing timed sets from the paper's marks and minutes.

First step

Start from the log, not from the syllabus order. Count the lost marks by topic and by cause: not known, misread, a slip, or time. The topics that lose the most marks get most of the new study time, and the cause decides what kind of practice each one gets.
Follow-ups
- Your log says you lose marks mostly by running out of time, not by not knowing. What changes?
- How will you know by the third week whether the plan is working?
- A week is lost to illness. What do you drop, and what do you keep?
From the lesson GATE databases: syllabus map and strategy
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

On MySQL with InnoDB, SELECT * FROM orders WHERE customer_id = ? uses a secondary index on customer_id, returns 2,000 rows and takes 400 ms, while the same query selecting only order_id, the primary key, takes 5 ms. Explain the gap, give two fixes, and say how PostgreSQL would differ.
What it tests

What an InnoDB secondary index entry holds, the cost of the second lookup per row, covering indexes, and the heap and visibility map in PostgreSQL.

First step

Both forms read the same 2,000 entries of the same index, so the 395 ms must come from what happens after each entry is found. Start from what an index entry stores, and count the B-tree searches each form of the query needs per row.
Follow-ups
- Which fix would you try first, and what does each one cost?
- The table's primary key is a random UUID. Does that change anything?
- How would you confirm the diagnosis before changing anything?
- What would PostgreSQL do with the same index and query?
From the lesson DBMS interview questions explained properly
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

Two requests withdraw 300 and 500 from a wallet holding 1,000 at the same moment. Each runs BEGIN, reads the balance, computes the new balance in the application, writes it with UPDATE and commits. At the default isolation levels of PostgreSQL and MySQL InnoDB, can money be lost? How do you fix it?
What it tests

Lost updates in a read-modify-write, what the default isolation levels of PostgreSQL and InnoDB really do, and choosing between atomic updates, row locks and retries.

First step

Write the interleaving first: both requests read 1,000 before either writes, so one computes 700 and the other 500. Then decide, for each engine's default isolation level, what the second UPDATE sees when it runs: the snapshot of its own transaction, or the latest committed row.
Follow-ups
- Would switching PostgreSQL to REPEATABLE READ fix it, and what must the application add?
- Which fix would you choose for a payments service, and why?
- The balance must never go below zero. How do you guarantee it?
From the lesson DBMS interview questions explained properly
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

Design the tables for a ticketing site that holds a chosen seat for 10 minutes while the buyer pays. Two people must never hold or buy the same seat, and holds must expire without a cleanup job. Walk through keys, constraints and indexes, and say what happens when two requests claim the same seat at once.
What it tests

Running a schema-design round in order, using keys and constraints as guarantees rather than application checks, and reasoning about concurrent claims under real isolation behaviour.

First step

State the invariants before drawing any table: a seat is free, held until a time, or sold; at most one live hold per seat; at most one sale per seat. Then, for each invariant, ask which feature of the database can enforce it, so that no application check has to.
Follow-ups
- How would you enforce the hold rule declaratively in PostgreSQL?
- A popular show opens and 5,000 requests a second hit 200 seats. Where does it hurt?
- The payment succeeds after the hold has expired. What do you do?
- How do you show a seat map without locking anything?
From the lesson DBMS interview questions explained properly
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes

All Database Management Systems (DBMS) interview questions · The Database Management Systems (DBMS) track

Warm-up questions

Under negative marking of one third, when does answering an MCQ you are unsure of raise your expected score?

In the DA databases syllabus, does "normalization" mean normal forms?

What is the difference between UNION and UNION ALL?

Questions with model answers

In timed database sets you leave every 2-mark MCQ blank unless you are sure of it. Your log shows that when you narrow such a question to two options, you pick the right one 55 % of the time. Should you change the habit? Estimate what it is worth over the 8 such questions a typical set gives you.

On MySQL with InnoDB, SELECT * FROM orders WHERE customer_id = ? uses a secondary index on customer_id, returns 2,000 rows and takes 400 ms, while the same query selecting only order_id, the primary key, takes 5 ms. Explain the gap, give two fixes, and say how PostgreSQL would differ.

Two requests withdraw 300 and 500 from a wallet holding 1,000 at the same moment. Each runs BEGIN, reads the balance, computes the new balance in the application, writes it with UPDATE and commits. At the default isolation levels of PostgreSQL and MySQL InnoDB, can money be lost? How do you fix it?