Skip to content
Tutorials Center
My learning

Topics

Lesson 40 of 40 ยท Projects and Practice

Practice: MariaDB Exercises and Interview Questions

Test your MariaDB skills with graded exercises on aggregates, joins, window functions, CTEs, indexes and stored programs, each with a runnable solution, plus common interview questions.

Reading is not learning until you can produce the answer yourself. This page gives you graded exercises for the second half of the course: functions, aggregates, window functions, CTEs, views, stored programs, indexes and administration. Try each one in your head or on paper first, then run the solution in the editor and compare. Change the query and re-run: breaking it on purpose is the fastest way to understand it.

The runnable editors use a portable SQL engine, so MariaDB-only features appear as read-only code, with the equivalent portable solution next to them. Exercises use three practice databases: company (employees, departments, projects), bookstore (authors, books, customers, orders) and shop (products, orders, order items).

Level 1: warm-up

Exercise 1. List each department's name with its headcount and average salary rounded to a whole number, only for departments with at least 3 employees, biggest first.

SQL Solution 1: headcount and average ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Check: did you filter with HAVING (a group condition), not WHERE?

Exercise 2. Show each employee's name and e-mail style login: the first letter of the first name plus the last name, in lower case (mariam ali becomes mali). In MariaDB you would use LOWER(CONCAT(LEFT(first_name, 1), last_name)).

SQL Solution 2: build a login ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Level 2: joins, subqueries, functions

Exercise 3. Find employees who earn more than the average salary of their own department, with the amount above that average.

SQL Solution 3: above the department average ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Bonus: solve it again with a window function (AVG(salary) OVER (PARTITION BY department_id) in a CTE). Which version is easier to read?

Exercise 4. List the authors who have no books in the shop, using NOT EXISTS. Then, in the same result, list the authors with at least one book priced above 20, using EXISTS.

SQL Solution 4: NOT EXISTS and EXISTS ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Exercise 5. Tenure report: for each employee, how many complete years since hire (as of 2026-03-15), plus the date of the next work anniversary. MariaDB: TIMESTAMPDIFF(YEAR, hire_date, '2026-03-15') and DATE_ADD(hire_date, INTERVAL n YEAR).

SQL Solution 5: tenure and anniversary ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Level 3: windows and CTEs

Exercise 6. The three highest-paid employees in each department, with their rank, and the gap to the top earner in the same department.

SQL Solution 6: top 3 per department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Exercise 7. Everyone below manager id 1 in the org chart, with depth. Then count the size of each manager's whole team (direct and indirect reports).

SQL Solution 7: org tree and team size ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The trick: start the recursion from every employee as its own root, then count how many nodes sit under each root. The depth < 10 guard protects against cyclic data.

Exercise 8. Monthly order counts for the shop with a running total and the share of the year's orders each month represents.

SQL Solution 8: running total and share ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Level 4: MariaDB design and operations (read-only)

These exercises need a real server. Write the answer on paper, then compare with the solution text below each one.

Exercise 9: stored function. Write net_price(p_gross DECIMAL(10,2), p_vat DECIMAL(4,3)) that returns the price without VAT, rounded to 2 decimals.

SQL
DELIMITER //
CREATE FUNCTION net_price(p_gross DECIMAL(10,2), p_vat DECIMAL(4,3))
RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
  RETURN ROUND(p_gross / (1 + p_vat), 2);
END //
DELIMITER ;
SELECT net_price(121.00, 0.21);   -- 100.00

Exercise 10: procedure with a transaction. Transfer money between two accounts so that either both updates happen or none, and refuse a transfer that would overdraw the source.

SQL
DELIMITER //
CREATE PROCEDURE transfer(IN p_from INT, IN p_to INT, IN p_amount DECIMAL(12,2))
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
  START TRANSACTION;
  UPDATE accounts SET balance = balance - p_amount WHERE id = p_from AND balance >= p_amount;
  IF ROW_COUNT() = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
  END IF;
  UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
  COMMIT;
END //
DELIMITER ;

The portable version of the same logic (runnable): the guarded update changes nothing when funds are insufficient.

SQL Solution 10: guarded transfer

Press Run (or Ctrl + Enter) to see the result here.

Exercise 11: index design. The query SELECT id, total FROM orders WHERE customer_id = ? AND status = 'paid' ORDER BY ordered_on DESC LIMIT 20 is slow. Propose one index.

Answer: CREATE INDEX idx_orders_cust_status_date ON orders (customer_id, status, ordered_on); Both filters are equalities, so they come first; ordered_on last lets the engine read the rows in order and stop after 20 (no filesort). Adding total at the end (..., ordered_on, total) would make it a covering index. Verify with EXPLAIN: key shows the index, Extra has no Using filesort. See EXPLAIN and index design.

Exercise 12: grants. Create an account for a reporting tool that may read everything in shop but never see customers.email.

SQL
CREATE USER 'reporter'@'10.0.0.%' IDENTIFIED BY '...' REQUIRE SSL;
GRANT SELECT ON shop.orders      TO 'reporter'@'10.0.0.%';
GRANT SELECT ON shop.order_items TO 'reporter'@'10.0.0.%';
GRANT SELECT ON shop.products    TO 'reporter'@'10.0.0.%';
GRANT SELECT (id, first_name, last_name, city, country) ON shop.customers TO 'reporter'@'10.0.0.%';

Column-level grants (or a view over customers) hide email. See users, grants and roles.

Exercise 13: backup plan. A 300 GB database must be restorable within 1 hour and lose at most 5 minutes of data. Outline the plan.

Answer: weekly full mariadb-backup plus daily incrementals (physical, so restore is fast), the binary log copied off-server every few minutes (or a replica or semi-sync standby) for the 5-minute RPO, backups encrypted and stored off-site, and a monthly timed restore test to prove the 1-hour RTO. See backup and restore.

Level 5: spot the bug

SQL Find the mistake: it runs, but the answer is wrong ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

If a department had no employees, COUNT(*) would report 1 (the single NULL-extended row). The fix is COUNT(e.id), which ignores NULL. Edit the query and run it again. (All departments in this data set have people, so the bug is invisible until one is empty: that is exactly why such bugs survive in production.)

Interview questions

Short answers you should be able to give without notes.

  1. What is the difference between WHERE and HAVING? WHERE filters rows before grouping, HAVING filters groups after aggregation.
  2. COUNT(*) versus COUNT(col)? COUNT(*) counts rows; COUNT(col) counts rows where col is not NULL.
  3. RANK, DENSE_RANK, ROW_NUMBER? Ties get the same rank then a gap (RANK), the same rank with no gap (DENSE_RANK), or unique sequential numbers (ROW_NUMBER).
  4. What is a covering index? One that contains every column a query needs, so rows are never read from the table (Using index).
  5. Why can a leading-wildcard LIKE '%x' not use a B-tree index? The sort order of the index is by the beginning of the string.
  6. View versus CTE versus temporary table? View: stored definition, no data. CTE: named step inside one statement. Temporary table: real rows, session-scoped, can be indexed.
  7. Function versus procedure? A function returns one value and is used in expressions; a procedure is called with CALL, can return result sets and change data.
  8. When would you avoid a trigger? When a constraint, default or application logic can do it clearly; triggers hide behaviour and run per row.
  9. What does EXPLAIN type = ALL mean? A full table scan.
  10. How does Galera differ from primary-replica replication? Galera is multi-primary with certification-based, virtually synchronous replication and quorum; primary-replica is asynchronous with one writable server.
  11. What is a GTID? A global transaction ID that identifies a transaction across servers, simplifying failover.
  12. How do you recover to a point in time? Restore the last backup, then replay the binary log up to just before the bad statement with mariadb-binlog --stop-datetime.
  13. How do you prevent SQL injection? Prepared statements with bound parameters, least-privilege accounts, allow-lists for identifiers.
  14. What does FOR UPDATE SKIP LOCKED do? Locks the selected rows and skips rows locked by others, ideal for work queues (MariaDB 10.6+).

Where to go next

  • Revisit the library project and add the reservation queue.
  • Take a table from your own work, run EXPLAIN on its three slowest queries, and write down what you would change.
  • Practice with other data sets in the SQL tutorial.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A LEFT JOIN with GROUP BY reports 1 for a department without employees when you use COUNT(*). How do you fix it?

    The NULL-extended row is counted by COUNT(*), while COUNT(e.id) ignores NULL and returns 0.
  2. 2. For WHERE customer_id = ? AND status = 'paid' ORDER BY ordered_on DESC LIMIT 20, which index order is best?

    Equality columns first, then the sort column, so rows come out in order without a filesort.
  3. 3. Which is the safest way to move money between two accounts?

    Atomicity guarantees both updates happen or neither does.
  4. 4. Why might you grant a reporting account SELECT on specific columns instead of whole tables?

    Column-level privileges (or views) expose only what the account needs.

Summary

  • Practice in four levels: aggregates and joins, subqueries and functions, windows and CTEs, then design and operations.
  • Typical traps: COUNT(*) after an outer join, WHERE versus HAVING, window functions in WHERE, wrong index column order.
  • Keep a list of your own slow queries and rewrite them: it is the best exercise there is.

Next: back to the MariaDB track