Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 37 ยท Projects and Practice

Practice: SQLAlchemy Exercises with Solutions

Seven hands-on SQLAlchemy exercises with self-checking tests: joins, aggregates, window functions, anti-joins, bulk updates, self-referential walks and query counting.

You learn an ORM by using it. This page gives you seven exercises against a small company database (departments, employees with managers, and projects). Each exercise is a function with an empty body. Press Run, and a built-in checker tells you which ones pass. Solutions follow, but try each one first: the struggle is where the learning happens.

The setup

Departments have employees; each employee may have a manager (another employee); employees work on projects (many-to-many). The checker at the bottom runs every exercise and prints PASS or FAIL. Write your code in the marked functions only.

Python Exercises: write the function bodies, then Run

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

When you are stuck, read the hint for that exercise before you open the solution.

  • 1. select(...).join(Employee.department).where(...).
  • 2. func.avg() with group_by(Department.id).
  • 3. func.row_number().over(partition_by=..., order_by=Employee.salary.desc()) in a subquery, then keep rank 1. Or compare each salary with a max() per department.
  • 4. Employee.projects.any() is true when at least one project exists; negate it with ~.
  • 5. update(Employee).where(Employee.department_id.in_(select(...))) with an expression on the right-hand side of values(). Integer math: salary * (100 + percent) / 100.
  • 6. Follow employee.manager in a loop until it is None.
  • 7. joinedload(Employee.department) makes one query for all of it (or selectinload, two).

Solutions

Compare your version with these. Several correct answers exist; if yours passes the checker and is readable, it is right.

Python
def employees_in(session, dept_name):
    stmt = (select(Employee.name).join(Employee.department)
            .where(Department.name == dept_name).order_by(Employee.name))
    return list(session.scalars(stmt))

def avg_salary_by_dept(session):
    stmt = (select(Department.name, func.avg(Employee.salary))
            .join(Employee.department).group_by(Department.id))
    return dict(session.execute(stmt).all())

def top_earner_per_dept(session):
    rank = func.row_number().over(partition_by=Employee.department_id,
                                  order_by=Employee.salary.desc()).label("rn")
    ranked = select(Employee.name.label("emp"), Employee.department_id.label("dept"), rank).subquery()
    stmt = (select(Department.name, ranked.c.emp)
            .join(ranked, ranked.c.dept == Department.id).where(ranked.c.rn == 1))
    return dict(session.execute(stmt).all())

def without_projects(session):
    stmt = select(Employee.name).where(~Employee.projects.any()).order_by(Employee.name)
    return list(session.scalars(stmt))

def give_raise(session, dept_name, percent):
    dept_ids = select(Department.id).where(Department.name == dept_name)
    result = session.execute(
        update(Employee).where(Employee.department_id.in_(dept_ids))
        .values(salary=Employee.salary * (100 + percent) / 100))
    return result.rowcount

def chain_of_command(session, name):
    person = session.scalars(select(Employee).where(Employee.name == name)).one()
    chain = []
    while person is not None:
        chain.append(person.name)
        person = person.manager
    return chain

def staff_listing(session):
    stmt = select(Employee).options(joinedload(Employee.department)).order_by(Employee.name)
    return [(e.name, e.department.name) for e in session.scalars(stmt)]

Things worth studying in the solutions:

  • Exercise 3 uses the window function pattern: rank inside a subquery, then filter on the rank in the outer query (you cannot filter on a window function in the same WHERE).
  • Exercise 4 compiles to NOT EXISTS (...), an anti-join. It is safer than NOT IN because it behaves correctly with NULL values.
  • Exercise 5 is a single UPDATE ... WHERE department_id IN (SELECT ...). The identity map is not refreshed by it; the checker reads fresh rows in a new session. If you need the objects updated, add execution_options(synchronize_session="fetch") or expire them.
  • Exercise 6 does one lazy load per level, which is fine for a short chain. For a long hierarchy, see the recursive CTE in Cascades and Self-Referential Relationships.
  • Exercise 7 is the N+1 fix in its simplest form. Compare with Loading Strategies.

More ideas to practice

  1. Write an employees_on_project(project_name) using the many-to-many relationship, then make it return the department names involved without duplicates (distinct()).
  2. Find employees who earn more than their manager (a self-join with aliased(Employee)).
  3. Report the number of employees per project, including projects with none (outer join).
  4. Add a unique constraint on (department_id, name) and prove it with pytest.raises or a try/except IntegrityError.
  5. Create an Alembic revision that adds a hired_on column, then backfill it (Production Migrations).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why use ~Employee.projects.any() rather than a NOT IN subquery to find employees with no projects?

    ~any() compiles to NOT EXISTS, which avoids the NULL trap of NOT IN.
  2. 2. You cannot write WHERE row_number() OVER (...) = 1 directly. What is the usual approach?

    Window functions are evaluated after WHERE, so the filter must happen in an outer query.
  3. 3. After a bulk update() statement, why might objects already in the session show old values?

    Set-based statements bypass per-object tracking; use synchronize_session or expire objects.
  4. 4. Which loader option turns the staff listing into a single query?

    joinedload on a many-to-one adds a LEFT OUTER JOIN to the main query.

Summary

  • Practice with self-checking functions shows exactly which skills are solid.
  • Joins, group_by aggregates, window functions in subqueries, any() anti-joins and bulk update() cover most everyday queries.
  • Walk simple hierarchies with the manager relationship; use a CTE for deep ones.
  • Always count queries for list endpoints.
  • Keep building: migrations, repositories and the projects in this chapter are good places to keep experimenting.

Next: back to the SQLAlchemy tutorial home