SQL Interview Questions

A relational database stores data in tables, rows and columns, where each row represents one record and each column represents one specific attribute of that record. Tables can be related to each other through shared key values, which is exactly where the term relational comes from.

DDL (Data Definition Language) defines structure, CREATE, ALTER, DROP. DML (Data Manipulation Language) works with actual data, SELECT, INSERT, UPDATE, DELETE. DCL (Data Control Language) manages permissions, GRANT and REVOKE. TCL (Transaction Control Language) manages transactions, COMMIT and ROLLBACK.

DDL commands define or change a table's actual structure, like CREATE TABLE or ALTER TABLE adding a column. DML commands work with the data that already lives inside that structure, like SELECT to read rows or INSERT to add new ones, without changing the table's underlying schema at all.

A table is the fundamental structure holding data in a relational database, organized into rows and columns. Columns define what kind of data is stored, along with each column's data type, and rows hold the actual individual records, each one a specific instance of that structure.

A database is the overall container, potentially holding many tables along with other objects like views and stored procedures. A table is one specific structure within that database, holding a particular set of related records, like a Customers table or an Orders table.

SELECT * FROM table_name; retrieves every column and every row from the specified table. In practice, selecting specific named columns instead of using * is generally preferred once a query is actually used in real application code, since it avoids pulling data you don't need and breaking if the table's columns change later.

The WHERE clause filters rows based on a condition, SELECT * FROM employees WHERE department = 'Sales'; returns only rows where that condition evaluates to true. Multiple conditions can be combined using AND and OR.

WHERE filters individual rows before any grouping happens. HAVING filters groups after a GROUP BY has already been applied, typically used to filter based on the result of an aggregate function, like only showing departments with more than five employees, which WHERE alone can't do since aggregates don't exist yet at the point WHERE is evaluated.

ORDER BY column_name sorts results in ascending order by default. Adding DESC after the column name sorts in descending order instead. You can sort by multiple columns, and the order they're listed in determines which one takes priority when there's a tie on the first.

It removes duplicate rows from the result set, returning only unique values. SELECT DISTINCT department FROM employees; returns each department name exactly once, no matter how many employees actually belong to it.

LIMIT (in MySQL and PostgreSQL) or TOP (in SQL Server) restricts the result to a specific number of rows. SELECT * FROM products LIMIT 10; returns only the first ten matching rows, commonly combined with ORDER BY to control exactly which rows those ten actually are.

A join combines rows from two or more tables based on a related column between them, letting you retrieve data that's spread across multiple tables in a single query. It's needed because relational databases deliberately split data into separate, focused tables rather than one giant flat table, and joins are how you bring related pieces back together when you actually need them.

An INNER JOIN returns only rows that have a matching value in both tables being joined. A LEFT JOIN returns every row from the left table regardless of whether a match exists in the right table, filling in NULL for the right table's columns when no match is found.

A RIGHT JOIN returns every row from the right table regardless of whether a matching row exists in the left table, filling in NULL for the left table's columns when there's no match. It's essentially the mirror image of a LEFT JOIN, and the same result can always be achieved by swapping the table order and using a LEFT JOIN instead.

A FULL OUTER JOIN returns every row from both tables, matched together where a relationship exists, and filled with NULL on whichever side has no matching row. It effectively combines what a LEFT JOIN and a RIGHT JOIN would each return on their own.

A self join joins a table to itself, treated as if it were two separate tables through aliasing. It's used when rows in the same table are related to each other, like an employees table where each row has a manager_id referencing another employee's own ID in that same table.

You get a cross join, also called a Cartesian product, pairing every row from the first table with every single row from the second table. This produces a result set with a row count equal to the two tables' row counts multiplied together, which is almost never what you actually want and can produce an enormous, unusable result on tables of any real size.

COUNT() counts rows. SUM() adds up numeric values. AVG() calculates an average. MIN() and MAX() find the smallest and largest values. All of them collapse multiple rows down into a single summary value, either across the whole table or within each group when combined with GROUP BY.

It groups rows sharing the same value in one or more specified columns, letting you apply an aggregate function separately to each group rather than across the entire table at once. SELECT department, COUNT(*) FROM employees GROUP BY department; returns a separate employee count for each individual department.

COUNT(*) counts every row regardless of NULL values in any column. COUNT(column_name) counts only the rows where that specific column has a non-NULL value, skipping over any row where that particular column is NULL.

No, at least not directly, because WHERE is evaluated before GROUP BY and aggregation happen, so an aggregate value simply doesn't exist yet at that point in query processing. That's exactly why HAVING exists separately, to filter based on an aggregate result after grouping has already taken place.

SELECT department, AVG(salary) FROM employees GROUP BY department ORDER BY AVG(salary) DESC LIMIT 1; groups employees by department, calculates each department's average salary, sorts those averages from highest to lowest, and returns just the top row.

Grouping by multiple columns, GROUP BY department, job_title, creates a separate group for every unique combination of those columns together, rather than one group per department alone. This is useful when you need a more granular breakdown than a single column can provide on its own.

A primary key uniquely identifies each row in a table. It enforces that its value must be unique across every row and can never be NULL. A table can have only one primary key, though that key can span more than one column if needed.

A foreign key is a column (or set of columns) in one table that references the primary key of another table, establishing and enforcing a relationship between them. It solves the problem of referential integrity, preventing you from, for example, inserting an order that references a customer ID that doesn't actually exist in the customers table.

Both enforce uniqueness across a column's values, but a table can have only one primary key while it can have several unique constraints. A primary key also can't be NULL, while a column with a unique constraint alone can typically still allow NULL values, depending on the specific database.

It requires a column to always have a value, rejecting any attempt to insert or update a row leaving that column empty. It's commonly applied to columns that genuinely must always be present for a record to make logical sense, like a required email field.

A composite key is a primary key made up of two or more columns combined together, where the combination of those column values is unique even if no single column among them is unique on its own. A common example is a table tracking enrollments, where the combination of student_id and course_id together forms the composite key, since a student could take many courses and a course could have many students.

A candidate key is any column, or combination of columns, that could legitimately serve as a table's unique identifier. A table can have several candidate keys, and exactly one of them is actually chosen to be the primary key, while the others remain valid, unused alternatives.

INSERT INTO employees (name, department, salary) VALUES ('Anu', 'Sales', 55000); adds a new row with the specified values into the named columns. If you're providing values for every single column in the table's exact order, the column list itself can be omitted, though including it explicitly is generally the safer, clearer habit.

UPDATE employees SET salary = 60000 WHERE name = 'Anu'; changes the salary column's value for rows matching the WHERE condition. Leaving off the WHERE clause updates every single row in the entire table, which is a common and genuinely costly mistake.

DELETE FROM employees WHERE department = 'Sales'; removes rows matching the given condition. Just like UPDATE, omitting the WHERE clause deletes every row in the table, so double-checking that clause before actually running a DELETE statement is a habit worth building early.

DELETE removes rows one at a time and can be filtered with a WHERE clause, and it can be rolled back within a transaction. TRUNCATE removes every row in the table at once, can't be filtered, and in many databases can't be rolled back, though it's typically much faster than a DELETE affecting the same number of rows.

CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(100), salary DECIMAL(10,2)); defines a new table's name, its columns, and each column's data type and any constraints, like marking id as the primary key.

A non-correlated subquery runs independently and only once, producing a single result the outer query then uses. A correlated subquery references a column from the outer query, so it actually runs once for every row the outer query processes, which makes it generally slower on a large table since it can't simply be evaluated a single time upfront.

IN compares a value against a list of values returned by a subquery, and can behave unpredictably if that subquery happens to return a NULL. EXISTS just checks whether a subquery returns any rows at all, without comparing specific values, and often performs better on a large dataset since the database can stop searching the moment it finds a single matching row.

A correlated subquery works well here: SELECT name, salary, department FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department);. The inner subquery recalculates the average specifically for each outer row's department, comparing that employee's salary against their own department's average.

Both can often achieve the same actual result, but a join typically performs better since the database can optimize it as a single combined operation, while a correlated subquery may need to run repeatedly, once per outer row. Subqueries tend to be more readable for genuinely nested, conditional logic, like checking existence, while joins fit combining and displaying columns from multiple tables together directly.

An index is a separate data structure, typically a B-tree, that lets the database find rows matching a specific value quickly, without scanning every single row in the table one by one. It works much like a book's index, letting you jump directly to a relevant page instead of reading the entire book start to finish just to find one specific topic.

A clustered index determines the actual physical order rows are stored on disk, so a table can have only one. A non-clustered index is a separate structure that points back to the actual row's location, and a table can have several of them, each built on a different column or combination of columns.

A composite index spans multiple columns together, useful when queries frequently filter or sort by that exact same combination of columns. The order the columns are listed in matters significantly, since a composite index on (a, b) can efficiently serve a query filtering on just a alone, but generally can't efficiently serve a query filtering on b alone without a.

Every index has to be updated whenever a row is inserted, updated, or deleted, so more indexes mean more overhead on every single write operation. An index also consumes real storage space. Indexing every column just in case tends to slow down writes significantly, without providing a matching benefit if many of those indexes are never actually used by any real query.

Check the query's execution plan first to see whether the database is currently doing a full table scan, reading every single row, for a query that only genuinely needs a small subset of them. If it's scanning far more rows than the query actually needs, and that specific query runs frequently enough to matter, adding an appropriate index on the filtered or joined columns is usually worth doing.

Normalization organizes a database's tables to reduce data redundancy and prevent certain kinds of update anomalies, splitting data into multiple related tables rather than storing everything redundantly in one large, flat table. It's done to keep data consistent, since a value stored in exactly one place only ever needs to be updated in that one place.

1NF requires each column to hold a single, atomic value, with no repeating groups or embedded lists within one field. 2NF additionally requires every non-key column to depend on the entire primary key, not just part of it, relevant specifically for tables with a composite key. 3NF additionally requires every non-key column to depend only on the primary key, not on another non-key column.

Denormalization intentionally introduces some redundancy back into a database design, often by duplicating a piece of data across tables, specifically to improve read performance by reducing the number of joins a common query needs to perform. It's a deliberate trade-off, accepting a bit more redundancy and more careful write logic in exchange for meaningfully faster reads on a system where read performance genuinely matters more than storage efficiency or write simplicity.

An update anomaly happens when the same piece of data is duplicated across multiple rows, and updating it in one place but forgetting another leaves the data inconsistent. Normalization prevents this by ensuring each piece of data lives in exactly one place, so there's only ever one row to update, with nothing left out of sync elsewhere.

A window function performs a calculation across a set of related rows, like an aggregate does, but without collapsing those rows into a single output row the way GROUP BY does. Each individual row keeps its own place in the result while still having access to a calculated value based on its surrounding window of related rows.

ROW_NUMBER() assigns a unique, sequential number to each row within a defined partition, based on a specified order. A practical use is finding the most recent order for each customer, partitioning by customer and ordering by date, then filtering for just the row numbered 1 within each partition.

Both assign a rank to rows based on a specified order, and both give tied rows the exact same rank. The difference shows up after a tie: RANK() skips the next rank number by the number of tied rows (1, 2, 2, 4), while DENSE_RANK() continues sequentially with no gap at all (1, 2, 2, 3).

It divides the result set into separate groups, or windows, and the window function's calculation is applied independently within each group rather than across the entire result set as one single window. It behaves conceptually similarly to GROUP BY, but without collapsing rows down into one row per group the way GROUP BY does.

SUM(amount) OVER (ORDER BY date) calculates a cumulative sum, adding up the current row's value and every prior row's value according to the specified order, without needing a self-join or a correlated subquery to achieve the same running-total result.

A transaction groups one or more database operations together into a single, all-or-nothing unit of work. It matters because it guarantees that either every operation in the group succeeds together, or none of them take effect at all, preventing a database from ever being left in a partially-updated, genuinely inconsistent state.

Atomicity guarantees a transaction is all-or-nothing. Consistency guarantees a transaction takes the database from one genuinely valid state to another valid state, never violating a defined constraint along the way. Isolation guarantees concurrent transactions don't interfere with each other in unexpected ways. Durability guarantees that once a transaction commits, its changes survive even a subsequent system crash.

COMMIT makes a transaction's changes permanent, actually saving them to the database. ROLLBACK undoes every change made so far within the current transaction, reverting the database back to the state it was in before that transaction began.

A deadlock happens when two transactions are each waiting on a resource the other one currently holds, so neither transaction can ever actually complete. A common cause is two transactions acquiring locks on the same two rows, but in the opposite order from each other, each one waiting on the very row the other transaction currently holds.

An execution plan shows the exact sequence of operations a database will actually perform to run a given query, including which indexes it plans to use, if any, and the join method chosen for each join in the query. I'd specifically look for a full table scan on a genuinely large table where an index should have been used instead, and for a join method that seems poorly suited to the actual size of the tables involved.

If the query is expected to return a large fraction of the table's total rows, a full table scan can actually be faster than an index lookup followed by looking up each individual row it points to, since the overhead of that extra lookup step per row adds up. Outdated table statistics can also cause the query planner to make a genuinely poor choice, which is why keeping statistics reasonably up to date matters for consistent performance.

It happens when fetching a list of records triggers one query for the list itself, then one additional separate query per record to fetch each one's related data, N+1 queries total instead of a single, more efficient one. It's commonly fixed by using a JOIN to fetch the related data in the same original query, rather than looping through results in application code and issuing a fresh, separate query for each one.

Check that appropriate indexes actually exist on every column used in each join condition, verify the join order the database chose is genuinely sensible given the actual size of each table, and consider whether filtering conditions can be applied earlier in the query to reduce the number of rows that need to be joined together in the first place, rather than joining everything first and filtering the combined, much larger result afterward.

A covering index includes every single column a specific query actually needs, both the columns used for filtering and the columns being selected in the result. This lets the database satisfy the entire query directly from the index itself, without ever needing to look up the actual full row in the underlying table, which meaningfully reduces the total work required.

A hash index uses a hash function to map a value directly to a specific location, giving very fast exact-match lookups but no ability to efficiently support a range query, like finding everything between two dates. A B-tree index keeps values sorted, which supports both exact matches and range queries efficiently, and is why B-tree remains the default index type in most relational databases even though a hash index can be marginally faster for the narrow case of exact-match lookups alone.

Pessimistic locking acquires a lock on a row upfront, before making a change, blocking any other transaction from touching that same row until the lock is released. Optimistic locking assumes conflicts are genuinely rare, allows concurrent access without an upfront lock, and instead checks at the moment of actually committing whether the underlying data changed since it was first read. Pessimistic locking fits scenarios with frequent, likely conflicts, while optimistic locking fits scenarios where conflicts are actually uncommon and unnecessary blocking would hurt overall throughput.

A shared lock allows multiple transactions to simultaneously read the exact same data, but blocks any of them from writing to it while that shared lock is held. An exclusive lock allows only one single transaction to both read and write that data, blocking every other transaction from doing either until it's released.

Check the database's own deadlock logs, which most database engines record automatically, showing exactly which two queries were involved and precisely what locks each one was actually waiting on at the moment of conflict. The typical fix is ensuring transactions across the whole application consistently acquire locks on shared resources in the exact same order, or shortening how long a transaction actually holds a lock before releasing it.

As rows are inserted, updated, and deleted over time, an index's underlying physical structure can become fragmented, spread out across storage in a way that's genuinely less efficient to actually read sequentially than a freshly built, well-organized index would be. Periodically rebuilding or reorganizing a heavily fragmented index restores that efficiency, which is why most production databases schedule this kind of index maintenance on a regular basis.

A filtered index only includes rows matching a specific defined condition, rather than indexing every single row in the entire table. It's useful when queries consistently filter on a specific, common subset of the data, like only active records, since indexing just that relevant, smaller subset keeps the index noticeably smaller and faster than indexing the entire table would be.

Horizontal partitioning splits a table by rows, like separating records by year into different partitions, with every partition sharing the exact same set of columns. Vertical partitioning splits a table by columns instead, separating frequently-accessed columns from rarely-accessed ones into different physical tables, while still logically representing the exact same overall entity.

Replication maintains one or more copies (replicas) of a database, kept continuously in sync with the primary database as changes occur. It primarily solves two problems: providing a failover option if the primary database goes down, and allowing read queries to be distributed across replicas, taking real load off the primary, which then handles writes more freely without competing for resources with read traffic.

Synchronous replication waits for a replica to confirm it has received and applied a specific change before the primary database considers that transaction actually committed, guaranteeing the replica is never behind but adding real latency to every single write. Asynchronous replication commits on the primary immediately and sends the change to replicas separately afterward, which is faster for writes but means a replica can briefly lag behind the primary's actual, current state.

Sharding distributes data across multiple entirely separate database instances, often running on different physical machines, rather than partitioning within a single database instance the way regular partitioning does. It's used specifically when a dataset or its write load has genuinely grown too large for even a well-partitioned single database server to handle on its own.

The shard key determines which specific shard a given row actually lives on. A poorly chosen shard key can lead to uneven distribution, some shards ending up with dramatically more data or traffic than others, largely defeating the entire purpose of sharding, or it can force a query needing data from multiple shards to become far more expensive and complicated than it would have been on a single, unsharded database.

The CAP theorem states that a distributed system can genuinely guarantee at most two of three properties at once: Consistency (every node sees the same data at the same time), Availability (every request gets a response), and Partition tolerance (the system keeps working despite a network partition between nodes). Since network partitions are a real possibility in any genuinely distributed system, the practical choice usually comes down to favoring consistency or availability when a partition actually occurs.

An operational database is optimized for fast, frequent reads and writes supporting an application's real-time, day-to-day transactions, typically highly normalized. A data warehouse is optimized instead for large-scale analytical queries scanning huge volumes of historical data, often denormalized specifically to make those big, complex aggregate queries genuinely faster to run.

A CTE, defined with WITH, creates a temporary, named result set that can be referenced elsewhere within the same larger query, making a genuinely complex query more readable by breaking it into clearly named, logical steps rather than deeply nesting several subqueries inside each other.

A recursive CTE repeatedly references itself to process hierarchical or genuinely recursive data, like an organizational chart where each employee has a manager, and that manager in turn has their own manager further up the chain. It's commonly used to traverse an entire hierarchy of unknown, variable depth, which a standard, non-recursive query genuinely can't do on its own.

A regular view is just a stored, reusable query definition, recomputed fresh from the underlying tables every single time it's actually queried. A materialized view physically stores the query's actual result set on disk, which makes reading from it dramatically faster, but that stored result needs to be periodically refreshed to reflect any changes in the underlying data, since it isn't automatically kept live and current the way a regular view's live query would be.

UNION combines the results of two queries and removes any duplicate rows from the combined result. UNION ALL combines them too but keeps every row, including duplicates, and skips the extra work of actually checking for and removing duplicates, which makes it meaningfully faster whenever you already know duplicates genuinely aren't a concern for that particular case.

Some databases provide a dedicated PIVOT operator directly for this. Where that specific feature isn't available, a combination of conditional aggregation, using CASE WHEN inside an aggregate function like SUM or MAX, achieves the exact same practical result, computing a separate column's value for each specific category being pivoted into its own column.

A lateral join lets a subquery on the right side of the join reference columns from the row currently being processed on the left side, which a standard join genuinely can't do. It's useful for something like finding each customer's top three most recent orders, where the subquery's own LIMIT or TOP needs to be applied freshly and independently for every single individual customer row.

A scalar subquery returns exactly one single value, one row and one column, and can be used anywhere a single value is expected, like directly in a SELECT list or a comparison in a WHERE clause. A table-valued subquery returns multiple rows and columns, and is used in contexts expecting a full result set, like the FROM clause or as the right side of an IN condition.

Run it incrementally wherever that's genuinely possible: keep both the old and new systems running in parallel for a defined transition period, validate that data and query results genuinely match between the two, and cut traffic over gradually rather than all at once in a single, high-risk, big-bang switch. A full stop-everything migration is rarely something the business will actually tolerate for very long.

I check whether the normalization level genuinely fits the actual, real access patterns rather than following normalization rules purely dogmatically, whether appropriate indexes are already planned for the specific queries the application will actually run most frequently, and whether the design will hold up reasonably well as the data volume grows substantially larger over time, not just at today's comparatively small scale.

Automate what can genuinely be automated, schema linting and required-index checks run directly in each team's own CI pipeline, so standards aren't purely a matter of individual opinion during manual code review. For the standards that resist full automation, I'd document the handful of decisions that actually matter most, along with the concrete reasoning behind each one, rather than a long, exhaustive database style guide nobody actually reads end to end.

I'd look at the actual, concrete access patterns and consistency requirements rather than choosing based on which technology happens to currently be more fashionable or widely discussed. Data with a genuinely flexible, frequently evolving schema and no strong requirement for complex joins across many different entities might fit a NoSQL document store well. Data with real relationships that genuinely need to be queried together, or that requires strong transactional consistency, usually still fits a relational database meaningfully better.

First check whether the actual test data volume genuinely resembles production, since a query that's fast on a small test dataset can become dramatically slower once the real, much larger production table is actually involved. Then I'd look specifically at lock contention and connection pool exhaustion under real concurrent load, both of which rarely show up at all in a low-concurrency test environment.

Track query latency, active connection count, replication lag if replicas are genuinely in use, and disk space over time, alerting on meaningful deviation from a normal, established baseline rather than relying only on a hard, static threshold. A slowly growing query latency trend is often a genuine early warning sign well before it actually causes a full, hard outage.

Treat the schema as a genuine contract with every consumer. Adding a new, nullable column is generally safe. Renaming or removing an existing column needs a proper, communicated migration path, adding the new column first, migrating consuming code over to actually use it, and only removing the old column once nothing genuinely still references it anymore.

I'd check first whether the underlying table's data volume or actual shape has genuinely changed recently, and whether the query's execution plan has itself changed, since a database can silently switch to a meaningfully worse plan once table statistics drift enough from what they were when the previous, faster plan was originally chosen. Updating statistics or rebuilding a fragmented index is a surprisingly common, and comparatively simple, actual fix for exactly this kind of issue.

Start from actual load testing against a realistically scaled-up dataset and traffic pattern, rather than a rough guess based purely on the application's current traffic numbers. I'd specifically identify whether the coming bottleneck is genuinely likely to be read capacity, write capacity, or storage, since each of those calls for a meaningfully different scaling solution, read replicas, a bigger primary instance, or partitioning, respectively.

This is a judgment question interviewers use to see how you reason under genuine uncertainty, not to test a specific textbook fact. A strong answer names the actual constraint that forced the decision, the realistic options that were genuinely on the table, why you picked one knowing it wasn't guaranteed to be right, and what you'd do differently with what you know now.

I'd walk through one of their actual queries together against a realistically large dataset, showing the execution plan directly and pointing out concretely where the real cost is genuinely coming from, rather than just telling them the query is inefficient in the abstract. Seeing their own query's actual plan and its real cost tends to shift how they write the next query far more effectively than a general warning about performance ever does.

I wouldn't lead with normalization theory in the abstract. I'd point to a specific, already-experienced, real data inconsistency bug that traced directly back to redundant, poorly modeled data, and show concretely how a more disciplined design would have genuinely prevented that exact bug from ever happening in the first place. A real, already-felt consequence is far more persuasive than an argument for best practices made purely in the abstract.

I'd bring the actual query patterns and real, measured performance data behind my position, rather than a general, unsubstantiated preference for one particular approach over another. Most disagreements like this genuinely resolve once both sides are looking at the exact same concrete numbers together, instead of arguing from each side's own differing, unstated assumptions about how the data is actually being used in practice.

I'd translate the work into terms leadership already tracks: the cost of a specific, already-experienced outage or slowdown that traced back to the current setup, and what continued growth in traffic or data volume would do to that same problem if left unaddressed. Framed as risk reduction with a concrete, already-incurred cost attached, it competes far better for prioritization than framed as a purely technical improvement.