FREE SQL FOR REPORTING LEARNING PATH

WikiFree original practice workbook

The query bench

CASE STATUS: fictional/synthetic teaching material. Do not present the example as real research, employment experience or measured business performance.

OUTCOME
Create a SQLite reporting database, join customers to orders and produce a reconciled paid-sales report including customers with no paid orders.

BEFORE YOU START
Basic spreadsheet tables and arithmetic. A local SQLite-compatible editor; no server subscription.

CASE
The fictional Harbor Workshop sells classes. Customer IDs C1 and C3 belong to North, C2 to South and C4 to West. The order amounts below are original synthetic data.

INPUTS
Order,Customer,Status,Amount
O1,C1,paid,100
O2,C1,paid,50
O3,C2,paid,80
O4,C2,refunded,40
O5,C3,paid,70
O6,C3,pending,60


ENTRY CHECK

Question: What identifies a row?

Self-check: A declared key, such as order_id.

Question: Will a join always preserve the number of rows?

Self-check: No. Non-unique matches can multiply rows.

Question: Is a pending order paid revenue here?

Self-check: No. The reporting rule includes only status paid.

STAGE 1: Define tables and keys

Prerequisite: Basic spreadsheet tables and arithmetic. A local SQLite-compatible editor; no server subscription.

Instruction: Separate customers from orders because one customer may place many orders. Define the row grain before the columns: one row per customer, one row per order. A primary key rejects repeated IDs, while a foreign key describes a valid relationship. The companion SQL uses SQLite; the provider explains general SQL and may demonstrate different software or dialect syntax.

Practice task: Create customers and orders tables using the supplied setup file. Inspect row counts and list the two grains.

Deliverable: A runnable database with four customers and six orders.

Acceptance check: Customer and order IDs are unique; counts are 4 and 6.

If stuck: Do not combine repeated customer attributes into an unexplained duplicate customer table.

Record your work, decisions and evidence:




Record your result against the acceptance check:



STAGE 2: Filter the reporting population

Prerequisite: Bring your previous output: A runnable database with four customers and six orders.

Instruction: A WHERE condition defines which rows enter the report. Use the business rule explicitly rather than assuming every order is complete. Start by inspecting the filtered rows before aggregating. For this case, paid orders are O1, O2, O3 and O5. Sorting makes manual comparison easier, but it does not change which rows qualify.

Practice task: Select order ID, customer ID and amount for paid orders, ordered by order ID. Compute their independent total.

Deliverable: A paid-order detail query.

Acceptance check: Four rows remain and their amounts sum to 300.

If stuck: If the total is 400, pending and refunded orders have leaked into the result.

Record your work, decisions and evidence:




Record your result against the acceptance check:



STAGE 3: Join without multiplying value

Prerequisite: Bring your previous output: A paid-order detail query.

Instruction: Join each order to exactly one customer using customer_id. Check the detail row count before and after the join; a larger count can signal duplicate dimension keys or an incorrect condition. An inner join is appropriate for inspecting valid paid-order detail, but it omits customers with no matching order and therefore cannot answer every customer-coverage question.

Practice task: Join paid orders to customer regions. Confirm each order appears once and reconcile total value to the earlier query.

Deliverable: A checked paid-order join.

Acceptance check: Four joined rows total 300, with North 220 and South 80.

If stuck: Repair the join keys before adding DISTINCT, which can hide a modeling error.

Record your work, decisions and evidence:




Record your result against the acceptance check:



STAGE 4: Include customers with zero sales

Prerequisite: Bring your previous output: A checked paid-order join.

Instruction: A LEFT JOIN preserves every customer. Put the paid-status condition in the join condition when retaining customers with no paid orders; a WHERE condition on the order side can remove their null-extended rows. Use COALESCE on the sum to display zero, and COUNT(order_id) rather than COUNT(*) to count actual matches.

Practice task: Write a customer report with paid count and paid amount, including C4. Then aggregate to region.

Deliverable: A zero-inclusive customer and region report.

Acceptance check: C1=150/2 orders; C2=80/1; C3=70/1; C4=0/0; total remains 300.

If stuck: If C4 disappears, examine the placement of the paid-status condition.

Record your work, decisions and evidence:




Record your result against the acceptance check:



STAGE 5: Make the report repeatable

Prerequisite: Bring your previous output: A zero-inclusive customer and region report.

Instruction: A report handover needs the query, metric definition, source setup and expected checks. A saved result without its logic is difficult to refresh reliably. Explain how a new row affects totals and how missing or duplicate keys should be handled. Use a transaction or a fresh copy when testing changes so the baseline remains reproducible.

Practice task: Add test order O7 for C4, paid amount 20, in a copy. Rerun the report and document the changed total and zero-sales count.

Deliverable: A report package with a refresh check.

Acceptance check: The test total becomes 320 and no customer remains at zero paid sales.

If stuck: If a refresh changes unrelated rows, rebuild from the clean setup and inspect the update steps.

Record your work, decisions and evidence:




Record your result against the acceptance check:



WORKED CASE
The baseline paid total is 300: North 220, South 80 and West 0. A correct customer LEFT JOIN retains C4 with count 0 and value 0. Adding paid order O7 for 20 changes only West and the grand total, now 320.

FINAL PROJECT
Submit schema, seed data, detail and summary queries, expected results and a README. Demonstrate duplicate-ID rejection and the O7 refresh scenario. A reviewer must be able to recreate the database from scratch.

REQUIRED CRITICAL CHECK
The paid total reconciles and the zero-order customer is retained without counting a phantom order.

SCORING
Score each of the five stage acceptance checks from 0–16 (0 missing/incorrect; 8 partially supported; 16 complete and evidenced). Score the final integrated project from 0–20 (0 missing; 10 incomplete handover; 20 complete, reproducible and consistent with the case). Target: at least 80/100 AND the critical check must pass. A total score never overrides a failed critical check.

SCOPE LIMIT
This is a small relational reporting project, not database administration, query tuning or complete SQL Server Reporting Services training.

SOURCES AND ACCESS
Public videos and SQLite are free. Install an official SQLite distribution or use a SQLite editor you already trust. SQL Server tools shown in the provider videos are optional; the supplied project uses SQLite.

SQL Querying and Reporting: Free Course
WikiFree: https://wikifree.com/courses/free-learn-skills-daily-sql-querying-reporting/
Official source: https://www.youtube.com/playlist?list=PLoyECfvEFOjYH2C2vl2A8yBPrbkVSbj9l
Why selected: The first two public SQL lessons establish query fundamentals; the SQLite schema, join exercises and reconciliation checks are original additions.

Stage 1 selected lessons:

1. SQL Querying Fundamentals Tutorial (1:28:54)
https://www.youtube.com/watch?v=yh92BHmvls8&list=PLoyECfvEFOjYH2C2vl2A8yBPrbkVSbj9l&index=7

Stage 2 selected lessons:

2. SQL Querying for Beginners Tutorial (2:44:47)
https://www.youtube.com/watch?v=AFY3z4FwRg0&list=PLoyECfvEFOjYH2C2vl2A8yBPrbkVSbj9l&index=1

SQLite SELECT documentation
https://www.sqlite.org/lang_select.html

REUSE
Original WikiFree teaching text and synthetic fixtures are supplied for personal learning and adaptation with attribution. Provider lessons remain on their original sites and retain their own terms. No provider certificate is included.