What SQL is and where it's used, the statement categories (DDL, DML, DQL, DCL, TCL), joins, SELECT, normalization, MySQL data types, primary vs unique vs foreign keys, NULL vs zero, and transactions with ACID.
Published September 25, 2026
Backend interviews almost always include SQL, even for Java roles. The basics below are asked quickly, often as a warm-up before a query-writing question. Accuracy matters: mixing up DCL and TCL, or saying "NULL equals NULL", costs easy marks.
Short answer: SQL (Structured Query Language) is the standard declarative language for working with relational databases. You describe what data you want (or want to change), and the database's query optimiser decides how to get it. It's used to define schemas, query and modify data, control access, and manage transactions.
Key points to cover:
Learn it in depth → Practical SQL & JDBC for Interviews
Short answer:
Short answer:
| Category | Purpose | Commands |
|---|---|---|
| DDL — Data Definition | Define and change structure | CREATE, ALTER, DROP, TRUNCATE, RENAME |
| DML — Data Manipulation | Change data | INSERT, UPDATE, DELETE, MERGE |
| DQL — Data Query | Read data | SELECT (often grouped under DML) |
| DCL — Data Control | Permissions | GRANT, REVOKE |
| TCL — Transaction Control | Manage transactions | COMMIT, ROLLBACK, SAVEPOINT |
Common trap: saying DCL covers "transactions, locks, commit and rollback". Those belong to TCL. DCL is only about privileges (GRANT/REVOKE).
Key points to cover:
TRUNCATE is DDL. In MySQL and Oracle it causes an implicit commit, and can't be rolled back. PostgreSQL allows it inside a transaction.Short answer: A join combines rows from two or more tables, based on a related column (usually a foreign key matching a primary key), into a single result. Relational designs split data across tables, and joins put it back together at query time.
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id; -- INNER JOIN: only orders that have a matching customer
Key points to cover:
INNER, LEFT/RIGHT OUTER, FULL OUTER (not in MySQL), CROSS and self joins. They're covered in the next lesson.Learn it in depth → SQL Joins
SELECT statement?Short answer: SELECT retrieves data. It lets you choose columns, filter rows (WHERE), join tables, aggregate (GROUP BY with COUNT, SUM, AVG), filter groups (HAVING), sort (ORDER BY), and limit results (LIMIT/OFFSET).
SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
WHERE active = TRUE
GROUP BY department
HAVING COUNT(*) >= 5
ORDER BY avg_salary DESC
LIMIT 10;
Key points to cover:
FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That explains why a column alias defined in SELECT can't be used in WHERE.SELECT * in application code. Name the columns, so you transfer less data and schema changes don't break things.Short answer: Normalization organises tables to remove redundancy and the update anomalies it causes, by splitting data into related tables so that each fact is stored once.
| Normal form | Rule (simplified) |
|---|---|
| 1NF | Atomic values; no repeating groups or lists in a column |
| 2NF | 1NF, and every non-key column depends on the whole primary key (matters for composite keys) |
| 3NF | 2NF, and no non-key column depends on another non-key column (no transitive dependencies) |
| BCNF | Every determinant is a candidate key |
Example: a student_courses table that repeats each instructor's name and email on every row. When the email changes, you must update many rows, and missing one leaves inconsistent data. Moving instructors into their own table, and referencing them by ID, stores each email once. (Strictly, this removes a transitive dependency, course → instructor → email, so it's a 3NF fix.)
Key points to cover:
Learn it in depth → Normalization
Short answer:
TINYINT, INT, BIGINT, DECIMAL(p,s) (exact, use it for money), FLOAT/DOUBLE (approximate).CHAR(n) (fixed length), VARCHAR(n) (variable length), TEXT/LONGTEXT, ENUM, BLOB (binary).DATE, TIME, DATETIME, TIMESTAMP (stored in UTC, converted to the session time zone), YEAR.JSON, BOOLEAN (an alias for TINYINT(1)) and spatial types.Common trap: storing money in FLOAT or DOUBLE. Rounding errors creep in, so use DECIMAL(19,4) (and BigDecimal in Java).
Short answer: Both enforce uniqueness. A primary key identifies each row. There's only one per table, it can't be NULL, and in InnoDB it's the clustered index that determines the physical row order. A unique key prevents duplicates in other columns. A table can have many unique keys, and they allow NULLs. In MySQL and PostgreSQL, multiple NULLs are allowed, because NULL isn't equal to NULL.
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(255) NOT NULL UNIQUE, -- a business rule: one account per email
phone VARCHAR(20) UNIQUE -- optional, but unique when present
);
Short answer: A foreign key makes a column's values reference the primary (or unique) key of another table. The database then enforces referential integrity: you can't insert a row pointing to a non-existent parent, or delete a parent that's still referenced, unless you define an ON DELETE action.
CREATE TABLE enrollments (
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) -- default: RESTRICT
);
Key points to cover:
ON DELETE actions: RESTRICT/NO ACTION (block the delete), CASCADE (delete the children too), SET NULL.Short answer: Zero is a known numeric value. NULL means "unknown or missing". It isn't a value at all, and it behaves differently everywhere:
NULL + 1 → NULL).WHERE amount = NULL matches nothing. Use IS NULL / IS NOT NULL.COUNT(amount) counts only non-NULL values, while COUNT(*) counts every row. AVG ignores NULLs.-- payments: amount NULL = not yet charged, amount 0 = attempted but nothing charged
SELECT COUNT(*) AS all_rows, COUNT(amount) AS charged_or_zero, SUM(COALESCE(amount, 0)) AS total
FROM payments;
Key points to cover:
COALESCE(col, default) (or MySQL's IFNULL) substitutes a value for NULL.Integer, BigDecimal), not primitives.Short answer: A transaction is a group of operations executed as one logical unit: either all of them take effect (commit) or none do (rollback). Transactions provide the ACID guarantees:
START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1 AND balance >= 500;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT; -- or ROLLBACK if any step fails, so the money is never lost halfway
Key points to cover:
@Transactional wraps a service method in exactly this, rolling back on runtime exceptions.READ COMMITTED, REPEATABLE READ, the MySQL default, and SERIALIZABLE) trade consistency against concurrency.Learn it in depth → ACID Properties
Q: What is a candidate key? A super key? An alternate key?
A: A super key is any set of columns that uniquely identifies a row. A candidate key is a minimal super key. The primary key is the candidate key you choose. The other candidate keys are alternate keys, usually enforced with UNIQUE.
Q: What is a surrogate key vs a natural key?
A: A natural key comes from the business domain (email, PAN or ISBN). A surrogate key is system-generated (auto-increment or UUID), with no business meaning. Surrogate keys are stable, even when business data changes, so they're the usual choice for primary keys. Keep natural keys UNIQUE.
Q: Can a foreign key reference a column that isn't a primary key?
A: Yes, as long as that column is UNIQUE, or a primary key.
Q: Is COUNT(1) faster than COUNT(*)?
A: No. Modern databases treat them identically. COUNT(column) is the one that differs, because it skips NULLs.