Call +91 9649695189
SQL Tutorial

Subqueries, Transactions and Window Functions

Move beyond basic CRUD with reusable query patterns and analytical SQL.

Concept

Subqueries can provide scalar values, sets or derived tables. Common table expressions can improve readability when a query has several logical steps.

Transactions group changes under ACID guarantees provided by the database. Window functions compute values across related rows without collapsing the result the way GROUP BY does.

Example

SELECT name, city,
       ROW_NUMBER() OVER (PARTITION BY city ORDER BY name) AS city_row
FROM students;
Type the example yourself and change at least one value. Small experiments reveal syntax and behavior faster than passive reading.

Practice Tasks

  1. Write a subquery that finds rows above an average value.
  2. Use BEGIN/ROLLBACK while testing an update.
  3. Use ROW_NUMBER or RANK over a partition.

Key Takeaways

  • Subqueries and CTEs structure complex logic.
  • Transactions protect multi-step changes.
  • Window functions enable analytical calculations while retaining detail rows.
Track complete
Back to SQL Tutorial →