SQL Interview Questions and Answers

SQL is the language for defining, reading and changing data in a relational database; MySQL is one database server that runs it, like PostgreSQL, SQL Server, Oracle and SQLite. The core (SELECT, JOIN, GROUP BY) is the same everywhere, while date functions, pagination and upserts differ by dialect.

They group SQL statements by what they act on: structure, data, permissions or transactions.

Follow-up: can you roll back a DROP TABLE? Not in MySQL or Oracle, where DDL commits the open transaction; in PostgreSQL and SQL Server, yes.

Both reject duplicates. A table has one primary key, which is never NULL and identifies the row; it can have many unique keys, which usually allow NULL.

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE);
INSERT INTO users VALUES (1, NULL), (2, NULL), (3, 'asha@example.com');
SELECT COUNT(*) AS total_rows, COUNT(email) AS emails FROM users;

The trap is NULL: two NULLs are not equal, so both NULL emails are accepted and the result is 3 | 1. PostgreSQL, MySQL, Oracle and SQLite allow many NULLs in a unique column by default; SQL Server allows one. See unique constraints.

A foreign key is a column whose values must exist in another table's primary or unique key, so a child row never points at a missing parent. ON DELETE CASCADE deletes the child rows when their parent is deleted.

PRAGMA foreign_keys = ON;
CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  dept_id INTEGER REFERENCES departments(id) ON DELETE CASCADE
);
INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales');
INSERT INTO employees VALUES (1, 'Asha', 1), (2, 'John', 2), (3, 'Priya', 2);
DELETE FROM departments WHERE id = 2;
SELECT id, name FROM employees;

Deleting Sales removes John and Priya, leaving 1 | Asha; without CASCADE the delete would fail. SQLite enforces foreign keys only after PRAGMA foreign_keys = ON. More in foreign keys.

A candidate key is a minimal set of columns that identifies a row. The primary key is the candidate key you choose (never NULL); the others are alternate keys, enforced with UNIQUE. A super key is any unique column set, a composite key spans several columns, and a foreign key references another table's key.

Follow-up: natural or surrogate key? Most schemas use a generated id as the primary key and keep the natural key (email, PAN number) as a UNIQUE alternate key, because natural keys can change.

DELETE removes rows (optionally filtered by WHERE), TRUNCATE removes every row at once, and DROP removes the table itself.

SQLite has no TRUNCATE; DELETE FROM t without a WHERE does the same job fast.

WHERE filters rows before grouping; HAVING filters groups after GROUP BY, so only HAVING can use aggregates such as COUNT(*).

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT dept, COUNT(*) AS people, SUM(salary) AS payroll
FROM employees
WHERE salary > 60000
GROUP BY dept
HAVING COUNT(*) >= 2
ORDER BY dept;

WHERE drops Karan before grouping, so HR disappears, and HAVING keeps Engineering and Sales. Put any condition that needs no aggregate in WHERE, so fewer rows get grouped. More in GROUP BY and HAVING.

Logically: FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT (window functions included), DISTINCT, ORDER BY, then LIMIT. The order explains the classic errors: an aggregate or a window function cannot go in WHERE, and a SELECT alias works in ORDER BY but not in WHERE (SQLite allows it as an extension). The optimizer may execute in another physical order with the same result.

CHAR(n) is fixed length and pads shorter values with spaces; VARCHAR(n) stores only what you give it, up to n. Use CHAR for fixed-size codes like INR.

CREATE TABLE countries (code VARCHAR(3));
INSERT INTO countries VALUES ('INDIA');
SELECT code, length(code) FROM countries;

SQLite prints INDIA | 5 because it never enforces a declared length; even STRICT tables only check the base type, and they reject VARCHAR as a column type. PostgreSQL, SQL Server, Oracle and strict-mode MySQL reject the insert. More on type affinity.

Each database has its own syntax for a column that numbers new rows:

-- MySQL
CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY, item VARCHAR(50));
-- PostgreSQL 10+ and Oracle 12c+ (standard); older PostgreSQL used SERIAL
CREATE TABLE orders (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, item TEXT);
-- SQL Server
CREATE TABLE orders (id INT IDENTITY(1, 1) PRIMARY KEY, item VARCHAR(50));

In SQLite an INTEGER PRIMARY KEY is the row id and gets the largest id plus one, so without AUTOINCREMENT it can reuse a deleted last id; see the SQLite AUTOINCREMENT docs. The others do not reuse a value (MySQL before 8.0 could after a restart) but leave gaps (a rolled-back insert burns its number), so never count rows with MAX(id).

Use COALESCE(a, b, ...), which returns its first non-NULL argument in every database.

CREATE TABLE contacts (name TEXT, phone TEXT, email TEXT);
INSERT INTO contacts VALUES
  ('Asha', NULL, 'asha@example.com'),
  ('Ravi', '98450 12345', NULL),
  ('Meera', NULL, NULL);
SELECT name, COALESCE(phone, email, 'no contact') AS contact FROM contacts;

Two-argument versions: IFNULL (MySQL, SQLite), ISNULL (SQL Server), NVL (Oracle). The reverse, NULLIF(a, b), returns NULL when a = b, which avoids division by zero: total / NULLIF(count, 0).

% matches any run of characters, including none, and _ matches exactly one, so '_a%' means "second letter is a".

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT name FROM employees WHERE name LIKE '_a%' ORDER BY name;

The result is Karan and Ravi. PostgreSQL and Oracle LIKE is case-sensitive (PostgreSQL adds ILIKE); SQLite (ASCII letters only), MySQL and SQL Server ignore case by default. A leading % cannot use a B-tree index; a prefix like 'kum%' can.

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT. They make the database reject bad data whichever application writes it.

-- fails on purpose: the CHECK constraint rejects a negative price
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price REAL CHECK (price >= 0),
  stock INTEGER DEFAULT 0
);
INSERT INTO products (name, price) VALUES ('Pen', 20);
INSERT INTO products (name, price) VALUES ('Refund', -5);

The second insert fails with CHECK constraint failed: price >= 0; the first gets stock = 0 from the default. MySQL parsed and ignored CHECK before 8.0.16; see the MySQL CHECK docs.

A view is a saved SELECT you query like a table. It stores the query, not the data, so it always shows current rows.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
CREATE VIEW high_earners AS
  SELECT name, dept, salary FROM employees WHERE salary > 85000;
SELECT * FROM high_earners ORDER BY salary DESC, name;

Use one to hide a complex join behind a name or to expose only some columns. A materialized view (PostgreSQL, Oracle) stores the result and must be refreshed; an indexed view in SQL Server stores it too and is updated on every write. See views.

Sort with ORDER BY, then cut: LIMIT n in MySQL, PostgreSQL and SQLite, TOP n in SQL Server, and the standard FETCH FIRST n ROWS ONLY in PostgreSQL, Oracle 12c+ and SQL Server (after OFFSET 0 ROWS).

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT name, salary
FROM employees
ORDER BY salary DESC, name
LIMIT 3;

The result is Asha, Meera and Ravi. Without ORDER BY you get any 3 rows, and since Ravi and Meera tie at 90000, name is what fixes the order. More in LIMIT and OFFSET.

INNER keeps only matching rows, LEFT and RIGHT keep every row of one side with NULLs where nothing matches, FULL OUTER keeps both sides, and CROSS returns every combination. A self join joins a table to itself.

CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'HR');
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept_id INTEGER);
INSERT INTO employees VALUES (1, 'Asha', 1), (2, 'Ravi', 1), (3, 'John', 2), (4, 'Neha', NULL);
SELECT 'INNER' AS join_type, COUNT(*) FROM employees e INNER JOIN departments d ON e.dept_id = d.id
UNION ALL
SELECT 'LEFT', COUNT(*) FROM employees e LEFT JOIN departments d ON e.dept_id = d.id
UNION ALL
SELECT 'RIGHT', COUNT(*) FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id
UNION ALL
SELECT 'FULL', COUNT(*) FROM employees e FULL OUTER JOIN departments d ON e.dept_id = d.id;

Neha has no department and HR has no employees: INNER gives 3, LEFT adds Neha (4), RIGHT adds HR (4), FULL adds both (5). MySQL has no FULL OUTER JOIN; SQLite added RIGHT and FULL in 3.39. More in INNER JOIN.

A self join joins a table to itself under two aliases, the standard way to read a hierarchy such as manager_id pointing at another employees row.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.id;

LEFT JOIN keeps Asha, who has no manager; an inner join would drop her. For the whole chain of managers, use a recursive CTE. More in self join.

Use an anti-join: LEFT JOIN the orders and keep rows where the order side is NULL, or use NOT EXISTS.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meera');
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
INSERT INTO orders VALUES (1, 1), (2, 1), (3, 2);
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Only Meera is returned. WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) gives the same result. Avoid NOT IN (SELECT customer_id FROM orders): one NULL customer_id makes it return no rows.

Take the LEFT JOIN, then UNION ALL the right-table rows that had no match.

CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'HR');
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept_id INTEGER);
INSERT INTO employees VALUES (1, 'Asha', 1), (2, 'Ravi', 1), (3, 'John', 2), (4, 'Neha', NULL);
SELECT e.name AS employee, d.name AS department
FROM employees e LEFT JOIN departments d ON e.dept_id = d.id
UNION ALL
SELECT NULL, d.name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

The result has the three matched employees, Neha with a NULL department, and HR with a NULL employee. LEFT JOIN ... UNION ... RIGHT JOIN also works here, but UNION would merge genuinely identical rows, such as two Ravis in one department.

A non-equi join matches on a condition other than =, such as BETWEEN, < or >. The classic example puts each employee into a salary band.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
CREATE TABLE grades (grade TEXT, min_salary INTEGER, max_salary INTEGER);
INSERT INTO grades VALUES ('C', 0, 69999), ('B', 70000, 99999), ('A', 100000, 1000000);
SELECT e.name, e.salary, g.grade
FROM employees e
JOIN grades g ON e.salary BETWEEN g.min_salary AND g.max_salary
ORDER BY e.salary DESC, e.name;

Only Asha is in band A and only Karan in band C. Other uses: the price valid at an event's time, and overlapping bookings.

They are the three physical ways to run a join, chosen by the optimizer and shown by EXPLAIN. A nested loop looks up inner rows for each outer row and wins when the outer side is small and the inner join column is indexed. A hash join hashes the smaller input and probes it, good for large unsorted inputs joined with =. A merge join walks two inputs already sorted on the key.

In most engines only a nested loop handles a condition like a.x < b.y. SQLite only does nested loops, and MySQL added hash joins in 8.0.18 and from 8.0.20 also runs non-equi joins as a hash join plus a filter; see the MySQL hash join docs. A nested loop over two big tables with no index on the inner side is the classic slow query.

They turn many rows into one value: COUNT, SUM, AVG, MIN and MAX. With GROUP BY they return one value per group.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT dept,
       COUNT(*) AS people,
       SUM(salary) AS payroll,
       AVG(salary) AS average,
       MIN(salary) AS lowest,
       MAX(salary) AS highest
FROM employees
GROUP BY dept
ORDER BY dept;

Every aggregate except COUNT(*) ignores NULLs. See aggregate functions.

Because a selected column has several values per group and you did not say which one to show: after GROUP BY dept, name has three values for Engineering.

PostgreSQL, SQL Server, Oracle and MySQL with ONLY_FULL_GROUP_BY (the default since 5.7.5) reject it. SQLite accepts it, and with a single MAX or MIN takes the bare columns from that row:

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT dept, name, MAX(salary)
FROM employees
GROUP BY dept
ORDER BY dept;

That works here but fails in PostgreSQL. The portable version uses RANK() OVER (PARTITION BY dept ORDER BY salary DESC).

Put a CASE inside the aggregate: SUM(CASE WHEN condition THEN 1 ELSE 0 END). PostgreSQL and SQLite 3.30+ also have the standard FILTER clause; MySQL and SQL Server do not.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT dept,
       COUNT(*) AS people,
       SUM(CASE WHEN salary >= 85000 THEN 1 ELSE 0 END) AS high_paid,
       COUNT(*) FILTER (WHERE salary < 85000) AS others
FROM employees
GROUP BY dept
ORDER BY dept;

It computes several counts in one pass, and it is also how you pivot rows into columns.

Use the string aggregate: GROUP_CONCAT in MySQL and SQLite, STRING_AGG in PostgreSQL and SQL Server 2017+, LISTAGG in Oracle.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT dept, GROUP_CONCAT(name, ', ' ORDER BY name) AS people
FROM employees
GROUP BY dept
ORDER BY dept;

Without an ORDER BY inside the aggregate the order is not guaranteed (SQLite accepts it from 3.44). MySQL cuts the result at group_concat_max_len, 1024 bytes by default.

They add subtotal rows in one query. ROLLUP (a, b) groups by (a, b), then (a), then the grand total; CUBE groups by every combination; GROUPING SETS lists exactly the groupings you want.

-- PostgreSQL, SQL Server, Oracle
SELECT dept, SUM(salary) FROM employees GROUP BY ROLLUP (dept);
-- MySQL (only ROLLUP; no CUBE or GROUPING SETS)
SELECT dept, SUM(salary) FROM employees GROUP BY dept WITH ROLLUP;

GROUPING(dept) returns 1 on a subtotal row, telling it apart from a real NULL. SQLite has none of these, so use UNION ALL:

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT dept, total FROM (
  SELECT 0 AS k, dept, SUM(salary) AS total FROM employees GROUP BY dept
  UNION ALL
  SELECT 1, 'All departments', SUM(salary) FROM employees
) AS t
ORDER BY k, dept;

The last row is the grand total, 512000.

Without aggregates they return the same rows and usually get the same plan. GROUP BY exists to compute aggregates per group; DISTINCT only removes duplicate result rows.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT
  (SELECT COUNT(*) FROM (SELECT DISTINCT dept FROM employees) AS d) AS distinct_rows,
  (SELECT COUNT(*) FROM (SELECT dept FROM employees GROUP BY dept) AS g) AS grouped_rows;

Both return 3. A DISTINCT added to hide duplicates from a bad join is a smell; fix the join.

A subquery is a SELECT inside another statement. A correlated subquery uses a column of the outer query, so it is logically run once per outer row.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
-- employees who earn more than the average of their own department
SELECT name, dept, salary
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept)
ORDER BY salary DESC;

e.dept makes it correlated: Asha beats the Engineering average and Priya the Sales one. On a large table, AVG(salary) OVER (PARTITION BY dept) computes each average once. More in subqueries.

IN checks whether a value is in a list or a subquery's result; EXISTS checks whether a subquery returns any row and can stop at the first match.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meera');
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
INSERT INTO orders VALUES (1, 1, 500), (2, 1, 1500), (3, 2, 800);
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.amount > 1000
);

Modern optimizers usually turn both into the same semi-join. The difference that matters is NOT IN versus NOT EXISTS: one NULL in the subquery makes NOT IN match nothing.

A CTE (common table expression) is a named subquery written before the main query with WITH name AS (...), alive for that one statement.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
WITH dept_avg AS (
  SELECT dept, AVG(salary) AS avg_salary FROM employees GROUP BY dept
)
SELECT e.name, e.dept, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON d.dept = e.dept
WHERE e.salary > d.avg_salary
ORDER BY e.salary DESC;

Unlike a subquery it can be referenced several times and can be recursive; unlike a temporary table it cannot be indexed or reused by later queries. MySQL has CTEs from 8.0. See common table expressions.

An anchor query returns the starting rows, and a recursive query joins the CTE back to the table, combined with UNION ALL and repeated until no new rows appear.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
WITH RECURSIVE chain(id, name, level) AS (
  SELECT id, name, 1 FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, c.level + 1
  FROM employees e
  JOIN chain c ON e.manager_id = c.id
)
SELECT name, level FROM chain ORDER BY level, name;

Asha is level 1, her four reports level 2, and Priya level 3. On cyclic data the recursion never ends by itself (SQL Server stops at 100 levels, MySQL at 1000), so add a depth limit like WHERE c.level < 20. More in recursive CTEs.

Not by itself. A CTE is for readability, and whether it is computed once (materialized) or inlined like a subquery depends on the database. PostgreSQL 12+ inlines a plain SELECT CTE referenced once and materializes one referenced more (override with AS MATERIALIZED or AS NOT MATERIALIZED); PostgreSQL 11 and older always materialized, which blocked filter pushdown. SQL Server always inlines, so a CTE used twice runs twice. See the PostgreSQL WITH docs, and check the plan before deciding.

Both stack the results of two queries; UNION removes duplicate rows and UNION ALL keeps every row, which is faster.

CREATE TABLE online_orders (customer TEXT);
INSERT INTO online_orders VALUES ('Asha'), ('Ravi');
CREATE TABLE store_orders (customer TEXT);
INSERT INTO store_orders VALUES ('Ravi'), ('Meera');
SELECT
  (SELECT COUNT(*) FROM (SELECT customer FROM online_orders
                         UNION SELECT customer FROM store_orders) AS u) AS union_rows,
  (SELECT COUNT(*) FROM (SELECT customer FROM online_orders
                         UNION ALL SELECT customer FROM store_orders) AS ua) AS union_all_rows;

UNION gives 3 rows (Ravi once), UNION ALL gives 4. Both need the same number of columns with compatible types. Default to UNION ALL.

A window function computes a value over related rows but keeps every row; GROUP BY collapses each group into one row.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT name, dept, salary,
       AVG(salary) OVER (PARTITION BY dept) AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY dept) AS diff
FROM employees
ORDER BY dept, salary DESC, name;

Each employee keeps a row with the department average beside it. Inside OVER, PARTITION BY splits the groups, ORDER BY orders rows within them, and an optional frame (ROWS BETWEEN ...) limits which rows count. See window functions.

Use SUM(...) OVER (ORDER BY ...): each row gets the sum of all rows up to and including itself.

CREATE TABLE daily_sales (day TEXT, amount INTEGER);
INSERT INTO daily_sales VALUES
  ('2026-01-01', 100), ('2026-01-02', 250), ('2026-01-03', 50), ('2026-01-04', 300);
SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total,
       MIN(amount) OVER (ORDER BY day) AS lowest_so_far
FROM daily_sales
ORDER BY day;

The total ends at 700. The same pattern with MIN gives the lowest value so far, the core of the "best time to buy and sell a stock" problem. Add PARTITION BY customer_id to restart the total per customer.

LAG(col) returns the previous row's value in the window order and LEAD(col) the next row's, so you compare neighbors without a self join.

CREATE TABLE daily_sales (day TEXT, amount INTEGER);
INSERT INTO daily_sales VALUES
  ('2026-01-01', 100), ('2026-01-02', 250), ('2026-01-03', 50), ('2026-01-04', 300);
SELECT day, amount,
       amount - LAG(amount) OVER (ORDER BY day) AS change,
       LEAD(amount) OVER (ORDER BY day) AS next_day
FROM daily_sales
ORDER BY day;

The first row's change is NULL because it has no previous day; LAG(amount, 1, 0) returns 0 instead. Typical uses are month-over-month growth and time between sessions.

Use a ROWS frame: AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) averages the current row and the two before it.

CREATE TABLE daily_sales (day TEXT, amount INTEGER);
INSERT INTO daily_sales VALUES
  ('2026-01-01', 100), ('2026-01-02', 250), ('2026-01-03', 50), ('2026-01-04', 300);
SELECT day, amount,
       ROUND(AVG(amount) OVER (
         ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 1) AS avg_3_days
FROM daily_sales
ORDER BY day;

The first two rows average fewer values (100.0, then 175.0). ROWS counts rows, so a missing day widens the window; fill gaps with a calendar table first.

The default frame ends at the current row, so LAST_VALUE returns the current row's value. Extend the frame to the end of the partition:

CREATE TABLE scores (name TEXT, score INTEGER);
INSERT INTO scores VALUES ('Asha', 100), ('Ravi', 90), ('John', 80);
SELECT name, score,
       LAST_VALUE(name) OVER (ORDER BY score) AS default_frame,
       LAST_VALUE(name) OVER (
         ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS whole_partition
FROM scores
ORDER BY score;

default_frame repeats each row's own name; whole_partition shows Asha on every row. Simpler: FIRST_VALUE(name) OVER (ORDER BY score DESC), since the default frame starts at the first row.

No. Window functions run after WHERE, GROUP BY and HAVING, so compute the window in a CTE or subquery and filter outside it.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
WITH t AS (
  SELECT name, salary, AVG(salary) OVER (PARTITION BY dept) AS dept_avg
  FROM employees
)
SELECT name, salary, dept_avg
FROM t
WHERE salary > dept_avg
ORDER BY salary DESC;

Snowflake, BigQuery, Databricks and DuckDB have a QUALIFY clause for this; PostgreSQL, MySQL, SQL Server and SQLite do not.

Take the highest salary below the maximum. It handles ties and returns NULL when there is no second salary.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

The answer is 90000, though two people earn it. The OFFSET form needs DISTINCT, or a tie at the top returns the top salary again:

-- MySQL, PostgreSQL, SQLite: DISTINCT matters when salaries repeat
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

-- SQL Server
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

It also returns no row, not NULL, when there is only one salary.

Rank the salaries with DENSE_RANK and keep rank N. Ties share a rank without gaps, so N means the Nth distinct value.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
WITH ranked AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT DISTINCT salary FROM ranked WHERE rnk = 3;

The distinct salaries are 120000, 90000, 82000, 70000 and 60000, so the 3rd highest is 82000. Add PARTITION BY dept for the Nth highest per department.

Group by the columns that should be unique and keep groups with more than one row.

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO users VALUES
  (1, 'asha@example.com'), (2, 'ravi@example.com'), (3, 'asha@example.com'),
  (4, 'ravi@example.com'), (5, 'meera@example.com'), (6, 'asha@example.com');
SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;

The asha address appears three times and the ravi address twice. To see the full duplicate rows, filter on COUNT(*) OVER (PARTITION BY email) > 1 in a CTE.

Keep the row with the smallest id in each group and delete the rest.

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO users VALUES
  (1, 'asha@example.com'), (2, 'ravi@example.com'), (3, 'asha@example.com'),
  (4, 'ravi@example.com'), (5, 'meera@example.com');
DELETE FROM users
WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email);
SELECT id, email FROM users ORDER BY id;

Rows 1, 2 and 5 remain. MySQL rejects this query (error 1093: you cannot select from the table you are deleting from), so use a self join there:

-- MySQL
DELETE u FROM users u
JOIN users keep ON keep.email = u.email AND keep.id < u.id;

Then add a UNIQUE constraint so the duplicates cannot come back.

Rank employees inside each department and keep rank 1.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
WITH ranked AS (
  SELECT name, dept, salary,
         RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT dept, name, salary FROM ranked WHERE rnk = 1 ORDER BY dept;

RANK returns both people if two tie for the top; ROW_NUMBER returns exactly one. Without window functions, join back to SELECT dept, MAX(salary) ... GROUP BY dept.

Same pattern with <= N instead of = 1; here N is 2.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
WITH ranked AS (
  SELECT name, dept, salary,
         DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT dept, name, salary, rnk
FROM ranked
WHERE rnk <= 2
ORDER BY dept, salary DESC, name;

Engineering returns three people because Ravi and Meera tie at rank 2. DENSE_RANK gives the top 2 salary levels and ROW_NUMBER exactly 2 people, so ask which the interviewer means.

Self join the employees to their managers on manager_id and compare salaries.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT e.name, 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;

Priya earns 82000 and her manager John 70000. An inner join is right here: someone with no manager has nobody to compare with.

Number the rows with ROW_NUMBER() and keep the odd numbers. Filtering on id % 2 breaks once the ids have gaps.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
WITH numbered AS (
  SELECT name, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM employees
)
SELECT name FROM numbered WHERE rn % 2 = 1;

The result is Asha, Meera and Priya; use rn % 2 = 0 for the even rows.

Use one UPDATE with a CASE in the SET, so each row changes exactly once.

CREATE TABLE people (id INTEGER, name TEXT, gender TEXT);
INSERT INTO people VALUES (1, 'Asha', 'F'), (2, 'Ravi', 'M'), (3, 'Meera', 'F');
UPDATE people
SET gender = CASE gender WHEN 'M' THEN 'F' WHEN 'F' THEN 'M' ELSE gender END;
SELECT name, gender FROM people ORDER BY id;

Two separate updates would turn every row into the same value. ELSE gender keeps any other value instead of setting it to NULL.

Compare each row with its neighbors using LAG and LEAD; if all three match, the number appears three times in a row.

CREATE TABLE logs (id INTEGER PRIMARY KEY, num INTEGER);
INSERT INTO logs VALUES (1, 1), (2, 1), (3, 1), (4, 2), (5, 1), (6, 2), (7, 2);
SELECT DISTINCT num FROM (
  SELECT num,
         LAG(num)  OVER (ORDER BY id) AS prev,
         LEAD(num) OVER (ORDER BY id) AS next
  FROM logs
) AS t
WHERE num = prev AND num = next;

Only 1 qualifies; 2 appears twice in a row at the end. A triple self join on id + 1 breaks when ids have gaps. For runs of any length, ROW_NUMBER() OVER (ORDER BY id) - ROW_NUMBER() OVER (PARTITION BY num ORDER BY id) is constant within a run (gaps and islands).

Generate the full range with a recursive CTE and keep the numbers that are not in the table.

CREATE TABLE invoices (id INTEGER PRIMARY KEY);
INSERT INTO invoices VALUES (1), (2), (4), (5), (8);
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < (SELECT MAX(id) FROM invoices)
)
SELECT n AS missing_id FROM seq
WHERE n NOT IN (SELECT id FROM invoices);

The missing ids are 3, 6 and 7. NOT IN is safe here because a primary key is never NULL. PostgreSQL can generate the range with generate_series(1, n).

Two intervals overlap when each starts before the other ends: a.start < b.end AND b.start < a.end. Self join on the room, with a.id < b.id so each pair appears once.

CREATE TABLE bookings (id INTEGER, room TEXT, start_time TEXT, end_time TEXT);
INSERT INTO bookings VALUES
  (1, 'A', '09:00', '10:00'),
  (2, 'A', '09:30', '11:00'),
  (3, 'A', '11:00', '12:00'),
  (4, 'B', '09:00', '10:00');
SELECT a.id AS booking, b.id AS clashes_with
FROM bookings a
JOIN bookings b
  ON a.room = b.room
 AND a.id < b.id
 AND a.start_time < b.end_time
 AND b.start_time < a.end_time;

Only bookings 1 and 2 clash. Booking 3 starts exactly when 2 ends, which strict < does not count, and booking 4 is in another room.

An index is a separate sorted structure, usually a B-tree, that maps column values to rows, so the database finds matches in O(log n) steps instead of reading every row.

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, name TEXT);
CREATE INDEX idx_users_email ON users(email);
EXPLAIN QUERY PLAN SELECT name FROM users WHERE email = 'asha@example.com';

The plan says SEARCH users USING INDEX idx_users_email (email=?) instead of SCAN users. The cost: every write updates each index, and an index on a column with few distinct values rarely helps. More in indexes.

A clustered index is the table itself, stored in key order, so there is only one. A non-clustered index is a separate structure of keys and row pointers; reading other columns costs an extra lookup unless the index covers the query.

SQL Server clusters on the primary key by default. MySQL InnoDB always does, and its secondary indexes store the primary key as the pointer, so a wide primary key bloats every index. PostgreSQL tables are heaps with no clustered index.

An index on (last_name, first_name) is sorted by last_name, then by first_name within each last name, like a phone book. It serves filters on the leading column but usually not on first_name alone: the leftmost prefix rule.

CREATE TABLE people (id INTEGER PRIMARY KEY, last_name TEXT, first_name TEXT, city TEXT);
CREATE INDEX idx_name ON people(last_name, first_name);
EXPLAIN QUERY PLAN SELECT * FROM people WHERE first_name = 'Ravi';

The plan is SCAN people. Put equality columns first, then the range or sort column: WHERE customer_id = ? ORDER BY created_at wants (customer_id, created_at).

Usually because the condition, as written, cannot use it:

1. A function on the column: lower(email) = ..., YEAR(created_at) = 2026.
2. A leading wildcard: LIKE '%kumar'.
3. An implicit type conversion, such as a text column compared with a number.
4. A filter that skips the leftmost column of a composite index.
5. Low selectivity or stale statistics, where a scan really is cheaper.

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, name TEXT);
CREATE INDEX idx_users_email ON users(email);
EXPLAIN QUERY PLAN SELECT name FROM users WHERE lower(email) = 'asha@example.com';

The plan is SCAN users. Rewrite the condition on the bare column, or add an expression index on lower(email). Confirm with EXPLAIN QUERY PLAN.

Atomicity (all statements succeed or none do), Consistency (constraints hold before and after), Isolation (concurrent transactions do not see each other's unfinished work) and Durability (a committed change survives a crash).

CREATE TABLE accounts (id INTEGER PRIMARY KEY, name TEXT, balance INTEGER);
INSERT INTO accounts VALUES (1, 'Asha', 5000), (2, 'Ravi', 1000);
BEGIN;
UPDATE accounts SET balance = balance - 3000 WHERE id = 1;
UPDATE accounts SET balance = balance + 3000 WHERE id = 2;
ROLLBACK;
SELECT name, balance FROM accounts;

The rollback undoes both updates, so the balances stay 5000 and 1000: atomicity. Durability comes from a log written to disk before the commit returns, such as PostgreSQL's WAL. See transactions.

The SQL standard defines four levels, each preventing more anomalies at the cost of more locking or more retries.

The default is Read committed in PostgreSQL, SQL Server and Oracle, and Repeatable read in MySQL InnoDB. PostgreSQL's Repeatable read is snapshot isolation and shows no phantoms; see the PostgreSQL isolation docs. Serializable transactions can fail with an error the application must retry.

Two transactions each hold a lock the other needs. The database detects the cycle, rolls one back with an error, and lets the other finish.

-- Session 1                           -- Session 2
BEGIN;                                 BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
                                       UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- waits for session 2
                                       UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- deadlock

Prevent most by locking rows in the same order everywhere, keeping transactions short, and indexing UPDATE and DELETE conditions so fewer rows get locked. Some cannot be designed away, so retry on the deadlock error.

Read the plan first, then fix the biggest cost:

1. EXPLAIN ANALYZE: look for full scans of big tables and estimates far from actual rows.
2. Index the WHERE, JOIN and ORDER BY columns; a covering index skips table lookups.
3. Keep conditions index-friendly: no functions on indexed columns, no leading %.
4. Read less: no SELECT *, aggregate before joining.
5. Replace correlated subqueries with window functions and big OFFSET with keyset pagination.
6. Refresh statistics with ANALYZE.

Then measure again on the same data.

OFFSET 100000 still reads and discards 100000 rows, so later pages get slower. Keyset pagination starts after the previous page's last key with an indexed WHERE, so every page costs the same.

CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 100)
INSERT INTO posts SELECT i, 'Post ' || i FROM n;
-- the previous page ended at id 40
SELECT id, title FROM posts WHERE id > 40 ORDER BY id LIMIT 3;

The next page starts at 41. For a non-unique sort column, add the id as a tiebreaker: WHERE (created_at, id) < (?, ?). The trade-off is that you cannot jump straight to page 50.

Normalization splits data into tables so each fact is stored once, preventing update, insert and delete anomalies.

Follow-up: when do you denormalize? In read-heavy and analytics systems, a stored copy like posts.comment_count saves joins, at the cost of keeping it in sync.

Use the upsert syntax, which detects the conflict through a primary key or unique constraint: INSERT ... ON CONFLICT ... DO UPDATE in SQLite and PostgreSQL, ON DUPLICATE KEY UPDATE in MySQL.

CREATE TABLE page_views (page TEXT PRIMARY KEY, views INTEGER);
INSERT INTO page_views VALUES ('/home', 1);
INSERT INTO page_views VALUES ('/home', 1)
  ON CONFLICT (page) DO UPDATE SET views = page_views.views + excluded.views;
INSERT INTO page_views VALUES ('/about', 1)
  ON CONFLICT (page) DO UPDATE SET views = page_views.views + excluded.views;
SELECT page, views FROM page_views ORDER BY page;

/home becomes 2 and /about is inserted; excluded is the row you tried to insert. Select-then-insert is wrong under concurrency: two sessions both see no row and both insert. See upsert.

SQL injection is user input pasted into the SQL text that changes what the query does. Prevent it with parameterized queries: the SQL and the values travel separately, so a value can never become code.

# Unsafe: the input becomes part of the SQL
cur.execute(f"SELECT * FROM users WHERE email = '{email}'")
# email = "x' OR '1'='1" returns every user

# Safe: the driver sends the value as data
cur.execute("SELECT * FROM users WHERE email = ?", (email,))

Parameters cannot replace table or column names; check those against an allow list. More in preventing SQL injection.

A trigger is code the database runs automatically before or after an INSERT, UPDATE or DELETE. A good use is an audit log no application can forget to write.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, salary INTEGER);
CREATE TABLE salary_log (emp_id INTEGER, old_salary INTEGER, new_salary INTEGER);
CREATE TRIGGER log_salary_change
AFTER UPDATE OF salary ON employees
BEGIN
  INSERT INTO salary_log VALUES (OLD.id, OLD.salary, NEW.salary);
END;
INSERT INTO employees VALUES (1, 'Asha', 90000);
UPDATE employees SET salary = 99000 WHERE id = 1;
SELECT * FROM salary_log;

The log row is 1 | 90000 | 99000. Avoid triggers for business logic: they are invisible in application code and slow every write. SQL Server triggers fire once per statement, not per row.

A function returns a value and can be used inside a query (SELECT tax(amount) FROM orders). A stored procedure is called on its own with CALL or EXEC, can return result sets, and is meant for multi-step work that changes data.

SQL Server functions cannot change data, while PostgreSQL functions can. Heavy procedure logic is harder to test and deploy than application code and ties you to one database.

A cursor walks a query result one row at a time inside a stored procedure. People avoid it because a row-by-row loop is far slower than one set-based statement.

-- SQL Server (T-SQL)
DECLARE @name VARCHAR(50);
DECLARE emp_cursor CURSOR FOR SELECT name FROM employees;
OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @name;
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @name;
  FETCH NEXT FROM emp_cursor INTO @name;
END;
CLOSE emp_cursor;
DEALLOCATE emp_cursor;

The lifecycle is declare, open, fetch in a loop, close, deallocate. Most cursor loops can become one UPDATE with a join or a CASE.

Turn the date into a month value and group by it.

CREATE TABLE orders (id INTEGER, order_date TEXT, amount INTEGER);
INSERT INTO orders VALUES
  (1, '2026-01-05', 200), (2, '2026-01-20', 300),
  (3, '2026-02-11', 150), (4, '2026-03-02', 400), (5, '2026-03-28', 100);
SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY month
ORDER BY month;

The function differs by database: strftime('%Y-%m', d) in SQLite, DATE_FORMAT(d, '%Y-%m') in MySQL, DATE_TRUNC('month', d) in PostgreSQL. A month with no orders is missing; LEFT JOIN from a calendar to show a zero. More in date and time functions.

Divide by a window total: SUM(amount) OVER () repeats the grand total on every row, so no subquery is needed.

CREATE TABLE region_sales (region TEXT, amount INTEGER);
INSERT INTO region_sales VALUES ('North', 450), ('South', 300), ('East', 150), ('West', 100);
SELECT region, amount,
       ROUND(100.0 * amount / SUM(amount) OVER (), 1) AS pct_of_total
FROM region_sales
ORDER BY amount DESC;

The total is 1000, so North is 45.0. Write 100.0, not 100, or SQLite, PostgreSQL and SQL Server do integer division.

Group by the row label and write one conditional aggregate per output column.

CREATE TABLE sales (region TEXT, quarter TEXT, amount INTEGER);
INSERT INTO sales VALUES
  ('North', 'Q1', 100), ('North', 'Q2', 150), ('South', 'Q1', 80),
  ('South', 'Q2', 120), ('North', 'Q1', 50);
SELECT region,
       SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS q1,
       SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS q2
FROM sales
GROUP BY region
ORDER BY region;

North's two Q1 rows add up to 150, and the query works in every database. SQL Server's PIVOT and PostgreSQL's crosstab also need the columns listed in advance.

Number the sorted rows, count them, and average the middle one or two.

CREATE TABLE orders (id INTEGER, amount INTEGER);
INSERT INTO orders VALUES (1, 3), (2, 1), (3, 4), (4, 1), (5, 5), (6, 9);
WITH ordered AS (
  SELECT amount,
         ROW_NUMBER() OVER (ORDER BY amount) AS rn,
         COUNT(*) OVER () AS n
  FROM orders
)
SELECT AVG(amount) AS median
FROM ordered
WHERE rn IN ((n + 1) / 2, (n + 2) / 2);

Sorted, the values are 1, 1, 3, 4, 5, 9, so the median is 3.5. Integer division picks the positions: 3 and 4 for n = 6, 3 and 3 for n = 5 (MySQL needs FLOOR). PostgreSQL and Oracle have PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount); MySQL has no built-in, and SQLite's median() needs its percentile extension compiled in.

For each signup cohort, count the users who logged in the day after signing up and divide by the cohort size.

CREATE TABLE users (id INTEGER, signup_date TEXT);
INSERT INTO users VALUES (1, '2026-03-01'), (2, '2026-03-01'), (3, '2026-03-02'), (4, '2026-03-02');
CREATE TABLE logins (user_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
  (1, '2026-03-02'), (1, '2026-03-02'), (2, '2026-03-05'), (3, '2026-03-03'), (4, '2026-03-03');
SELECT u.signup_date,
       COUNT(DISTINCT u.id) AS signups,
       COUNT(DISTINCT l.user_id) AS returned_next_day,
       ROUND(100.0 * COUNT(DISTINCT l.user_id) / COUNT(DISTINCT u.id), 1) AS day1_pct
FROM users u
LEFT JOIN logins l
  ON l.user_id = u.id AND l.login_date = date(u.signup_date, '+1 day')
GROUP BY u.signup_date
ORDER BY u.signup_date;

March 1 retained one of two users (50.0), March 2 both (100.0). The date condition sits in ON so users who did not return stay in the denominator, and COUNT(DISTINCT ...) stops repeat logins from counting twice.

Subtract the row number from the date (gaps and islands): within a run of consecutive days both rise by one, so the difference stays constant and identifies the streak.

CREATE TABLE logins (user_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
  (1, '2026-03-01'), (1, '2026-03-02'), (1, '2026-03-03'),
  (1, '2026-03-05'), (1, '2026-03-06'), (2, '2026-03-01'), (2, '2026-03-03');
WITH days AS (
  SELECT DISTINCT user_id, login_date FROM logins
), grouped AS (
  SELECT user_id,
         julianday(login_date) - ROW_NUMBER() OVER (
           PARTITION BY user_id ORDER BY login_date
         ) AS streak_key
  FROM days
)
SELECT user_id, MAX(streak_len) AS longest_streak
FROM (SELECT user_id, COUNT(*) AS streak_len FROM grouped GROUP BY user_id, streak_key) AS s
GROUP BY user_id
ORDER BY user_id;

User 1's longest streak is 3 days (March 1 to 3); user 2's is 1. The DISTINCT matters because two logins on one day would break the arithmetic.

Label each row with a CASE expression and group by the label.

CREATE TABLE customers (name TEXT, age INTEGER);
INSERT INTO customers VALUES
  ('Asha', 19), ('Ravi', 24), ('Meera', 31), ('John', 45), ('Priya', 28), ('Karan', 62);
SELECT CASE
         WHEN age < 25 THEN '18-24'
         WHEN age < 35 THEN '25-34'
         WHEN age < 50 THEN '35-49'
         ELSE '50+'
       END AS age_group,
       COUNT(*) AS customers
FROM customers
GROUP BY age_group
ORDER BY age_group;

CASE stops at the first true branch, so each branch needs only an upper bound. Grouping by the alias works in MySQL, PostgreSQL and SQLite; SQL Server needs the whole CASE in GROUP BY.