SQL interviews test joins, GROUP BY and HAVING, subqueries and CTEs, window functions, indexes, transactions, normalisation and a few classic problems: second highest salary, duplicates, running totals and gaps and islands. These 40 questions share one practice schema, and every quoted result was run on SQLite. Where MySQL, PostgreSQL or SQL Server differ, the answer says so.
For students and freshers preparing for data analyst, backend and BI interviews. All 40 questions use the practice schema below, and every quoted result came from running the query on SQLite through Python's sqlite3 module. Differences in MySQL, PostgreSQL and SQL Server come from their manuals, read on 11 November 2026. I could not run those engines here, so their behaviour is cited, not tested.
In Google Trends India, the latest 8 weeks of SQL interview questions sit at about 41% of the same weeks a year earlier, but the term peaks in February at about 1.3 times its yearly average. Start with SQL vs MySQL and set up an engine from the free data analytics tools list.
The practice schema
Four tables cover every question. The script builds them in memory, checks that SQLite supports window functions (3.25.0 or later, released 15 September 2018) and runs ten checks.
import sqlite3
SCHEMA = """
CREATE TABLE departments (dept_id INTEGER PRIMARY KEY, dept_name TEXT NOT NULL);
CREATE TABLE employees (
emp_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept_id INTEGER REFERENCES departments (dept_id),
manager_id INTEGER REFERENCES employees (emp_id),
salary INTEGER NOT NULL,
hire_date TEXT NOT NULL
);
CREATE TABLE sales (
sale_id INTEGER PRIMARY KEY,
emp_id INTEGER REFERENCES employees (emp_id),
sale_date TEXT NOT NULL,
amount INTEGER NOT NULL
);
CREATE TABLE logins (user_id INTEGER NOT NULL, login_date TEXT NOT NULL);
INSERT INTO departments VALUES (1,'Sales'), (2,'Engineering'), (3,'HR'), (4,'Legal');
INSERT INTO employees VALUES
(1, 'Asha Rao', 2, NULL, 120000, '2019-03-15'),
(2, 'Ravi Shah', 2, 1, 95000, '2020-07-01'),
(3, 'Meera Iyer', 2, 1, 95000, '2021-01-10'),
(4, 'Kabir Singh', 1, 1, 80000, '2020-02-20'),
(5, 'Neha Gupta', 1, 4, 70000, '2022-06-01'),
(6, 'Arjun Nair', 1, 4, 70000, '2023-09-05'),
(7, 'Divya Menon', 3, 1, 60000, '2021-11-11'),
(8, 'Sana Khan', 3, 7, 62000, '2024-01-08'),
(9, 'Imran Ali', NULL, 1, 50000, '2025-08-18'),
(10, 'Tara Das', 2, 2, 65000, '2025-05-12');
INSERT INTO sales VALUES
(1, 4, '2026-01-05', 1000), (2, 4, '2026-01-20', 1500), (3, 5, '2026-01-28', 500),
(4, 4, '2026-02-10', 2000), (5, 6, '2026-02-14', 700), (6, 5, '2026-02-25', 800),
(7, 5, '2026-03-03', 1200), (8, 5, '2026-03-03', 1200), (9, 6, '2026-03-18', 900),
(10, 4, '2026-03-27', 1100);
INSERT INTO logins VALUES
(1,'2026-04-01'), (1,'2026-04-02'), (1,'2026-04-03'), (1,'2026-04-05'), (1,'2026-04-06'),
(1,'2026-04-09'), (2,'2026-04-02'), (2,'2026-04-03'), (2,'2026-04-04');
"""
con = sqlite3.connect(":memory:", isolation_level=None) # we issue BEGIN and ROLLBACK ourselves
assert sqlite3.sqlite_version_info >= (3, 25, 0), "window functions need SQLite 3.25.0 or later"
con.executescript(SCHEMA)
def show(label, sql):
print(label, con.execute(sql).fetchall())
def plan(label, sql):
print(label, [row[-1] for row in con.execute("EXPLAIN QUERY PLAN " + sql)])
show("= NULL :", "SELECT COUNT(*) FROM employees WHERE dept_id = NULL")
show("IS NULL :", "SELECT COUNT(*) FROM employees WHERE dept_id IS NULL")
show("2nd highest :", "SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)")
show("NOT IN :", "SELECT dept_name FROM departments WHERE dept_id NOT IN (SELECT dept_id FROM employees)")
show("NOT EXISTS :", "SELECT dept_name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id)")
show("islands :", """SELECT MIN(login_date), MAX(login_date), COUNT(*) FROM (
SELECT login_date, julianday(login_date) - ROW_NUMBER() OVER (ORDER BY login_date) AS grp
FROM logins WHERE user_id = 1) GROUP BY grp""")
query = "SELECT * FROM employees WHERE hire_date = '2020-02-20'"
plan("plan before :", query)
con.execute("CREATE INDEX idx_emp_hire ON employees (hire_date)")
plan("plan after :", query)
con.execute("BEGIN")
con.execute("UPDATE employees SET salary = salary + 5000 WHERE dept_id = 3")
show("HR in txn :", "SELECT SUM(salary) FROM employees WHERE dept_id = 3")
con.execute("ROLLBACK")
show("HR after :", "SELECT SUM(salary) FROM employees WHERE dept_id = 3")
Output, repeated in the answers:
= NULL : [(0,)]
IS NULL : [(1,)]
2nd highest : [(95000,)]
NOT IN : []
NOT EXISTS : [('Legal',)]
islands : [('2026-04-01', '2026-04-03', 3), ('2026-04-05', '2026-04-06', 2), ('2026-04-09', '2026-04-09', 1)]
plan before : ['SCAN employees']
plan after : ['SEARCH employees USING INDEX idx_emp_hire (hire_date=?)']
HR in txn : [(132000,)]
HR after : [(122000,)]Basics and joins (Q1 to Q11)
Basics
- Q1. What are DDL, DML, DQL, DCL and TCL? A textbook grouping. DDL defines structure (
CREATE,ALTER,DROP), DML changes rows (INSERT,UPDATE,DELETE), DQL reads them (SELECT), DCL controls access (GRANT,REVOKE) and TCL controls transactions (COMMIT,ROLLBACK). - Q2. What are a primary key, a foreign key and a unique key? A primary key identifies each row: unique, never NULL, one per table (
emp_id). A foreign key points at a key in another table and blocks orphan rows (dept_id). A unique constraint stops repeats, and a table can have several. SQLite enforces foreign keys only afterPRAGMA foreign_keys = ON. - Q3. What is NULL, and why does
= NULLmatch nothing? NULL means unknown, not zero or empty text. Any comparison with NULL is unknown, sodept_id = NULLcounted 0 rows andIS NULLcounted 1, Imran Ali.COALESCE(dept_id, 0)supplies a default.AVGandSUMskip NULLs. - Q4. DELETE, TRUNCATE and DROP: what is the difference?
DELETEremoves chosen rows (WHEREallowed).TRUNCATEempties the whole table fast.DROPremoves the table. MySQL's manual says truncation causes an implicit commit and cannot be rolled back. PostgreSQL and SQL Server can roll it back in a transaction. SQLite has noTRUNCATE. - Q5. UNION or UNION ALL? What are INTERSECT and EXCEPT?
UNIONremoves duplicates,UNION ALLkeeps them and is cheaper. Three rows plus four rows gave 7 withUNION ALLand 1 withUNION.INTERSECTkeeps common rows andEXCEPTsubtracts. MySQL added both in 8.0.31.
-- Q3: NULL
SELECT COUNT(*) FROM employees WHERE dept_id = NULL; -- 0
SELECT COUNT(*) FROM employees WHERE dept_id IS NULL; -- 1
SELECT name, COALESCE(dept_id, 0) FROM employees WHERE emp_id = 9; -- Imran Ali, 0
-- Q5: UNION ALL keeps duplicates, UNION removes them
SELECT dept_id FROM employees WHERE salary >= 95000 -- 3 rows, all department 2
UNION ALL
SELECT dept_id FROM employees WHERE dept_id = 2; -- 4 rows: 7 rows in all, 1 row with UNIONJoins
- Q6. Explain INNER, LEFT, RIGHT and FULL joins.
INNERkeeps matches only.LEFTkeeps every left row, with NULL where the right has no match.RIGHTmirrors it andFULLkeeps unmatched rows from both. Employees to departments gave 9, 10, 10 and 11 rows: Imran Ali has no department and Legal has no employee. - Q7. How do you get a full outer join in MySQL? Its join syntax lists only LEFT and RIGHT.
UNION ALLaLEFT JOINwith the unmatched rows of the oppositeLEFT JOIN(other sideIS NULL). Our data gave 11 rows, matchingFULL JOIN. SQLite addedRIGHTandFULLin 3.39.0, June 2022. - Q8. What is the difference between a condition in ON and in WHERE? In a
LEFT JOIN,ONdecides what matches.WHEREfilters the result, and a test on a right-table column drops the NULL rows, making it an inner join. Counting staff paid 70,000 or more: withON, HR and Legal show 0. WithWHERE, they vanish. - Q9. How do you find rows with no match?
LEFT JOINthenWHERE e.emp_id IS NULL, orNOT EXISTS. Both returned Legal. PreferNOT EXISTStoNOT IN, which breaks on NULL (Q19). - Q10. What is a self join? A table joined to itself under two aliases, such as employee and manager. Employees who earn more than their manager gave one row: Sana Khan (62,000) over Divya Menon (60,000). Use
LEFT JOINto keep Asha Rao, who has no manager. - Q11. Why does a SUM go wrong after a join? Joining to the many side repeats the one side's values. Summing
salaryafter joining employees to sales gave 740,000 against a true 220,000. Aggregate sales first in a subquery or CTE.
-- Q6: row counts are INNER 9, LEFT 10, RIGHT 10, FULL 11
SELECT COUNT(*) FROM employees e INNER JOIN departments d ON e.dept_id = d.dept_id;
SELECT COUNT(*) FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id;
SELECT COUNT(*) FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id; -- SQLite 3.39.0 or later
SELECT COUNT(*) FROM employees e FULL JOIN departments d ON e.dept_id = d.dept_id; -- SQLite 3.39.0 or later
-- Q7: a full outer join built from LEFT JOINs only (11 rows)
SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION ALL
SELECT e.name, d.dept_name FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;
-- Q8: the same condition in ON, then in WHERE
SELECT d.dept_name, COUNT(e.emp_id) FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id AND e.salary >= 70000
GROUP BY d.dept_name; -- 4 rows: Engineering 3, HR 0, Legal 0, Sales 3
SELECT d.dept_name, COUNT(e.emp_id) FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE e.salary >= 70000
GROUP BY d.dept_name; -- 2 rows: Engineering 3, Sales 3
-- Q9: departments with no employees
SELECT d.dept_name FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id WHERE e.emp_id IS NULL; -- Legal
SELECT dept_name FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id); -- Legal
-- Q10: self join, employees who earn more than their manager
SELECT e.name, m.name AS manager FROM employees e
JOIN employees m ON e.manager_id = m.emp_id WHERE e.salary > m.salary; -- Sana Khan, Divya Menon
-- Q11: fan-out
SELECT SUM(e.salary) FROM employees e JOIN sales s ON s.emp_id = e.emp_id; -- 740000, wrong
SELECT SUM(salary) FROM employees WHERE emp_id IN (SELECT emp_id FROM sales); -- 220000Aggregation, subqueries and CTEs (Q12 to Q21)
Aggregation
- Q12. What is the difference between WHERE and HAVING?
WHEREfilters rows before grouping.HAVINGfilters groups after aggregates. The logical order isFROM,WHERE,GROUP BY,HAVING,SELECT,ORDER BY, then the limit. For staff hired before 2025,HAVING AVG(salary) > 70000kept Sales (73,333) and Engineering (103,333). PostgreSQL's manual bars output aliases inWHEREandHAVING. - Q13. How do the COUNT variants differ?
COUNT(*)counts rows,COUNT(col)counts non-NULL values andCOUNT(DISTINCT col)counts distinct non-NULL values. Fordept_idthey return 10, 9 and 3. - Q14. What is the rule for columns in a GROUP BY query? Each selected column must be grouped or aggregated. MySQL's default
ONLY_FULL_GROUP_BYmode and PostgreSQL reject the rest unless they depend on grouped columns. SQLite returns a value from one row of the group, so a query can pass there and fail elsewhere. - Q15. How does GROUP BY treat NULL? All NULLs form one group:
GROUP BY dept_idgave NULL 1, Sales 3, Engineering 4, HR 2. AddWHERE dept_id IS NOT NULLto drop it. - Q16. What is conditional aggregation? A
CASEinside an aggregate, such asSUM(CASE WHEN salary >= 70000 THEN 1 ELSE 0 END), counts several conditions in one pass. Sales has 3 of 3 above the line, Engineering 3 of 4, HR 0 of 2. PostgreSQL and SQLite also acceptFILTER (WHERE ...).
-- Q12: WHERE filters rows, HAVING filters groups
SELECT dept_id, COUNT(*) AS n, ROUND(AVG(salary)) AS avg_salary
FROM employees WHERE hire_date < '2025-01-01'
GROUP BY dept_id HAVING AVG(salary) > 70000; -- (1, 3, 73333.0) and (2, 3, 103333.0)
-- Q13: three kinds of COUNT
SELECT COUNT(*), COUNT(dept_id), COUNT(DISTINCT dept_id) FROM employees; -- 10, 9, 3
-- Q15: NULLs form one group
SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id; -- NULL 1, 1 3, 2 4, 3 2
-- Q16: conditional aggregation
SELECT dept_id,
SUM(CASE WHEN salary >= 70000 THEN 1 ELSE 0 END) AS high_paid,
COUNT(*) AS total
FROM employees WHERE dept_id IS NOT NULL GROUP BY dept_id; -- (1, 3, 3) (2, 3, 4) (3, 0, 2)Subqueries and CTEs
- Q17. What kinds of subquery are there? Scalar (one value), derived table (in
FROM) and correlated (refers to the outer row). Four people beat the company average of 76,700: Asha Rao, Ravi Shah, Meera Iyer and Kabir Singh. Beating their own department's average adds Sana Khan. - Q18. IN, EXISTS or JOIN?
INtests membership.EXISTStests whether a row exists, and PostgreSQL's manual says that subquery generally stops once one row is found. AJOINreturns columns from both tables and can multiply rows. - Q19. What is the NOT IN trap?
x NOT IN (list)is never true if the list holds a NULL.dept_id NOT IN (SELECT dept_id FROM employees)returned no rows because of Imran Ali's NULL, whileNOT EXISTSreturned Legal. - Q20. CTE, subquery, view or temp table? A CTE (
WITH name AS (...)) names a subquery for one statement. A view saves a query in the database and a temp table stores rows. The CTE form of Q17 returned the same five people. MySQL gained CTEs in 8.0. - Q21. What is a recursive CTE? An anchor query plus a recursive step joined by
UNION ALL, repeated until no rows appear. Walking down from Asha Rao gave 1 person at level 0, 5 at level 1 and 4 at level 2. SQL Server uses plainWITH. MySQL, PostgreSQL and SQLite needWITH RECURSIVE.
-- Q17: scalar subquery (company average is 76700) and correlated subquery
SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
SELECT e.name, e.salary FROM employees e
WHERE e.salary > (SELECT AVG(x.salary) FROM employees x WHERE x.dept_id = e.dept_id);
-- Q19: the NOT IN trap
SELECT dept_name FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM employees); -- no rows
SELECT dept_name FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id); -- Legal
-- Q20: the same correlated result with a CTE
WITH dept_avg AS (
SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id
)
SELECT e.name, e.salary, ROUND(d.avg_salary) AS dept_avg
FROM employees e JOIN dept_avg d ON d.dept_id = e.dept_id
WHERE e.salary > d.avg_salary;
-- Q21: recursive CTE, people per level under the top manager
WITH RECURSIVE org (emp_id, lvl) AS (
SELECT emp_id, 0 FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, o.lvl + 1 FROM employees e JOIN org o ON e.manager_id = o.emp_id
)
SELECT lvl, COUNT(*) FROM org GROUP BY lvl; -- (0, 1) (1, 5) (2, 4)Window functions (Q22 to Q25)
- Q22. How does a window function differ from GROUP BY?
GROUP BYcollapses rows. A window function keeps every row:AVG(salary) OVER (PARTITION BY dept_id)put 73,333 on each Sales row. MySQL allows windows only in theSELECTlist andORDER BY, so filter them in an outer query. - Q23. ROW_NUMBER, RANK or DENSE_RANK?
ROW_NUMBERnever ties.RANKgives ties one number and skips the next.DENSE_RANKdoes not skip. For salaries 95,000, 95,000, 80,000 they return 2, 3, 4, then 2, 2, 4, then 2, 2, 3. - Q24. What do LAG and LEAD do? They read a value from the previous or next row in window order. Monthly sales of 3,000, 3,500 and 4,400 give changes of +500 and +900 (16.7% and 25.7%). The first month has no previous row, so it is NULL.
- Q25. Why do tied rows share a running total? With
ORDER BYand no frame, the default frame runs to the current row and its peers, as SQLite's manual says. Two sales on 2026-03-03 both showed 8,900. AddingROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWgave 7,700 and 8,900.
-- Q22: window average keeps every row
SELECT name, salary, ROUND(AVG(salary) OVER (PARTITION BY dept_id)) AS dept_avg
FROM employees WHERE dept_id = 1; -- dept_avg is 73333.0 on all three rows
-- Q23: ties
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC, emp_id) AS rn,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;
-- Ravi Shah 95000: 2, 2, 2 Meera Iyer 95000: 3, 2, 2 Kabir Singh 80000: 4, 4, 3
-- Q24: month-over-month change
SELECT month, total, total - LAG(total) OVER (ORDER BY month) AS change
FROM (SELECT substr(sale_date, 1, 7) AS month, SUM(amount) AS total FROM sales GROUP BY 1);
-- ('2026-01', 3000, None) ('2026-02', 3500, 500) ('2026-03', 4400, 900)
-- Q25: default frame versus ROWS frame, then a moving average
SELECT sale_id, sale_date, amount,
SUM(amount) OVER (ORDER BY sale_date) AS default_frame,
SUM(amount) OVER (ORDER BY sale_date, sale_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame,
ROUND(AVG(amount) OVER (ORDER BY sale_date, sale_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg3
FROM sales;
-- sale 7: 8900, 7700, 900.0 sale 8: 8900, 8900, 1066.7Indexes, transactions and normalisation (Q26 to Q35)
Indexes and performance
- Q26. What is an index, and what is a clustered index? A sorted structure, usually a B-tree, that finds rows without scanning the table, costing storage and write speed. PostgreSQL makes B-trees by default. In MySQL's InnoDB the primary key is the clustered index that stores the rows. SQL Server allows one per table. Indexing
hire_datechanged SQLite's plan fromSCANtoSEARCH. - Q27. How does a composite index work? Equality columns first, then range columns. An index on (
dept_id,salary) serveddept_id = 2 AND salary > 90000but notsalary > 90000alone, which scanned. MySQL's manual calls this the leftmost prefix rule. - Q28. Why is my index ignored? A function on the column hides it:
substr(hire_date, 1, 4) = '2020'andlower(name) = ...both scanned. Use a range (hire_date >= '2020-01-01' AND hire_date < '2021-01-01'used the index) or index the expression, which SQLite, PostgreSQL and MySQL support. - Q29. How do you debug a slow query? Read the plan:
EXPLAINin MySQL,EXPLAIN ANALYZEin PostgreSQL (it runs the statement),EXPLAIN QUERY PLANin SQLite. Look for scans of large tables, select only needed columns, add or fix an index, then measure again.
-- Q26 and Q27: SQLite plans (the part after the arrow is the plan line)
SELECT * FROM employees WHERE hire_date = '2020-02-20';
-- no index: SCAN employees
CREATE INDEX idx_emp_hire ON employees (hire_date);
-- with the index: SEARCH employees USING INDEX idx_emp_hire (hire_date=?)
CREATE INDEX idx_emp_dept_sal ON employees (dept_id, salary);
SELECT * FROM employees WHERE dept_id = 2 AND salary > 90000;
-- uses the index: SEARCH employees USING INDEX idx_emp_dept_sal (dept_id=? AND salary>?)
SELECT * FROM employees WHERE salary > 90000;
-- not the leftmost column: SCAN employees
-- Q28: a function on the column hides the index
SELECT * FROM employees WHERE substr(hire_date, 1, 4) = '2020'; -- SCAN employees
SELECT * FROM employees
WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01'; -- SEARCH ... idx_emp_hire (hire_date>? AND hire_date<?)
SELECT * FROM employees WHERE lower(name) = 'asha rao'; -- SCAN employees
CREATE INDEX idx_emp_lower_name ON employees (lower(name)); -- then: SEARCH ... idx_emp_lower_name (<expr>=?)Transactions and isolation
- Q30. What does ACID mean? Atomicity: a transaction's changes all happen or none do. Consistency: constraints hold after commit. Isolation: concurrent transactions do not see half-done work. Durability: committed data survives a crash. In SQLite a 5,000 raise for HR inside
BEGINtook their total from 122,000 to 132,000, andROLLBACKrestored 122,000. - Q31. What are the isolation levels and anomalies? A dirty read sees uncommitted data, a non-repeatable read finds a re-read row changed, and a phantom read finds a different set of rows. Higher levels block more. Defaults: MySQL InnoDB REPEATABLE READ, PostgreSQL and SQL Server READ COMMITTED, SQLite serializable.
- Q32. What is a deadlock? Two transactions each hold a lock the other needs, so the database aborts one and your code retries. PostgreSQL's manual says the best defence is taking locks in a consistent order. Keep transactions short.
Normalisation
- Q33. What are 1NF, 2NF and 3NF? 1NF: one value per field, no repeating groups, a primary key. 2NF: every non-key column depends on the whole key. 3NF: no non-key column depends on another non-key column. Microsoft calls third normal form the highest level most applications need.
- Q34. How would you normalise a flat orders table? Take
orders_flat(order_id, customer_name, customer_city, product, price). The city repeats on every order, so fixing it means many updates. Split into customers, products, orders and order lines linked by foreign keys, so each fact is stored once. - Q35. When do you denormalise? Normalised tables suit write-heavy systems. Reporting tables are sometimes flattened to avoid joins, accepting duplicated data. Microsoft notes that normalisation needs more tables and real designs cannot always comply fully.
Query-writing problems (Q36 to Q40)
- Q36. Find the second highest salary.
MAXbelowMAX,DISTINCT ... LIMIT 1 OFFSET 1(SQL Server:OFFSET 1 ROW FETCH NEXT 1 ROW ONLY) andDENSE_RANK() = 2all give 95,000.DENSE_RANKalso returns both tied people, Ravi Shah and Meera Iyer.ROW_NUMBERreturns one. With one distinct salary, theMAXform returns NULL andOFFSETreturns no row. - Q37. Find and delete duplicate rows.
GROUP BYevery column withHAVING COUNT(*) > 1found employee 5's 1,200 sale on 2026-03-03 twice. Delete withROW_NUMBER() OVER (PARTITION BY ... ORDER BY sale_id)and removern > 1, which dropped sale 8. MySQL's manual bars a subquery on the table being deleted from, so use its multi-tableDELETE. - Q38. Who earns the most in each department? Rank within each department and keep rank 1: Asha Rao (Engineering, 120,000), Sana Khan (HR, 62,000) and Kabir Singh (Sales, 80,000). Keeping ranks up to 2 returns 8 rows because of ties. PostgreSQL's
DISTINCT ONis another route. - Q39. Write a running total with percentage of total. Aggregate by month, then add
SUM(total) OVER (ORDER BY month)and divide bySUM(total) OVER (). Totals of 3,000, 3,500 and 4,400 give running totals of 3,000, 6,500 and 10,900: 27.5%, 59.6% and 100%. - Q40. Solve gaps and islands. Number each user's rows by date and subtract the number from the date. Consecutive days share one result, so grouping by it gives islands: user 1 has 1 to 3 April, 5 to 6 April and 9 April.
LEADfinds the gaps, after 3 and 6 April. Date arithmetic differs: PostgreSQLdate - integer(cast the row number toint), MySQLDATE_SUB, SQL ServerDATEADD. I ran only the SQLite form.
-- Q36: second highest salary, three ways (all give 95000)
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
SELECT name, salary FROM (
SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees
) WHERE r = 2; -- Ravi Shah and Meera Iyer
-- Q37: find duplicates (the delete is the last statement below, because it changes the totals in Q39)
SELECT emp_id, sale_date, amount, COUNT(*) FROM sales
GROUP BY emp_id, sale_date, amount HAVING COUNT(*) > 1; -- (5, '2026-03-03', 1200, 2)
-- Q38: highest paid person in each department
WITH r AS (
SELECT d.dept_name, e.name, e.salary,
DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rnk
FROM employees e JOIN departments d ON d.dept_id = e.dept_id
)
SELECT dept_name, name, salary FROM r WHERE rnk = 1;
-- Engineering Asha Rao 120000, HR Sana Khan 62000, Sales Kabir Singh 80000
-- Q39: monthly total, running total and cumulative share (before the duplicate delete)
SELECT month, total,
SUM(total) OVER (ORDER BY month) AS running,
ROUND(100.0 * SUM(total) OVER (ORDER BY month) / SUM(total) OVER (), 1) AS cum_pct
FROM (SELECT substr(sale_date, 1, 7) AS month, SUM(amount) AS total FROM sales GROUP BY 1);
-- ('2026-01', 3000, 3000, 27.5) ('2026-02', 3500, 6500, 59.6) ('2026-03', 4400, 10900, 100.0)
-- Q40: islands (consecutive days) and gaps, SQLite date arithmetic
SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS days
FROM (
SELECT user_id, login_date,
julianday(login_date) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS grp
FROM logins
)
GROUP BY user_id, grp;
-- user 1: 04-01 to 04-03 (3), 04-05 to 04-06 (2), 04-09 (1) user 2: 04-02 to 04-04 (3)
SELECT user_id, login_date AS last_day_before_gap, next_date FROM (
SELECT user_id, login_date,
LEAD(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS next_date
FROM logins
) WHERE julianday(next_date) - julianday(login_date) > 1;
-- (1, '2026-04-03', '2026-04-05') and (1, '2026-04-06', '2026-04-09')
-- Q37 continued: delete all but the lowest sale_id in each duplicate group
DELETE FROM sales WHERE sale_id IN (
SELECT sale_id FROM (
SELECT sale_id, ROW_NUMBER() OVER (PARTITION BY emp_id, sale_date, amount ORDER BY sale_id) AS rn
FROM sales
) WHERE rn > 1
); -- removes sale_id 8How to rehearse these answers
No company publishes an SQL rubric, so this plan is our own. Type each query from memory, predict the output, run it, then add a tie, a NULL and a duplicate to the data: those three cause most wrong answers.
- Days 1 to 2: Q1 to Q11.
- Days 3 to 4: Q12 to Q21.
- Days 5 to 6: Q22 to Q25 and Q36 to Q40.
- Day 7: Q26 to Q35 aloud, then three random problems against a timer.
Pair this with the data analyst roadmap, the data analyst interview questions for the non-SQL rounds and the Python interview questions. Masai's post on SQL vs Python for data analysts covers which to learn first.
Frequently asked questions
How do I find the second highest salary in SQL?
Use SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees), or rank with DENSE_RANK() OVER (ORDER BY salary DESC) and keep rank 2. On the sample table both return 95,000. Prefer DENSE_RANK when ties matter, because it returns everyone at that salary.
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping and cannot use aggregates. HAVING filters groups after GROUP BY, so HAVING AVG(salary) > 70000 works. Put row conditions in WHERE so fewer rows reach the grouping step.
What is the difference between RANK and DENSE_RANK?
Both give tied rows the same number. RANK then skips numbers, so salaries 95,000, 95,000 and 80,000 rank 2, 2 and 4. DENSE_RANK does not skip and gives 2, 2 and 3. ROW_NUMBER never ties and gives 2, 3 and 4.
How should I prepare for an SQL interview in a week?
Cover joins and NULL first, then grouping, subqueries, window functions and the five classic problems. Run every query yourself and say each answer aloud in under a minute. The plan above splits the 40 questions across seven days.
Is SQL alone enough for a data analyst interview?
Rarely. Analyst rounds usually add Excel or a BI tool, statistics and a business case next to SQL, though no company publishes a standard format. Treat SQL as one round and prepare the rest from the data analyst interview questions page.
Sources checked
- SQLite window functions since 3.25.0, default frame
- RIGHT and FULL OUTER JOIN added in SQLite 3.39.0
- SQLite transactions are serializable
- SQLite foreign keys off by default
- SQLite bare columns in aggregate queries
- InnoDB default isolation REPEATABLE READ
- MySQL TRUNCATE implicit commit
- ONLY_FULL_GROUP_BY in default mode
- MySQL join syntax lists LEFT and RIGHT only
- Cannot delete from a table and select from it in a subquery
- Leftmost prefix rule
- InnoDB clustered index is the primary key
- INTERSECT and EXCEPT added in 8.0.31
- PostgreSQL default isolation and anomalies
- Deadlocks: lock in a consistent order
- SQL Server default READ COMMITTED
- Normal forms
Learn this with mentors
Data and business analytics programmes with admissions open: live classes, mentors and project work.


