SQL is asked in interviews for developers, data analysts, data scientists, testers and MIS roles. These SQL interview questions cover SELECT, joins and aggregation for freshers, then subqueries, window functions, indexes, normalisation, query optimisation and classic SQL query problems for experienced candidates.
30 questions · Updated
Fundamentals, definitions and simple scenarios. Good for freshers and warm-ups.
DDL (Data Definition Language) defines structure: CREATE, ALTER, DROP and TRUNCATE.
DML (Data Manipulation Language) changes data: INSERT, UPDATE and DELETE.
DQL queries data with SELECT.
DCL controls access with GRANT and REVOKE, and TCL manages transactions with COMMIT, ROLLBACK and SAVEPOINT.
INNER JOIN returns rows with matching values in both tables.
LEFT JOIN returns all rows from the left table and matches from the right, with NULLs where there is no match.
RIGHT JOIN is the reverse.
FULL OUTER JOIN returns all rows from both.
CROSS JOIN returns every combination, and a self join joins a table to itself, for example employees to their managers.
WHERE filters rows before grouping and cannot use aggregate functions.
HAVING filters groups after GROUP BY and can use aggregates, such as HAVING COUNT(*) > 5.
For example, to find departments with more than five employees earning over 50,000, WHERE filters the salary and HAVING filters the count.
SQL (Structured Query Language) is the standard language for creating, reading, updating and deleting data in relational databases. A relational database stores data in tables of rows and columns, with keys linking related tables, and a relational database management system such as MySQL, PostgreSQL, SQL Server or Oracle enforces data types, constraints and transactions.
GROUP BY collects rows with the same values in the chosen columns into groups, and aggregate functions calculate one value per group: COUNT, SUM, AVG, MIN and MAX.
For example, SELECT department, COUNT(*), AVG(salary) FROM employees GROUP BY department.
Every selected column must either be in the GROUP BY or inside an aggregate.
ORDER BY sorts results by one or more columns, ascending by default or with DESC.
To return only some rows, MySQL and PostgreSQL use LIMIT and OFFSET, SQL Server uses TOP or OFFSET ... FETCH, and the SQL standard and Oracle use FETCH FIRST n ROWS ONLY.
Without ORDER BY, the order of rows is not guaranteed.
DISTINCT removes duplicate rows from the result, considering all selected columns together, so SELECT DISTINCT city FROM customers returns each city once.
COUNT(DISTINCT city) counts unique values.
On large tables DISTINCT requires sorting or hashing, so GROUP BY or a better query design is sometimes clearer.
NULL means unknown or missing, so comparisons with = or <> never match it; use IS NULL and IS NOT NULL.
Any arithmetic with NULL gives NULL, and aggregate functions ignore NULLs (COUNT(column) skips them, COUNT(*) does not).
COALESCE(phone, 'Not given') returns the first non-null value, and NULLIF(a, b) returns NULL when two values are equal, which avoids division by zero.
NOT NULL requires a value, UNIQUE prevents duplicates, PRIMARY KEY uniquely identifies each row, FOREIGN KEY enforces links to another table, CHECK validates a condition such as price > 0, and DEFAULT supplies a value when none is given. Constraints keep data correct at the database level, even if application code has bugs.
Both combine the results of two queries with the same number and types of columns.
UNION removes duplicate rows, which requires extra sorting or hashing.
UNION ALL keeps every row and is faster.
Use UNION ALL unless you specifically need duplicates removed.
INTERSECT and EXCEPT return common rows or rows only in the first query.
LIKE matches text patterns: % matches any number of characters and _ matches exactly one, so name LIKE 'Ra%' finds names starting with Ra, and LIKE '_a%' finds names whose second letter is a. A pattern starting with % cannot use a normal index, so it is slow on large tables; full-text search is better for searching inside text. PostgreSQL has ILIKE for case-insensitive matching.
Applied problems, trade-offs and questions about your own projects.
DELETE removes selected rows using a WHERE clause, logs each row, fires triggers and can be rolled back.
TRUNCATE removes all rows quickly, resets identity counters and usually cannot be rolled back in some databases (MySQL), though it can in PostgreSQL and SQL Server within a transaction.
DROP removes the whole table including its structure.
One way is SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees).
A more general way for the Nth highest uses a window function: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) t WHERE rnk = 2.
DENSE_RANK handles ties correctly.
LIMIT 1 OFFSET 1 on distinct salaries also works in MySQL and PostgreSQL.
Window functions calculate across a set of related rows without collapsing them into one row like GROUP BY does. Examples are ROW_NUMBER, RANK and DENSE_RANK for ranking, SUM() OVER for running totals, LAG and LEAD to compare with previous or next rows, and AVG() OVER (PARTITION BY department) to compare each employee with their department average.
A primary key uniquely identifies each row, cannot be NULL, and a table has only one.
A unique key also enforces uniqueness but allows NULLs (how many depends on the database), and a table can have several.
A foreign key references a primary or unique key in another table to enforce referential integrity, so you cannot add an order for a customer who does not exist.
Normalisation organises tables to reduce duplicate data and update anomalies.
First normal form: each column holds atomic values and there are no repeating groups.
Second normal form: in 1NF and every non-key column depends on the whole primary key, not part of a composite key.
Third normal form: in 2NF and non-key columns do not depend on other non-key columns, so customer city belongs in the customers table, not in orders.
A subquery is a query inside another query, used in WHERE, FROM or SELECT, and an independent one runs once, such as finding employees in departments located in Pune.
A correlated subquery refers to columns of the outer query, so it is evaluated for each outer row, such as finding employees earning more than their own department's average.
Correlated subqueries can be slow and are often rewritten as joins or window functions.
A view is a saved query that behaves like a virtual table.
It simplifies complex joins, hides sensitive columns from some users and provides a stable interface when tables change.
A normal view runs its query each time it is used.
A materialized view stores the result physically for speed and must be refreshed.
A stored procedure is precompiled SQL logic called with CALL or EXEC; it can perform inserts and updates, manage transactions and return multiple result sets.
A user-defined function returns a single value or a table, can be used inside SELECT statements, and usually cannot change data.
Business logic in procedures is fast but harder to version and test than application code.
A transaction groups statements into one unit that either fully succeeds with COMMIT or is undone with ROLLBACK, such as debiting one account and crediting another. ACID means Atomicity (all or nothing), Consistency (data always satisfies rules and constraints), Isolation (concurrent transactions do not interfere) and Durability (committed data survives crashes).
CASE adds conditional logic inside a query, similar to if/else: CASE WHEN score >= 75 THEN 'Distinction' WHEN score >= 40 THEN 'Pass' ELSE 'Fail' END. It is used to create categories, apply conditional aggregation such as SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END), and custom sort orders.
Group by the columns that define a duplicate and keep groups with more than one row: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1.
To see or delete the extra rows while keeping one, number rows with ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) and remove those with a number greater than 1.
Then add a unique constraint to prevent new duplicates.
System design, deep internals, leadership and tough follow-ups.
An index, usually a B-tree, lets the database find rows without scanning the whole table, speeding up WHERE, JOIN and ORDER BY.
Composite index column order matters: an index on (city, age) helps queries filtering by city, but not by age alone.
Indexes slow down inserts, updates and deletes and use storage, so I index columns that are queried often and selective, and remove unused ones.
I read the execution plan with EXPLAIN or EXPLAIN ANALYZE to see full table scans, bad join orders or wrong row estimates. Then I add or adjust indexes, select only needed columns instead of SELECT *, avoid functions on indexed columns in WHERE, replace correlated subqueries with joins, filter early, paginate with keyset pagination for large offsets, and keep statistics up to date.
A Common Table Expression, defined with WITH, names a temporary result set so complex queries can be split into readable steps. A recursive CTE references itself to walk hierarchies, such as an organisation chart or category tree: an anchor query selects the top level, and a recursive part joins back to find children until no more rows are found.
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 prevents those but may allow phantom reads (new rows appear in a range).
Serializable prevents all of them, as if transactions ran one at a time.
Higher isolation means more locking or retries, so most systems use Read Committed or Repeatable Read.
FROM and JOIN first, then WHERE, GROUP BY, HAVING, SELECT (including window functions), DISTINCT, ORDER BY and finally LIMIT or OFFSET.
This explains why you cannot use a SELECT alias in WHERE but can in ORDER BY, and why aggregates belong in HAVING rather than WHERE.
The database optimiser may physically execute steps differently, but the results must match this order.
Denormalisation deliberately duplicates data to make reads faster, for example storing an order total or a customer name on the order, or keeping summary tables for dashboards.
It suits read-heavy systems and reporting, where joins across many large tables are too slow.
The cost is extra storage and the need to keep copies in sync, using triggers, application logic or scheduled jobs.
IN checks whether a value is in a list or subquery result.
EXISTS checks whether a subquery returns any row and can stop at the first match, which suits correlated checks such as customers who have placed an order.
JOIN combines rows and can create duplicates if the relationship is one-to-many.
NOT IN behaves unexpectedly if the subquery returns NULL, so NOT EXISTS is safer.
Modern optimisers often produce similar plans.
A trigger is code that runs automatically before or after INSERT, UPDATE or DELETE on a table, used for audit logs, enforcing complex rules or maintaining derived data.
The risks are hidden behaviour that developers do not expect, slower writes, harder debugging and cascading effects between triggers.
I use them sparingly, mainly for auditing, and keep business logic in the application.
Tasks you build live in 30 to 60 minutes. What interviewers look for, plus a compact solution to compare with yours.
A very common window-function question. DENSE_RANK keeps ties; use ROW_NUMBER if exactly three rows per department are required.
SELECT department_id, name, salary
FROM (
SELECT department_id, name, salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk <= 3
ORDER BY department_id, salary DESC;Tests self joins. Join the employees table to itself, matching each employee to their manager.
SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;Checks outer joins or NOT EXISTS. Both work; NOT EXISTS avoids the NULL trap of NOT IN.
-- Using a LEFT JOIN
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
-- Using NOT EXISTS
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);Combines aggregation, a CTE and LAG. Explain how you handle the first month, which has no previous value.
WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders
GROUP BY 1
)
SELECT month, revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM monthly
ORDER BY month;See all Database Engineer interview questions, covering SQL, MySQL, PostgreSQL, Oracle, SQL Server and more.