lesson 06 Filtering & sorting · beginner

Lists & text patterns

Match lists of values and patterns in text.

the idea

IN tests membership in a list and replaces a chain of ORs. LIKE matches text patterns: % stands for any run of characters and _ for exactly one. In SQLite, LIKE ignores letter case for plain ASCII letters.

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

   Match descriptions that start a certain way. Run this. LIKE with VINTAGE% keeps rows whose description begins with VINTAGE.the pattern

   example.sql Run

   sql›

   SELECT stock_code, description
   FROM products
   WHERE description LIKE 'VINTAGE%';

2. 2

   Test many values with IN. Run this. IN is a compact way to filter to customers in Sweden, Norway, or Denmark.

   step-2.sql Run

   sql›

   SELECT customer_id, country
   FROM customers
   WHERE country IN ('Sweden', 'Norway', 'Denmark');

common mistake

LIKE 'LUNCH BAG' without % matches only that exact text. Use '%LUNCH BAG%' to find it anywhere in a description.