Rullst AcademyRullst Academy/Lesson
Databases and SQL I · Lesson 12 / 12

Mission: a verifiable catalog

Build an availability report and solve a new question with tests.

Learning paths24 min

Let's discover together

  1. 1

    availability.sql LEFT JOINs open loans, groups by identity and subtracts units from stock. Initial availability by id is 2, 0, 0 and 2. Check by hand before running.

  2. 2

    Run python -m unittest discover -v. Tests cover repeated names, kits without loans, returns, empty data and insufficient stock. Do not hide negatives: the schema still allows over-lending, and the report must reveal it.

  3. 3

    Create needs_restock.sql listing only materials with availability at or below zero. Test initial data and after returning loan 3 in the same run. Add your own test: supplied tests do not prove this new task.

  4. 4

    Fill REVIEW.md with prediction, execution, original query, test and limit. Explain to someone; alone, try again later without copying. Save .sql files and report. Rullst Academy sends one confirmed file to GitHub; it does not send the database or run SQL there.

Your data workbench

Our model: kits.id ← loans.kit_id. One material can have many loans; names may repeat. We use fictional data only.

Predict before opening the reference. It shows verified output from the original example, not execution of your text. Run your changes with the kit on your computer.

-- Mission: a verifiable catalog
SELECT k.id, k.label,
       k.stock - COALESCE(SUM(l.units), 0) AS available
FROM kits AS k
LEFT JOIN loans AS l ON l.kit_id = k.id AND l.returned IS NULL
GROUP BY k.id, k.label, k.stock
ORDER BY k.id;
Compare with reference output
[
  {
    "columns": [
      "id",
      "label",
      "available"
    ],
    "rows": [
      [
        1,
        "Orbit",
        2
      ],
      [
        2,
        "Sprout",
        0
      ],
      [
        3,
        "Orbit",
        0
      ],
      [
        4,
        "Nova",
        2
      ]
    ]
  }
]

Run the command in the extracted folder’s terminal. Use python3 on macOS/Ubuntu or py if that is your Windows launcher. Every run starts in memory; read README.md for saving files and recovery practice.

python lab.py run lesson.sql
My files and GitHub

The editor saves your .sql draft in this browser. Download it and replace lesson.sql in the kit; for the final project, availability.sql. The ZIP remains the original template. GitHub publishing sends only the confirmed file, without executing SQL or sending a database.

Official links and next step

✦
YOUR MISSION

Build an availability report and solve a new question with tests.

Now it's your turn

Discovery workshop

Read, predict and check

Build an availability report and solve a new question with tests.
Build your answers and check when you are ready.
A moment to understand

What evidence goes beyond repeating the example?

I need a hint

Run python -m unittest discover -v. Tests cover repeated names, kits without loans, returns, empty data and insufficient stock. Do not hide negatives: the schema still allows over-lending, and the report must reveal it.

Loading your journey…

How can I tell what I learned?

Use the objectives and challenge below for reflection or a conversation with someone who can help. This is guided self-assessment: it sends no answers, awards no XP and does not automatically verify independence.

In Building, integrate a small original solution, test an unseen variation, record limits and seek review when possible. Reproducing the example is a start; explaining and adapting develops independence.

Your transfer challenge

Adapt to fictional hobby or workshop materials. Define a new question, query, error and recovery. Record help received and independent work.

Keep evidence: prediction, attempt, result, an original change and what you still cannot explain. Seek feedback when possible; if studying alone, return later without copying and compare attempts.

Try again another time

After a break, explain the idea without opening the solution and try a different example. Compare with your first attempt: what can you do independently, and where do you need a hint? You may write, speak or draw; choose a way to check the same idea. Record help received, including AI, and revisit the part you need. There is no universal deadline or extra XP for this record.

Completions and XP record Rullst Academy assessments. An introductory track can help everyday life and prepare further study; alone it does not prove complete mastery or professional readiness. Level indicates depth, not age.

Take it with you

Adapt to fictional hobby or workshop materials. Define a new question, query, error and recovery. Record help received and independent work.

At your own pace

My files and GitHub

Every lesson can become your own record: code, notes or a practice plan. Saving a file to GitHub is optional and does not change your XP.

Student guide

The editor and downloads open when this lesson is available to your account. Check access below or return to the track map.

Checking your access to this lesson…

Rullstpetbot

AI can make mistakes. Check the lesson material.