When you work with a database, the way you structure and access the data affects almost everything built on top of it. A poorly chosen schema can make common operations difficult, an unnecessary join can slow down a query, and a missing index can turn a simple lookup into an expensive operation as the data grows.
That means you’ll work with several connected areas when building your database skills: relational models, SQL, schema design, keys and constraints, normalisation, indexing, transactions, query optimisation, and different types of data stores. The depth you need in each area depends on the role, so the database skills expected from a backend engineer can differ from those needed for a data engineer or database administrator.
This guide takes you through these areas in the order you’re likely to encounter them, from relational and SQL fundamentals to schema design, indexing, query performance, transactions, and modern data stores. Along the way, the focus is on applying each concept to real development problems and understanding the decisions behind how a database is designed and used.
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 more Modern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 more Advanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 more DevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 more AI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
The Database Skills Checklist: All Six Tiers
Database skills cover the knowledge and practical ability needed to work with data in applications. They include relational concepts, SQL querying, schema design, indexing and performance, transactions and database operations, and newer approaches such as NoSQL and vector storage. You can explain these concepts and apply them to practical problems.
| Skill | Why employers screen for it | How it’s tested in interviews | How to evidence it |
| Foundations | |||
| Relational model | Tests whether you understand how application data is structured. | Model relationships between entities. | Create an ER diagram and schema. |
| Keys and constraints | Shows you can protect data integrity. | Choose appropriate keys and constraints. | Use them in a working schema. |
| Normalisation | Tests whether you can avoid unnecessary duplication. | Normalise a flawed schema. | Show a 1NF–3NF example. |
| ACID | Tests your understanding of reliable transactions. | Explain what happens when a transaction fails. | Demonstrate a transactional workflow. |
| Querying | |||
| Joins | Most real queries combine data from multiple tables. | Choose the appropriate join for a scenario. | Write multi-table queries |
| Aggregation | Tests whether you can turn rows into useful results. | Solve a grouped-data problem. | Use GROUP BY and HAVING. |
| Subqueries and CTEs | Tests whether you can structure multi-step queries. | Break a problem into intermediate results. | Include readable CTE-based queries. |
| Window functions | Tests more advanced query fluency. | Solve ranking or running-total problems. | Use window functions in a project. |
| NULL semantics | Incorrect handling can produce silent errors. | Predict results involving NULL. | Show explicit NULL handling. |
| Design | |||
| Schema modelling | Poor schemas create problems later. | Design tables for an application. | Publish the ERD and schema. |
| Indexing strategy | Indexes affect both reads and writes. | Choose an index for a query. | Document index decisions. |
| Denormalisation | Some workloads justify duplicated data. | Explain when you’d denormalise. | Document the trade-off. |
| Data types | Incorrect types can cause storage and correctness problems. | Choose types for common fields. | Explain non-obvious type choices. |
| Performance | |||
| Reading a query plan | Slow queries require more than reading the SQL. | Identify the expensive operation in an EXPLAIN plan. | Include an annotated execution plan. |
| Optimisation and sargability | Query structure affects whether indexes can be used. | Rewrite a non-sargable query. | Show before-and-after timings. |
| Index selection | More indexes are not always better. | Choose indexes for a workload. | Benchmark the query with and without them. |
| N+1 | ORM queries can multiply unexpectedly. | Identify the pattern and propose a fix. | Show the query count before and after. |
| Operations | |||
| Transactions and isolation | Concurrent operations can create inconsistent results. | Explain a race condition and isolation choice. | Reproduce and document a concurrency case. |
| Backups and PITR | Recovery is part of operating a database. | Explain recovery after accidental deletion. | Document a recovery procedure. |
| Replication | Production systems often need availability and read scaling. | Explain replicas and consistency trade-offs. | Demonstrate or document a replicated setup. |
| Connection pooling | Poor connection management can exhaust resources. | Diagnose connection exhaustion. | Show pool configuration and behaviour. |
| Migrations | Schema changes must be introduced without breaking applications. | Design a safe schema migration. | Include versioned migrations. |
| Modern | |||
| When NoSQL is right | Some workloads fit non-relational stores better. | Choose between relational and NoSQL approaches. | Document the workload and decision. |
| Stores by access pattern | Database choice should follow application access patterns. | Select a store for a given workload. | Map access patterns to the chosen store. |
| ORMs and their failure modes | Abstraction can hide inefficient database behaviour. | Diagnose SQL generated by an ORM. | Show the problematic query and fix. |
| Vector storage | Similarity search uses a different retrieval model. | Explain when vector search fits the application. | Build a small semantic-search example. |
You can use this table as a self-audit. Find the highest tier where you can honestly tick everything below it. That is your current level; the tier above it is the next gap to close.
Read More: What Are Technical Skills?
Tier 1: Foundations
Understanding the Relational Model
When you work with a relational database, you describe the data you need through SQL while the database handles how that data is stored and retrieved. This separation matters because the database can change its indexes or execution plan without requiring you to rewrite the query in your application.
The relational model was introduced by Edgar F. Codd in his 1970 paper, A Relational Model of Data for Large Shared Data Banks. Understanding the model gives you a useful base for working with tables, relationships, keys, and queries before moving into schema design and performance.
You can also check out the DBMS Syllabus.
Keys and Constraints
Application validation is relevant, but it only protects the paths through your application that actually perform the check. A constraint in the database applies regardless of whether the data comes from the API, an admin script, a reporting job, or a migration.
Consider an orders table with a customer_id column but no foreign key. The application may validate that the customer exists when an order is created. Three years later, someone runs a data migration or reporting import that bypasses that validation and creates orphaned orders. The reporting query joins orders to customers and quietly loses those rows. The database could have rejected the invalid reference at the point of insertion.
Keys also involve design choices. A natural key uses an existing business value, such as an email address or government-issued identifier. A surrogate key uses an identifier created specifically to identify the record. UUIDs can help avoid some coordination issues in distributed systems, while auto-incrementing integers are compact and straightforward.
Normalisation to 3NF and Knowing When to Stop
Take a college course-enrolment table containing:
student_id | student_name | course_id | course_name | instructor
At first, the table looks convenient because everything needed for an enrolment is in one place. But student and course information gets repeated across multiple rows.
1NF: keep each field atomic and each row represents one enrolment.
2NF: remove attributes that depend on only part of a composite key. student_name belongs with the student, while course_name and instructor belong with the course rather than being repeated for every enrolment.
3NF: remove non-key dependencies.
The resulting model separates Students, Courses, and Enrolments, with keys connecting them. The benefit isn’t that the schema now satisfies an exam definition of 3NF; it is that changing a student’s name or a course’s details happens in one place instead of creating conflicting copies.
BCNF goes further by tightening the dependency rules, but it is more likely to appear as an exam question than as the deciding point in a typical application interview.
The interview question to prepare for is: “Normalise this, and now tell me when you wouldn’t.” A read-heavy workload may justify controlled denormalisation when avoiding joins is worth the additional consistency and update cost. Knowing the rule is baseline; knowing when to break it is the stronger answer.
ACID, and What Each Letter Buys You
- Atomicity: the transaction completes as a unit or doesn’t take effect.
- Consistency: committed transactions preserve the database’s defined integrity rules.
- Isolation: concurrent transactions don’t interfere in ways the chosen isolation level permits.
- Durability: once committed, the result survives a failure.
Isolation isn’t simply “on” or “off”; databases provide different isolation levels with different guarantees and concurrency costs. We’ll return to that trade-off when we cover transactions and isolation later.
Tier 2: Querying
Once the interview moves past basic SQL, the questions get less predictable. Multiple-table queries, NULLs, duplicates, window functions, CTEs and subqueries become fair game. You don’t need every SQL function memorised; you need to understand what your query is doing and why.
Working With Different Types of Joins
As your queries involve more than one table, you’ll need to choose a join based on which rows you want to keep in the result. An INNER JOIN returns rows where both tables have a match, while a LEFT JOIN keeps every row from the left table even when there is no matching row on the right. A self-join is useful when rows in the same table are related to each other, such as connecting employees with their managers.
You’ll also encounter queries that require several joins together when the data you need is spread across multiple related tables. This is where understanding which table should be on each side of the join becomes important.
One detail to pay attention to is where you place your filters. If you put a condition on the right-hand table in the WHERE clause of a LEFT JOIN, rows without a match will be removed from the result. In practice, that can make the query return the same rows you would have expected from an INNER JOIN.
SELECT o.id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.status = ‘active’;
If the requirement was to keep all orders and only attach active customers, that query is wrong. The filter belongs in the join condition.
Aggregation, GROUP BY, and HAVING
Aggregation tests whether you understand the difference between filtering rows and filtering groups. WHERE operates before grouping; HAVING filters the groups produced by the aggregation.
There are also small details that expose shaky understanding. COUNT(*) counts rows, while COUNT(column) ignores rows where that column is NULL.
Another common problem is join fan-out. If you join one customer to several orders and several payments before aggregating, the combinations can multiply and inflate your totals. The query may execute perfectly and still return the wrong number.
Subqueries and CTEs
Subqueries can return a single value, filter against a set with IN or EXISTS, or reference the outer query through a correlated subquery. The right choice comes down to what you’re trying to return and how the tables relate to each other.
CTEs provide another way to structure the same reasoning into named steps. They can make a complicated query easier to inspect without changing the underlying problem into a procedural program. Recursive CTEs extend this to hierarchical data such as employee-manager relationships and are worth knowing once you’re beyond the basics.
Window Functions: The Clearest Beginner/Intermediate Divide
Window functions let you calculate across related rows without reducing them to one row per group. ROW_NUMBER() and RANK() handle ranking, LAG() and LEAD() compare rows, and SUM() can calculate running totals. PARTITION BY defines the group for each calculation.
Three interview problems cover a large part of the territory:
| The problem | The beginner approach | The window-function approach |
| Top 3 employees by salary in each department | Loop through departments and sort each group separately. | Rank rows with ROW_NUMBER() or RANK() over each department |
| Running account balance | Fetch rows and maintain a running value in application code. | Use a windowed SUM() ordered by transaction date. |
| Compare each sale with the previous sale | Fetch rows and track the previous value manually. | Use LAG() over the required ordering. |
NULL Semantics and the Quiet Wrong Answers
NULL represents an unknown or missing value, which introduces three-valued logic into SQL. Comparisons involving NULL can therefore produce UNKNOWN rather than TRUE or FALSE. If you need to revisit how SQL handles these comparisons, the SQL Tutorial can help you refresh on the fundamentals.
One particularly tricky example is NOT IN. If the subquery contains a NULL, the comparison can evaluate to UNKNOWN for every candidate row, leaving you with no results when you expected several. You’ll want to account for this when working with subqueries and missing values.
COALESCE can give you a fallback value when a value is missing, but it doesn’t remove the distinction between a missing value and a real value. These details become easier to work with once you understand how SQL handles NULL throughout a query. For a broader view of the concepts and skills to learn, you can also refer to the SQL Roadmap.
Tier 3: Database Design
Database design brings together the decisions you make about how data should be structured and accessed. You’ll work with normalisation, relationships, constraints, and indexes while considering how those choices affect queries, updates, and the application as a whole.
Modelling a Real Domain from Requirements
Start with the domain: identify the entities, define their relationships and cardinality, then determine attributes, keys and constraints. Before finalising the schema, check it against the queries the application actually needs to run.
Take a library system. You might have Book, Member, Loan and Author as core entities. A book can have multiple authors, while an author can write multiple books, so an intermediate BookAuthor table connects the two. A member can have many loans, while each loan refers to one member and one book copy.
That last detail: before drawing the final ER diagram, ask “What are the main queries?” If the application needs to show a member’s current loans, find overdue books, and list all books by an author, those access patterns should influence the design. A schema designed without knowing how the application will read and write the data is incomplete.
Indexing Is a Design Decision
An index gives the database a faster path to finding rows, but it isn’t free. It consumes storage and creates write amplification because the index has to be maintained when rows are inserted, updated, or deleted.
Indexes can speed up reads, but they also use disk space and make inserts and updates more expensive because the index has to be updated too.
Column order is important in a composite index, and selectivity affects how relevant an index is for a particular query. A covering index can go further by containing everything needed for a query, potentially avoiding a separate table lookup.
So don’t stop at “I’d add an index.” A stronger answer names the query, the index, the expected benefit and the cost it introduces.
Denormalisation
Denormalisation can be the right choice for read-heavy paths, expensive repeated joins or precomputed aggregates. But it creates another problem: the same information now exists in multiple places and has to remain consistent.
So you need to normalise first. Denormalise when you’ve measured a real performance problem and have a clear way to keep the duplicated data consistent. That could mean a transaction, an application-level update, a trigger, a materialised view or a background synchronisation process.
Without the measurement, you’re guessing about performance. Without the consistency mechanism, you’re creating a data-integrity problem.
Data Types and Key Choices
Data type choices matter once a schema reaches production. Choose numeric types according to the range and precision the data requires, and avoid FLOAT for monetary values. Use DECIMAL when you need exact decimal representation.
For timestamps, be deliberate about timezone handling. TIMESTAMP WITH TIME ZONE is often safer for events that represent an actual point in time, particularly when applications and users operate across regions.
TEXT versus VARCHAR(n) should reflect an actual data constraint rather than a habit. JSON columns can be pragmatic when part of the data is genuinely flexible, but putting an entire relational model inside JSON usually means you’ve avoided making a schema decision.
Keys deserve the same scrutiny. Auto-incrementing integers are compact and straightforward; UUIDs work well when identifiers need to be generated independently across systems. The right choice depends on how the data is created, referenced, and exposed, not on which type looks more modern.
For a broader guide to system design: System Design Roadmap
Free Courses by top Scaler instructors
Tier 4: Performance
A common response to a slow query is to change the SQL until it feels faster. That’s guesswork. The important skill is being able to stop guessing and read what the database says it is doing.
You can also explore these 15 Backend Developer Skills.
How to Read an Execution Plan
The database planner works out how to execute a query, including which indexes to use, how to join tables, and how many rows it expects to process. EXPLAIN shows the planned operations without executing the query. EXPLAIN ANALYZE runs the query and adds the actual timings and row counts.
When reading a plan, use this order:
- Large sequential scan – check whether an index is missing or unusable.
- Estimated rows far from actual rows – check for stale or inaccurate statistics.
- Nested loop over a large outer relation – investigate whether the join strategy is appropriate.
- Sort or hash spilling to disk – check memory pressure and the operation’s data volume.
- Most expensive node first -don’t automatically start at the top of the plan.
For practice, free visualisers such as explain.dalibo.com and explain.depesz.com make execution plans much easier to inspect.
Required visual: an annotated EXPLAIN ANALYZE output highlighting the scan, estimated-versus-actual rows, and the most expensive node.
Why Is This Query Slow? Use a Diagnostic Order
Don’t start by rewriting the query. First ask whether the problem affects everyone or only appears at scale. Then run EXPLAIN ANALYZE and work through the likely causes.
See if the predicate is sargable. For instance, WHERE YEAR(created_at) = 2026 can prevent a created_at index from being used efficiently. Check the amount of data your query will pull in, see if you’re dealing with one query or many due to an N+1 situation involving hundreds of queries, and see if the database is waiting for locks or connections.
Don’t quote a made-up “percentage of database incidents caused by slow queries.” A real before-and-after measurement is stronger. If you actually reduce a query from 4.2 seconds to 40 milliseconds, show the original plan, the change, and the resulting plan.
The N+1 Problem, and Why Your ORM Caused It
Suppose an application fetches 100 orders and then lazily loads the customer for each order. The first query returns the orders; the loop triggers another query for every customer. You now have 101 queries even though each individual query looks fast.
This is the N+1 problem. Common fixes include eager loading, select_related or prefetch_related, JOIN FETCH, batching, or deliberately using SQL for the specific access pattern.
The important diagnostic step is to count the queries. An ORM can hide the problem because the application code looks clean while the database is doing far more work than expected.
Index Selection: Why More Indexes Can Make Things Worse
Every index has a maintenance cost. Inserts, updates, and deletes have to maintain the relevant index structures, while indexes also consume storage and can increase write latency.
Redundant indexes are another problem. A composite index on (a, b) can often make a separate index on (a) unnecessary, while it does not generally replace an index whose leading column is b.
Look at actual query patterns and index usage before adding another one. The interview question to prepare for is straightforward:
“You added indexes, and the application got slower. Explain.”
A good answer starts with write amplification, storage and maintenance overhead, then looks at whether the new indexes were actually being used.
Tier 5: Operations
Once you’re working with a database in production, you’ll also need to deal with concurrency, failures, recovery, replication, and connection management. A transaction may behave differently when several operations run at the same time, a replica may fall behind, a restore may fail, or the application may run out of database connections. When something like this happens, you’ll need to trace the problem through the database and application, identify what caused it, and decide how to bring the system back to a healthy state. Working through these situations is an important part of building database skills for production environments.
Transactions and Isolation Levels
Isolation controls what concurrent transactions are allowed to observe. PostgreSQL, for example, treats READ UNCOMMITTED as READ COMMITTED, and its REPEATABLE READ implementation prevents phantom reads. MySQL InnoDB has REPEATABLE READ as its default and uses a different concurrency model.
| Isolation level | Dirty read | Non-repeatable read | Phantom read | Typical use |
| Read Uncommitted | Possible | Possible | Possible | Rare; weak consistency requirements |
| Read Committed | Prevented | Possible | Possible | Common transactional workloads |
| Repeatable Read | Prevented | Prevented | Possible under the SQL standard | Workloads needing a stable transaction snapshot |
| Serializable | Prevented | Prevented | Prevented | Operations requiring the strongest isolation |
One detail often comes up in interviews: PostgreSQL’s default is READ COMMITTED. Two identical SELECT statements in the same transaction can therefore see different committed data if another transaction commits between them.
Deadlocks are another production problem. They commonly arise when two transactions acquire locks in different orders; for example, transaction A locks row 1 then waits for row 2 while transaction B has row 2 and waits for row 1. A consistent lock-acquisition order reduces the risk, but your application should also be prepared to retry transactions that the database aborts because of a deadlock.
Backups, Recovery, and the Restore Nobody Tested
A backup strategy isn’t just “we take backups.” Full backups capture the database at a point in time, while incremental approaches capture changes since an earlier backup. Point-in-time recovery (PITR) combines a base backup with transaction logs such as PostgreSQL’s WAL or MySQL’s binary log to recover to a specific point.
Two terms belong in every recovery discussion:
- RPO (Recovery Point Objective): how much recent data the business can afford to lose.
- RTO (Recovery Time Objective): how long the service can afford to remain unavailable.
And the practical rule is worth remembering:
A backup you have never restored is not a backup; it is a hypothesis.
A recovery procedure that has never been tested can fail because of missing credentials, incompatible versions, incomplete backups, incorrect retention, or a restore process nobody has documented.
Replication, Read Replicas, and Lag
Replication is not simply “make a copy of the database.” Read replicas introduce a consistency problem that the application has to account for.
Imagine a user posts a comment. The write reaches the primary, but the next page load is routed to a replica that hasn’t caught up yet. The user refreshes, and their comment appears to have disappeared. Replication lag has become a product bug, even though the database infrastructure is technically working.
Replication can support read scaling and availability, but it doesn’t automatically give you stronger consistency. It is also different from sharding and partitioning. Sharding distributes data across database instances, while partitioning divides data within a database according to a defined strategy. Most applications don’t need sharding early on, and introducing it too soon can add unnecessary operational complexity.
Connection Pooling: The Failure Mode You’ll Hit First
Applications usually reuse database connections through a connection pool rather than opening a new connection for every request. The pool should not simply be made “as large as possible”: every active connection consumes database and application resources.
A common failure starts when the connection pool reaches its limit. New requests begin waiting for a connection, application latency climbs, and the dashboards point to the application servers even though the database is barely busy. The real bottleneck is the time spent waiting for a connection.
HikariCP is widely used for JDBC connection pooling, while PgBouncer provides connection pooling in front of PostgreSQL. The right pool size depends on the workload, database capacity, and concurrency, so it should be measured rather than set arbitrarily.
Schema Migrations Without Downtime
Production schemas change while applications are still serving traffic. Tools such as Flyway, Liquibase, Alembic, and Django migrations help version and apply those changes, but the tool doesn’t make a dangerous migration safe.
Schema changes can cause trouble when the application is still serving traffic. Adding a NOT NULL column with a default to a large table, building an index that holds up production traffic, or renaming a column before every application instance has been updated can all create problems. PostgreSQL provides CREATE INDEX CONCURRENTLY for cases where you need to build an index while allowing concurrent writes.
With an expand-and-contract migration, you first add the new schema without removing the old one. Then deploy application code that supports both versions, and remove the old column or structure only after all instances have moved to the new version.
Being able to explain that sequence is a much stronger signal of production experience than simply listing “database migrations” on a resume.
Learn more about: 12 Essential Data Engineer Skills
How to Get Better at Databases
Courses and certifications are a great start and a good place to validate your skills, but they also often fail to build database fluency because databases are a feedback-loop skill. You write a query against real data, get the wrong result or a slow plan, work out why, and fix it. Watching someone else write the query gives you recognition; writing it yourself builds fluency.
A simple practice loop is:
- Write queries you cannot yet write.
- Read the execution plan, even when the query already works.
- Break performance deliberately, then fix it.
- Model a real domain and defend your decisions out loud.
Repeat that loop with increasingly difficult data and problems. The point isn’t to accumulate completed exercises; it’s to create situations where the database gives you feedback.
Practise Against a Real Dataset, Not Toy Tables
Tutorial tables usually have five rows, clean values and predictable relationships. Real data has NULLs, duplicates, missing references, unexpected values and uneven distributions. Working with it forces you to deal with the problems that toy examples conveniently remove.
For PostgreSQL, Pagila and DVD Rental are good databases to practise with. MySQL has the Employees and Sakila sample databases, while Chinook is available for several database engines. For more realistic data, look at Kaggle and data.gov.in
Try modelling the schema of an application you use every day without looking at its implementation. Then find its actual implementation or a documented schema and compare the two. The differences can show you where your assumptions about relationships, keys, and access patterns were off.
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
Google Amazon 
Microsoft 
Flipkart 
Adobe 1200+ more Deliberate Practice: The Four-Move Loop
1. Write queries you can't yet write.
Use SQLZoo, PostgreSQL Exercises, LeetCode Database, HackerRank SQL, StrataScratch, or DataLemur for problems you haven't solved before. When possible, run them against a real database rather than a browser sandbox that hides the execution plan.
2. Read the plan for every query.
Don't wait until a query is slow. Looking at EXPLAIN regularly turns scans, joins, estimates, and index usage from terminology into things you've actually seen.
3. Break performance deliberately, then fix it.
Load a large dataset, time a query, remove the index and time it again. Add redundant indexes and measure the effect on writes. Use a non-sargable predicate and compare the plan. You learn indexing faster by making a query slow on purpose than by memorising what an index does.
4. Model a domain and defend it out loud.
Choose a real application, design its schema, identify the likely access patterns, and explain why you chose each relationship, key, and constraint. Then challenge your own design with questions about scale, consistency, and performance. That's close to the format of an actual design interview.
| Skill tier | One concrete exercise | How you know you've got it |
| Foundations | Design and constrain a small relational schema. | You can explain every key, relationship, and constraint. |
| Querying | Solve the same data problem using joins, CTEs, and windows. | You can choose the approach without trial-and-error syntax. |
| Design | Model a real application from its access patterns. | You can defend the schema's trade-offs. |
| Performance | Diagnose and fix a deliberately slow query. | You can explain the plan before changing the SQL. |
| Operations | Simulate a migration, backup, or concurrency problem. | You can describe the failure and recovery path. |
| Modern | Choose a database for a specific workload. | You can defend the choice against a relational alternative. |
References for Database Skills
The PostgreSQL documentation is one of the best free references for understanding how a production relational database works. For distributed systems and database operations, Designing Data-Intensive Applications by Martin Kleppmann is a great read, while Use The Index, Luke! goes deeper into indexing and query performance.
You can get more from these resources when you use them alongside your projects. Once you run into a problem yourself, look up the relevant section and use it to understand what happened and how the database handles it.
You can check out this SQL Syllabus and begin your learning journey with this free SQL Tutorial with certificate.
What Database Interviews Test
Database-heavy interviews usually test the same four areas in increasing depth: a live SQL round, schema design, indexing and performance, and transaction concepts. The exact format varies by company, but knowing what each round is trying to uncover makes preparation much more targeted.
The SQL Query Round
A typical live round starts with a straightforward join and then gets harder: a GROUP BY with a filter, followed by a problem that needs a window function or CTE. The interviewer is testing whether you can move beyond familiar query patterns when the problem changes.
You are generally evaluated first on correctness, then on how you reason through the problem, readability, and whether you consider edge cases. Before writing the query, ask about NULLs, duplicates, and what the expected result should be. Better still, explain your approach before you start writing. Getting the right answer isn't enough; the interviewer also needs to see how you arrived at it.
The Schema-Design Round
You might get an open-ended prompt such as “Design the schema for a ride-hailing app.” Give yourself time to clarify the requirements before drawing tables: what entities exist, how they relate, what the main access patterns are, and which operations need to be fast.
From there, move through relationships, cardinality, keys and constraints before discussing indexes or denormalisation. You may also be asked what happens as the data grows.
The most common mistake is drawing tables immediately. The interviewer is looking for the reasoning behind the schema, not how quickly you can produce an ER diagram.
“Why Is This Query Slow?”
This is often where the difference between a mid-level and senior candidate becomes visible. A strong answer follows a procedure: establish the scale of the problem, inspect the execution plan, check indexes and predicate structure, look for excessive rows or columns, and consider N+1 queries, locks, or connection pressure.
A weak answer starts suggesting indexes before asking how much data exists or what the current plan looks like. If you propose an index, explain its write and storage cost as well as the expected read benefit.
Concept Questions on Transactions and Isolation
Interviewers often start with something familiar: “What is ACID?” Then they make it concrete: two users try to buy the last available item at the same time. What happens? What should the transaction guarantee? Which isolation behaviour is appropriate?
Everyone can recite Atomicity, Consistency, Isolation and Durability. Fewer candidates can explain what READ COMMITTED permits, how concurrent transactions can interact, or why a particular isolation level is appropriate for the operation.
The ORM Question and Being Honest About It
For many application developers, the database is accessed through an ORM such as Hibernate, JPA, SQLAlchemy, Django, Sequelize, or Prisma. That's not a problem. ORMs remove genuine boilerplate and give application code a simpler way to interact with the database.
The problem is when the ORM becomes the only way you've ever seen the database. It shows up when a candidate reaches for ORM method names instead of writing a join, doesn't recognise an N+1 because the application never failed outright, or can't answer “What SQL does this generate?”
The fix is surprisingly small. Turn on SQL logging, show_sql, echo=True, Django's connection. queries, or Prisma's query logging and read the generated SQL during normal development for a week. You will quickly see the queries your application is actually sending and start connecting ORM operations to database behaviour.
Are Database Skills Still Worth Learning When AI Writes SQL?
AI assistants can generate competent SQL, which makes it easier to produce a query without writing every part of it yourself. You still need to check whether the query matches your schema, returns the correct result, remains performant at 50 million rows, or holds a transaction open for too long.
The query may be generated for you, but you still have to understand what it is doing and whether it is appropriate for your database. That makes schema design, query performance, and database operations important skills to build alongside your SQL knowledge.
SQL vs NoSQL and Which Skills Your Target Role Needs
When SQL Is Right, and When NoSQL Genuinely Is
“NoSQL for unstructured data and scale” is too vague to help with a real design decision. Relational databases can handle JSON, and most applications never reach a scale that requires abandoning them simply because the data is large.
So you need to ask this question: how will the application access the data?
| Access pattern | Likely choice |
| Clear relationships and strong write correctness | Relational |
| Query patterns are not yet predictable | Relational |
| A document is always read and written as a whole | Document store |
| High-throughput key lookups, caching, sessions or rate limits | Key-value |
| Extreme write volume, time-series or event data | Wide-column |
| Relationships themselves are the primary queries | Graph |
| Analytical aggregates over billions of rows | Columnar analytics store |
| Similarity search over embeddings | Vector store / pgvector |
The term “schemaless” can also be misleading. The schema hasn't disappeared; it has often moved into application code, where the database may no longer enforce it.
A relational database might handle orders and payments while a key-value store handles sessions or a vector store handles semantic search. In an interview, “I'd use MongoDB” isn't a strong answer by itself, just as “I'd always use PostgreSQL” isn't. Name the access pattern and explain why the chosen store fits it.
Which Database Skills Does Your Role Need?
Foundations and Querying are the baseline for every role here. The difference starts in the higher tiers: a backend developer needs enough Design and Performance to build reliable application data access, while a DBA needs much deeper Operations knowledge.
| Role | Must be strong in | Should be competent in | Can defer |
| Backend Developer | Foundations, Querying | Design, Performance | Advanced Operations, Modern |
| SQL/Database Developer | Foundations, Querying, Design | Performance, Operations | Modern |
| DBA | Foundations, Design, Operations | Performance, Querying | Modern |
| Data Engineer | Foundations, Querying, Operations | Design, Performance, Modern | — |
| Data Analyst | Foundations, Querying | Basic Design | Performance, Operations, Modern |
For database developer skills, the expectation usually extends to schema design, indexing, performance, and database-side development.
The point isn't to master all six tiers before applying for a role. Build a strong foundation and querying layer first, then go deeper where your target role actually demands it.
Also read: Backend Developer Roadmap
Scaler Alumni and Their Success Stories
How to Put Database Skills on Your Resume
Every claimed skill should have an artefact behind it. “Proficient in SQL” is quite overused; a repository containing a schema, migrations, ER diagram, and a README explaining one indexing decision gives an interviewer something they can evaluate.
Portfolio Evidence That Reads as Evidence
Build your database project around the decisions you make about the data. Show the schema, relationships between tables, queries used by the application, and the changes you make as the dataset and access patterns grow.
For example, if a query went from 4.2 seconds to 40 milliseconds, include the execution plans from before and after the change and explain what caused the improvement. You might have added an index, changed the query, or changed the way the data was structured. Show the connection between the problem and the change you made.
Keep the migration history with the project as well. Versioned, reversible migrations show how the schema changed as you added features or changed how the application used the data.
These details give you specific database decisions to discuss in an interview, from why you chose a particular relationship to what changed in an execution plan and why.
Listing Database Skills on a Resume Without Keyword-Stuffing
Name the database engine and the capability, rather than repeatedly writing “database.” For example:
PostgreSQL: schema design, query optimisation with EXPLAIN ANALYZE, indexing and migrations
Don't list a skill you couldn't defend in an interview. A SQL round can expose a weak claim in four minutes.
Where you have a genuine number, include it: queries reduced from 4.2s to 40ms, 30% fewer database calls, or a migration completed without downtime. The number should describe something you actually measured, not a result invented to make the bullet stronger.
As you build your database skills alongside your broader technical skill set, you can also look at how upskilling has affected career transitions in Scaler’s latest Career Transition Assessment Report.
Conclusion
Database skills start with understanding how data is structured and queried, but the way you work with a database changes as the application around it grows. You’ll move from writing queries and modelling relationships to thinking about indexes, query plans, transactions, concurrency, recovery, and the data stores that fit different requirements.
Use the six tiers to decide where you want to build further. Foundations and Querying give you the base for working with relational databases; from there, Design, Performance, Operations, and Modern Databases become more relevant depending on the role and systems you want to work with.
Pick a project with a real dataset and start investigating how the database handles your queries. Run EXPLAIN ANALYZE, look at the execution plan, change something, and run it again. That process will teach you far more about database performance than looking at the query alone.
Explore These In-Demand Career Skills
FAQ
1. What are database skills?
Database skills cover six areas: Foundations, Querying, Design, Performance, Operations, and Modern database technologies. Together, they include relational concepts, SQL, schema design, indexing, query optimisation, transactions, recovery, and newer approaches such as NoSQL and vector storage.
2. What database skills do employers look for?
You’ll see SQL and database fundamentals in most database-related roles, but the skills you work with after that depend on the kind of role you’re targeting. Schema design and indexing become important when you’re building application databases, while query optimisation, transactions, isolation, and database operations become more important as you work with larger and more heavily used systems.
For interviews, be ready to explain why you chose a particular schema or index, read an execution plan, find the cause of a slow query, and understand what happens when multiple transactions access the same data.
3. Is SQL the same as database skills?
No. SQL covers querying, but database work also involves schema design, indexing, performance, transactions, operations, and choosing the right data store for a given workload.
4. What are the basic database skills a beginner should learn first?
Start with the relational model, keys and constraints, 3NF, joins, and GROUP BY. Work with these concepts on a real dataset so you can see how they affect the way data is structured and queried. From there, move into indexing, query performance, transactions, and NoSQL as your projects introduce those requirements.
5. How can I improve my database skills?
You can improve your database skills by working through problems where you have to decide how the data should be stored, queried, and updated. Start with a real application or dataset and practise writing queries, reading execution plans, finding the cause of slow queries, and changing the schema or indexes when the requirements call for it. As you work on larger projects, take on problems involving more tables, more data, concurrency, and different access patterns.
6. How long does it take to get good at databases?
You can build a foundation in querying in roughly 4 - 8 weeks with consistent practice. Give yourself more time for schema design, query performance, and database operations, since these skills develop through working with different schemas, workloads, concurrency patterns, and failure scenarios.
7. Do I need to learn NoSQL as well as SQL?
Start with a relational database and work with its tables, relationships, queries, indexes, and transactions. Once you have built a few projects with those concepts, you can explore NoSQL databases and see where their data models and access patterns fit. Pick the systems that match the kind of applications you want to build and go deeper into those.
8. How do I list database skills on a resume?
Name the database engines you have worked with and the specific work you did. For example, list PostgreSQL alongside schema design, EXPLAIN ANALYZE, indexing, or migrations if you have actually used them. Only list skills you can defend in an interview.
9. Are database skills still worth learning now that AI can write SQL?
Yes. AI can generate SQL, but you still need to check the result, handle edge cases, and understand its performance against the schema and workload. Database knowledge lets you review the query properly and change it when necessary.
