SQL Joins, Triggers, Procedures, Functions & Indexes — Interview Questions

How to use this lesson

This lesson covers database-side logic (triggers, procedures, functions, cursors) and performance basics (indexes, execution plans). Interviewers want both halves: know how to write each object, and when not to use it. Business logic hidden in triggers is a classic maintenance trap.

Q1. What's the difference between an INNER JOIN and a NATURAL JOIN?

Short answer: With an INNER JOIN you state the join condition explicitly (ON e.department_id = d.id). A NATURAL JOIN joins automatically on every column with the same name in both tables.

SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;        -- explicit and safe

SELECT * FROM employees NATURAL JOIN departments;          -- joins on ALL same-named columns (id? name? created_at?)

Common trap: relying on NATURAL JOIN in real code. If both tables have id, name or created_at, it joins on those as well, and silently returns wrong or empty results. A later schema change can break it too. Prefer explicit ON, or USING (department_id).

Learn it in depth → SQL Joins

Q2. How do you perform a self-join?

Short answer: Join a table to itself, using two different aliases. It's typically used for hierarchies (employee → manager), or for comparing rows within the same table.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;      -- LEFT keeps the CEO, who has no manager

Key points to cover:

• For deep hierarchies (a manager's manager, and so on), use a recursive CTE (WITH RECURSIVE, MySQL 8+).

Q3. What is a trigger, and how do you create one in MySQL?

Short answer: A trigger is code the database runs automatically BEFORE or AFTER an INSERT, UPDATE or DELETE on a table, for each affected row. It can see the NEW and OLD row values.

DELIMITER //
CREATE TRIGGER trg_order_audit
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_audit (order_id, action, changed_at)
    VALUES (NEW.id, 'CREATED', NOW());
END //
DELIMITER ;

Key points to cover:

• Good uses: audit trails, maintaining derived columns, and enforcing invariants the database must guarantee whatever the client.
• Drawbacks: the logic is hidden from application developers, hard to test and version, adds latency to every write, and can cascade unexpectedly. Many teams keep business logic in the application, and use triggers only for auditing.
• DELIMITER is a client command. It stops the ; inside the body from ending the CREATE statement early.

Q4. What is a stored procedure, and how do you create one?

Short answer: A stored procedure is a named, precompiled block of SQL stored in the database. It can take IN, OUT and INOUT parameters, contain control flow, and run multiple statements. You invoke it with CALL.

DELIMITER //
CREATE PROCEDURE apply_discount(IN p_order_id BIGINT, IN p_rate DECIMAL(5,2), OUT p_final DECIMAL(12,2))
BEGIN
    UPDATE orders SET total = total * (1 - p_rate / 100) WHERE id = p_order_id;
    SELECT total INTO p_final FROM orders WHERE id = p_order_id;
END //
DELIMITER ;

CALL apply_discount(42, 10.00, @final);
SELECT @final;

Key points to cover:

• Benefits: fewer network round trips, logic shared across different client applications, and tighter permission control (users can execute the procedure without having table access).
• Costs: vendor lock-in, harder testing, debugging and deployment, and logic split across two codebases. Call them from Java with SimpleJdbcCall, or JPA's @Procedure.

Q5. What is a cursor, and how do you use one in MySQL?

Short answer: A cursor lets a stored program iterate over a query result row by row:

1. DECLARE it for a SELECT.
2. OPEN it.
3. FETCH rows in a loop, until a NOT FOUND handler sets a flag.
4. CLOSE it.

DELIMITER //
CREATE PROCEDURE post_monthly_interest()
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE v_id BIGINT;
    DECLARE v_balance DECIMAL(12,2);
    DECLARE cur CURSOR FOR SELECT id, balance FROM accounts WHERE type = 'SAVINGS';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_id, v_balance;
        IF done THEN LEAVE read_loop; END IF;
        INSERT INTO interest_postings (account_id, amount) VALUES (v_id, ROUND(v_balance * 0.004, 2));
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

Common trap: using a cursor when one set-based statement would do. The procedure above is equivalent to a single INSERT … SELECT id, ROUND(balance * 0.004, 2) FROM accounts WHERE type = 'SAVINGS', which is far faster. Use cursors only when each row genuinely needs procedural logic that depends on earlier rows.

Q6. What is a user-defined function, and how do you create one?

Short answer: A stored function takes parameters and returns a single value. It can be used inside SQL expressions, unlike a procedure.

DELIMITER //
CREATE FUNCTION gst_amount(p_price DECIMAL(12,2), p_rate DECIMAL(5,2))
RETURNS DECIMAL(12,2)
DETERMINISTIC
BEGIN
    RETURN ROUND(p_price * p_rate / 100, 2);
END //
DELIMITER ;

SELECT name, price, gst_amount(price, 18.00) AS gst FROM products;

Key points to cover:

• Function vs procedure: a function returns one value and is usable in SELECT and WHERE. A procedure is invoked with CALL, can return several result sets or OUT parameters, and can manage transactions.
• A function applied to a column in WHERE (WHERE gst_amount(price, 18) > 100) prevents index use on that column.

Q7. What are aggregate functions?

Short answer: Functions that compute one value from many rows: COUNT, SUM, AVG, MIN, MAX, and MySQL's GROUP_CONCAT (or the standard STRING_AGG in other databases). With GROUP BY, they compute one value per group.

SELECT department,
       COUNT(*)                       AS headcount,
       COUNT(bonus)                   AS with_bonus,     -- NULL bonuses aren't counted
       SUM(salary)                    AS payroll,
       GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS members
FROM employees
GROUP BY department;

Key points to cover:

• Aggregates ignore NULLs (except COUNT(*)).
• Every non-aggregated column in SELECT must appear in GROUP BY (ONLY_FULL_GROUP_BY mode, on by default in MySQL 5.7+).

Q8. What's the difference between WHERE and HAVING?

Short answer: WHERE filters individual rows before grouping, and can't use aggregates. HAVING filters groups after GROUP BY, and usually tests aggregates.

SELECT department, SUM(salary) AS engineering_payroll
FROM employees
WHERE role = 'ENGINEER'                 -- row filter
GROUP BY department
HAVING SUM(salary) > 1000000;           -- group filter

Key points to cover:

• Put conditions in WHERE whenever possible. Filtering early reduces the work done by grouping.

Learn it in depth → Window Functions

Q9. What are indexes?

Short answer: An index is a separate data structure, usually a B+ tree, that lets the database find rows by a column's value without scanning the whole table, much like a book's index. It speeds up WHERE, JOIN, ORDER BY and GROUP BY, at the cost of extra storage and slower writes, because every insert or update must also maintain the index.

CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);   -- composite
CREATE UNIQUE INDEX uq_users_email ON users (email);

Key points to cover:

• In InnoDB, the primary key is the clustered index: rows are stored in PK order. Secondary indexes store the PK, and "look up" the row.
• Composite indexes follow the leftmost-prefix rule. An index on (customer_id, created_at) helps queries that filter on customer_id alone, or on both, but not on created_at alone.
• A covering index contains every column a query needs, so the table itself is never touched.
• Hash indexes (Memory engine, some others) support only equality lookups.

Learn it in depth → Indexes & Performance

Q10. How do you find out which indexes a query uses?

Short answer: Look at the execution plan:

• EXPLAIN SELECT … in MySQL. Check the type (ALL means a full scan; ref and range are good), key (the index chosen), rows (estimated rows examined) and Extra (Using index, Using filesort).
• EXPLAIN ANALYZE (MySQL 8.0.18+, PostgreSQL) actually runs the query, and reports real timings.

EXPLAIN SELECT id, total FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;

Key points to cover:

• Indexes are skipped when:
  • a function is applied to the column (WHERE YEAR(created_at) = 2026; rewrite it as a range);
  • a LIKE pattern starts with a wildcard ('%abc');
  • there's an implicit type conversion;
  • the column has low selectivity.

Learn it in depth → Query Execution Plans

Q11. Can you create an index on a view?

Short answer: Only if the view's results are stored. A normal view is just a saved query, so there's nothing to index. The indexes on its base tables are used when the view is queried. Databases differ:

• SQL Server: indexed views. Create the view WITH SCHEMABINDING, then add a UNIQUE CLUSTERED index on it.
• PostgreSQL and Oracle: materialized views can be indexed, and are refreshed on demand or on a schedule (REFRESH MATERIALIZED VIEW).
• MySQL: there are no materialized or indexed views. Emulate them with a summary table, kept up to date by scheduled jobs or triggers.

Follow-up questions this topic invites — and their answers

Q: Stored procedures or application code: where should business logic live? A: Usually in application code. It's easier to test, version, review and scale horizontally. Stored procedures make sense for data-heavy operations, where moving data to the application is too costly, or for shared legacy databases.

Q: What's the difference between a clustered and a non-clustered index? A: A clustered index defines the physical order of the rows, and there can be only one (in InnoDB, the primary key). A non-clustered (secondary) index is a separate structure pointing to the rows. A table can have many.

Q: Why not index every column? A: Every index slows down inserts, updates and deletes, uses memory and disk, and gives the optimiser more options to choose between (sometimes badly). Index for your actual query patterns, and drop unused indexes.

Q: What does Using filesort mean in MySQL's EXPLAIN output? A: MySQL has to sort the rows itself, rather than reading them in index order. An index matching the WHERE and ORDER BY columns can often eliminate the sort.