Capstone: Plan and Requirements

Every report begins with a sentence that sounds simple: 'Tell me how the teams are doing.' That sentence is not a query — it is an invitation to misinterpretation. One stakeholder means headcount and average salary; another means project delivery rate and budget burn. If you open your editor and start typing SELECT before you know which question you are answering, you will build something beautiful that answers the wrong thing.

The first skill of a capstone project is therefore not SQL syntax — it is requirements translation. You listen to the vague request, ask one clarifying question, and write down a specific sentence that ends with a number: 'How many people work in each department, and what is their average salary?' Once the sentence is specific, the tables, columns, and aggregates reveal themselves. Until then, you are guessing.

Guessing is expensive. A report that returns every row in the database overwhelms the reader; a report that omits a critical filter misleads them. The planning stage exists to prevent both. In this lesson you will learn to decompose a business question into its parts — the tables that hold the facts, the columns that name the dimensions, and the aggregates that produce the numbers — before a single line of SQL is written.

flowchart LR
  Q["Stakeholder question"] --> T["Identify tables"]
  T --> C["Choose columns"]
  C --> M["Pick metrics"]
  M --> S["Write SQL"]
  style T fill:#0e7490,color:#fff
  style M fill:#0e7490,color:#fff

From vague request to concrete SQL: identify tables, choose columns, pick metrics, then write.

Breaking a question into parts

Take the question: 'What is Engineering spending on active projects?' It sounds like one query, but it contains four distinct decisions.

Tables: spending lives in projects, but the department name lives in departments. You need both.

Filter: 'active' means status = 'active' in the projects table. 'Engineering' means name = 'Engineering' in departments. Both conditions must hold.

Metric: 'spending' translates to SUM(budget). If you asked for 'how many' instead, the metric would be COUNT(*).

Dimension: you want one row per department, so you group by d.name.

Every question in this capstone follows the same decomposition. The dimension is what you put in GROUP BY. The metric is what you aggregate in SELECT. The filter is what you place in WHERE. Name the parts aloud and the SQL almost writes itself.

One trap is hiding here: the word 'spending' sounds like it could mean salary, overhead, or budget. In our dataset it can only mean budget, because that is the only spending column we have. Scoping the metric to the data that exists is part of the planner's job.

Preview the teams: one summary row per department with headcount and average salary.

SELECT d.name, COUNT(e.id) AS headcount, AVG(e.salary) AS avg_salary
FROM departments d
JOIN employees e ON d.id = e.department_id
GROUP BY d.name;

Scoping to the data you have

A perfect question is useless if the database cannot answer it. Before you commit to a report section, inspect the tables and ask: 'Do we have this column?'

Our dataset holds salaries in employees, project budgets in projects, and department locations in departments. It does not hold fully-loaded costs, contractor rates, or revenue. That means we can report payroll and project budgets accurately, but we cannot report profit, burn rate, or cost-per-hire without making numbers up.

Identifying a gap early is a win, not a failure. It lets you reframe the question — 'We don't have cost, but we can show budget and headcount' — instead of surprising the stakeholder with missing data later. The best analysts scope the promise to the schema.

Run a quick query to see what is actually there: count rows per table, list column names in your head, and note any NULLs. These thirty seconds of inspection prevent hours of rewriting queries that ask for columns that do not exist.

erDiagram
  DEPARTMENTS ||--o{ EMPLOYEES : "employs"
  DEPARTMENTS ||--o{ PROJECTS : "owns"
  DEPARTMENTS {
    int id
    text name
    text city
  }
  EMPLOYEES {
    int id
    text name
    int department_id
    text role
    int salary
    text hired_on
  }
  PROJECTS {
    int id
    text name
    int department_id
    text status
    int budget
  }

The three tables you will work with across the capstone, and how they relate.

Answer a sample stakeholder question: total active budget per department.

SELECT d.name, SUM(p.budget) AS total_budget
FROM departments d
JOIN projects p ON d.id = p.department_id
WHERE p.status = 'active'
GROUP BY d.name;

From questions to milestones

A multi-section report is not one giant query. It is a sequence of small, verifiable questions answered one at a time. Each milestone produces a single SELECT that you can run, inspect, and debug independently.

Milestone one might ask 'How big is each team?' — a join of departments and employees with COUNT and AVG. Milestone two asks 'What is active work costing us?' — a filtered join of departments and projects with SUM. Milestone three asks 'Which departments are spread thin?' — a HAVING filter on project counts.

By breaking the report into milestones, you turn an intimidating wall of SQL into a checklist. If the final numbers look wrong, you know exactly which milestone to revisit. That modularity is how professional analysts work: small queries, verified early, composed into a narrative later.

Treat each milestone as a contract with yourself. Before you write the next one, run the current one and read every row. If a department is missing, ask whether a filter or a join excluded it. If a total looks high, check whether a many-to-many relationship duplicated rows. Verification at each step is what separates a working report from a broken one.

flowchart TD
  M1["Milestone 1<br/>Team size & payroll"] --> M2["Milestone 2<br/>Active spending"]
  M2 --> M3["Milestone 3<br/>Portfolio breadth"]
  M3 --> M4["Milestone 4<br/>Quality checks"]
  M4 --> R["Final report"]
  style M2 fill:#0e7490,color:#fff
  style R fill:#0e7490,color:#fff

A capstone report is built as a sequence of milestones, each one a single verifiable query.

Check data quality before you build: count missing departments and missing cities.

SELECT 'Missing department' AS issue, COUNT(*) AS n
FROM employees WHERE department_id IS NULL
UNION ALL
SELECT 'Missing city', COUNT(*)
FROM departments WHERE city IS NULL;

Plain English first

Before you write SQL, write the question in plain English and circle the nouns. Those are your tables. Underline the number you want — that is your aggregate. Draw a box around the restriction — that is your WHERE. Only when the sentence is unambiguous should you open your editor. The thirty seconds of translation save thirty minutes of debugging a query that answered a question nobody asked.

Keep the plan visible while you write. When you are tempted to add a slick subquery or a clever join, ask whether it serves the plan. If it does not, it is complexity you will have to explain and maintain. The best capstone queries are the simplest ones that answer the question exactly.

Exercise

Which table should you query to find the total budget of all active projects?

Exercise

Before building the report, inspect the Engineering team. Return the name and role of every employee in the Engineering department, sorted alphabetically by name.

SELECT e.name, e.role
FROM employees e
JOIN departments d ON e.department_id = d.id
-- add the filter and sort
;

SELECT e.name, e.role FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.name = 'Engineering' ORDER BY e.name;

Exercise

This query is meant to return each department's name and total salary as total_salary, but it joins the wrong table and sums project budgets instead. Fix it.

SELECT d.name, SUM(p.budget) AS total_salary
FROM departments d
JOIN projects p ON d.id = p.department_id
GROUP BY d.name
;

SELECT d.name, SUM(e.salary) AS total_salary FROM departments d JOIN employees e ON d.id = e.department_id GROUP BY d.name;

Exercise

Return each department's name and the number of active projects it owns as active_project_count. Only count projects whose status is 'active'.

SELECT d.name, COUNT(p.id) AS active_project_count
FROM departments d
JOIN projects p ON d.id = p.department_id
-- filter and group here
;

SELECT d.name, COUNT(p.id) AS active_project_count FROM departments d JOIN projects p ON d.id = p.department_id WHERE p.status = 'active' GROUP BY d.name;

Exercise

Return every department's name, city, and the total budget of its projects as total_budget. Include departments that have no projects — their total will appear as NULL, which is correct.

SELECT d.name, d.city, SUM(p.budget) AS total_budget
FROM departments d
-- join projects so departments without projects are still included
GROUP BY d.name, d.city
;

SELECT d.name, d.city, SUM(p.budget) AS total_budget FROM departments d LEFT JOIN projects p ON d.id = p.department_id GROUP BY d.name, d.city;

Recap

A stakeholder question is not a query until it names the tables, the metrics, and the filters.
Break every question into dimension (what you group by), metric (what you aggregate), and filter (what you keep or discard).
Scope the report to the columns that exist; flag gaps early rather than inventing data.
A capstone report is a sequence of milestones, each one a single SELECT that you verify independently.
Inspect the data for NULLs and missing relationships before you trust your results.

In the next lesson you will extend the schema itself — adding tables and columns so the report can answer questions the current design cannot reach.

Capstone: Plan and Requirements

Breaking a question into parts

Scoping to the data you have

From questions to milestones

Plain English first

Recap

Checkpoint quiz

Go deeper — technical resources