$ settings --editor

$ settings --general

$ save --settings

SQL for beginners

Pattern matching with LIKE #

= only matches an exact value. When you need to match part of a text value, use LIKE together with a wildcard.

Syntax #

SELECT
    column_name
FROM
    table_name
WHERE
    column_name LIKE pattern;

Two wildcard characters can be used inside pattern:

  • % matches any sequence of zero or more characters.
  • _ matches exactly one character.

Example #

To find every customer whose first name starts with J, we put the % wildcard after it:

SELECT
    *
FROM
    customers
WHERE
    first_name LIKE 'J%';

Result:

+----+------------+-----------+-----+--------+
| id | first_name | last_name | age | gender |
+----+------------+-----------+-----+--------+
| 2  |  Jonathan  |   Dixon   | 71  |   F    |
| 4  |    Juan    |  Campos   | 99  |   F    |
| 10 |  Jennifer  |    Ross   | 100 |   M    |
+----+------------+-----------+-----+--------+

'J%' matches any value that starts with J, no matter how many characters follow. If we instead wanted names that end with a letter, we'd put the % before it, e.g. '%n' matches Jonathan. LIKE is case-insensitive by default in SQLite, so 'j%' would match the same rows.

Exercise #

Write a query that retrieves all columns from the customers table, keeping only the rows where first_name starts with 'J'.

  • The result has a first_name column.
  • The result contains exactly the rows where first_name starts with 'J', and no others.
Solution 
SELECT
    *
FROM
    customers
WHERE
    first_name LIKE 'J%';
Output will be displayed here