SQL interview questions

SQL stands for Structured Query Language, and it is the standard language used to store, read, update, and manage data in relational databases. It is important because most real applications need reliable ways to query business data, reports, users, payments, inventory, and many other structured records.

In interviews, a strong short answer is that SQL is the language used to work with relational data. You can add that SQL is not just for reading data with `SELECT`; it also handles inserts, updates, deletes, schema creation, constraints, and transaction control.

SQL is the language, while MySQL is one database management system that uses SQL. In simple words, SQL is like the grammar, and MySQL is one software product that understands and executes that grammar.

This is a common interview trap because many people use the names as if they mean the same thing. A safe answer is: SQL is the standard query language, and MySQL is one database platform, just like PostgreSQL, SQL Server, and Oracle are database platforms too.

A primary key is a column or a group of columns that uniquely identifies each row in a table. It should not contain duplicate values, and in most database systems it also cannot contain `NULL` values.

In interviews, the simplest answer is that a primary key gives every row its unique identity. It is important because it prevents duplicate records at the row-identity level and is commonly used when other tables need to reference that row.

Both are used to enforce uniqueness, but a primary key is the main identifier of a row, while a unique key is an additional rule that also prevents duplicate values. A table usually has one primary key, but it can have multiple unique keys.

In interviews, one more important point is that a primary key is normally not allowed to be `NULL`, while unique constraints are often more flexible depending on the database system. A good summary is: primary key for main identity, unique key for extra uniqueness rules.

A foreign key is a column or group of columns in one table that refers to the primary key or unique key of another table. It is used to create relationships between tables and to help maintain referential integrity.

A simple interview example is that `orders.customer_id` may refer to `customers.id`. The main point is that a foreign key prevents invalid relationships, such as storing an order for a customer that does not exist in the customer table.

`NULL` represents missing, unknown, or not-applicable data. It is not the same as zero, an empty string, or the word `NULL`; it means the value is absent or unknown in a database sense.

Interviewers ask this because `NULL` affects comparisons and filtering in special ways. A strong answer is that normal equality checks do not work the same with `NULL`, so SQL uses checks like `IS NULL` and `IS NOT NULL` instead of `= NULL`.

`WHERE` filters rows before grouping and aggregation happen, while `HAVING` filters groups after aggregation has already been applied. That is why `WHERE` is used for row-level filtering and `HAVING` is used for aggregate-based filtering.

A short interview memory trick is: `WHERE` works before `GROUP BY`, and `HAVING` works after `GROUP BY`. For example, you use `WHERE department = 'Sales'` to limit rows first, but `HAVING COUNT(*) > 5` to keep only groups whose aggregate result meets a condition.

`DELETE` removes selected rows and can use a `WHERE` clause, `TRUNCATE` removes all rows from a table much more directly, and `DROP` removes the table structure itself from the database. So the difference is not just speed, but also what actually survives after the command runs.

In interviews, the clean summary is: `DELETE` keeps the table and can remove specific rows, `TRUNCATE` clears all data but keeps the table, and `DROP` removes the whole table object. It is also good to mention that exact transactional behavior can vary by database system.

`GROUP BY` is used to collect rows with the same values into groups so aggregate functions like `COUNT`, `SUM`, `AVG`, `MIN`, and `MAX` can be applied to each group. It is one of the most common tools for reporting and summary queries.

In interviews, explain it as turning row-level data into summary-level data. For example, instead of showing every employee row, `GROUP BY department` can show one row per department with a count or total salary.

A view is a virtual table based on the result of a query. It does not always store data separately like a normal table; instead, it presents data from one or more tables through a saved query definition.

Views are useful for simplifying complex queries, hiding unnecessary columns, and giving controlled access to data. In interviews, the best short answer is that a view is a stored query that behaves like a table in many query situations.

A join is used to combine rows from two or more tables based on a related column. Joins are important because normalized databases usually keep related data in separate tables, so joins are how we bring that data together when querying.

In interviews, say that joins connect related tables such as `customers` and `orders`. A strong simple line is: joins let us query across relationships instead of storing everything in one big repeated table.

`INNER JOIN` returns only the rows that match in both tables, while `LEFT JOIN` returns all rows from the left table and the matching rows from the right table. If no match exists on the right side, the right-side columns appear as `NULL`.

This is one of the most asked SQL interview questions, so the answer should be very clear. A simple memory trick is: `INNER JOIN` keeps only matches, and `LEFT JOIN` keeps all left rows whether they match or not.

A self join is a join where a table is joined with itself using different aliases. It is useful when the rows in one table have relationships with other rows in the same table, such as employee-manager relationships.

In interviews, the key point is that the table is not duplicated physically; it is just referenced twice in the query with separate names. A common example is joining an `employees` table to itself to show each employee and that employee's manager.

A cross join returns the Cartesian product of two tables, which means every row from the first table is paired with every row from the second table. It can create very large result sets, so it should be used only when that behavior is actually intended.

The safest interview explanation is that cross joins multiply rows. If one table has 10 rows and another has 5 rows, the result has 50 rows, which is why accidental cross joins can be dangerous in real systems.

`UNION` combines results from two queries and removes duplicates, while `UNION ALL` combines them without removing duplicates. Because of that extra duplicate-check work, `UNION ALL` is usually faster when duplicate removal is not needed.

In interviews, a simple answer is: `UNION` deduplicates, `UNION ALL` does not. It is also worth mentioning that both queries must return the same number of columns with compatible data types.

A subquery is a query written inside another query. It is used when the result of one query is needed as part of the logic of a larger query.

In interviews, explain it simply as a query within a query. Subqueries are commonly used in `WHERE`, `FROM`, or `SELECT` clauses, depending on whether you need filtering, temporary derived data, or computed values.

A correlated subquery is a subquery that depends on values from the outer query, so it cannot run independently in the same way a normal subquery can. It is evaluated in relation to each row being considered by the outer query.

In interviews, the main difference to mention is dependency. A regular subquery can often run once on its own, but a correlated subquery uses values from the outer row, which is why it can be more expensive if not written carefully.

`IN` checks whether a value appears in a list or subquery result, while `EXISTS` checks whether at least one matching row exists. They can sometimes solve similar problems, but their behavior and performance can differ depending on the database system and query shape.

The best interview answer is that `EXISTS` is often used for existence checks tied to the outer query, especially in correlated cases, while `IN` feels more like membership testing. Avoid saying one is always faster, because real performance depends on the optimizer and data.

A CTE is a named temporary result set defined using the `WITH` clause and used within a single query. It makes complex queries easier to read, structure, and maintain.

In interviews, a clean answer is that a CTE improves readability by breaking a large query into logical steps. It is especially useful when the same derived logic needs to be referenced clearly within a query or when recursive querying is needed.

A CTE is usually chosen when the query becomes hard to read or when you want to break the logic into named steps. A subquery is fine for smaller cases, but CTEs often make complex logic much easier to understand during interviews and in production code.

The safest answer is not that CTEs are always faster, because optimization behavior depends on the database system. The main benefit to emphasize is readability, maintainability, and cleaner query structure.

Aggregate functions perform calculations on multiple rows and return one summary result for each group or for the whole result set. Common examples are `COUNT`, `SUM`, `AVG`, `MIN`, and `MAX`.

In interviews, explain aggregate functions as tools that turn detailed row data into summary data. They are heavily used in reporting, dashboards, analytics, and interview questions involving totals, averages, counts, and rankings.

`COUNT(*)` counts all rows that match the query, while `COUNT(column)` counts only the rows where that column is not `NULL`. That is why the two counts can be different when missing values exist.

This is a very common interview detail because it checks whether you understand how `NULL` affects aggregates. A strong memory trick is: star counts rows, column counts non-null values in that column.

A window function performs a calculation across a set of related rows without collapsing them into a single output row the way `GROUP BY` does. It lets you keep the original rows while also adding ranking, running totals, moving averages, and similar computed values.

In interviews, the easiest way to explain it is: aggregate functions summarize rows into fewer rows, but window functions return calculations while still showing each row. That makes window functions very useful in reporting and ranking questions.

`ROW_NUMBER()` gives every row a unique sequence number, even when values tie. `RANK()` gives the same rank to tied rows but leaves gaps after ties, while `DENSE_RANK()` also gives the same rank to ties but does not leave gaps.

This is one of the most useful SQL interview ranking questions, so the tie behavior is the key point to remember. A short memory trick is: `ROW_NUMBER()` no ties, `RANK()` ties with gaps, `DENSE_RANK()` ties without gaps.

`PARTITION BY` divides the result set into smaller groups before the window function is applied. The calculation then restarts within each partition instead of running across the entire result set.

A common interview example is ranking employees within each department instead of ranking all employees globally. The simplest explanation is that `PARTITION BY` creates separate windows for the function to work on.

A common way is to use a ranking function like `DENSE_RANK()` and then filter for rank 2, or to use a subquery depending on the database and the interview style. The important part is handling duplicates correctly when salaries repeat.

Interviewers often use this question to test both SQL syntax and thinking. A strong answer is that ranking functions are usually cleaner and more readable than deeply nested queries, especially when ties must be handled clearly.

The `CASE` expression is used to apply conditional logic inside a query. It works like an if-else structure and is useful for creating labels, categories, conditional calculations, and readable result columns.

In interviews, explain it as SQL's built-in conditional expression. It is very commonly used in reports, such as converting salary ranges into labels like 'Low', 'Medium', and 'High'.

`WHERE` filters rows, `GROUP BY` forms groups for aggregation, and `ORDER BY` sorts the final result. They solve different problems, and mixing them up is one of the most common SQL interview mistakes.

A safe interview summary is: filter first, group if needed, then sort the output. This order-based explanation helps candidates reason about why aggregate filters belong in `HAVING` instead of `WHERE`.

To remove duplicate rows from a query result, you usually use `SELECT DISTINCT`. If the goal is to clean duplicates from stored table data, then you need a separate deletion strategy, often using row numbering or grouping logic.

In interviews, clarify whether the question means duplicate rows in the output or duplicate data inside the table. `DISTINCT` solves result duplication, but permanent data cleanup usually needs more careful logic.

A common approach is to assign row numbers within duplicate groups and then delete rows whose row number is greater than 1. The exact syntax can vary slightly by database system, but the core idea is to identify one row to keep and remove the rest.

This is a popular interview problem because it tests practical SQL, not just theory. The safest answer is to say you would first define what makes rows duplicates, then rank them, keep one row, and delete the others carefully.

Normalization is the process of organizing data into well-structured tables to reduce redundancy and improve data consistency. The goal is to avoid storing the same fact in many places and to reduce update, insert, and delete anomalies.

In interviews, the most useful answer is not a textbook definition but the purpose behind it. Normalization makes databases cleaner and more reliable by separating data into related tables instead of one large repeated table.

1NF means data should be stored in atomic form with no repeating groups, 2NF means non-key columns should fully depend on the whole primary key, and 3NF means non-key columns should not depend on other non-key columns. These normal forms gradually reduce redundancy and dependency problems.

In interviews, keep the explanation simple and practical rather than overly formal. A strong answer is that each normal form removes a specific kind of design problem, making updates safer and the schema easier to maintain.

Denormalization means intentionally adding some redundancy back into a schema to improve read performance or simplify heavily used queries. It is usually used in reporting, analytics, or very high-read systems where faster access matters more than perfectly normalized structure.

In interviews, the key point is trade-off. Normalization improves consistency, while denormalization can improve speed and reduce expensive joins, but it also increases the risk of duplicated or inconsistent data if not handled carefully.

A super key is any set of columns that can uniquely identify a row, a candidate key is a minimal super key, and a primary key is the candidate key chosen as the main row identifier. So the relationship is based on uniqueness first, minimality second, and official selection third.

This is a theory-heavy interview question, but the easiest way to explain it is by narrowing from broad to specific. Every primary key is a candidate key, and every candidate key is a super key, but not the other way around.

A composite key is a key made of two or more columns together. It is used when one column alone is not enough to uniquely identify a row, but the combination of columns is unique.

A common interview example is an enrollment table where `student_id` and `course_id` together identify one enrollment row. The main idea is that uniqueness comes from the combination, not from each individual column by itself.

Constraints are rules applied to table columns or tables to enforce data correctness and integrity. Common constraints include `PRIMARY KEY`, `FOREIGN KEY`, `UNIQUE`, `NOT NULL`, `CHECK`, and `DEFAULT`.

In interviews, the best summary is that constraints protect the database from invalid data. Instead of relying only on application code, constraints let the database itself enforce important rules.

A normal view is usually a saved query that returns current data when queried, while a materialized view stores the query result physically and may need refreshing to stay updated. Because of that, materialized views can improve read speed for expensive queries but may show stale data between refreshes.

In interviews, say that the key trade-off is freshness versus speed. Normal views are lighter and always reflect underlying data, while materialized views can be faster for reporting but require refresh strategy and vendor-specific support.

A stored procedure is usually used to perform a set of operations, while a function is generally used to return a value or a result that can be reused in expressions or queries. The exact capabilities differ across database systems, but this is the general interview-level distinction.

The safest answer is to describe their usual purpose instead of making vendor-specific absolute statements. Procedures are often used for business operations and workflows, while functions are more commonly used for reusable calculation or transformation logic.

A trigger is a database object that runs automatically when a specified event happens on a table, such as an insert, update, or delete. It is often used for auditing, validation, derived updates, or enforcing special business rules.

In interviews, mention that triggers are powerful but should be used carefully because they can make behavior less visible and harder to debug. A strong short answer is: triggers are automatic database-side reactions to data changes.

An index is a database structure that helps the database find rows faster without scanning the whole table every time. It improves query performance, especially for filtering, joining, and sorting on frequently searched columns.

In interviews, the key trade-off is very important: indexes improve reads but add extra cost for inserts, updates, and deletes because the index must also be maintained. A strong answer is: indexes speed lookups, but too many indexes can slow writes and consume storage.

At an interview level, a clustered index is usually explained as one that affects how table data is physically organized, while a non-clustered index is a separate structure that points to the data. The exact implementation can vary by database system, so it is important to mention that this concept is somewhat vendor-specific.

The safest answer is to focus on the general idea instead of absolute engine-specific details. A clustered index is often used for the main physical ordering, while non-clustered indexes are additional lookup helpers built on top of the table data.

A transaction is a sequence of database operations treated as one logical unit of work. Either all important steps succeed together or the database can roll them back to keep the data consistent.

In interviews, explain transactions with a banking-style example like transferring money between accounts. If one step succeeds and the second fails, the transaction should roll back so the database does not stay in a half-complete state.

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties describe how transactions should behave so the database remains correct and reliable even when errors, concurrency, or system failures occur.

In interviews, do not just expand the letters; briefly explain them. Atomicity means all-or-nothing, Consistency means valid rules are preserved, Isolation means concurrent transactions do not interfere incorrectly, and Durability means committed changes survive crashes.

`COMMIT` permanently saves the transaction changes, `ROLLBACK` undoes uncommitted changes, and `SAVEPOINT` creates a checkpoint inside a transaction so you can roll back only part of the work if needed. These commands are used to control transactional behavior safely.

In interviews, a strong answer is that `SAVEPOINT` gives partial recovery inside a transaction instead of forcing a full rollback every time. This is useful in workflows with multiple steps where only some later step fails.

Isolation levels define how much one transaction can see or be affected by the work of other concurrent transactions. They are used to balance data correctness against concurrency and performance.

A good interview answer is that stronger isolation reduces concurrency problems but can add more locking or overhead. The exact level names and behavior are database-specific, but the main idea is controlling visibility and interference between transactions.

A dirty read happens when one transaction reads uncommitted data from another transaction, a non-repeatable read happens when the same row gives different values in the same transaction, and a phantom read happens when repeating a range query returns a different set of rows. These are classic concurrency problems used to explain isolation levels.

In interviews, the easiest way to remember them is data not committed, same row changed, and new or missing rows in a repeated range query. This question is less about memorizing words and more about understanding what can go wrong when many users access data together.

A deadlock happens when two or more transactions wait on each other in a way that prevents any of them from moving forward. In databases, this often happens when transactions lock resources in different orders and each waits for the other to release a lock.

In interviews, the most useful point is prevention and handling. Good transaction design, shorter transactions, and consistent lock ordering reduce deadlock risk, and database systems usually detect deadlocks and abort one transaction to break the cycle.

The first step is to understand why the query is slow by checking the execution plan, indexes, joins, filters, and amount of scanned data. After that, common fixes include adding the right indexes, reducing unnecessary columns, simplifying logic, avoiding expensive full scans when possible, and rewriting inefficient joins or subqueries.

In interviews, do not answer with 'add an index' only. A stronger answer is that query tuning starts with measurement and plan analysis, because performance problems can come from missing indexes, bad join order, poor filtering, unnecessary sorting, or returning far more data than needed.

A full table scan reads all rows of a table to find the needed data, while an index scan uses an index structure to reach matching rows more selectively. Which is better depends on the query, selectivity, and database optimizer decisions.

The best interview answer is balanced: index scans are not always better. If a query needs a large percentage of the table, the optimizer may prefer a full scan because using the index plus fetching many rows can cost more than scanning the table directly.

Joins combine related data from multiple tables in one result set, while subqueries use the result of one query inside another query. Both can solve many of the same problems, so the real choice often depends on readability, query intent, and how the database optimizer handles the final query.

In interviews, avoid saying joins are always faster or subqueries are always easier. A better answer is that joins are often clearer for combining tables directly, while subqueries are useful when one query logically depends on another result, especially for filtering or step-by-step reasoning.