lesson 04 Filtering & sorting · beginner

Comparisons & ranges

Filter numbers and dates with comparisons and ranges.

the idea

Comparison operators (<, <=, >, >=, <>) and BETWEEN filter numeric ranges; BETWEEN includes both ends. Timestamps stored as ISO text sort in time order, so a half-open range (>= start AND < next start) selects a whole period without missing the last day’s later hours.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   See how timestamps sort as text. Run this. ordered_at is stored as text in YYYY-MM-DD HH:MM:SS form, so comparing as text follows clock order.

   step-1.sql Run

   sql›

   SELECT order_id, ordered_at
   FROM orders
   WHERE ordered_at >= '2010-12-31'
   LIMIT 10;

2. 2

   Keep products in a price range. Run this. Only products with list_price at least 100 remain.the pattern

   example.sql Run

   sql›

   SELECT stock_code, list_price
   FROM products
   WHERE list_price >= 100;

common mistake

ordered_at <= '2010-12-31' excludes invoices later on 31 December, because '2010-12-31 14:05:00' is greater than '2010-12-31'.