25 SQL Interview Questions and Answers

SQL is the language itself. MySQL is one specific database management system (like PostgreSQL, SQL Server, or Oracle) that implements SQL, along with its own extensions and tooling.

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

A column (or combination of columns) that uniquely identifies each row in a table. It cannot contain NULL values, and a table can have only one primary key.

A column that references the primary key of another table, used to enforce a relationship between the two — for example, Employees.dept_id referencing Departments.dept_id.

Both enforce uniqueness, but a table can have only one primary key (which also cannot be NULL), while it can have several unique keys, and most databases allow a unique column to contain a single NULL value.

WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY has run and can use aggregates like COUNT() or SUM(). See our full GROUP BY vs HAVING guide.

DELETE removes rows one at a time, can be filtered with WHERE, and can be rolled back. TRUNCATE removes all rows at once and resets auto-increment counters, but generally can't be filtered. DROP removes the table itself, structure and all.

INNER JOIN (only matching rows), LEFT JOIN (all left rows, matched or NULL), RIGHT JOIN (all right rows, matched or NULL), and FULL OUTER JOIN (all rows from both sides). See the full JOINs guide for worked examples.

A join where a table is joined to itself, typically to compare rows within the same table — classic example: matching each employee to their manager, who is also a row in the Employees table.

SELECT e.first_name AS employee, m.first_name AS manager
FROM Employees e
LEFT JOIN Employees m ON m.emp_id = e.manager_id;

The process of organizing tables to reduce data redundancy and avoid update anomalies, typically by splitting data into related tables. The common normal forms: 1NF (atomic columns, no repeating groups), 2NF (no partial dependency on part of a composite key), and 3NF (no column depends on a non-key column).

Deliberately introducing redundancy (e.g. duplicating a column across tables, or pre-computing aggregates) to reduce the number of joins needed for read-heavy workloads, trading some write complexity and storage for faster reads.

An index is a separate, sorted data structure (typically a B-tree) that lets the database look up rows matching a condition without scanning the entire table. It speeds up reads on the indexed column(s) at the cost of extra storage and slightly slower writes, since the index must also be updated.

A clustered index determines the physical order rows are stored on disk — a table can have only one. A non-clustered index is a separate structure that points back to the actual rows, and a table can have several.

When you only need to filter or compare against a value computed from another table, rather than pull columns from it into the result. See the full subqueries guide.

A subquery that references a column from the outer query, so it can't be run independently — it's conceptually re-evaluated once for every row the outer query considers (e.g. an EXISTS check per customer).

Functions that compute a single result from a set of rows: COUNT(), SUM(), AVG(), MIN(), and MAX() are the most common, typically used with GROUP BY.

Both stack the results of two SELECT statements with the same column shape. UNION removes duplicate rows from the combined result (extra processing); UNION ALL keeps every row, including duplicates, and is faster.

A transaction is a group of one or more SQL statements executed as a single unit — either all of them succeed, or none do. ACID: Atomicity (all-or-nothing), Consistency (valid state to valid state), Isolation (concurrent transactions don't interfere), Durability (once committed, changes survive a crash).

COMMIT permanently saves all changes made in the current transaction. ROLLBACK undoes them, returning the data to how it was before the transaction started.

Functions that compute a value across a set of rows related to the current row (a "window"), without collapsing rows into groups the way GROUP BY does — so you keep every original row plus a computed column.

SELECT
    first_name,
    dept_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept
FROM Employees;

Both assign a rank within a window, with ties getting the same rank. RANK() leaves a gap after ties (1, 2, 2, 4), while DENSE_RANK() doesn't (1, 2, 2, 3).

A saved SELECT query that behaves like a virtual table — you can query it like a regular table, but it doesn't store data itself (unless it's a materialized view); it just re-runs the underlying query each time.

Group by the column(s) that should be unique, and filter to groups with more than one row using HAVING:

SELECT email, COUNT(*) AS occurrences
FROM Employees
GROUP BY email
HAVING COUNT(*) > 1;

A JOIN combines columns from two tables side by side, based on a matching condition, producing wider rows. A UNION stacks the rows of two queries on top of each other, producing more rows — the queries must return the same number and type of columns.