Autocoder

SQL Interview Questions and Answers

Last updated 2026-11-19

SQL is tested in almost every backend, data and analyst interview, and in many fresher technical rounds. This page covers the core ground in one place: the language categories, constraints, NULL behaviour, set operators, views, transactions and the logical order in which a query runs. Where an answer involves a query, the result shown was produced by running it on the sample tables.

Use it as the overview and then go deeper on the topics that matter for your role: SQL joins, window functions, GROUP BY and HAVING, subqueries and indexes. For the round-level plan see how to crack a SQL interview.

DialectsMySQL and PostgreSQL differences noted
Sample dataemployees, departments
LevelEasy to hard

Sample data

Used by the queries below. Gita has no department, and the Legal department has no employees.

sql
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
CREATE TABLE employees (emp_id INT PRIMARY KEY, name VARCHAR(20), dept_id INT, salary INT, manager_id INT);
INSERT INTO departments VALUES (10,'Sales'),(20,'Engineering'),(30,'HR'),(40,'Legal');
INSERT INTO employees VALUES
(1,'Asha',20,90000,NULL),(2,'Bilal',20,75000,1),(3,'Chitra',10,60000,1),
(4,'Dev',10,60000,3),(5,'Esha',30,50000,1),(6,'Farid',20,75000,1),(7,'Gita',NULL,40000,5);

Logical order of a SELECT

SQL Interview Questions and Answers: questions with worked answers

  1. 1

    What are DDL, DML, DCL and TCL?

    easy

    DDL (data definition) changes structure: CREATE, ALTER, DROP, TRUNCATE. DML (data manipulation) changes data: INSERT, UPDATE, DELETE (and SELECT is often counted as DQL, data query). DCL (data control) manages permissions: GRANT and REVOKE. TCL (transaction control) manages transactions: COMMIT, ROLLBACK, SAVEPOINT. In MySQL and Oracle, DDL statements commit implicitly, while PostgreSQL supports transactional DDL, so a failed migration can be rolled back.

  2. 2

    What is the difference between DELETE, TRUNCATE and DROP?

    easy

    DELETE removes rows one by one, supports a WHERE clause, fires row triggers and can be rolled back, and logs each row. TRUNCATE removes all rows quickly by deallocating pages, has no WHERE, resets auto-increment counters in MySQL, does not fire row-level delete triggers and is rolled back only in databases with transactional DDL (PostgreSQL and SQL Server, not MySQL or Oracle). DROP removes the table itself, its data, indexes and privileges.

  3. 3

    What are the types of constraints in SQL?

    easy

    PRIMARY KEY (unique and not null, one per table), FOREIGN KEY (references a key in another table), UNIQUE, NOT NULL, CHECK (a condition on the values) and DEFAULT. MySQL parses CHECK constraints but only enforces them from 8.0.16. A foreign key can specify ON DELETE CASCADE, SET NULL or RESTRICT to decide what happens to child rows.

    sql
    CREATE TABLE employees (
      emp_id INT PRIMARY KEY,
      email  VARCHAR(50) UNIQUE NOT NULL,
      salary INT CHECK (salary > 0),
      dept_id INT,
      FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE SET NULL
    );
  4. 4

    How does SQL treat NULL? Show the three-valued logic traps.

    medium

    NULL means unknown, so any comparison with it (= NULL, <> NULL) is unknown, not true or false, and WHERE keeps only rows where the condition is true. Test with IS NULL and IS NOT NULL. NULL + 1 is NULL. NOT IN with a NULL in the list returns nothing. COUNT(column) skips NULLs. Use COALESCE(x, default) to replace them. Below, "dept_id = NULL" matches nothing while IS NULL finds Gita.

    sql
    SELECT name FROM employees WHERE dept_id = NULL;
    -- (0 rows)
    sql
    SELECT name FROM employees WHERE dept_id IS NULL;
    -- name
    -- ----
    -- Gita
  5. 5

    What is the difference between UNION and UNION ALL?

    easy

    UNION combines two result sets and removes duplicate rows, which needs a sort or hash step. UNION ALL keeps every row and is faster. Both need the same number of columns with compatible types, and column names come from the first query. INTERSECT and EXCEPT (MINUS in Oracle) are the related set operators; MySQL added them in 8.0.31.

    sql
    SELECT dept_id FROM employees WHERE salary >= 75000 UNION SELECT dept_id FROM employees WHERE salary >= 60000 ORDER BY dept_id;
    -- dept_id
    -- -------
    -- 10
    -- 20
  6. 6

    What is the difference between WHERE and HAVING?

    easy

    WHERE filters rows before grouping and cannot use aggregates. HAVING filters groups after aggregation. Details and results in GROUP BY and HAVING questions.

    sql
    SELECT dept_id, COUNT(*) AS n FROM employees WHERE salary >= 50000 GROUP BY dept_id HAVING COUNT(*) >= 2 ORDER BY dept_id;
    -- dept_id | n
    -- --------+--
    -- 10      | 2
    -- 20      | 3
  7. 7

    What is a view? What are its advantages and limits?

    medium

    A view is a stored SELECT that behaves like a virtual table. It simplifies complex queries, restricts column and row access, and gives a stable interface when the underlying tables change. A normal view stores no data, so it is as fresh and as slow as its query. A materialized view stores the result and must be refreshed (PostgreSQL REFRESH MATERIALIZED VIEW; MySQL has no native materialized views). Simple views are updatable, but views with joins, aggregates or DISTINCT generally are not.

    sql
    CREATE VIEW high_earners AS
    SELECT emp_id, name, salary FROM employees WHERE salary >= 75000;
    SELECT * FROM high_earners;
  8. 8

    Explain ACID with an example.

    medium

    Atomicity: all statements of a transaction happen or none do, so a transfer cannot debit one account and fail to credit the other. Consistency: a transaction moves the database from one valid state to another, respecting constraints. Isolation: concurrent transactions do not see each other's partial work, to a degree set by the isolation level. Durability: once committed, the change survives a crash, via the write-ahead log. More in DBMS transaction questions.

    sql
    START TRANSACTION;                       -- BEGIN in PostgreSQL
    UPDATE accounts SET balance = balance - 500 WHERE id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE id = 2;
    COMMIT;                                   -- or ROLLBACK on error
  9. 9

    What are the isolation levels and the anomalies each prevents?

    hard

    READ UNCOMMITTED allows dirty reads (seeing uncommitted data). READ COMMITTED prevents dirty reads but allows non-repeatable reads (the same row read twice differs) and phantoms. REPEATABLE READ prevents non-repeatable reads; the standard still allows phantoms (new rows matching a range), though InnoDB prevents most through gap locks. SERIALIZABLE prevents all of them. The defaults differ: PostgreSQL and Oracle use READ COMMITTED, MySQL InnoDB uses REPEATABLE READ. PostgreSQL treats READ UNCOMMITTED as READ COMMITTED.

  10. 10

    What is normalisation, and when would you denormalise?

    medium

    Normalisation organises tables to remove redundancy and update anomalies: 1NF has atomic values, 2NF removes partial dependence on part of a composite key, 3NF removes dependence between non-key columns, and BCNF tightens it. Denormalisation deliberately duplicates data, for example storing a customer name on each order or a precomputed total, to avoid expensive joins in read-heavy reporting at the cost of harder updates. See DBMS normalisation questions.

  11. 11

    What is the difference between CHAR and VARCHAR, and between INT types?

    medium

    CHAR(n) is fixed length and pads with spaces (MySQL strips trailing spaces on read), so it suits fixed codes such as country codes. VARCHAR(n) stores only the actual length plus a small header. TEXT is for large strings. Integer types differ in range: in MySQL TINYINT is 1 byte, INT 4 bytes (up to about 2.1 billion signed) and BIGINT 8 bytes, so use BIGINT for ids that may pass 2 billion. For money use DECIMAL, not FLOAT, because binary floating point cannot represent 0.1 exactly.

  12. 12

    Write a query to find the department with the most employees, handling ties.

    medium

    Aggregate counts, then compare to the maximum count rather than using LIMIT 1, which drops tied departments. Engineering has three employees, the most.

    sql
    SELECT dept_id, COUNT(*) AS n FROM employees WHERE dept_id IS NOT NULL GROUP BY dept_id HAVING COUNT(*) = (SELECT MAX(c) FROM (SELECT COUNT(*) AS c FROM employees WHERE dept_id IS NOT NULL GROUP BY dept_id) t);
    -- dept_id | n
    -- --------+--
    -- 20      | 3
  13. 13

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

    medium

    A function returns a value (or table), can be called inside a SELECT, and is generally meant to have no side effects. A stored procedure is invoked with CALL (or EXEC), may return several result sets or output parameters, can change data and manage transactions. Details vary: PostgreSQL had only functions until version 11 added procedures with transaction control; Oracle PL/SQL has both as first-class objects (PL/SQL questions).

  14. 14

    How do you find and remove duplicate rows?

    medium

    To find them, group by the columns that should be unique and keep HAVING COUNT(*) > 1. To remove them, keep one row per group, typically the minimum id or the latest date, using ROW_NUMBER() in a CTE or a self join delete. Always run the SELECT version first and wrap the DELETE in a transaction. After cleaning, add a UNIQUE constraint so the duplicates cannot return.

    sql
    SELECT salary, COUNT(*) AS n FROM employees GROUP BY salary HAVING COUNT(*) > 1 ORDER BY salary;
    -- salary | n
    -- -------+--
    -- 60000  | 2
    -- 75000  | 2
  15. 15

    What is the difference between primary key and unique key, and between candidate and super key?

    medium

    A primary key is the chosen unique identifier: not NULL, one per table. A unique key enforces uniqueness on other columns and may allow NULL. A candidate key is any minimal set of columns that uniquely identifies a row. A super key is any set of columns that uniquely identifies rows, including non-minimal ones (emp_id plus name). The primary key is the candidate key you pick, and the rest become alternate keys.

Frequently asked questions

What SQL topics are asked most in interviews?

Joins, GROUP BY and HAVING, subqueries, window functions, NULL handling and indexes. Nth highest salary and duplicate-row queries are the classic practical questions.

Which database should I practise on?

MySQL or PostgreSQL, both free. Read the job description, since Oracle and SQL Server roles use T-SQL or PL/SQL features.

How long does it take to prepare SQL for an interview?

It depends on your starting point. Most people need to write queries by hand daily for a few weeks to be fluent with joins and windows.

Is SQL enough for a data analyst role?

It is the core skill, alongside a spreadsheet or BI tool and some statistics. See how to crack a data analyst interview.

Related guides

More in Programming Language Interview Questions by Language

See all in this section