CandidateToHR

Top SQL Interview Questions Interview Questions | CandidateToHR

Master your SQL database interview. Covers Joins, Window Functions, Indexing, Normalization, and Query Optimization.

CandidateToHR provides highly optimized, professional tech career resources. Build, customize, and analyze your tech career credentials completely free.

50 real-world SQL interview questions ranging from basic aggregations to advanced query tuning and architecture.

Top Interview Questions & Answers

Beginner Interview Questions

Q: What is the difference between SQL and NoSQL?
A: SQL (Relational databases like PostgreSQL, MySQL) uses structured tables with predefined schemas and relies heavily on ACID compliance and JOIN operations. NoSQL (like MongoDB, Redis) uses flexible, schema-less data models (documents, key-value), designed to scale out horizontally across distributed clusters for massive unstructured data.
Q: What are DDL, DML, and DCL?
A: DDL (Data Definition Language) defines schema structures (`CREATE`, `ALTER`, `DROP`). DML (Data Manipulation Language) manipulates table data (`SELECT`, `INSERT`, `UPDATE`, `DELETE`). DCL (Data Control Language) manages user access and permissions (`GRANT`, `REVOKE`).
Q: Explain the different types of JOINs.
A: INNER JOIN returns records with matching values in both tables. LEFT JOIN returns all records from the left table, and matched records from the right (or NULL). RIGHT JOIN does the opposite. FULL OUTER JOIN returns all records when there is a match in either left or right table. CROSS JOIN returns the Cartesian product of both tables.
Q: What is a Primary Key?
A: A Primary Key is a column (or set of columns) that uniquely identifies each row in a table. It cannot contain NULL values and must be completely unique. By default, assigning a Primary Key automatically creates a clustered index on that column to speed up retrieval.
Q: What is a Foreign Key?
A: A Foreign Key is a column in one table that links to the Primary Key of another table. It enforces referential integrity, ensuring that the relationship between data in the two tables remains synchronized (e.g., an Order cannot exist for a Customer ID that does not exist).
Q: What is the difference between WHERE and HAVING?
A: Both filter data, but `WHERE` filters rows *before* aggregation (GROUP BY) takes place, while `HAVING` filters aggregated data *after* the GROUP BY clause. You cannot use aggregate functions like `SUM()` or `COUNT()` in a WHERE clause.
Q: What is the order of execution for a SQL query?
A: The logical execution order differs from the syntax order. It executes as follows: 1) FROM/JOIN, 2) WHERE, 3) GROUP BY, 4) HAVING, 5) SELECT, 6) ORDER BY, 7) LIMIT/OFFSET.
Q: Explain UNION vs UNION ALL.
A: Both combine the result sets of two or more SELECT statements. `UNION` automatically removes duplicate rows, which requires an expensive internal sort operation. `UNION ALL` keeps all duplicates and is therefore significantly faster.
Q: What is the difference between DELETE and TRUNCATE?
A: `DELETE` is a DML command; it removes rows one by one, fires database triggers, and can be rolled back inside a transaction. `TRUNCATE` is a DDL command; it rapidly deallocates the data pages containing the table data, does not fire triggers, and is much faster but cannot be targeted with a WHERE clause.
Q: What are Aggregate functions?
A: Aggregate functions perform calculations on a set of values and return a single scalar value. Common examples include `COUNT()`, `SUM()`, `AVG()`, `MIN()`, and `MAX()`. They are almost always used in conjunction with the `GROUP BY` clause.
Q: How do you find unique values in a column?
A: Use the `DISTINCT` keyword immediately after SELECT. For example, `SELECT DISTINCT department FROM employees;` returns a list of unique departments without duplicates.
Q: What is a View in SQL?
A: A View is a virtual table based on the result-set of a SQL statement. It contains rows and columns just like a real table, but the data isn't physically stored (unless it's a Materialized View). Views are used to encapsulate complex queries, hide sensitive columns, and simplify data access for end users.
Q: What is the `IN` operator used for?
A: The `IN` operator allows you to specify multiple values in a `WHERE` clause. It acts as shorthand for multiple `OR` conditions. Example: `WHERE country IN ('USA', 'UK', 'France')`.
Q: What is a database Schema?
A: A schema is a logical collection of database objects, including tables, views, triggers, and procedures. It acts as a namespace, helping to organize database architecture and manage access permissions (e.g., separating `hr` schema from `finance` schema).
Q: How does the `LIKE` operator work?
A: `LIKE` is used in a WHERE clause to search for a specified pattern in a column. It uses two wildcards: `%` represents zero, one, or multiple characters, and `_` represents a single character. For example, `LIKE 'A%'` finds any string starting with 'A'.
Q: What is a Subquery?
A: A subquery (or inner query) is a query nested inside another SQL query (the outer query). Subqueries can be used in SELECT, INSERT, UPDATE, or DELETE statements to compute values on the fly to drive the logic of the main query.
Q: What is the `COALESCE` function?
A: `COALESCE` evaluates its arguments in order and returns the first non-NULL value. It is incredibly useful for replacing NULLs with default fallback values. Example: `COALESCE(phone_number, 'No Phone Number Provided')`.

Intermediate Interview Questions

Q: What are Window Functions in SQL?
A: Window functions perform calculations across a set of table rows that are somehow related to the current row (the 'window'). Unlike `GROUP BY` aggregates which collapse rows into a single output, window functions retain the original rows. They are defined using the `OVER()` clause and are vital for running totals, rankings (`RANK()`, `ROW_NUMBER()`), and moving averages.
Q: Explain ROW_NUMBER(), RANK(), and DENSE_RANK().
A: All three are Window Functions for assigning rankings. `ROW_NUMBER()` gives a unique, sequential number to every row, breaking ties arbitrarily. `RANK()` gives identical values the same rank, but skips subsequent numbers (e.g., 1, 2, 2, 4). `DENSE_RANK()` gives identical values the same rank but does not skip numbers (e.g., 1, 2, 2, 3).
Q: What are Database Indexes and how do they work?
A: An index is an internal data structure (usually a B-Tree) that improves the speed of data retrieval operations. Instead of scanning the entire table (a full table scan), the database engine traverses the B-Tree to find the physical disk location of the rows instantly. However, indexes slow down write operations (INSERT/UPDATE/DELETE) because the index tree must be updated.
Q: What is the difference between Clustered and Non-Clustered Indexes?
A: A Clustered Index dictates the physical sorting order of the data on the disk. Therefore, a table can only have one clustered index (usually the Primary Key). A Non-Clustered index contains pointers (or clustered index keys) to the physical data rows. A table can have many non-clustered indexes.
Q: Explain ACID properties.
A: ACID guarantees transactional integrity in relational databases. Atomicity: All operations in a transaction succeed, or all fail (no partial state). Consistency: Transactions bring the DB from one valid state to another, upholding constraints. Isolation: Concurrent transactions do not interfere with each other. Durability: Once committed, data is saved permanently, even after a crash.
Q: What is Normalization?
A: Normalization is the process of organizing data to reduce redundancy and improve data integrity. 1NF ensures atomic values (no lists in a column). 2NF eliminates partial dependencies (all non-key columns depend on the whole primary key). 3NF eliminates transitive dependencies (non-key columns depend ONLY on the primary key).
Q: What is Denormalization and when is it used?
A: Denormalization is the deliberate introduction of redundancy into a database schema. While normalization is excellent for transactional (OLTP) systems, heavily normalized tables require complex, slow JOINs for reads. Denormalization is used in data warehousing and read-heavy systems (OLAP) to pre-join data into wide tables, drastically improving SELECT speed.
Q: Explain Common Table Expressions (CTEs).
A: A CTE is a temporary named result set defined using the `WITH` keyword. It exists only for the duration of a single query. CTEs make complex, multi-layered queries much more readable compared to nested subqueries. They can also reference themselves to create Recursive CTEs.
Q: What is a Stored Procedure?
A: A Stored Procedure is a prepared SQL code batch that you can save in the database. It can accept parameters, execute complex logic (including conditional IF/ELSE blocks and loops), and be reused repeatedly. They reduce network traffic and encapsulate business logic, though they tie the application tightly to a specific database vendor.
Q: What is the difference between a Stored Procedure and a User-Defined Function (UDF)?
A: A Function must return a value, cannot modify database state (no INSERT/UPDATE), and can be used directly inline within a `SELECT` or `WHERE` clause. A Stored Procedure may or may not return values, can modify database state, and is executed using a `CALL` or `EXEC` statement.
Q: What are Database Triggers?
A: A trigger is a special type of stored procedure that automatically executes ('fires') when a specific event occurs in the database table, such as an `INSERT`, `UPDATE`, or `DELETE`. They are often used for strict auditing, automatic timestamp updates, or enforcing complex business rules.
Q: What is the difference between an INNER JOIN and an EXISTS clause?
A: An `INNER JOIN` matches and returns rows combined from two tables. An `EXISTS` clause is a boolean operator checking if a subquery returns any rows. `EXISTS` is often much faster for checking presence because the database engine stops searching as soon as it finds the first match, whereas a JOIN processes all permutations.
Q: What is a Self-Join?
A: A Self-Join is a regular join where a table is joined to itself. This is highly useful for querying hierarchical data stored in a single table, such as an `employees` table where a `manager_id` column points to the `employee_id` of the same table. You must use table aliases to distinguish the instances.
Q: What are Correlated Subqueries?
A: A correlated subquery is a subquery that depends on values from the outer query. Unlike standard subqueries that execute once, a correlated subquery executes repeatedly—once for each row processed by the outer query. This makes them notorious performance killers.
Q: How does the `CASE` statement work?
A: `CASE` is SQL's version of an IF-THEN-ELSE statement. It allows you to conditionally transform data inside a SELECT statement. `CASE WHEN condition THEN result ELSE fallback_result END`. It is highly useful for pivoting data or categorizing numeric values into buckets.
Q: What is the purpose of `EXPLAIN` or `EXPLAIN ANALYZE`?
A: The `EXPLAIN` command shows the execution plan generated by the database query optimizer. It reveals how tables are scanned (Full Table Scan vs Index Scan), join methods (Hash Join, Nested Loop), and cost estimates. It is the primary tool developers use to debug slow queries and identify missing indexes.
Q: What are Isolation Levels?
A: Isolation levels dictate how database engines handle concurrent transactions. From lowest to highest strictness: Read Uncommitted (allows dirty reads), Read Committed (default in PG, prevents dirty reads), Repeatable Read (prevents non-repeatable reads), and Serializable (highest isolation, completely sequential execution, prevents phantom reads).

Advanced Interview Questions

Q: Explain SQL Injection and how to prevent it.
A: SQL Injection is a severe vulnerability where an attacker manipulates input fields to inject malicious SQL commands into the backend query, allowing them to bypass auth, dump databases, or drop tables. Prevention: ALWAYS use Parameterized Queries (Prepared Statements) or an ORM. Never concatenate raw user input strings directly into SQL statements.
Q: What is a Materialized View?
A: Unlike standard views which run the underlying query every time they are accessed, a Materialized View physically calculates and saves the result set to the disk. They offer massive performance boosts for complex analytical dashboards. The downside is data staleness; they must be periodically refreshed via cron jobs or triggers.
Q: How do you optimize a query that is running a Full Table Scan?
A: First, analyze the `WHERE` or `JOIN` clauses. Identify columns used for filtering or joining and create an Index on them. If the query uses multiple columns, create a Composite Index. Ensure functions aren't wrapping indexed columns (e.g., `WHERE YEAR(date) = 2023` ignores indexes; use `date >= '2023-01-01'` instead). Finally, avoid `SELECT *`; fetch only needed columns.
Q: What is a Covering Index?
A: A covering index contains all the columns needed to satisfy the query (both the columns in the WHERE clause and the columns in the SELECT clause). When a covering index is hit, the database engine retrieves the data directly from the index tree without needing to perform a 'Key Lookup' back to the physical table rows, resulting in ultra-fast queries.
Q: Explain the difference between Hash Join and Nested Loop Join.
A: These are execution plan strategies. A Nested Loop Join iterates through each row of the outer table and searches for matches in the inner table (great for small datasets or highly indexed queries). A Hash Join builds an in-memory hash table of the smaller table, then hashes the larger table's join column to find matches (great for joining massive, unindexed tables).
Q: What are Recursive CTEs and when do you use them?
A: A Recursive CTE references itself to iterate over data repeatedly until a condition is met. It consists of an 'anchor member' (base case) and a 'recursive member' combined with a `UNION ALL`. They are the standard tool for querying hierarchical or tree-structured data, such as an employee org chart or nested category structures.
Q: What is Database Sharding?
A: Sharding is a method of horizontal scaling. It breaks a massive database into smaller, manageable chunks called shards, which are distributed across multiple independent servers. A sharding key (like User ID or Region) dictates which shard holds the data. It massively increases throughput but introduces immense complexity for cross-shard JOINs.
Q: Explain Deadlocks and how to mitigate them.
A: A deadlock occurs when Transaction A holds a lock on Row 1 and needs Row 2, while Transaction B holds Row 2 and needs Row 1. Neither can proceed. Mitigation: 1) Always access tables/rows in the exact same order across all application code. 2) Keep transactions as short as possible. 3) Use lower isolation levels if acceptable. 4) Use retry logic in the application layer.
Q: What are dirty reads, non-repeatable reads, and phantom reads?
A: Dirty Read: Reading uncommitted data from an active transaction. Non-repeatable Read: A row is read twice in a transaction, but a concurrent transaction modifies it in between, yielding different values. Phantom Read: A query is run twice, but a concurrent transaction inserts new rows that match the query criteria in between.
Q: What is the difference between `Varchar` and `Char`?
A: `CHAR` is fixed-length. If you define `CHAR(10)` and insert 'abc', the DB pads it with 7 spaces, wasting disk space but offering faster retrieval for strict sizes (like hashes). `VARCHAR` is variable-length. `VARCHAR(10)` inserting 'abc' only takes 3 bytes plus overhead. Use `VARCHAR` for almost all text fields.
Q: Explain Database Partitioning.
A: Partitioning divides a large table into smaller, physical, hidden pieces on the disk, while maintaining a single logical table structure. E.g., partitioning a massive `logs` table by month. Queries filtering by month can use 'Partition Pruning' to scan only the relevant partition disk area, ignoring the rest, vastly improving query speed and archiving.
Q: How do you handle upserts (Insert or Update)?
A: An upsert attempts to insert a record, and if a unique constraint violation occurs, it updates the existing record. In PostgreSQL, this is done using `INSERT ... ON CONFLICT (id) DO UPDATE SET...`. In MySQL, it is `INSERT ... ON DUPLICATE KEY UPDATE`. It handles concurrency cleanly compared to a SELECT followed by an INSERT.
Q: What is a partial index?
A: A partial index (supported by Postgres, SQL Server) is an index built on a subset of a table, defined by a conditional expression (e.g., `CREATE INDEX idx_active ON users(id) WHERE status = 'active'`). This keeps the index size extremely small and updates fast, perfect for querying specific subsets of large tables.
Q: Explain the LEAD() and LAG() window functions.
A: These functions allow you to access data from subsequent or previous rows in the same result set without using Self-Joins. `LAG()` accesses the previous row, and `LEAD()` accesses the following row. They are heavily used in time-series data to calculate month-over-month growth or time elapsed between consecutive user events.
Q: What is CAP Theorem?
A: CAP theorem states that a distributed data store can only simultaneously provide two of three guarantees: Consistency (all nodes see the same data), Availability (every request receives a response), and Partition Tolerance (system continues to operate despite network failures). In distributed systems, Partition Tolerance is mandatory, so engineers must choose between Consistency (Relational DBs) and Availability (NoSQL).
Q: Why is `COUNT(column_name)` different from `COUNT(*)`?
A: `COUNT(*)` counts all rows in the result set, including rows that contain NULL values. `COUNT(column_name)` counts only the rows where the specified column is NOT NULL. Mixing these up is a common source of inaccurate reporting dashboards.

Frequently Asked Questions

How many questions are covered in this guide?

This guide covers 50+ of the most frequently asked questions.

Are these questions suitable for beginners?

Yes, the guide is divided into beginner, intermediate, and advanced sections.

How often is this guide updated?

We update our interview questions quarterly to ensure they reflect current industry standards.

Should I memorize the answers?

No, it is better to understand the underlying concepts rather than memorizing answers word-for-word.

Are these questions asked at FAANG companies?

Yes, many of these questions are standard in interviews at top tech companies like Google, Amazon, and Meta.

Related Resources & Next Steps