Your First Real Query on Alpenkorb Orders

The shape of every query

You have run small queries in the first four labs. This lesson puts the pieces in order, because almost every analysis query has the same skeleton.

SELECT   columns or calculations
FROM     table
WHERE    conditions on rows
ORDER BY sort columns
LIMIT    number of rows;

SQL runs the clauses in a different order from the one you write them in. First FROM picks the table, then WHERE removes rows, then SELECT computes the columns, then ORDER BY sorts and LIMIT cuts. That is why WHERE cannot filter on a name you create in SELECT, while ORDER BY can sort by it.

Filters that match the business

Lena wants to know whether discount codes buy bigger baskets. In the orders table, discount_code holds codes such as WELCOME10 for a first order or AFFILIATE15 from partner sites, and is empty when no code was used. In SQL an empty value is NULL, which means unknown, and it needs its own test.

SELECT order_id, order_date, source, discount_code, net_amount
FROM orders
WHERE status = 'delivered'
  AND discount_code IS NOT NULL
  AND order_date >= '2026-01-01'
ORDER BY net_amount DESC
LIMIT 3;

discount_code = NULL would return no rows at all. Comparing anything with unknown gives unknown, and WHERE keeps only rows where the condition is true. Use IS NULL and IS NOT NULL instead.

• AND requires every condition, OR at least one. When you mix them, add brackets: WHERE status = 'delivered' AND (region = 'Bern' OR region = 'Basel').
• IN is a short form for several ORs: region IN ('Bern', 'Basel').
• Text comparisons need single quotes and the exact spelling. The regions are written 'Zurich', 'Geneva' and 'St. Gallen'.

Three small errors cause most failed first queries. Text values use single quotes: in DuckDB "delivered" in double quotes means a column called delivered, and the query fails. Dates are written as text in ISO form, '2026-01-01', which DuckDB compares correctly with a date column. And a missing or extra comma between columns produces a syntax error near the next word.

The status trap

The orders table contains every order ever placed, including 67 cancelled and 80 returned ones. Their net_amount is still filled in, because the amount was known when the order was placed. If you forget status = 'delivered', revenue is too high. In the first quarter of 2026 that mistake turns CHF 37,107.20 into CHF 38,787.67. The error is not spread evenly either: cancellations and returns are more frequent in some sources than in others.

What did the discount question show? In the first quarter of 2026, 90 of the 648 delivered orders used a code. Their average value was CHF 57.16, against CHF 57.28 for orders without a code. On this evidence codes do not buy bigger baskets. They may still win new customers, which is a different question and needs a different query. Before trusting any result, compare its row count with a number you already know: the lab in lesson 2 returned 648 delivered orders for this quarter, so a filter on the same quarter can never return more.