Top 50 SQL Interview Questions and Answers

SQL (Structured Query Language) is the standard language used to communicate with relational databases.

• Used to define schemas, query, manipulate, and control access to data.
• Split into sub-languages: DDL (schema), DML (data manipulation), DCL (access control), TCL (transactions), DQL (querying).
• Supported (with dialect variations) by MySQL, PostgreSQL, SQL Server, Oracle, SQLite, and more.

They differ in data model, schema flexibility, and scaling approach.

• SQL (relational): structured tables with fixed schemas, strong consistency, and JOINs across tables.
• NoSQL: flexible schemas (documents, key-value, graph, column-family), often prioritizes horizontal scalability and eventual consistency.
• Choose SQL for structured, relational data with complex queries; NoSQL for flexible, high-volume, rapidly evolving data.

SQL commands are grouped by purpose:

• DDL (Data Definition Language): CREATE, ALTER, DROP — define schema structure.
• DML (Data Manipulation Language): INSERT, UPDATE, DELETE — modify data.
• DQL (Data Query Language): SELECT — retrieve data.
• DCL (Data Control Language): GRANT, REVOKE — manage permissions.
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT — manage transactions.

A Primary Key uniquely identifies each row in a table.

• Must contain unique, non-null values.
• A table can have only one primary key, though it can span multiple columns (composite key).
• Automatically creates a unique index for fast lookups.

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));

A Foreign Key is a column (or set of columns) that references the primary key of another table.

• Enforces referential integrity — you can't insert a value that doesn't exist in the referenced table.
• Used to establish relationships between tables (e.g., one-to-many).

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT REFERENCES users(id)
);

Both enforce uniqueness, but with key differences:

• A table can have only one Primary Key, but multiple Unique Keys.
• Primary Key columns cannot be NULL; Unique Key columns can allow one NULL (in most databases).
• Primary Key is typically used as the main identifier and default clustering key.

A Composite Key is a primary key made up of two or more columns combined to uniquely identify a row.

• Used when no single column is unique enough on its own.
• Common in many-to-many join tables.

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id)
);

JOINs combine rows from two or more tables based on a related column.

• INNER JOIN: returns only matching rows in both tables.
• LEFT (OUTER) JOIN: all rows from the left table, matched rows from the right (NULL if no match).
• RIGHT (OUTER) JOIN: all rows from the right table, matched rows from the left.
• FULL (OUTER) JOIN: all rows from both tables, matched where possible.
• CROSS JOIN: Cartesian product of both tables.
• SELF JOIN: a table joined with itself.

They differ in how unmatched rows are handled.

• INNER JOIN: returns only rows with a match in both tables — unmatched rows are excluded entirely.
• OUTER JOIN (LEFT/RIGHT/FULL): includes unmatched rows from one or both tables, filling missing columns with NULL.

SELECT * FROM a INNER JOIN b ON a.id = b.a_id;
SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.a_id;

Both are outer joins, differing only in which table's unmatched rows are preserved.

• LEFT JOIN: keeps all rows from the left table; unmatched right-side columns are NULL.
• RIGHT JOIN: keeps all rows from the right table; unmatched left-side columns are NULL.
• A RIGHT JOIN can always be rewritten as a LEFT JOIN by swapping table order — most developers stick to LEFT JOIN for consistency.

A Self Join joins a table to itself, treating it as two separate tables via aliases.

• Useful for hierarchical or comparative data within the same table, like an employee-manager relationship.

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

A CROSS JOIN produces the Cartesian product of two tables — every row from the first table paired with every row from the second.

• If table A has m rows and table B has n rows, the result has m × n rows.
• Used rarely, typically to generate all possible combinations (e.g., sizes × colors).

SELECT * FROM sizes CROSS JOIN colors;

Normalization organizes data to reduce redundancy and avoid update anomalies.

• 1NF: each column holds atomic (indivisible) values, no repeating groups.
• 2NF: 1NF, plus every non-key column depends on the entire primary key (relevant for composite keys).
• 3NF: 2NF, plus no transitive dependencies — non-key columns depend only on the primary key, not on other non-key columns.

Denormalization intentionally introduces redundancy by combining tables or duplicating data.

• Trades some redundancy for faster reads, avoiding expensive JOINs.
• Common in read-heavy systems, reporting/analytics tables, and caching layers.
• Downside: more complex updates, since duplicated data must stay in sync.

An Index is a data structure (usually a B-tree) that speeds up data retrieval on a table.

• Allows the database to find rows without scanning the entire table, similar to a book's index.
• Speeds up SELECT queries with WHERE, JOIN, and ORDER BY on indexed columns.
• Trade-off: indexes slow down INSERT/UPDATE/DELETE since the index must also be updated.

CREATE INDEX idx_users_email ON users(email);

They differ in how data is physically stored relative to the index.

• Clustered Index: determines the physical order of rows in the table — a table can have only one.
• Non-Clustered Index: a separate structure with pointers back to the actual rows — a table can have many.
• Clustered index lookups are typically faster since the data lives directly in the index's leaf nodes.

A View is a virtual table defined by a stored SELECT query.

• Doesn't store data itself — it's re-executed each time it's queried.
• Useful for simplifying complex queries, restricting access to specific columns/rows, and improving readability.

CREATE VIEW active_users AS
SELECT id, name FROM users WHERE active = true;

A key difference is where the data physically lives.

• A Table stores actual data on disk.
• A View stores only a query definition — it computes results on the fly from underlying tables each time it's accessed.
• Views don't take up storage for data (aside from the query definition), but querying them adds the underlying query's cost each time.

Constraints enforce rules on the data allowed in a table.

• PRIMARY KEY: unique, non-null identifier.
• FOREIGN KEY: enforces a valid reference to another table.
• UNIQUE: ensures all values in a column are distinct.
• NOT NULL: disallows NULL values.
• CHECK: ensures values satisfy a specific condition.
• DEFAULT: provides a default value when none is specified.

All three remove data, but at different levels:

• DELETE: removes specific rows (optionally with WHERE), is logged row-by-row, and can be rolled back.
• TRUNCATE: removes all rows quickly by deallocating data pages — faster than DELETE, minimally logged, resets auto-increment counters.
• DROP: removes the entire table structure and its data permanently.

A Subquery is a query nested inside another query.

• Non-correlated subquery: runs independently of the outer query — executed once.
• Correlated subquery: references a column from the outer query, so it's re-evaluated once per row of the outer query — generally slower.

-- correlated
SELECT name FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);

Both filter rows, but at different stages of query execution.

• WHERE: filters individual rows before grouping/aggregation happens.
• HAVING: filters grouped results after GROUP BY and aggregate functions are applied.

SELECT dept_id, COUNT(*) FROM employees
WHERE active = true
GROUP BY dept_id
HAVING COUNT(*) > 5;

Aggregate functions compute a single summary value from a set of rows.

• COUNT() — number of rows.
• SUM() — total of a numeric column.
• AVG() — average value.
• MIN() / MAX() — smallest/largest value.
• Typically used together with GROUP BY to summarize data per group.

GROUP BY groups rows sharing the same value(s) in specified columns, so aggregate functions compute per group rather than across the entire table.

SELECT dept_id, AVG(salary)
FROM employees
GROUP BY dept_id;

• Every non-aggregated column in SELECT must appear in the GROUP BY clause (in standard SQL).

A Transaction is a sequence of operations executed as a single logical unit of work.

• Atomicity: all operations succeed, or none do.
• Consistency: the database moves from one valid state to another.
• Isolation: concurrent transactions don't interfere with each other's intermediate states.
• Durability: once committed, changes persist even after a crash.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Isolation levels control how much one transaction can see of another's uncommitted changes, trading consistency for concurrency.

• Read Uncommitted: allows dirty reads (lowest isolation).
• Read Committed: only sees committed data (default in many databases).
• Repeatable Read: guarantees the same rows return identical values within a transaction.
• Serializable: highest isolation — transactions behave as if executed one at a time.

A Deadlock happens when two transactions each hold a lock the other needs, so neither can proceed.

• Example: Transaction A locks Row 1 and waits for Row 2; Transaction B locks Row 2 and waits for Row 1.
• Most databases automatically detect deadlocks and abort one transaction to break the cycle.
• Avoidance: always access tables/rows in a consistent order, keep transactions short, and use appropriate isolation levels.

Both combine result sets from multiple SELECT queries.

• UNION: removes duplicate rows from the combined result — requires an internal sort/dedup step.
• UNION ALL: keeps all rows, including duplicates — faster since no deduplication is needed.
• Both require the queries to have the same number of columns with compatible types.

A Stored Procedure is a precompiled set of SQL statements saved in the database, callable by name.

• Can accept parameters and contain control-flow logic (loops, conditionals).
• Improves performance (precompiled, less network round-trips) and centralizes business logic.

CREATE PROCEDURE get_user(IN uid INT)
BEGIN
  SELECT * FROM users WHERE id = uid;
END;

A Trigger is a stored procedure automatically executed in response to a specific event (INSERT, UPDATE, DELETE) on a table.

• Runs BEFORE or AFTER the triggering event.
• Commonly used for auditing, enforcing complex business rules, or maintaining derived/denormalized data.

CREATE TRIGGER log_update AFTER UPDATE ON accounts
FOR EACH ROW INSERT INTO audit_log VALUES (OLD.id, NOW());

Both encapsulate reusable SQL logic, but differ in usage:

• A Function must return a value and can be used directly inside a SELECT statement or expression.
• A Stored Procedure may or may not return a value, can have output parameters, and is called independently (not inline in a query).
• Functions typically can't modify database state (in strict implementations); procedures can.

A Cursor allows row-by-row processing of a query result set, instead of operating on the whole set at once.

• Useful when logic must be applied sequentially to each row (rare in modern set-based SQL, but common in older procedural code).
• Generally slower than set-based operations — should be avoided unless truly necessary.

Both store character/string data, but differently:

• CHAR(n): fixed-length — always stores exactly n characters, padding shorter values with spaces.
• VARCHAR(n): variable-length — stores only as many characters as needed, up to the max n.
• CHAR can be marginally faster for fixed-size data; VARCHAR saves space for variable-length text.

NULL represents the absence of a value — it's not the same as zero, an empty string, or false.

• Any comparison with NULL (e.g., NULL = NULL) returns NULL (unknown), not true.
• Must use IS NULL / IS NOT NULL to test for it, since = NULL never matches.

Several functions and operators exist to handle NULLs explicitly:

• IS NULL / IS NOT NULL — check for null.
• COALESCE(a, b, c) — returns the first non-null value.
• IFNULL(a, b) / ISNULL(a, b) (database-specific) — replaces null with a default.

SELECT COALESCE(nickname, name) AS display_name FROM users;

A CTE is a named, temporary result set defined using WITH, usable within a single query.

• Improves readability by breaking complex queries into logical steps.
• Can be recursive, useful for hierarchical data like org charts or category trees.

WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 100000
)
SELECT * FROM high_earners WHERE dept_id = 3;

A Window Function performs a calculation across a set of rows related to the current row, without collapsing them into a single output row (unlike GROUP BY).

SELECT name, salary,
  AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;

• Uses the OVER() clause with optional PARTITION BY and ORDER BY.

All three assign a sequential rank within a window, but handle ties differently.

• ROW_NUMBER(): assigns a unique number to every row, even if values tie.
• RANK(): gives tied rows the same rank, but skips subsequent rank numbers (1,1,3).
• DENSE_RANK(): gives tied rows the same rank, with no gaps in numbering (1,1,2).

A Schema is a logical namespace that groups related database objects (tables, views, procedures).

• Helps organize objects, manage permissions, and avoid naming collisions between different applications sharing a database.
• In PostgreSQL, for example, the default schema is public.

Referential Integrity ensures relationships between tables remain valid and consistent.

• Enforced primarily via Foreign Key constraints — you can't reference a row that doesn't exist.
• Prevents "orphaned" rows, e.g., an order referencing a deleted customer.
• Databases can enforce cascading behavior (ON DELETE CASCADE, ON DELETE SET NULL) to maintain integrity automatically.

Anomalies are problems caused by poor normalization, mainly in three forms:

• Insertion anomaly: can't add data without also having unrelated data (e.g., can't add a course without a student).
• Update anomaly: the same data is duplicated in multiple rows, so updates must be repeated everywhere it's stored.
• Deletion anomaly: deleting a row unintentionally removes other useful information.

They serve different workload types:

• OLTP (Online Transaction Processing): many short, frequent read/write transactions — e.g., order processing systems. Optimized for speed and consistency.
• OLAP (Online Analytical Processing): complex read-heavy queries over large historical datasets — e.g., reporting/data warehouses. Optimized for aggregation and analysis.

A Materialized View stores the actual result set of a query on disk, unlike a regular view which recomputes on every access.

• Must be manually or periodically refreshed to reflect underlying data changes.
• Trades storage and staleness for much faster read performance on expensive queries.

SQL Injection is an attack where malicious input is inserted into a query, altering its logic.

-- vulnerable: user input concatenated directly
"SELECT * FROM users WHERE name = '" + input + "'"

• Prevented by using parameterized queries / prepared statements instead of string concatenation.
• Also mitigated by input validation, least-privilege database accounts, and ORM libraries that escape input automatically.

Both check for matching rows, but differ in evaluation and performance characteristics.

• IN: compares a value against a fixed list or subquery result — can be slower with large subquery results since it may materialize the full list.
• EXISTS: checks only whether a subquery returns any row, stopping as soon as one match is found — often faster for large or correlated subqueries.

Sharding splits a large database into smaller, independent pieces called shards, each holding a subset of the data (usually by a shard key).

• Enables horizontal scaling by distributing load across multiple servers.
• Adds complexity: cross-shard queries/joins and rebalancing become harder.

Replication copies data from one database server (primary) to one or more other servers (replicas).

• Improves read scalability (reads can be served from replicas) and fault tolerance (failover if the primary goes down).
• Can be synchronous (waits for replicas to confirm) or asynchronous (faster writes, replicas may lag slightly).

They're optimized for different purposes:

• A Database (typically OLTP) handles day-to-day transactional operations with normalized schemas.
• A Data Warehouse (OLAP) consolidates data from multiple sources for historical analysis and reporting, often using denormalized star/snowflake schemas.

An Execution Plan shows how the database engine intends to execute a query — which indexes it uses, join order, and estimated cost.

• Generated with EXPLAIN (or EXPLAIN ANALYZE for actual runtime stats).
• Essential for diagnosing slow queries and deciding whether an index is being used effectively.

EXPLAIN SELECT * FROM orders WHERE customer_id = 5;

They represent two different consistency philosophies:

• ACID (Atomicity, Consistency, Isolation, Durability): prioritizes strict consistency — typical of relational databases.
• BASE (Basically Available, Soft state, Eventual consistency): prioritizes availability and scalability over immediate consistency — common in many NoSQL systems.
• The choice reflects a trade-off described by the CAP theorem (Consistency, Availability, Partition tolerance — pick two).