Read the schema and write your first analytical query

Understand the method

SQL lets you express a question about related tables. Before writing a query, identify what each row represents and which field connects it to another table. A query can run without errors and still answer the wrong question if you mistake an order line for an order, or cents for euros. Your first task is to understand the schema and the case definition.

Open your own practice copy

Download northstar_sql_practice.sqlite and open a local copy in an SQLite-compatible SQL editor. The site does not execute arbitrary SQL, ask for your database credentials or connect this practice to the live website. Use the supplied fictional data. If you prefer the SQLite command-line shell, open the downloaded file with sqlite3 northstar_sql_practice.sqlite and enter one SELECT at a time.

The alternative setup_sqlite.sql creates the same tables in a new empty local practice database. In the SQLite shell you can open a new file and use .read setup_sqlite.sql. The dot-command is a shell instruction, not SQL for every editor. Do not paste the setup into your hosting or website database. There is no DROP command; an existing conflicting table causes an error rather than being silently replaced.

Four tables, four purposes

• orders: 12 verified records, one row per order, keyed by order_id.
• order_items: 24 fictional item records, two per order. A line has a quantity and a unit price in cents. These item allocations are teaching additions consistent with each order total.
• channels: three unique channels: web, store and partner. Partner has no orders and is included to teach a zero-activity dimension.
• raw_orders: the original 13-row imperfect extract, without a unique-key constraint.

The verified orders are the same Northstar case as Modules 1 and 2. This module stores monetary inputs as integer cents. A value of 10000 means €100, not €10,000. SQL expressions divide by 100.0 only when presenting euro amounts. The database contains no personal, client or payment data.

Start with a small projection

SELECT order_id, status, order_value_cents
FROM orders
ORDER BY order_id;

SELECT lists the output expressions. FROM names the source. AS gives a calculated column a meaningful label. ORDER BY makes the intended output order explicit. A table is not guaranteed to return insertion order, so do not rely on what happened in an earlier run.

Use explicit columns for a report instead of SELECT *. A star can be useful while exploring a tiny table, but a later schema change can alter the report unexpectedly. Do not rename a cents field as euros without converting it. SQL keywords are shown in capitals for readability; the convention does not make a calculation correct.

Run SELECT sqlite_version(); to inspect your engine. The downloadable answers are tested with SQLite; MySQL, PostgreSQL and SQL Server have differences in date functions, quoting, grouping and division. This module teaches analytical habits that transfer, while keeping executable examples tied to a declared engine.