Skip to content
Laviyx Learn

Try

SQL Recursive CTEs, Views and Transactions Practice

Generate series and walk hierarchies with recursive CTEs, save queries as views, and make changes safely with transactions and indexes.

Advanced7 problemsUpdated

Review first: Recursive CTEs, Views, Indexes, Transactions

Each problem starts from these tables.

categories (parent_id is the category_id of the parent category)

category_idnameparent_id
1All ProductsNULL
2Electronics1
3Computers2
4Laptops3
5Phones2
6Furniture1
7Chairs6

accounts

account_idownerbalance
1Asha5000
2Ravi3000
3Meera0
Try your answers on these tables in the SQL Playground

  1. 1.Generate the numbers 1 to 5

    Advanced

    Without using any table, return the numbers 1 to 5 in a column called n, using a recursive CTE.

    Show hint

    The anchor part selects 1. The recursive part selects n + 1 from the CTE itself, while n < 5.

    Show solution
    SQLRun
    WITH RECURSIVE numbers(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n FROM numbers;
    Output
    n
-
1
2
3
4
5

    The WHERE n < 5 condition is what stops the recursion. Without it the query would run forever (or until the database's recursion limit). SQL Server and Oracle write just WITH, without RECURSIVE.

  2. 2.Category tree with levels

    Advanced

    Starting from All Products (the category with no parent), return every category's name and its depth in the tree (0 for the top). Sort by depth, then name.

    Show hint

    The anchor selects the root with depth 0. The recursive part joins categories to the CTE on parent_id and adds 1 to the depth.

    Show solution
    SQLRun
    WITH RECURSIVE tree AS (
  SELECT category_id, name, 0 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.category_id, c.name, t.depth + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.category_id
)
SELECT name, depth
FROM tree
ORDER BY depth, name;
    Output
    name         | depth
-------------+------
All Products | 0
Electronics  | 1
Furniture    | 1
Chairs       | 2
Computers    | 2
Phones       | 2
Laptops      | 3

  3. 3.Everything under Electronics

    Advanced

    Return the names of Electronics and all of its subcategories, at any depth, sorted by name.

    Show hint

    Same pattern as the tree, but the anchor starts at Electronics instead of the root.

    Show solution
    SQLRun
    WITH RECURSIVE sub AS (
  SELECT category_id, name
  FROM categories
  WHERE name = 'Electronics'

  UNION ALL

  SELECT c.category_id, c.name
  FROM categories c
  JOIN sub s ON c.parent_id = s.category_id
)
SELECT name
FROM sub
ORDER BY name;
    Output
    name
-----------
Computers
Electronics
Laptops
Phones

    A plain self join only finds direct children (Computers and Phones). The recursive CTE keeps going, so it also finds Laptops, which is a grandchild of Electronics.

  4. 4.Save a query as a view

    Advanced

    Create a view called account_status that shows each owner, their balance, and a status of empty when the balance is 0 or active otherwise. Then select the active accounts from the view, sorted by owner.

    Show hint

    CREATE VIEW name AS SELECT .... After that, query the view like a table.

    Show solution
    SQLRun
    CREATE VIEW account_status AS
SELECT owner, balance,
       CASE WHEN balance = 0 THEN 'empty' ELSE 'active' END AS status
FROM accounts;

SELECT owner, balance
FROM account_status
WHERE status = 'active'
ORDER BY owner;
    Output
    owner | balance
------+--------
Asha  | 5000
Ravi  | 3000

    A view stores the query, not a copy of the data. If a balance changes in accounts, the view shows the new value the next time it is queried.

  5. 5.A safe transfer

    Advanced

    Move 1000 from Asha (account 1) to Meera (account 3) inside a transaction, so that either both changes happen or neither does. Then show every owner and balance.

    Show hint

    BEGIN, two UPDATE statements, then COMMIT.

    Show solution
    SQLRun
    BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 3;

COMMIT;

SELECT owner, balance
FROM accounts
ORDER BY account_id;
    Output
    owner | balance
------+--------
Asha  | 4000
Ravi  | 3000
Meera | 1000

    If the database crashed between the two updates without a transaction, 1000 would disappear from Asha's account without reaching Meera's. Inside a transaction, an unfinished transfer is rolled back automatically. SQL Server writes BEGIN TRANSACTION; MySQL also accepts START TRANSACTION.

  6. 6.Undo a mistake

    Advanced

    Inside a transaction, take 9000 from Ravi's account (account 2). You notice the balance would go negative, so undo the change. Show Ravi's balance afterwards.

    Show hint

    ROLLBACK cancels every change made since BEGIN.

    Show solution
    SQLRun
    BEGIN;

UPDATE accounts SET balance = balance - 9000 WHERE account_id = 2;

ROLLBACK;

SELECT owner, balance
FROM accounts
WHERE account_id = 2;
    Output
    owner | balance
------+--------
Ravi  | 3000

    In a real table you would also add CHECK (balance >= 0), so the database itself refuses any update that makes a balance negative.

  7. 7.An index does not change results

    Advanced

    Create an index called idx_accounts_owner on the owner column, then look up Ravi's account by owner.

    Show hint

    CREATE INDEX index_name ON table (column).

    Show solution
    SQLRun
    CREATE INDEX idx_accounts_owner ON accounts (owner);

SELECT account_id, owner, balance
FROM accounts
WHERE owner = 'Ravi';
    Output
    account_id | owner | balance
-----------+-------+--------
2          | Ravi  | 3000

    An index never changes what a query returns, only how fast the database can find the rows. On a table with millions of rows, the database can jump straight to owner = 'Ravi' instead of scanning every row. The cost is extra storage and slightly slower inserts and updates, because the index must be kept up to date.

← All practice setsLearn SQL course →