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

Database Management Systems (DBMS) Module 1

Foundations: what a DBMS does and how it works interview questions

Interview questions on “Foundations: what a DBMS does and how it works”: warm-ups, then what each harder question tests and how a good answer starts.

9 questions: 4 warm-ups and 5 with model answers · Levels: Fresher to senior

Warm-up questions

Short questions with short answers, each answered in full.

Fresher

What is data independence, and which kind is harder to achieve?

Answered in the lesson What a DBMS does and why files are not enough
Fresher

Is TRUNCATE a DDL or a DML statement?

Answered in the lesson What a DBMS does and why files are not enough
Fresher

What happens to a SQL query between the moment it is sent and the first row coming back?

Answered in the lesson Inside a database engine: the life of a query
Fresher

What is the difference between an embedded database engine and a client-server engine?

Answered in the lesson Inside a database engine: the life of a query

Questions with model answers

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

Fresher to mid

A five-person team keeps customer orders as JSON files on a shared network drive, one file per day, changed by two scripts and a small web app. Which failures should they expect as the business grows, and how would you demonstrate each one before arguing for a database?
What it tests

Naming the failures of shared files with their causes, reproducing them instead of asserting them, and knowing what a DBMS adds and what it does not fix by itself.

First step

List the writers and how each one saves. If any of them reads a whole file, changes it and writes it back, two of them can interleave and lose an update. Script that interleaving in a fixed order with two processes and show the lost change, before naming any product.
Follow-ups
- They answer that they will add a lock file. What does that fix, and what does it not?
- Would SQLite on the same network drive solve it?
- Which failure would you fix first, and how would you migrate without stopping the business?
From the lesson What a DBMS does and why files are not enough
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
Mid to senior

A wide customers table must be split into customers and customer_addresses. Twenty reports and three services read the table, and two of the services also write to it. How do you make the change without breaking any of them, and what can a view not hide?
What it tests

Logical data independence in practice: views for readers, a separate plan for writers, a staged migration with a way back at each step, and the limits of what any view can rebuild.

First step

Inventory the access first: which programs read which columns, and which write. Readers can keep the old name if a view rebuilds the old shape from the two new tables; writers need an INSTEAD OF trigger on the view or a change of code, so plan them separately.
Follow-ups
- One report is slower through the view than it was on the old table. Why, and what do you do?
- How would you roll the change back halfway through?
- What changes if the database is MySQL instead of PostgreSQL?
From the lesson What a DBMS does and why files are not enough
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Mid to senior

A report query took 40 ms yesterday and takes 9 seconds today. Nobody changed its text or the schema. Using the stages a query goes through, where do you look first, and in what order?
What it tests

Separating a changed plan from a slower execution of the same plan, reading estimated against actual rows, and knowing what changes a plan without a code change: statistics, data growth, caches and locks.

First step

Get the plan the query uses now and compare it with the last known good one. In PostgreSQL, run EXPLAIN (ANALYZE, BUFFERS) on a copy, or inside a transaction you roll back, and compare estimated rows with actual rows node by node: a large gap points at the statistics.
Follow-ups
- The plan is the same as yesterday's. What do you check next?
- One node estimates 10 rows and returns 400,000. What happened, and what do you do?
- How do you notice the next regression before the users do?
From the lesson Inside a database engine: the life of a query
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to mid

A web app runs 40 worker processes on two servers, all writing to one SQLite file on a network share. Users see "database is locked" errors and, twice this month, a corrupted database. Explain both problems and what you would change.
What it tests

SQLite's one-writer rule and busy timeouts, why network file systems break SQLite's locking, and choosing between one application host in front of SQLite and a client-server database, with numbers.

First step

Separate the two symptoms. "Database is locked" is SQLite refusing a second writer while a write transaction is open, which is expected under load. Corruption is not expected: SQLite's own guidance links it to buggy file locking in network file systems when many machines open one database file.
Follow-ups
- Would switching to WAL mode fix it?
- How long should the busy timeout be?
- When is keeping SQLite the right answer?
From the lesson Inside a database engine: the life of a query
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

The college report filters on course code, but its enrolment key starts with student_id and the plan scans enrolments. The table grows from 14 to two million rows. Why does reordering FROM not solve it, and what change would you assess?
What it tests

Reading every loop and its index prefix, separating written join order from planning, and proposing a measured access-path improvement without promising an unobserved plan.

First step

Read the complete plan and the composite key definition together. Locate the enrolment SCAN, check which column begins that key, and compare the two original FROM orders before changing the schema.
Follow-ups
- Why does a covering index still appear with SCAN?
- What work remains after a course-first access path is added?
- How would you establish whether the changed plan actually improves the report?
From the lesson Inside a database engine: the life of a query
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes

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

Warm-up questions

What is data independence, and which kind is harder to achieve?

Is TRUNCATE a DDL or a DML statement?

What happens to a SQL query between the moment it is sent and the first row coming back?

What is the difference between an embedded database engine and a client-server engine?

Questions with model answers

A five-person team keeps customer orders as JSON files on a shared network drive, one file per day, changed by two scripts and a small web app. Which failures should they expect as the business grows, and how would you demonstrate each one before arguing for a database?

A wide customers table must be split into customers and customer_addresses. Twenty reports and three services read the table, and two of the services also write to it. How do you make the change without breaking any of them, and what can a view not hide?

A report query took 40 ms yesterday and takes 9 seconds today. Nobody changed its text or the schema. Using the stages a query goes through, where do you look first, and in what order?

A web app runs 40 worker processes on two servers, all writing to one SQLite file on a network share. Users see "database is locked" errors and, twice this month, a corrupted database. Explain both problems and what you would change.

The college report filters on course code, but its enrolment key starts with student_id and the plan scans enrolments. The table grows from 14 to two million rows. Why does reordering FROM not solve it, and what change would you assess?