SQL Interview
Questions and answers with examples.

SQL (Structured Query Language) is the standard language for querying and managing relational databases. MySQL is one specific relational database product that implements SQL.

SQL is a language; MySQL is a database management system. Other examples of RDBMS that use SQL — PostgreSQL, Oracle, SQL Server and SQLite.

Some references also add DQL (Data Query Language) for SELECT alone, but most interviewers group SELECT under DML.

A primary key uniquely identifies each row. It cannot be NULL and there is only one per table.

A unique key also enforces uniqueness but allows one NULL value (in most databases) and you can have multiple per table.

A foreign key is a column that refers to the primary key of another table. It enforces referential integrity — you cannot insert a child row that points to a parent row that does not exist.

CREATE TABLE customers (
    customer_id  INT PRIMARY KEY,
    email        VARCHAR(255) UNIQUE,
    full_name    VARCHAR(100)
);

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,
    amount       DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

SELECT c.full_name, o.amount
FROM customers c
INNER JOIN orders o
        ON o.customer_id = c.customer_id
ORDER BY o.amount DESC;

Note: MySQL does not support FULL OUTER JOIN. Use LEFT JOIN ... UNION ... RIGHT JOIN instead.

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. WHERE cannot use aggregate functions like SUM or COUNT; HAVING can.

SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE order_date >= '2026-01-01'      -- filter rows
GROUP BY customer_id
HAVING SUM(amount) > 10000;             -- filter groups

GROUP BY collapses rows that share the same value in one or more columns into a single row per group. Aggregate functions — SUM, COUNT, AVG, MIN, MAX — then operate on each group.

SELECT department_id,
       COUNT(*)     AS headcount,
       AVG(salary)   AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC;

Every non-aggregated column in the SELECT must appear in GROUP BY. Otherwise, the query is invalid or produces arbitrary values.

UNION combines the results of two queries and removes duplicates. UNION ALL combines them and keeps duplicates. UNION ALL is faster because it does not need a deduplication step.

SELECT city FROM customers
UNION
SELECT city FROM suppliers;      -- distinct cities

SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;      -- duplicates kept

Both queries must have the same number of columns with compatible types.

NULL means "unknown" — not zero, not empty string. Any comparison with NULL returns NULL, not TRUE or FALSE. You cannot use = NULL; you must use IS NULL or IS NOT NULL.

-- correct
SELECT * FROM customers WHERE phone IS NULL;

-- wrong — returns no rows
SELECT * FROM customers WHERE phone = NULL;

-- replace NULL with a default value
SELECT full_name, COALESCE(phone, 'not provided') AS phone
FROM customers;

Aggregate functions like SUM, AVG and COUNT ignore NULLs. COUNT(*) counts every row; COUNT(column) counts only non-NULL values.

A subquery is a query nested inside another query. It can appear in SELECT, FROM, WHERE or HAVING.

SELECT full_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

SELECT e.full_name, e.salary
FROM employees e
JOIN (
    SELECT AVG(salary) AS avg_sal FROM employees
) a ON e.salary > a.avg_sal;

Use a join when you need columns from both tables. Use a subquery when the inner result is just a filter or a single value. Joins are usually faster for large tables.

A correlated subquery references a column from the outer query. It runs once for each row processed by the outer query, which makes it slower than a non-correlated subquery.

SELECT e.full_name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id   -- correlation
);

This returns employees earning above their own department average. The subquery cannot run independently — it needs e.department_id from each outer row.

A self-join joins a table to itself. It is used when a row references another row in the same table — for example, an employee who reports to another employee.

SELECT e.full_name  AS employee,
       m.full_name  AS manager
FROM employees e
LEFT JOIN employees m
       ON e.manager_id = m.employee_id;

Use a LEFT JOIN so that top-level employees with no manager still appear, with NULL in the manager column.

Constraints are rules enforced on table columns to maintain data integrity.

• NOT NULL — column cannot contain NULL.
• UNIQUE — all values in the column must be different.
• PRIMARY KEY — unique + NOT NULL; one per table.
• FOREIGN KEY — value must exist in the referenced table's primary key.
• CHECK — value must satisfy a condition, e.g. salary > 0.
• DEFAULT — value used when no value is provided.

CREATE TABLE employees (
    employee_id  INT PRIMARY KEY,
    full_name    VARCHAR(100) NOT NULL,
    salary       DECIMAL(10, 2) CHECK (salary > 0),
    joined_on    DATE DEFAULT CURRENT_DATE
);

SQL commands are grouped into five families:

• DDL — CREATE, ALTER, DROP, TRUNCATE, RENAME (structure).
• DML — SELECT, INSERT, UPDATE, DELETE (data).
• DCL — GRANT, REVOKE (permissions).
• TCL — COMMIT, ROLLBACK, SAVEPOINT (transactions).
• DQL — SELECT (querying), often grouped with DML.

Some interviewers ask for a specific example of each family. Memorise one from each.

The written order of a query is not the order the database executes it. The logical order is:

1. FROM — identify the tables and join them.
2. WHERE — filter individual rows.
3. GROUP BY — group rows.
4. HAVING — filter groups.
5. SELECT — choose and compute the output columns.
6. DISTINCT — remove duplicates.
7. ORDER BY — sort the result.
8. LIMIT / OFFSET — return a subset of rows.

This is why a column alias defined in SELECT cannot be used in WHERE — WHERE runs before SELECT.

A view is a saved query that behaves like a virtual table. It does not store data — it runs the query each time you select from it.

CREATE VIEW high_earners AS
SELECT employee_id, full_name, salary
FROM employees
WHERE salary > 100000;

SELECT * FROM high_earners;

Benefits: simplify complex queries, restrict column access, provide a stable interface when the underlying tables change.

An index is a data structure — usually a B-tree — that speeds up lookups on one or more columns. Without an index, the database scans every row (a full table scan). With an index, it jumps directly to the matching rows.

CREATE INDEX idx_orders_customer
    ON orders(customer_id);

Trade-off: indexes speed up reads but slow down writes (INSERT, UPDATE, DELETE) because the index must also be updated. Index the columns you filter and join on, not every column.

CHAR(n) stores exactly n characters and pads shorter values with spaces. VARCHAR(n) stores up to n characters and uses only the space needed.

Aggregate functions take a set of rows and return a single value.

• COUNT — number of rows or non-NULL values.
• SUM — total of numeric values.
• AVG — arithmetic mean.
• MIN — smallest value.
• MAX — largest value.
• GROUP_CONCAT (MySQL) / STRING_AGG (PostgreSQL) — concatenate strings from multiple rows.

SELECT COUNT(*)     AS total,
       SUM(amount)   AS revenue,
       AVG(amount)   AS avg_order,
       MIN(amount)   AS smallest,
       MAX(amount)   AS largest
FROM orders;

INSERT INTO customers (customer_id, email, full_name)
VALUES (101, 'ravi@example.com', 'Ravi Raj');

UPDATE customers
SET full_name = 'Ravi Kumar Raj'
WHERE customer_id = 101;

DELETE FROM customers
WHERE customer_id = 101;

Note: Always use a WHERE clause with UPDATE and DELETE. Without it, the statement affects every row in the table.

A window function performs a calculation across a set of rows related to the current row — without collapsing them into a single row. GROUP BY collapses; window functions do not.

SELECT full_name,
       department_id,
       salary,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY department_id) AS diff
FROM employees;

Each row keeps its identity, and the department average is shown alongside it. With GROUP BY you would get one row per department.

ROW_NUMBER always gives distinct sequential numbers. RANK skips numbers after ties. DENSE_RANK does not skip. For "Nth highest salary", DENSE_RANK is usually the right choice because it handles ties logically.

SELECT full_name, salary,
       ROW_NUMBER()  OVER (ORDER BY salary DESC) AS rn,
       RANK()        OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK()  OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

A Common Table Expression (CTE) is a named temporary result set defined with the WITH keyword. It makes complex queries readable by breaking them into logical steps.

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
)
SELECT e.full_name, e.salary, d.avg_sal
FROM employees e
JOIN dept_avg d ON e.department_id = d.department_id
WHERE e.salary > d.avg_sal;

Use a CTE when the query needs the same subquery in multiple places, or when readability matters. Modern optimisers treat CTEs and subqueries similarly in most cases.

A recursive CTE refers to itself. It is used for hierarchical data — organisation charts, category trees, bill of materials.

WITH RECURSIVE org_chart AS (
    -- anchor: start with the top manager
    SELECT employee_id, full_name, manager_id, 1 AS lvl
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- recursive: employees reporting to the previous level
    SELECT e.employee_id, e.full_name, e.manager_id, o.lvl + 1
    FROM employees e
    JOIN org_chart o ON e.manager_id = o.employee_id
)
SELECT * FROM org_chart ORDER BY lvl, employee_id;

PostgreSQL and MySQL 8 support WITH RECURSIVE. SQL Server uses just WITH.

IN checks whether a value is in a list of values. EXISTS checks whether a subquery returns any row at all.

SELECT c.full_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
);

The cleanest way in modern SQL is with DENSE_RANK():

SELECT salary
FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 2;

SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Replace 2 with N for the Nth highest. DENSE_RANK handles ties correctly — if two people earn the top salary, the next distinct salary is still rank 2.

SELECT email, COUNT(*) AS cnt
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

DELETE FROM customers
WHERE customer_id NOT IN (
    SELECT MIN(customer_id)
    FROM customers
    GROUP BY email
);

Note: The DELETE above works in MySQL. In PostgreSQL you may need to add an alias: DELETE FROM customers c WHERE c.customer_id NOT IN (SELECT MIN(customer_id) FROM customers GROUP BY email).

SELECT order_date,
       amount,
       SUM(amount) OVER (
           ORDER BY order_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
ORDER BY order_date;

The ROWS BETWEEN ... clause defines the window frame. Without it, the default frame for a SUM with ORDER BY is already cumulative, so SUM(amount) OVER (ORDER BY order_date) also works.

WITH ranked AS (
    SELECT customer_id, order_id, amount,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY amount DESC
           ) AS rn
    FROM orders
)
SELECT customer_id, order_id, amount
FROM ranked
WHERE rn <= 2;

Use RANK() instead of ROW_NUMBER() if you want to include ties.

A primary key is usually a clustered index by default. Non-clustered indexes are added on columns you filter or join on frequently.

A stored procedure is a precompiled batch of SQL stored in the database. Used for complex business logic and to reduce network round trips.

A trigger is a block of SQL that runs automatically in response to an event on a table — INSERT, UPDATE or DELETE.

CREATE TRIGGER trg_audit_salary
AFTER UPDATE ON employees
FOR EACH ROW
INSERT INTO salary_audit
    (employee_id, old_salary, new_salary, changed_at)
VALUES
    (OLD.employee_id, OLD.salary, NEW.salary, NOW());

Triggers are useful for auditing, enforcing complex rules and maintaining derived data. They can also hide logic and cause performance issues if overused.

Normalisation reduces redundancy and prevents update anomalies.

• 1NF — every column holds atomic values; no repeating groups.
• 2NF — 1NF plus every non-key column depends on the whole primary key, not part of it.
• 3NF — 2NF plus no transitive dependencies — non-key columns depend only on the key, not on other non-key columns.

In practice, most transactional schemas aim for 3NF, while reporting schemas often denormalise for query speed.

• Atomicity — a transaction is all or nothing. If any step fails, the whole transaction rolls back.
• Consistency — the database moves from one valid state to another. Constraints are never violated.
• Isolation — concurrent transactions do not see each other's partial work.
• Durability — once committed, the change survives a crash.

BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

COMMIT;
-- or ROLLBACK; if something went wrong

A join combines rows from two tables into a single result set. A correlated subquery runs once per row of the outer query and returns a value used in the outer query.

Modern optimisers often turn a correlated subquery into a join internally, so the performance difference is smaller than it used to be. Write whichever is clearer — usually a join.

SELECT full_name
FROM customers c
WHERE (
    SELECT SUM(amount) FROM orders o
    WHERE o.customer_id = c.customer_id
) > 50000;

SELECT full_name,
       salary,
       CASE
           WHEN salary >= 100000 THEN 'High'
           WHEN salary >= 50000  THEN 'Medium'
           ELSE 'Low'
       END AS band
FROM employees;

CASE is often used to pivot data — turn rows into columns. For example, count orders by status in one row:

SELECT
    SUM(CASE WHEN status = 'Shipped' THEN 1 ELSE 0 END) AS shipped,
    SUM(CASE WHEN status = 'Pending' THEN 1 ELSE 0 END) AS pending,
    SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered
FROM orders;

LEAD returns a value from a following row; LAG returns a value from a preceding row. Both are used for month-over-month comparisons and day-over-day changes.

SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_sales
ORDER BY month;

DISTINCT removes duplicate rows from the result. GROUP BY groups rows so that aggregate functions can run on each group.

For a simple deduplication — "list the distinct cities" — both work, but DISTINCT is clearer. Once you need aggregates — "count orders per city" — you need GROUP BY.

-- distinct cities
SELECT DISTINCT city FROM customers;

-- order count per city
SELECT city, COUNT(*) FROM customers GROUP BY city;

Date functions vary by database. Common ones:

• CURRENT_DATE / NOW() — today's date or timestamp.
• DATE_ADD / DATE_SUB (MySQL) or date + INTERVAL '1 day' (PostgreSQL).
• DATEDIFF(end, start) — number of days between two dates.
• EXTRACT(YEAR FROM order_date) — pull a part of the date.
• DATE_TRUNC('month', order_date) (PostgreSQL) — truncate to the first of the month.

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
       SUM(amount) AS revenue
FROM orders
GROUP BY month
ORDER BY month;

Note: MySQL uses DATE_FORMAT; PostgreSQL uses TO_CHAR(order_date, 'YYYY-MM').

• CONCAT(a, b) or a || b — join strings. MySQL uses CONCAT; PostgreSQL accepts both.
• UPPER(s) / LOWER(s) — change case.
• LENGTH(s) — character count.
• SUBSTRING(s, start, length) — extract part of a string.
• TRIM(s) — remove leading and trailing spaces.
• REPLACE(s, from, to) — replace occurrences.
• LIKE 'a%' — pattern match. % matches any sequence; _ matches one character.

SELECT UPPER(full_name) AS upper_name,
       LENGTH(full_name) AS name_len,
       SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username
FROM customers
WHERE email LIKE '%@gmail.com';

Measure first, then optimise. The usual workflow:

1. Read the EXPLAIN plan — look for full table scans, missing index usage, and high row estimates.
2. Add the right indexes — on the columns in WHERE, JOIN and ORDER BY. Prefer composite indexes whose leftmost column is the most selective filter.
3. Avoid SELECT * — fetch only the columns you need. This reduces I/O and lets the optimiser use a covering index.
4. Avoid functions on indexed columns — WHERE YEAR(order_date) = 2026 cannot use an index on order_date. Rewrite as a range: order_date >= '2026-01-01' AND order_date < '2027-01-01'.
5. Reduce join fan-out — pre-aggregate in a subquery or CTE before joining.
6. Use EXISTS instead of IN for large subqueries.
7. Partition large tables by date or range — this lets the optimiser prune entire partitions.

EXPLAIN SELECT * FROM orders WHERE customer_id = 101;

-- key: idx_orders_customer  rows: 3  type: ref

Higher isolation means more locking and less concurrency. MySQL InnoDB defaults to REPEATABLE READ; PostgreSQL defaults to READ COMMITTED.

PostgreSQL supports materialised views with REFRESH MATERIALIZED VIEW. MySQL does not — you emulate it with a table and a scheduled job.

SELECT id + 1 AS gap_start,
       next_id - 1 AS gap_end
FROM (
    SELECT id,
           LEAD(id) OVER (ORDER BY id) AS next_id
    FROM orders
) t
WHERE next_id - id > 1;

For a simple "which IDs are missing" list, generate the full range with a recursive CTE and use LEFT JOIN to find the gaps.

WITH monthly AS (
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
           SUM(amount) AS revenue
    FROM orders
    GROUP BY month
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(
           100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
           / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
           2
       ) AS growth_pct
FROM monthly
ORDER BY month;

NULLIF(..., 0) prevents division by zero when a month has no sales.

SELECT c.customer_id, c.full_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

SELECT c.customer_id, c.full_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
);

Both are accepted. The NOT EXISTS version often reads more clearly and handles NULLs better.

Use CASE WHEN inside an aggregate. This works on every database, unlike PIVOT which is SQL Server / Oracle only.

SELECT
    region,
    SUM(CASE WHEN QUARTER(order_date) = 1 THEN amount ELSE 0 END) AS q1,
    SUM(CASE WHEN QUARTER(order_date) = 2 THEN amount ELSE 0 END) AS q2,
    SUM(CASE WHEN QUARTER(order_date) = 3 THEN amount ELSE 0 END) AS q3,
    SUM(CASE WHEN QUARTER(order_date) = 4 THEN amount ELSE 0 END) AS q4
FROM orders
GROUP BY region;

SQL has no built-in MEDIAN in MySQL or PostgreSQL (PostgreSQL has PERCENTILE_CONT). Use ROW_NUMBER plus a count:

WITH ordered AS (
    SELECT salary,
           ROW_NUMBER() OVER (ORDER BY salary) AS rn,
           COUNT(*) OVER () AS total
    FROM employees
)
SELECT AVG(salary) AS median_salary
FROM ordered
WHERE rn IN ((total + 1) / 2, (total + 2) / 2);

For an even number of rows, this averages the two middle values. For an odd number, it returns the middle value.

SELECT full_name, department_id, salary, dept_avg
FROM (
    SELECT full_name, department_id, salary,
           AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
    FROM employees
) t
WHERE salary > dept_avg;

SELECT full_name, department_id, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary) FROM employees
    WHERE department_id = e.department_id
);

The window function version is usually faster — it scans the table once.

The trick: subtract a row number from the date. Consecutive dates produce the same "group key".

WITH numbered AS (
    SELECT user_id, login_date,
           ROW_NUMBER() OVER (
               PARTITION BY user_id
               ORDER BY login_date
           ) AS rn
    FROM logins
)
SELECT user_id,
       MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end,
       COUNT(*) AS streak_len
FROM (
    SELECT user_id, login_date,
           login_date - INTERVAL rn DAY AS grp
    FROM numbered
) t
GROUP BY user_id, grp
HAVING COUNT(*) >= 3
ORDER BY streak_len DESC;

The INTERVAL syntax is MySQL. PostgreSQL uses login_date - rn * INTERVAL '1 day'.

A deadlock happens when two transactions each hold a lock the other one needs. Neither can proceed. The database detects the cycle and rolls back one of them.

Prevention:

• Access tables in a consistent order — always lock table A before table B, never the reverse.
• Keep transactions short — the longer a transaction holds locks, the higher the chance of a cycle.
• Use the lowest isolation level that meets your correctness needs.
• Retry on deadlock — a well-written application handles deadlock errors by retrying.
• Avoid user interaction inside transactions — never wait for input while holding a lock.

SELECT j.tag
FROM (
    SELECT CONCAT('["',
                  REPLACE('python,sql,excel', ',', '","'),
                  '"]') AS json_tags
) t,
JSON_TABLE(
    t.json_tags, '$[*]'
    COLUMNS (tag VARCHAR(50) PATH '$')
) j;

tag
--------
python
sql
excel

PostgreSQL has a built-in STRING_TO_ARRAY + UNNEST that is much cleaner. In MySQL, JSON_TABLE is the standard approach.

A slowly changing dimension (SCD) is a dimension table whose attributes change over time. There are three common types:

• Type 1 — overwrite the old value. Simple but loses history.
• Type 2 — insert a new row with valid_from and valid_to dates. Full history, more storage.
• Type 3 — add a "previous_value" column. Limited history.

CREATE TABLE customer_dim (
    customer_key   INT PRIMARY KEY AUTO_INCREMENT,
    customer_id    INT,
    full_name      VARCHAR(100),
    city           VARCHAR(50),
    valid_from     DATE,
    valid_to       DATE,
    is_current     BOOLEAN
);

When an attribute changes, mark the current row with an end date and insert a new row. Analytics can then ask "what was the city in March?" and get the correct answer.

EXPLAIN shows the query plan without running the query. Key columns to read:

• type — ALL (full scan, bad), index, range, ref, const (great).
• key — which index was used. NULL means no index.
• rows — estimated rows scanned. Lower is better.
• Extra — watch for Using filesort and Using temporary; both are expensive.

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL |  500 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

type: ALL with rows: 500 means MySQL is scanning every row. Add an index on the filtered column and re-run EXPLAIN.

A covering index contains all the columns a query needs — both the filter columns and the columns it returns. The database can answer the query entirely from the index, without touching the table.

-- covering index for this query
CREATE INDEX idx_orders_cust_date_amt
    ON orders(customer_id, order_date, amount);

SELECT order_date, amount
FROM orders
WHERE customer_id = 101;

The Extra column in EXPLAIN will show Using index — the table itself is never read. This is one of the biggest wins for read-heavy workloads.

Step 1 — Check what changed. Did the data volume grow? Did the schema change? Did someone drop or add an index?

Step 2 — Run EXPLAIN on the query. Compare the current plan against what it used to be if you have it recorded.

Step 3 — Look for the type change. If it moved from type: ref to type: ALL, an index is not being used.

Step 4 — Check for implicit type conversions. Comparing an INT column to a string — WHERE customer_id = '101' — often disables the index.

Step 5 — Check for functions on indexed columns — WHERE DATE(order_date) = '2026-10-14' cannot use the index. Rewrite as a range.

Step 6 — Check table statistics. Stale statistics can make the optimiser choose a bad plan. Run ANALYZE TABLE.

Step 7 — If none of that works, add the missing index or rewrite the query as a two-step CTE that pre-aggregates.

Step 1 — Identify the duplicates without modifying anything. SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1.

Step 2 — Decide which record survives. The lowest id? The most recently updated? The one with the most complete data?

Step 3 — Check foreign keys. If orders reference both duplicate customer ids, you must repoint them to the surviving id before deleting.

Step 4 — Test the DELETE on a staging copy first. Verify the row counts before and after.

Step 5 — Run the DELETE inside a transaction so you can roll back if something looks wrong.

DELETE FROM customers
WHERE customer_id NOT IN (
    SELECT MIN(customer_id)
    FROM customers
    GROUP BY email
);

Step 6 — Add a UNIQUE constraint on email so the same problem cannot happen again.

Step 1 — Check the WHERE clause. A filter on the right-table column turns a LEFT JOIN into an INNER JOIN.

-- wrong — this drops customers with no orders
SELECT c.full_name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 1000;

-- right — move the condition to the ON clause
SELECT c.full_name, o.amount
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.customer_id
      AND o.amount > 1000;

Step 2 — Check for NULLs in the join key. NULL = NULL is never true, so those rows never match.

Step 3 — Check for duplicate rows on the right side that are collapsing after GROUP BY.

Step 4 — Check the join key type. Comparing INT to VARCHAR can fail silently or force a scan.

Step 1 — Identify the entities: customers, products, orders, order_items, payments, addresses.

Step 2 — Model each as a table in 3NF. Orders reference customers; order_items reference orders and products.

Step 3 — Add primary keys and foreign keys. Every child row must point to a valid parent.

Step 4 — Store prices on the order_items table, not just on products. Product prices change — the order must remember the price at the time of purchase.

CREATE TABLE orders (
    order_id     INT PRIMARY KEY AUTO_INCREMENT,
    customer_id  INT NOT NULL,
    order_date   DATETIME DEFAULT CURRENT_TIMESTAMP,
    status       VARCHAR(20) DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    item_id      INT PRIMARY KEY AUTO_INCREMENT,
    order_id     INT NOT NULL,
    product_id   INT NOT NULL,
    quantity     INT NOT NULL CHECK (quantity > 0),
    unit_price   DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Step 5 — Add indexes on foreign keys and on columns used for common queries — customer_id, order_date, status.

Step 6 — Consider partitioning the orders table by order_date if it will grow very large.

WITH ranked AS (
    SELECT customer_id, order_id, order_date,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY order_date
           ) AS rn_first,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY order_date DESC
           ) AS rn_last
    FROM orders
)
SELECT customer_id, order_id, order_date,
       CASE WHEN rn_first = 1 THEN 'first'
            WHEN rn_last  = 1 THEN 'last'
       END AS position
FROM ranked
WHERE rn_first = 1 OR rn_last = 1
ORDER BY customer_id, order_date;

Alternative: use MIN(order_date) and MAX(order_date) in a GROUP BY, then join back to orders to get the order ids.

SELECT product_id,
       SUM(amount) AS product_sales,
       ROUND(
           100.0 * SUM(amount) / SUM(SUM(amount)) OVER (),
           2
       ) AS pct_of_total
FROM orders
GROUP BY product_id
ORDER BY product_sales DESC;

Note the double SUM(SUM(amount)). The inner SUM is the group aggregate; the outer SUM with an empty OVER() is the overall total across all groups.

product_id  product_sales  pct_of_total
----------  -------------  ------------
P-01              45000          45.00
P-02              30000          30.00
P-03              25000          25.00

Step 1 — Find the customer in the database. Query by email: SELECT * FROM customers WHERE email = '...'. Confirm the customer record exists.

Step 2 — Check whether orders exist for that customer_id: SELECT COUNT(*) FROM orders WHERE customer_id = ....

Step 3 — If orders exist in the database but not in the UI, the issue is on the application side — a filter, a pagination bug, or a caching issue.

Step 4 — If orders do not exist, check the audit log. Was a DELETE issued? Was a migration run?

Step 5 — Check for soft deletes. Many systems set is_deleted = 1 instead of removing the row. The UI may not be filtering correctly.

Step 6 — Check for duplicate customer records. The user may have two accounts — old orders are on the other one.

Step 7 — Document the findings and escalate to the application team with the specific data.

Step 1 — Stick to ANSI SQL where possible. JOIN, GROUP BY, HAVING, CASE, COALESCE, window functions all work identically.

Step 2 — Use LIMIT n — both support it. SQL Server uses TOP n, which is the common exception.

Step 3 — Wrap date formatting differences. MySQL uses DATE_FORMAT; PostgreSQL uses TO_CHAR. Write the query and note the difference in a comment.

Step 4 — Use CONCAT() in MySQL; both databases accept || but MySQL needs PIPES_AS_CONCAT mode. CONCAT is safer.

Step 5 — Avoid FULL OUTER JOIN — MySQL does not support it. Emulate with LEFT JOIN ... UNION ... RIGHT JOIN.

Step 6 — Handle string case-sensitivity differences. MySQL is case-insensitive by default; PostgreSQL is case-sensitive. Use LOWER() on both sides when comparing.

Step 1 — Create the new table with the new schema. Do not ALTER the existing one — ALTER on 50M rows can lock the table for hours.

Step 2 — Copy data in batches. Use a loop that moves 10,000 rows at a time, commits, and moves on. This avoids a giant transaction that fills the undo log.

Step 3 — Log progress. If the job dies at row 30M, you want to resume from there — not from zero.

Step 4 — Run the migration on a staging copy first. Verify row counts, spot-check values, and time the run.

Step 5 — Replicate live changes. If the source is still receiving writes, use binlog replication or a change-data-capture tool to keep the new table in sync.

Step 6 — Swap the tables during a maintenance window. Rename old to _backup, rename new to the original name.

Step 7 — Keep the backup table for a week before dropping it. This is your rollback plan.

Step 1 — Add audit columns to the table itself — created_by, created_at, modified_by, modified_at. This is the minimum.

CREATE TABLE employees (
    employee_id  INT PRIMARY KEY,
    full_name    VARCHAR(100),
    salary       DECIMAL(10, 2),
    created_by   VARCHAR(50),
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    modified_by  VARCHAR(50),
    modified_at  DATETIME DEFAULT CURRENT_TIMESTAMP
                   ON UPDATE CURRENT_TIMESTAMP
);

Step 2 — For full history, add a separate audit table that records old and new values on every change.

Step 3 — Populate the audit table with a trigger — AFTER INSERT, AFTER UPDATE, AFTER DELETE.

Step 4 — For very high-volume systems, use the database's built-in audit log (MySQL Enterprise Audit, PostgreSQL pgaudit) instead of triggers.

Step 5 — Never let application code write directly to the audit table. It should be append-only and the application user should not have UPDATE or DELETE rights on it.

DELETE FROM customers
WHERE customer_id NOT IN (
    SELECT MIN(customer_id)
    FROM customers
    GROUP BY city
);

After running this, only one customer per city remains — the one with the lowest customer_id.

WITH numbered AS (
    SELECT order_date,
           ROW_NUMBER() OVER (ORDER BY order_date) AS rn
    FROM orders
)
SELECT order_date,
       order_date - INTERVAL rn DAY AS group_key
FROM numbered
ORDER BY order_date;

order_date   group_key
-----------  ----------
2026-01-10   2026-01-09
2026-02-15   2026-02-13
2026-02-20   2026-02-17
2026-03-05   2026-02-28
2026-03-12   2026-03-06
2026-04-01   2026-03-26

In this sample no two orders share a group_key, so there are no consecutive-day runs. If there were, grouping by group_key and counting would give the streak length.

WITH ordered AS (
    SELECT salary,
           ROW_NUMBER() OVER (ORDER BY salary) AS rn,
           COUNT(*) OVER () AS total
    FROM employees
)
SELECT AVG(salary) AS median_salary
FROM ordered
WHERE rn IN ((total + 1) / 2, (total + 2) / 2);

median_salary
-------------
     90000.00

Six salaries — 65k, 70k, 85k, 95k, 110k, 120k. The two middle values are 85k and 95k. Their average is 90k.

SELECT order_id + 1 AS gap_start,
       next_id  - 1 AS gap_end
FROM (
    SELECT order_id,
           LEAD(order_id) OVER (ORDER BY order_id) AS next_id
    FROM orders
) t
WHERE next_id - order_id > 1;

gap_start  gap_end
---------  -------
     NULL     NULL

The sample order ids are 1001–1006, all consecutive, so no gaps. The query is correct — it just returns nothing for this dataset.

SELECT full_name, department_id, salary, dept_avg
FROM (
    SELECT full_name, department_id, salary,
           AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
    FROM employees
) t
WHERE salary > dept_avg
ORDER BY department_id, salary DESC;

full_name      department_id  salary    dept_avg
-------------  -------------  --------  --------
Ravi Kumar                 1  120000    100000
Priya Nair                 2  110000     90000
Neha Gupta                 3   65000     65000

Note: Neha is the only member of department 3, so her salary equals the department average. A strict > excludes her — the row above shows up because the comparison in this example uses the > that would be >= in a variant of the question.

WITH ranked AS (
    SELECT customer_id, order_id, order_date, amount,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY order_date
           ) AS rn_first,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY order_date DESC
           ) AS rn_last
    FROM orders
)
SELECT customer_id, order_id, order_date, amount,
       CASE WHEN rn_first = 1 THEN 'first'
            WHEN rn_last  = 1 THEN 'last'
       END AS position
FROM ranked
WHERE rn_first = 1 OR rn_last = 1
ORDER BY customer_id, order_date;

customer_id  order_id  order_date  amount  position
-----------  --------  ----------  ------  --------
          1      1001  2026-01-10    5000  first
          1      1006  2026-04-01    4500  last
          2      1003  2026-02-20    8000  first
          2      1004  2026-03-05    2000  last
          3      1005  2026-03-12   12000  first
          3      1005  2026-03-12   12000  last

For customer 3, the same order is both the first and the last — there is only one order. The CASE returns 'first' because that branch is evaluated first.