Database & SQL Interview Questions

Primary key uniquely identifies each row - one per table, implicitly NOT NULL, usually the clustering key.

Unique key also enforces uniqueness but a table can have many of them, they may be NULL (and MySQL allows multiple NULLs), and they are used to protect a second set of columns from duplicates.

Normalisation removes redundancy and anomalies by organising data into related tables.

• 1NF - atomic values; no repeating groups or lists in a column.
• 2NF - 1NF plus every non-key column depends on the whole primary key (kills partial dependency).
• 3NF - 2NF plus no transitive dependency (non-key columns depend only on the key).

Real systems balance normal forms against performance; occasional denormalisation is deliberate, not accidental.

• DELETE - removes rows (optionally with WHERE), fires triggers, can be rolled back inside a transaction; keeps the table.
• TRUNCATE - removes all rows quickly by deallocating pages; resets auto-increment, cannot usually be rolled back; keeps the table.
• DROP - removes the whole table (structure and data).

A subquery is a SELECT nested inside another query (in WHERE, FROM, SELECT or HAVING). Examples: WHERE salary > (SELECT AVG(salary) FROM emp) or a correlated subquery that references the outer row.

For set logic, prefer joins or CTEs when they read more clearly. Correlated subqueries can be slow on large tables.

UNION combines result sets and removes duplicate rows (performing a sort/distinct). UNION ALL keeps all rows, so it is faster when duplicates are fine or impossible.

Rule of thumb: if you can not have duplicates or you do not care, use UNION ALL - it avoids the de-duplication cost.

Window functions (ROW_NUMBER, RANK, LEAD/LAG, SUM OVER) compute a value across a set of rows related to the current row without collapsing them. Each input row keeps its identity and gets an extra computed column.

GROUP BY collapses rows into one row per group. Use window functions for running totals, rankings, moving averages and comparing a row to its neighbours.

Normalization organizes columns and tables to eliminate data redundancy and ensure dependencies are logical. It reduces duplication, storage waste and update anomalies (insert/update/delete inconsistencies). Higher normal forms add rules: each level guards against a specific class of problem, at the cost of more joins - so normalize in business-critical designs, then denormalize deliberately where reads demand it.

1NF - atomic values, no repeating groups (one value per cell). 2NF - 1NF plus every non-key column fully depends on the whole primary key (matters mainly with composite keys). 3NF - 2NF plus no transitive dependency: non-key columns must not depend on other non-key columns. BCNF is a stricter 3NF that covers overlapping candidate keys.

Denormalization intentionally adds redundant data (duplicated columns, summary tables, cached values) to reduce expensive joins and aggregation at query time. Use it when reads dominate, the data changes rarely, or reporting needs fixed aggregates - and manage consistency through the application or scheduled refresh.

A super key is any set of columns that uniquely identifies a row. A candidate key is a minimal super key (no subset is unique). The primary key is the candidate key chosen as the main identifier. The unused candidate keys are alternate keys. A foreign key references a primary/unique key of another table.

DDL (Data Definition) - CREATE, ALTER, DROP, TRUNCATE. DML (Data Manipulation) - SELECT, INSERT, UPDATE, DELETE. DCL (Data Control) - GRANT, REVOKE. TCL (Transaction Control) - COMMIT, ROLLBACK, SAVEPOINT. Some lists also mention DQL for SELECT alone.

DELETE removes rows one by one, supports WHERE, fires triggers, keeps auto-increment positions in MySQL, and is rollbackable within a transaction. TRUNCATE removes all rows in one DDL operation - no WHERE, no triggers, resets identity counters, and is typically faster though less rollbackable. Both remove data but not the table structure.

A composite key is a primary key made of two or more columns whose combination must be unique. For example a many-to-many junction table uses (user_id, course_id) as its composite primary key. The entity reflected in app code typically becomes a composite key class (@EmbeddedId/@IdClass in JPA).

A view is a stored query that behaves like a table. Advantages: security (expose only needed columns), encapsulation/simplification of complex joins, and a stable interface that can change its backing tables. Depending on the DB, some views are updatable; materialized views physically store the result for speed.

A view is a named SELECT whose results are generated every time you query it - always current, but costs execution each read. A materialized view stores the query result physically, so reads are fast, but it must be refreshed to reflect changes and its data can be stale between refreshes.

An index is a data structure (typically a B-tree, or hash in some DBs) that lets the database find rows quickly instead of scanning the whole table. It speeds SELECT/WHERE/JOIN/ORDER BY but slows INSERT/UPDATE/DELETE (indexes must be maintained) and consumes storage. Index wisely: cover the real query workload, not every column.

A clustered index defines the physical order of rows in a table - there can be only one, and the leaf level contains the data itself. A non-clustered index is a separate structure whose leaves hold pointers to the rows. Clustered lookups are fast, but inserts in the middle can cause reordering; SQL Server/MySQL InnoDB default differently.

A stored procedure is a precompiled block of SQL (with logic, loops, cursors) kept on the database server and invoked with CALL/EXEC, optionally with parameters. Benefits: reduced network traffic, code reuse, centralized validation, and the ability to enforce operations at the data layer. Downsides: harder version control and testing than application code.

A constraint declaratively enforces data rules (NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY) - the database refuses invalid data automatically. A trigger is procedural code (BEFORE/AFTER INSERT/UPDATE/DELETE) that runs automatically and can perform actions, validations or side effects beyond what a constraint expresses. Constraints are simpler and more predictable; triggers can hide logic.

Primary key - uniquely identifies rows and is never NULL. Foreign key - references another table's key to enforce relationships. Candidate key - minimal unique identifier. Alternate key - candidate key not chosen as primary. Composite key - a key of multiple columns. Surrogate key - a system-generated artificial key (auto-increment/UUID).

A natural key is a real attribute with business meaning that is unique, such as an email or PAN number. A surrogate key is a meaningless system-generated value (auto-increment id, UUID). Surrogates are stable when business data changes, are efficient for joins, and do not leak business info - but indexes on the natural key should still exist to enforce uniqueness/lookups.

A recursive query walks a self-referencing structure (employee-manager trees, menus, org charts) by repeated evaluation. Standard SQL uses a CTE:

WITH RECURSIVE org AS (
  SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id FROM employees e
  JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;

MySQL 8+, PostgreSQL and SQL Server support WITH RECURSIVE.

A CTE (WITH clause) is a named, reusable temporary result set defined at the top of a query; it can reference itself (recursion), is easier to read for multi-step logic, and can be used multiple times in the same statement. A subquery is an inline query nested in SELECT/FROM/WHERE; it is less reusable and often less readable for complex pipelines.

An anomaly is an inconsistency caused by redundancy in a badly normalized schema: Update anomaly - changing one fact requires updating many rows. Insert anomaly - you cannot record a fact until another unrelated fact exists. Delete anomaly - deleting a row removes information you wanted to keep. Normalization removes these.

An INNER JOIN combines rows from two different tables where the join condition matches. A SELF JOIN joins a table with itself using aliases - needed when a single table stores hierarchical data, for example employees referencing their own manager_id column:

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

Ranking functions (window functions): ROW_NUMBER() gives a unique sequential number per partition (no ties), RANK() gives the same rank to ties and leaves gaps, DENSE_RANK() gives the same rank to ties without gaps. Use with PARTITION BY and ORDER BY:

SELECT name, score,
       RANK() OVER (ORDER BY score DESC) AS rnk
FROM players;

Aggregate functions (SUM, COUNT, AVG, MIN, MAX, GROUP_CONCAT) take many rows and collapse them into one value per group; used with GROUP BY or over the whole set. Scalar functions (UPPER, LENGTH, ABS, DATEADD) take values per row and return a single value per row in the result set.

SQL/relational: fixed schema, tables with rows/columns, ACID transactions, joins, SQL queries - excellent for transactional consistency. NoSQL: flexible/typeless documents (MongoDB), key-values (Redis), wide columns (Cassandra) or graphs (Neo4j) - horizontally scalable, relaxed consistency, specialized query models. Choose by access pattern: joins and strong consistency favor SQL; huge scale and flexible schemas favor NoSQL.

A transaction is a logical unit of one or more statements that must succeed as a whole. It must guarantee ACID: Atomicity (all or nothing), Consistency (integrity preserved), Isolation (concurrent transactions do not interfere unexpectedly) and Durability (committed changes survive a crash). Transactions end with COMMIT or ROLLBACK.

Aggregates ignore NULLs by default - COUNT(col) counts non-NULL values, while COUNT(*) counts all rows. SUM/AVG return NULL when every value is NULL. Use COALESCE/ISNULL/IFNULL to substitute defaults (COALESCE(SUM(sales),0)), and test with IS NULL/IS NOT NULL rather than = NULL in WHERE clauses.