Skip to main content

SQL Guide

Opening the Dataset in SQL​

The SQL guide is the practical starting point for students who want to work directly in the SQLite file. It shows the first tool, the first file, the first starter query, and the next move after a result set appears.

What File to Open​

For SQL work, start with CharlesRiver.sqlite.

This file contains the dataset tables you need for joins, filters, summaries, and document tracing.

The recommended starting tool is DB Browser for SQLite.

It works well for first-time SQLite work because it gives you:

  • a simple install path
  • a direct file-open workflow
  • a visible table browser
  • a built-in SQL editor
  • a result grid that is easy to review and export

If you are new to SQLite, start here before moving to a more advanced tool.

How to Open the Dataset in DB Browser​

  1. Download and install DB Browser for SQLite.
  2. Open DB Browser and choose Open Database.
  3. Select CharlesRiver.sqlite from the files shared for your course.
  4. Open the Browse Data tab and inspect a few tables such as Customer, SalesInvoice, or GLEntry.
  5. Open the Execute SQL tab so you are ready to paste a starter query.

At this point, you are ready to copy SQL from the analytics pages in the docs and run it against the dataset.

How to Run Your First Query​

Start with this first-pass workflow:

  1. Open Financial Queries.
  2. In the starter SQL list, expand Working-capital bridge by month.
  3. Copy the SQL from the code block.
  4. Paste it into the Execute SQL tab in DB Browser.
  5. Run the query.
  6. Review the result grid and column names before you move on.

This gives you a complete first-use path: open the file, copy a real starter query, run it, and review a business result that already connects to a guided case.

How to Read the Result and Keep Going​

After your first query runs, check four things before you write another one:

  • the row grain: what one row represents
  • the main dimensions: month, customer, item, cost center, or document
  • the main measures: amount, quantity, hours, or count
  • the business question: what the result explains

Then move to the next layer:

  1. stay on the topic page and run a second query in the same area
  2. open the paired case in Cases
  3. use Schema Reference when you need table and key help
  4. use GLEntry Posting Reference when the question reaches the ledger

The starter SQL is organized into three compact topic groups:

Topic pageWhat it covers
Financial Queriesrevenue, margin, working capital, accruals, payroll, close-cycle, and pricing review
Managerial Queriesbudget, portfolio mix, labor, service levels, planning, replenishment, and pricing governance
Audit Queriesdocument-chain review, approvals, workforce controls, planning support, pricing governance, and anomaly review

The topic pages surface these queries directly in the docs, so most students can work from the website without opening any repository folders.

Other Tools You Can Use​

If you already use another SQL tool, the same workflow still works:

  • DBeaver
  • SQLiteStudio
  • VS Code with a SQLite extension

Open CharlesRiver.sqlite, copy a starter query from the docs, run it, and review the result. DB Browser remains the recommended first tool because it is the simplest path for most students.

Secondary Workflows​

If you prefer code-first work, you can also run copied starter SQL through Python or the sqlite3 command-line tool.

Python​

import sqlite3
import pandas as pd

sql = """
-- paste a starter query copied from the docs here
SELECT *
FROM GLEntry
LIMIT 25;
"""

with sqlite3.connect("downloaded_dataset.sqlite") as connection:
    df = pd.read_sql_query(sql, connection)

print(df.head())

sqlite3 CLI​

sqlite3 downloaded_dataset.sqlite

Then paste a starter query copied from the docs into the prompt and run it directly.

Next Steps​

Notice a mistake?Report an error on this page