Skip to content
Tutorials Center
My learning

Topics

Lesson 123 of 125 ยท Practice and Review

SQL Interview Questions

50 common SQL interview questions with clear answers, from keys, joins and GROUP BY to window functions, indexes, transactions and isolation levels.

SQL interviews test two things at once: whether you know how queries actually behave, and whether you can explain that knowledge clearly and briefly. This page collects 50 questions that come up again and again for developers, analysts and data engineers, grouped by level (Junior, Mid, Senior) and by topic, each with a concise, accurate answer you can put into your own words.

Try to answer each question out loud before you open it. If you can't explain an idea in a few sentences, follow the link to its lesson and review it. Several questions ask you to read a query and predict its result; make your guess first, then press Run. The levels are a rough guide: senior interviews still start with junior questions, they just expect deeper follow-ups such as "how would that behave in PostgreSQL?" or "what happens with a million rows?"

Tip: many interviews include a live query exercise. Practice saying what you're doing as you write it: "I'll start from orders, join the customers, filter cancelled orders in WHERE, then group by customer." The exercises and the 50 classic problems are the best preparation for that part.

Junior level

Foundations

What is SQL, and what is a relational database?

SQL (Structured Query Language) is the standard language for defining, querying and changing data in relational databases. It is declarative: you describe the result you want, and the database's optimizer decides how to get it. A relational database stores data in tables of rows and columns, where each table describes one kind of thing and tables are connected through keys. PostgreSQL, MySQL, SQL Server, Oracle and SQLite are all relational database management systems (RDBMS). See Your First SQL Queries and Databases, Tables and Keys.

What are DDL, DML, DQL, DCL and TCL?

They are the families of SQL statements. DDL (Data Definition Language) defines structure: CREATE, ALTER, DROP. DML (Data Manipulation Language) changes rows: INSERT, UPDATE, DELETE. DQL (Data Query Language) reads them: SELECT (often counted as part of DML). DCL (Data Control Language) manages permissions: GRANT, REVOKE. TCL (Transaction Control Language) groups changes: BEGIN, COMMIT, ROLLBACK, SAVEPOINT. See SQL Command Types.

What is a primary key, and how is it different from a unique constraint?

A primary key identifies each row: its values must be unique and not NULL, and a table has at most one. A unique constraint also forbids duplicates, but a table can have many of them, and in most databases the column may hold NULL (often several NULLs, since NULL is not equal to NULL; SQL Server allows only one). Use the primary key as the row's identity and unique constraints for other "no duplicates" rules, such as an email address. See Keys and Constraints.

What is a foreign key?

A foreign key is a column (or group of columns) whose values must match a key in another table, such as orders.customer_id referencing customers.id. It enforces referential integrity: you can't insert an order for a customer that doesn't exist, and deleting a referenced customer is rejected, cascaded or set to NULL depending on the ON DELETE action. SQLite enforces foreign keys only after PRAGMA foreign_keys = ON. See Foreign Keys.

What does NULL mean, and why can't you test it with = NULL?

NULL means "unknown or missing", not zero and not an empty string. Any comparison with NULL, including NULL = NULL, is unknown, and WHERE keeps only rows whose condition is true, so WHERE email = NULL never returns anything. Use IS NULL and IS NOT NULL, COALESCE to substitute a value, and IS [NOT] DISTINCT FROM for null-safe comparisons. See Working with NULL.

What is the difference between DELETE, TRUNCATE and DROP?

DELETE FROM t WHERE ... removes chosen rows one by one, fires delete triggers and can be rolled back. TRUNCATE TABLE t removes all rows at once by deallocating storage: much faster, usually resets identity counters and doesn't fire row triggers; it can be rolled back in PostgreSQL and SQL Server but commits implicitly in MySQL and Oracle. DROP TABLE t removes the table itself, structure and all. SQLite has no TRUNCATE: DELETE FROM t without a WHERE is optimized to do the same job. See DELETE and ALTER TABLE and DROP TABLE.

Querying

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping, so it can't use aggregates. HAVING filters groups after GROUP BY and aggregation, so it can: HAVING COUNT(*) > 5. Conditions on plain columns belong in WHERE, which lets the database discard rows early. See HAVING.

What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

COUNT(*) counts rows. COUNT(column) counts non-NULL values in that column. COUNT(DISTINCT column) counts different non-NULL values. Predict the five numbers before you run this:

SQL Code reading: three kinds of COUNT ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

There are 36 employees, 3 without an email and 2 without a department, and 36 people share only 25 different first names. See Aggregate Functions.

What is the difference between UNION and UNION ALL?

Both stack the results of two queries with the same number of compatible columns. UNION removes duplicate rows, which costs a sort or hash step; UNION ALL keeps every row and is faster. Use UNION ALL unless you actually need de-duplication, or when you know the parts can't overlap. See UNION, INTERSECT and EXCEPT.

Is BETWEEN inclusive? And how do LIKE wildcards work?

BETWEEN a AND b is inclusive on both ends: it means >= a AND <= b. For timestamps prefer a half-open range (>= '2025-03-01' AND < '2025-04-01'), because BETWEEN '2025-03-01' AND '2025-03-31' misses everything after midnight on the 31st. In LIKE, % matches any run of characters (including none) and _ matches exactly one; ESCAPE lets you search for a literal %. See IN and BETWEEN and LIKE.

Where can you use a column alias?

An alias defined in SELECT can be used in ORDER BY, because sorting happens after the SELECT list is computed. Standard SQL does not allow it in WHERE, GROUP BY or HAVING, which are evaluated earlier; PostgreSQL and SQL Server reject it there. SQLite and MySQL are more permissive (SQLite even accepts it in WHERE), but portable code repeats the expression or wraps the query in a subquery or CTE. See Column and Table Aliases.

Joins

What kinds of joins are there?

INNER JOIN returns only rows that match on both sides. LEFT (OUTER) JOIN returns all rows from the left table, with NULLs where the right side has no match; RIGHT JOIN is the mirror image. FULL OUTER JOIN returns all rows from both sides. CROSS JOIN returns every combination. A self join joins a table to itself under two aliases. Semi-joins (EXISTS, IN) and anti-joins (NOT EXISTS, LEFT JOIN ... IS NULL) are patterns built from these. See Why Joins?.

How do you find rows in one table that have no match in another?

Two standard answers. The anti-join with LEFT JOIN: SELECT c.* FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL. Or NOT EXISTS: WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id). Both are safe with NULLs; NOT IN (subquery) is not (see the Mid-level question on it). See LEFT JOIN and EXISTS.

What is a self join? Give an example.

A self join joins a table to itself, using two aliases so each copy plays a role. The classic example is an employees table where manager_id points to another employee: FROM employees e JOIN employees m ON m.id = e.manager_id puts every employee next to their manager, and WHERE e.salary > m.salary finds people who earn more than their boss. Use LEFT JOIN to keep the top manager, whose manager_id is NULL. See Self Joins.

Mid level

How queries run

In what order are the clauses of a SELECT evaluated?

Logically: FROM (and joins), WHERE, GROUP BY, HAVING, SELECT (including window functions), DISTINCT, ORDER BY, then LIMIT/OFFSET. This explains several rules: WHERE can't use aggregates or window functions, aliases work in ORDER BY but not in WHERE, and filtering on a window function needs an outer query. The optimizer may physically execute things differently, but the result must be as if this order were followed. See HAVING.

What happens with NOT IN when the subquery returns a NULL?

x NOT IN (a, b, NULL) means x <> a AND x <> b AND x <> NULL. The last comparison is unknown, so the whole condition is never true, and the query returns no rows. Predict both counts:

SQL Code reading: NOT IN vs NOT EXISTS ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The CEO's manager_id is NULL, so the first query returns 0, while NOT EXISTS correctly finds 29 people who manage nobody. Prefer NOT EXISTS, or add WHERE manager_id IS NOT NULL to the subquery. See IN and BETWEEN and EXISTS.

What is the difference between a condition in ON and in WHERE for a LEFT JOIN?

For an inner join it makes no difference. For a LEFT JOIN, a condition in ON decides which right-side rows match; unmatched left rows still appear with NULLs. The same condition in WHERE runs after the join and removes rows where the right side is NULL, turning the outer join into an inner join. Predict both counts:

SQL Code reading: ON vs WHERE ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The first keeps all six departments (Engineering matches two big projects, so it appears twice: 7 rows); the second keeps only the 3 big projects. See LEFT JOIN.

What is a correlated subquery, and when is it a problem?

A correlated subquery refers to a column of the outer query, so logically it runs once per outer row: WHERE salary > (SELECT AVG(salary) FROM employees x WHERE x.department_id = e.department_id). It's clear and often fine, because optimizers can turn many of them into joins, but on large tables a per-row subquery without a supporting index can be slow. Alternatives are a join to a grouped derived table or a window function such as AVG(salary) OVER (PARTITION BY department_id). See Correlated Subqueries.

EXISTS vs IN vs JOIN: which should you use?

They answer different questions. A JOIN returns columns from both sides and repeats rows when there are several matches. IN and EXISTS are semi-joins: they return each outer row at most once, which is what "customers who have at least one order" means. Modern optimizers often produce the same plan for IN (subquery) and EXISTS. The practical differences are NULL handling (prefer NOT EXISTS over NOT IN) and readability: EXISTS suits correlated conditions, IN suits short lists. See EXISTS and Writing Fast Queries.

What is a CTE, and how does it differ from a subquery, a view and a temporary table?

A common table expression (WITH name AS (...)) names a subquery for the duration of one statement. It makes long queries readable, can be referenced several times, and can be recursive. A subquery does the same job inline, without a name. A view is a named query stored in the database and reusable by every statement. A temporary table physically stores rows for the session, so it can be indexed and reused across statements. Depending on the database, a CTE may be inlined or materialized; PostgreSQL 12+ lets you choose with MATERIALIZED / NOT MATERIALIZED. See Common Table Expressions and Views.

What is a recursive CTE, and what is it used for?

A recursive CTE has an anchor query, UNION ALL, and a recursive query that references the CTE itself. The database runs the anchor, then repeatedly runs the recursive part on the rows produced by the previous step until no new rows appear. It's used to walk hierarchies (org charts, category trees, bills of materials), traverse graphs and generate series of numbers or dates. Always make sure the recursion ends, for example with a depth limit. See Recursive CTEs.

Aggregation and windows

How are window functions different from GROUP BY?

GROUP BY collapses each group into one output row. A window function computes a value over a set of related rows (the window, defined with OVER (PARTITION BY ... ORDER BY ...)) and returns it on every row, keeping the details. That makes rankings, running totals, shares of a total and comparisons with the previous row easy. Window functions are evaluated after WHERE, GROUP BY and HAVING, so to filter on them you need an outer query. See Window Functions.

What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

All three number rows in window order. ROW_NUMBER gives unique numbers and breaks ties arbitrarily (1, 2, 3, 4). RANK gives ties the same number and then skips (1, 2, 2, 4). DENSE_RANK gives ties the same number without gaps (1, 2, 2, 3). Predict the three columns:

SQL Code reading: ranking with ties

Press Run (or Ctrl + Enter) to see the result here.

Dee is 4th by ROW_NUMBER and RANK but 3rd by DENSE_RANK. Use DENSE_RANK for "nth highest distinct value", ROW_NUMBER for exactly one row per group. See Ranking.

How do you find the nth highest salary?

Three common answers. With LIMIT: SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET n-1. With a window function: rank with DENSE_RANK() OVER (ORDER BY salary DESC) in a CTE and keep rank n, which also works per department with PARTITION BY. Without either: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees) for the second highest. Mention ties: DISTINCT or DENSE_RANK make "nth" mean the nth distinct value. See SQL Queries: 50 Classic Problems.

How do you find and remove duplicate rows?

To find them, group by the columns that should be unique and keep groups with more than one row: GROUP BY email HAVING COUNT(*) > 1. To remove them while keeping one, delete everything except a chosen survivor: DELETE FROM t WHERE id NOT IN (SELECT MIN(id) FROM t GROUP BY email), or number the rows with ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) and delete those with a number above 1. Afterwards add a UNIQUE constraint so the duplicates can't come back. See GROUP BY and DELETE.

Design and objects

What is normalization? Explain 1NF, 2NF and 3NF.

Normalization organizes tables so each fact is stored once, which prevents update, insert and delete anomalies. 1NF: each cell holds one value and there are no repeating groups (no phone1, phone2, phone3). 2NF: 1NF, and no non-key column depends on only part of a composite key. 3NF: 2NF, and no non-key column depends on another non-key column (a customer's city doesn't belong in the orders table). BCNF tightens 3NF so that every determinant is a candidate key. See Normalization.

When would you denormalize?

When measured read performance or simplicity matters more than the cost of keeping duplicated data in sync: reporting tables, data warehouses (star schemas), cached counters such as comment_count, or JSON snapshots of an order at purchase time. Denormalize deliberately, keep the normalized source of truth, and maintain the copies with triggers, scheduled jobs or application code. See Normalization and Triggers.

How do you model a many-to-many relationship?

With a junction table that holds a foreign key to each side, usually with a composite primary key on the pair: enrollments (student_id, course_id) links students and courses, order_items (order_id, product_id) links orders and products. The junction table is also the natural home for facts about the relationship itself, such as a grade or a quantity. See One-to-Many and Many-to-Many.

What is a view? Can you update through one?

A view is a stored, named SELECT that you query like a table. Views simplify complex joins, give stable interfaces to applications and can hide columns for security. Simple views over one table are often updatable in PostgreSQL, MySQL and SQL Server; complex ones (joins, aggregates, DISTINCT) need INSTEAD OF triggers. SQLite views are read-only unless you add INSTEAD OF triggers. A materialized view stores the result and must be refreshed. See Views.

What is the difference between a stored procedure and a function?

A function returns a value (or a table) and can be used inside queries, like SELECT tax(price). A stored procedure is called on its own (CALL in PostgreSQL and MySQL, EXEC in SQL Server), can return several result sets or output parameters, and can usually control transactions. Both run inside the database server, next to the data. SQLite has neither: applications put that logic in their own code, in triggers or in CTEs. See Stored Procedures and Functions.

What are triggers, and what are their drawbacks?

A trigger is code that the database runs automatically BEFORE, AFTER or INSTEAD OF an INSERT, UPDATE or DELETE. Typical uses are audit logs, keeping derived values in sync and enforcing rules that constraints can't express. Drawbacks: the logic is invisible to someone reading the application code, triggers slow down every write they're attached to, and chains of triggers are hard to debug. Prefer constraints when they can do the job. See Triggers.

What is an upsert, and how is it written?

An upsert inserts a row, or updates it if a row with the same key already exists, in one atomic statement. SQLite and PostgreSQL use INSERT ... ON CONFLICT (key) DO UPDATE SET col = excluded.col (or DO NOTHING). MySQL uses INSERT ... ON DUPLICATE KEY UPDATE. SQL Server, Oracle and PostgreSQL 15+ have MERGE. Doing "SELECT, then INSERT or UPDATE" in application code instead creates a race between concurrent requests. See Upserts with ON CONFLICT.

Senior level

Transactions and concurrency

What does ACID stand for?

Atomicity: a transaction's changes all happen or none do. Consistency: a transaction moves the database from one valid state to another, respecting constraints. Isolation: concurrent transactions don't see each other's intermediate states (to the degree the isolation level promises). Durability: once committed, changes survive crashes, typically thanks to a write-ahead log. See Transactions.

What are the isolation levels, and which anomalies does each prevent?

The standard defines four levels by the anomalies they forbid. Read Uncommitted allows dirty reads (seeing uncommitted changes). Read Committed prevents dirty reads but allows non-repeatable reads (a row changes between two reads). Repeatable Read also prevents those but may allow phantoms (new rows appearing in a repeated range query). Serializable makes the result equivalent to running transactions one at a time. Defaults differ: Read Committed in PostgreSQL, SQL Server and Oracle, Repeatable Read in MySQL InnoDB, and SQLite is effectively serializable. Real implementations differ too: PostgreSQL's Repeatable Read is snapshot isolation, which prevents phantoms but allows write skew. See Isolation Levels and Locking.

What is MVCC, and how does it differ from locking?

With pure locking, readers take shared locks and writers take exclusive locks, so readers and writers block each other. With multi-version concurrency control (PostgreSQL, Oracle, MySQL InnoDB, SQL Server with snapshot options), a write creates a new version of the row, and each transaction reads the versions that were committed as of its snapshot, so readers don't block writers and writers don't block readers. Writers still lock rows against other writers. The cost is old versions that must be cleaned up later (VACUUM in PostgreSQL, undo logs in InnoDB). See Isolation Levels and Locking.

What is a deadlock, and how do you prevent one?

A deadlock happens when two transactions each hold a lock the other needs: A locks row 1 and wants row 2, while B locks row 2 and wants row 1. The database detects the cycle and aborts one of them with an error. Prevent deadlocks by touching rows and tables in a consistent order, keeping transactions short, indexing the columns used to find rows (so fewer rows get locked) and, where appropriate, locking up front with SELECT ... FOR UPDATE. Applications should retry transactions that fail with a deadlock or serialization error. See Isolation Levels and Locking.

Optimistic vs pessimistic locking: what's the difference?

Pessimistic locking takes locks before changing data (SELECT ... FOR UPDATE), so others wait; it suits high-contention updates such as seat booking. Optimistic locking takes no lock while the user works; instead each row carries a version number, and the update checks it: UPDATE ... SET qty = 4, version = version + 1 WHERE id = 7 AND version = 3. If zero rows were affected, someone else changed the row first and the application reloads or retries. It suits web apps where conflicts are rare. See Transactions.

Performance

What is an index, and what does it cost?

An index is a separate, sorted data structure (usually a B-tree) that maps column values to rows, so the database can find matching rows without scanning the whole table, and can often return them already sorted. The costs: extra storage, and slower INSERT, UPDATE and DELETE, because every index on the table must be maintained. Index the columns used in selective filters, joins and sorts; don't index everything. See Indexes.

What is the difference between a clustered and a non-clustered index?

A clustered index determines the physical order of the table's rows: the table is the index, so there can be only one. SQL Server tables are clustered on their clustered index (usually the primary key), MySQL InnoDB always clusters on the primary key, and SQLite rowid tables are B-trees keyed by the rowid. A non-clustered (secondary) index is a separate structure that points back to the rows, by row id or primary key. PostgreSQL tables are unordered heaps; all its indexes are secondary. See Indexes and Advanced Indexing.

Does the column order in a composite index matter?

Yes. An index on (customer_id, ordered_at) is sorted by customer_id first and then by ordered_at within each customer, like a phone book sorted by last name, then first name. It serves WHERE customer_id = ?, WHERE customer_id = ? AND ordered_at > ? and WHERE customer_id = ? ORDER BY ordered_at, but not WHERE ordered_at > ? alone (the leftmost-prefix rule). Put equality columns first and range or sort columns after them. See Advanced Indexing.

What is a sargable condition, and why might an index not be used?

A sargable (Search ARGument-able) condition compares a bare indexed column with a value, so the database can seek into the index: ordered_at >= '2025-01-01'. Wrapping the column in a function or calculation hides it from the index: strftime('%Y', ordered_at) = '2025', lower(email) = ?, price * 1.2 > 100. Other reasons an index is skipped: a leading wildcard in LIKE '%x', type mismatches that force conversions, OR across different columns, low selectivity (the planner decides a scan is cheaper) and stale statistics. Fix them by rewriting the condition, by adding an expression index, or by running ANALYZE. See Writing Fast Queries.

How do you investigate a slow query?

Measure first, then read the plan. Use EXPLAIN (SQLite: EXPLAIN QUERY PLAN; PostgreSQL: EXPLAIN ANALYZE with real timings) and look for full scans of big tables, missing index use, large sorts or temporary structures, and bad row estimates. Then check sargability, add or adjust an index (perhaps a covering one), select only the needed columns, and look at the access pattern from the application: N+1 query loops and huge OFFSETs are frequent culprits. Re-measure after each change. See Reading Query Plans.

What is a covering index?

An index that contains every column a query needs, so the database can answer from the index alone without visiting the table rows. For SELECT ordered_at, status FROM orders WHERE customer_id = ?, an index on (customer_id, ordered_at, status) covers the query; SQLite's plan then says USING COVERING INDEX. PostgreSQL and SQL Server can add non-key columns with INCLUDE (...). Covering indexes are powerful for hot queries but make the index bigger and writes slower. See Advanced Indexing.

What is the N+1 query problem?

It's when application code runs one query to fetch a list and then one more query per item: 1 query for 100 orders, plus 100 queries for their customers. Each query is fast, but the round trips add up. Fix it by fetching related data in one go, with a join or a single WHERE id IN (...) query; ORMs call this eager loading. See Writing Fast Queries.

Why is OFFSET pagination slow on deep pages, and what is the alternative?

LIMIT 20 OFFSET 100000 makes the database produce and throw away 100,000 rows before returning 20, so every deeper page gets slower, and rows inserted between requests shift the pages. Keyset (seek) pagination remembers the last row seen and asks for the next ones: WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20. With a matching index, every page costs the same. The trade-off is that you can't jump straight to page 500. See LIMIT, OFFSET and Pagination.

Architecture and security

What is SQL injection, and how do you prevent it?

SQL injection happens when user input is pasted into the text of a query, so crafted input changes the query's meaning: a name like ' OR '1'='1 can turn a lookup into "return every row". The fix is parameterized queries (prepared statements), where the SQL text and the values travel separately and values are never parsed as SQL. Add least-privilege database accounts, allow-lists for identifiers that can't be parameters (such as sort columns), and never rely on hand-written escaping. See SQL Injection and Secure Queries and Users and Permissions.

Natural or surrogate keys? And what about UUIDs?

A natural key comes from the data (an ISBN, a country code); a surrogate key is generated and meaningless (an auto-increment id). Surrogates are stable when business data changes and keep foreign keys small, so most tables use them, with a UNIQUE constraint on the natural key. UUIDs can be generated anywhere without coordination and don't reveal row counts, but they are larger, and random UUIDs (version 4) scatter inserts across a B-tree index; time-ordered UUIDs (version 7) avoid most of that cost. See Keys and Auto-Increment and Identity Columns.

What is the difference between OLTP and OLAP?

OLTP (online transaction processing) systems serve applications: many small, concurrent reads and writes of individual rows, normalized schemas, strict integrity. OLAP (online analytical processing) systems serve analysis: fewer, heavier queries that scan and aggregate millions of rows, often on denormalized star schemas (fact tables plus dimension tables) in column-oriented warehouses. Many companies copy data from OLTP databases into an OLAP warehouse so reporting doesn't slow down the application. See SQL vs NoSQL.

How do replication, partitioning and sharding differ?

Replication keeps copies of the same data on several servers, for read scaling and failover; replicas may lag behind the primary. Partitioning splits one large table into pieces inside one database, for example by month, so queries and maintenance touch fewer rows and old partitions can be dropped instantly. Sharding splits data across separate database servers by a key such as customer id; it scales writes but makes cross-shard joins and transactions hard. Reach for them in that order, and only when indexes and query fixes are no longer enough. See Backups, Migrations and Maintenance.

How do you change a schema on a live production database safely?

Use versioned migrations checked into source control, and make each step backward compatible with the code that is running. The expand and contract pattern: add the new column or table (nullable or with a default), deploy code that writes both old and new, backfill in small batches, switch reads to the new structure, and only then drop the old one. Watch for operations that lock or rewrite big tables, build indexes concurrently where the database supports it (CREATE INDEX CONCURRENTLY in PostgreSQL), and have a tested backup and rollback plan. See Backups, Migrations and Maintenance.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does SELECT COUNT(*) FROM employees WHERE id NOT IN (SELECT manager_id FROM employees); return when one manager_id is NULL?

    The NULL in the list makes every NOT IN test unknown, so no row passes. NOT EXISTS gives the intended answer.
  2. 2. Which isolation level is the default in PostgreSQL?

    PostgreSQL, SQL Server and Oracle default to Read Committed. MySQL's InnoDB defaults to Repeatable Read.
  3. 3. An index exists on (customer_id, ordered_at). Which condition can NOT use it efficiently?

    The index is sorted by customer_id first, so a condition on ordered_at alone can't seek into it (the leftmost-prefix rule).
  4. 4. For the points 90, 85, 85, 80 in descending order, what does RANK() give the row with 80?

    RANK gives both 85s rank 2 and then skips 3, so 80 is ranked 4. DENSE_RANK would give it 3.
  5. 5. Which statement about TRUNCATE is true?

    TRUNCATE empties the whole table by deallocating its storage. It has no WHERE, doesn't fire row-level delete triggers and keeps the table's structure.

Summary

  • Junior questions check the foundations: what SQL and relational databases are, the statement families, primary, unique and foreign keys, NULL logic, WHERE vs HAVING, the COUNT variants, UNION vs UNION ALL, DELETE vs TRUNCATE vs DROP and the join types.
  • Mid-level questions focus on how queries run: logical clause order, the NOT IN trap, ON vs WHERE, correlated subqueries, EXISTS, CTEs, window functions and rankings, plus the classic tasks (nth highest, duplicates) and design topics such as normalization, junction tables, views, procedures, triggers and upserts.
  • Senior questions go under the hood: ACID, isolation levels and anomalies, MVCC, deadlocks and locking strategies, indexes (clustered, composite, covering), sargability, query plans, N+1 queries and keyset pagination, and architecture: injection, key choice, OLTP vs OLAP, scaling and safe migrations.
  • A good answer is short, correct, backed by a tiny example and honest about how databases differ.

You've reached the end of the SQL course. Keep your skills sharp with the practice exercises and the 50 classic problems, keep the cheat sheet close, and put everything together in a real project such as Sales Analysis. Happy querying!