path: root/SQL/ШПАРГАЛКА SQLite.md

- 09.02.2024
- [DATA ANALYST](https://uproger.com/category/data-analyst/), [SQL](https://uproger.com/category/sql/)

![Огромная шпаргалка с готовыми запросами SQL (SQLite)](https://uproger.com/wp-content/uploads/2024/02/image-2024-02-09-193350.jpg "Огромная шпаргалка с готовыми запросами SQL (SQLite)")

Все примеры изначально писались для СУБД SQLite, но почти всё из этого применимо также и к другим СУБД.

Вначале идут очень простые запросы, с них можно начать новичкам. Если хочется чего-то более интересного — листайте вниз. Здесь есть и примеры довольно сложных запросов с агрегирующими функциями, триггерами, длинными подзапросами, с оконными функциями. Помимо этого, часть примеров посвящена работе с SQL в Python, используя `sqlite3`, `pandas`, `polars`. Этот список запросов с комментариями можно использовать как наглядное пособие для изучения SQL.

Большинство советов я публиковал в своем [канале по анализу данных](https://t.me/+dXZpK8lQ0lY4YjAy), где вы найдете большое количество советов, инструментов и примеров с кодом. А [здесь большая полезная папка,](https://t.me/addlist/qht-ouKSGaQwNjcy) которую я собрал в которой куча полезного для работы с данными.

Кстати, все эти примеры SQL заботливо собраны в одном архиве, вы можете [скачать](https://github.com/gvwilson/sql-tutorial/raw/main/sql-tutorial.zip) его и экспериментировать локально. После скачивания и разархивирования, у вас будет 3 группы файлов:

- `./db/*.db` — базы данных SQLite, которые используются в примерах ниже
- `./src/*.*` — SQL-запросы, сценарии Python
- `./out/*.*` — ожидаемый результат для примеров

### Выбираем все значения из таблички

```
SELECT *
FROM little_penguins;
```

```
Adelie|Dream|37.2|18.1|178|3900|MALE
Adelie|Dream|37.6|19.3|181|3300|FEMALE
Gentoo|Biscoe|50|15.3|220|5550|MALE
Adelie|Torgersen|37.3|20.5|199|3775|MALE
Adelie|Biscoe|39.6|17.7|186|3500|FEMALE
Gentoo|Biscoe|47.7|15|216|4750|FEMALE
Adelie|Dream|36.5|18|182|3150|FEMALE
Gentoo|Biscoe|42|13.5|210|4150|FEMALE
Adelie|Torgersen|42.1|19.1|195|4000|MALE
Gentoo|Biscoe|54.3|15.7|231|5650|MALE
```

- ничего особенного, выбираем все записи из таблички `little_penguins`

### Дополнительные команды SQL

[src/admin_commands.sql](https://gvwilson.github.io/sql-tutorial/src/admin_commands.sql)

```
.headers on 
.mode markdown
SELECT *
FROM little_penguins;
```

[out/admin_commands.out](https://gvwilson.github.io/sql-tutorial/out/admin_commands.out)

```
| species |  island   | bill_length_mm | bill_depth_mm | flipper_length_mm | body_mass_g |  sex   |
|---------|-----------|----------------|---------------|-------------------|-------------|--------|
| Adelie  | Dream     | 37.2           | 18.1          | 178               | 3900        | MALE   |
| Adelie  | Dream     | 37.6           | 19.3          | 181               | 3300        | FEMALE |
| Gentoo  | Biscoe    | 50             | 15.3          | 220               | 5550        | MALE   |
| Adelie  | Torgersen | 37.3           | 20.5          | 199               | 3775        | MALE   |
| Adelie  | Biscoe    | 39.6           | 17.7          | 186               | 3500        | FEMALE |
| Gentoo  | Biscoe    | 47.7           | 15            | 216               | 4750        | FEMALE |
| Adelie  | Dream     | 36.5           | 18            | 182               | 3150        | FEMALE |
| Gentoo  | Biscoe    | 42             | 13.5          | 210               | 4150        | FEMALE |
| Adelie  | Torgersen | 42.1           | 19.1          | 195               | 4000        | MALE   |
| Gentoo  | Biscoe    | 54.3           | 15.7          | 231               | 5650        | MALE   |
```

- включаем заголовки и режим markdown; в `SQLite` подобные команды начинаются с `.`, а в PostgreSQL с `\`
- кстати, для просмотра дополнительной инфы или чтобы узнать, какие команды есть, используйте `.help`

### Выбираем нужные столбцы

[src/specify_columns.sql](https://gvwilson.github.io/sql-tutorial/src/specify_columns.sql)

```
SELECT species,
       island,
       sex
FROM little_penguins;
```

[out/specify_columns.out](https://gvwilson.github.io/sql-tutorial/out/specify_columns.out)

```
| species |  island   |  sex   |
|---------|-----------|--------|
| Adelie  | Dream     | MALE   |
| Adelie  | Dream     | FEMALE |
| Gentoo  | Biscoe    | MALE   |
| Adelie  | Torgersen | MALE   |
| Adelie  | Biscoe    | FEMALE |
| Gentoo  | Biscoe    | FEMALE |
| Adelie  | Dream     | FEMALE |
| Gentoo  | Biscoe    | FEMALE |
| Adelie  | Torgersen | MALE   |
| Gentoo  | Biscoe    | MALE   |
```

- выбираем колонки `species`, `island`, `sex` из таблички `little_penguins`

### Сортировка

[src/sort.sql](https://gvwilson.github.io/sql-tutorial/src/sort.sql)

```
SELECT species,
       sex,
       island
FROM little_penguins
ORDER BY island ASC,
         sex DESC;
```

[out/sort.out](https://gvwilson.github.io/sql-tutorial/out/sort.out)

```
| species |  sex   |  island   |
|---------|--------|-----------|
| Gentoo  | MALE   | Biscoe    |
| Gentoo  | MALE   | Biscoe    |
| Adelie  | FEMALE | Biscoe    |
| Gentoo  | FEMALE | Biscoe    |
| Gentoo  | FEMALE | Biscoe    |
| Adelie  | MALE   | Dream     |
| Adelie  | FEMALE | Dream     |
| Adelie  | FEMALE | Dream     |
| Adelie  | MALE   | Torgersen |
| Adelie  | MALE   | Torgersen |
```

- выбираем столбцы `species`, `island`, `sex` из таблички `little_penguins`
- сортируем все значения из `island` в возрастающем порядке (от A к Z)
- строки с одинаковыми значениями `island` дополнительно сортируем по их значениям `sex` в обратном порядке, от большего к меньшему (от Z к A)

### Ограничение выводимых записей

- Full dataset has 344 rows

[src/limit.sql](https://gvwilson.github.io/sql-tutorial/src/limit.sql)

```
SELECT species,
       sex,
       island
FROM penguins
ORDER BY species,
         sex,
         island
LIMIT 10;
```

[out/limit.out](https://gvwilson.github.io/sql-tutorial/out/limit.out)

```
| species |  sex   |  island   |
|---------|--------|-----------|
| Adelie  |        | Dream     |
| Adelie  |        | Torgersen |
| Adelie  |        | Torgersen |
| Adelie  |        | Torgersen |
| Adelie  |        | Torgersen |
| Adelie  |        | Torgersen |
| Adelie  | FEMALE | Biscoe    |
| Adelie  | FEMALE | Biscoe    |
| Adelie  | FEMALE | Biscoe    |
| Adelie  | FEMALE | Biscoe    |
```

- выбираем столбцы `species`, `sex`, `island` из таблички `penguins`
- сортируем по `species` в порядке возрастания, строки с одинаковым значением `species` сортируются по `sex`, с одинаковым `sex` дополнительно сортируются по `island`
- ну и выводим только первые 10 строк

### Ещё некоторые параметры вывода

[src/page.sql](https://gvwilson.github.io/sql-tutorial/src/page.sql)

```
SELECT species,
       sex,
       island
FROM penguins
ORDER BY species,
         sex,
         island
LIMIT 10
OFFSET 3;
```

[out/page.out](https://gvwilson.github.io/sql-tutorial/out/page.out)

```
| species |  sex   |  island   |
|---------|--------|-----------|
| Adelie  |        | Torgersen |
| Adelie  |        | Torgersen |
| Adelie  |        | Torgersen |
| Adelie  | FEMALE | Biscoe    |
| Adelie  | FEMALE | Biscoe    |
| Adelie  | FEMALE | Biscoe    |
| Adelie  | FEMALE | Biscoe    |
| Adelie  | FEMALE | Biscoe    |
| Adelie  | FEMALE | Biscoe    |
| Adelie  | FEMALE | Biscoe    |
```

- `OFFSET` указывается после `LIMIT` и позволяет пропустить сколько-то первых строк, в данном случае пропущены 3 первых строки

### Удаляем дубликаты

[src/distinct.sql](https://gvwilson.github.io/sql-tutorial/src/distinct.sql)

```
SELECT DISTINCT species,
                sex,
                island
FROM penguins;
```

[out/distinct.out](https://gvwilson.github.io/sql-tutorial/out/distinct.out)

```
|  species  |  sex   |  island   |
|-----------|--------|-----------|
| Adelie    | MALE   | Torgersen |
| Adelie    | FEMALE | Torgersen |
| Adelie    |        | Torgersen |
| Adelie    | FEMALE | Biscoe    |
| Adelie    | MALE   | Biscoe    |
| Adelie    | FEMALE | Dream     |
| Adelie    | MALE   | Dream     |
| Adelie    |        | Dream     |
| Chinstrap | FEMALE | Dream     |
| Chinstrap | MALE   | Dream     |
| Gentoo    | FEMALE | Biscoe    |
| Gentoo    | MALE   | Biscoe    |
| Gentoo    |        | Biscoe    |
```

- `SELECT DISTINCT` — выбираем уникальные комбинации из столбцов `species`, `sex`, `island`

### Фильтруем результаты

[src/filter.sql](https://gvwilson.github.io/sql-tutorial/src/filter.sql)

```
SELECT DISTINCT species,
                sex,
                island
FROM penguins
WHERE island = 'Biscoe';
```

[out/filter.out](https://gvwilson.github.io/sql-tutorial/out/filter.out)

```
| species |  sex   | island |
|---------|--------|--------|
| Adelie  | FEMALE | Biscoe |
| Adelie  | MALE   | Biscoe |
| Gentoo  | FEMALE | Biscoe |
| Gentoo  | MALE   | Biscoe |
| Gentoo  |        | Biscoe |
```

- выбираем уникальные комбинации значений `species`, `sex`, `island` из `penguins`, где значения поля `island` равно `Biscoe`

### Более сложные условия фильтрации

[src/filter_and.sql](https://gvwilson.github.io/sql-tutorial/src/filter_and.sql)

```
SELECT DISTINCT species,
                sex,
                island
FROM penguins
WHERE island = 'Biscoe'
  AND sex != 'MALE';
```

[out/filter_and.out](https://gvwilson.github.io/sql-tutorial/out/filter_and.out)

```
| species |  sex   | island |
|---------|--------|--------|
| Adelie  | FEMALE | Biscoe |
| Gentoo  | FEMALE | Biscoe |
```

- выбираем уникальные комбинации значений `species`, `sex`, `island` из `penguins`, где значения поля `island` равно `Biscoe`, а значения поля `sex` не равно `MALE`

### Некоторые математические действия

[src/calculations.sql](https://gvwilson.github.io/sql-tutorial/src/calculations.sql)

```
SELECT flipper_length_mm / 10.0,
       body_mass_g / 1000.0
FROM penguins
LIMIT 3;
```

[out/calculations.out](https://gvwilson.github.io/sql-tutorial/out/calculations.out)

```
| flipper_length_mm / 10.0 | body_mass_g / 1000.0 |
|--------------------------|----------------------|
| 18.1                     | 3.75                 |
| 18.6                     | 3.8                  |
| 19.5                     | 3.25                 |
```

- выводим 3 первых строки значений `flipper_length_mm`, делённых на 10.0, и значений `body_mass_g`, делённых на 1000.0

### Переименовываем столбцы

[src/rename_columns.sql](https://gvwilson.github.io/sql-tutorial/src/rename_columns.sql)

```
SELECT flipper_length_mm / 10.0 AS flipper_cm,
       body_mass_g / 1000.0 AS weight_kg,
       island AS where_found
FROM penguins
LIMIT 3;
```

[out/rename_columns.out](https://gvwilson.github.io/sql-tutorial/out/rename_columns.out)

```
| flipper_cm | weight_kg | where_found |
|------------|-----------|-------------|
| 18.1       | 3.75      | Torgersen   |
| 18.6       | 3.8       | Torgersen   |
| 19.5       | 3.25      | Torgersen   |
```

- делим значения `flipper_length_mm` на 10.0, делим значения `body_mass_g` на 1000.0
- переименовываем столбцы `flipper_length_mm` — в `flipper_cm`, `body_mass_g` — в `weight_kg`, `island` — в `where_found`
- выводим первые 3 строки

**Взаимосвязь рассмотренных понятий SQL можно показать так:**

![concept map: selection](https://habrastorage.org/getpro/habr/upload_files/806/9f9/4f0/8069f94f0a448b711e810ebe05ee69d1.png "Огромная шпаргалка с готовыми запросами SQL (SQLite)")

concept map: selection

### Подсчёт с пропущенными значениями

[src/show_missing_values.sql](https://gvwilson.github.io/sql-tutorial/src/show_missing_values.sql)

```
SELECT flipper_length_mm / 10.0 AS flipper_cm,
       body_mass_g / 1000.0 AS weight_kg,
       island AS where_found
FROM penguins
LIMIT 5;
```

[out/show_missing_values.out](https://gvwilson.github.io/sql-tutorial/out/show_missing_values.out)

```
| flipper_cm | weight_kg | where_found |
|------------|-----------|-------------|
| 18.1       | 3.75      | Torgersen   |
| 18.6       | 3.8       | Torgersen   |
| 19.5       | 3.25      | Torgersen   |
|            |           | Torgersen   |
| 19.3       | 3.45      | Torgersen   |
```

- делим значения из `flipper_length_mm` на 10, затем присваиваем результаты новому столбцу `flipper_cm`
- делим значения из столбца `body_mass_g` на 1000 и затем присваивание результатов новому столбцу `weight_kg`
- переименовываем `island` в `where_found`

### Вывод с условием при помощи WHERE

- Repeated from above so it doesn’t count against our query limit

[src/filter.sql](https://gvwilson.github.io/sql-tutorial/src/filter.sql)

```
SELECT DISTINCT species,
                sex,
                island
FROM penguins
WHERE island = 'Biscoe';
```

[out/filter.out](https://gvwilson.github.io/sql-tutorial/out/filter.out)

```
| species |  sex   | island |
|---------|--------|--------|
| Adelie  | FEMALE | Biscoe |
| Adelie  | MALE   | Biscoe |
| Gentoo  | FEMALE | Biscoe |
| Gentoo  | MALE   | Biscoe |
| Gentoo  |        | Biscoe |
```

- выбираем столбцы `species`, `sex`, `island`
- выводим все записи из `penguins`, где значение `island` равно `'Biscoe'`

[src/null_equality.sql](https://gvwilson.github.io/sql-tutorial/src/null_equality.sql)

```
SELECT DISTINCT species,
                sex,
                island
FROM penguins
WHERE island = 'Biscoe'
  AND sex = 'FEMALE';
```

[out/null_equality.out](https://gvwilson.github.io/sql-tutorial/out/null_equality.out)

```
| species |  sex   | island |
|---------|--------|--------|
| Adelie  | FEMALE | Biscoe |
| Gentoo  | FEMALE | Biscoe |
```

- выводим все записи из `penguins`, где значение `island` равно `'Biscoe'` и значение `sex` равно `'FEMALE'`

### Условие с отрицанием

- условие с оператором отрицания `!=` тоже без проблем работает

[src/null_inequality.sql](https://gvwilson.github.io/sql-tutorial/src/null_inequality.sql)

```
SELECT DISTINCT species,
                sex,
                island
FROM penguins
WHERE island = 'Biscoe'
  AND sex != 'FEMALE';
```

[out/null_inequality.out](https://gvwilson.github.io/sql-tutorial/out/null_inequality.out)

```
| species | sex  | island |
|---------|------|--------|
| Adelie  | MALE | Biscoe |
| Gentoo  | MALE | Biscoe |
```

### Выбираем NULL значения

[src/safe_null_equality.sql](https://gvwilson.github.io/sql-tutorial/src/safe_null_equality.sql)

```
SELECT species,
       sex,
       island
FROM penguins
WHERE sex IS NULL;
```

[out/safe_null_equality.out](https://gvwilson.github.io/sql-tutorial/out/safe_null_equality.out)

```
| species | sex |  island   |
|---------|-----|-----------|
| Adelie  |     | Torgersen |
| Adelie  |     | Torgersen |
| Adelie  |     | Torgersen |
| Adelie  |     | Torgersen |
| Adelie  |     | Torgersen |
| Adelie  |     | Dream     |
| Gentoo  |     | Biscoe    |
| Gentoo  |     | Biscoe    |
| Gentoo  |     | Biscoe    |
| Gentoo  |     | Biscoe    |
| Gentoo  |     | Biscoe    |
```

- выбираем строки со значениями `species`, `sex`, `island` из таблички `penguins`, где значения `sex` нет (`NULL`)

**Вот так можно показать связь понятий SQL, которые мы рассмотрели выше:**

![concept map: null](https://habrastorage.org/getpro/habr/upload_files/cd2/072/2cb/cd20722cb7505cf071a36076435c746f.png "Огромная шпаргалка с готовыми запросами SQL (SQLite)")

concept map: null

### Агрегирование в SQL

[src/simple_sum.sql](https://gvwilson.github.io/sql-tutorial/src/simple_sum.sql)

```
SELECT sum(body_mass_g) AS total_mass
FROM penguins;
```

[out/simple_sum.out](https://gvwilson.github.io/sql-tutorial/out/simple_sum.out)

```
| total_mass |
|------------|
| 1437000    |
```

- суммируем все значения колонки `body_mass_g`, сохраняем в новый столбец `total_mass`

### Распространённые агрегирующие функции в SQL

[src/common_aggregations.sql](https://gvwilson.github.io/sql-tutorial/src/common_aggregations.sql)

```
SELECT MAX(bill_length_mm) AS longest_bill,
       MIN(flipper_length_mm) AS shortest_flipper,
       AVG(bill_length_mm) / AVG(bill_depth_mm) AS weird_ratio
FROM penguins;
```

[out/common_aggregations.out](https://gvwilson.github.io/sql-tutorial/out/common_aggregations.out)

```
| longest_bill | shortest_flipper |   weird_ratio    |
|--------------|------------------|------------------|
| 59.6         | 172              | 2.56087082530644 |
```

- находим максимальное значение из столбца `bill_length_mm`, записываем это значение как `longest_bill`
- аналогично находим минимальное из `flipper_length_mm`, находим среднее из `bill_length_mm`, среднее из `bill_depth_mm`

### Подсчёт значений при помощи COUNT

[src/count_behavior.sql](https://gvwilson.github.io/sql-tutorial/src/count_behavior.sql)

```
SELECT COUNT(*) AS count_star,
       COUNT(sex) AS count_specific,
       COUNT(DISTINCT sex) AS count_distinct
FROM penguins;
```

[out/count_behavior.out](https://gvwilson.github.io/sql-tutorial/out/count_behavior.out)

```
| count_star | count_specific | count_distinct |
|------------|----------------|----------------|
| 344        | 333            | 2              |
```

- `COUNT(*)` — считаем все значения из `count_star`
- `COUNT(sex)` — считаем все значения из столбца `sex`
- `COUNT(DISTINCT sex)` — считаем уникальные значения из `sex` (очевидно их 2: `MALE`, `FEMALE`)
- записываем эти 3 числа как `count_star`, `count_specific`, `count_distinct` соответственно

### Группировка

[src/simple_group.sql](https://gvwilson.github.io/sql-tutorial/src/simple_group.sql)

```
SELECT AVG(body_mass_g) AS average_mass_g
FROM penguins
GROUP BY sex;
```

[out/simple_group.out](https://gvwilson.github.io/sql-tutorial/out/simple_group.out)

```
|  average_mass_g  |
|------------------|
| 4005.55555555556 |
| 3862.27272727273 |
| 4545.68452380952 |
```

- из таблички `penguins` находим среднее всех значений `body_mass_g`, сохраняем как `average_mass_g`
- группируем по значениям `sex` (группы `FEMALE`, `MALE`, `NULL`)

### Как себя ведут неагрегированные столбцы

[src/unaggregated_columns.sql](https://gvwilson.github.io/sql-tutorial/src/unaggregated_columns.sql)

```
SELECT sex,
       AVG(body_mass_g) AS average_mass_g
FROM penguins
GROUP BY sex;
```

[out/unaggregated_columns.out](https://gvwilson.github.io/sql-tutorial/out/unaggregated_columns.out)

```
|  sex   |  average_mass_g  |
|--------|------------------|
|        | 4005.55555555556 |
| FEMALE | 3862.27272727273 |
| MALE   | 4545.68452380952 |
```

- для того, чтобы было видно названия отдельных групп, выбираем не только среднее `AVG(body_mass_g)`, но и `sex`
- видим 3 группы: `NULL`, `FEMALE`, `MALE`

### Выбор нужных столбцов для агрегирования

[src/arbitrary_in_aggregation.sql](https://gvwilson.github.io/sql-tutorial/src/arbitrary_in_aggregation.sql)

```
SELECT sex,
       body_mass_g
FROM penguins
GROUP BY sex;
```

[out/arbitrary_in_aggregation.out](https://gvwilson.github.io/sql-tutorial/out/arbitrary_in_aggregation.out)

```
|  sex   | body_mass_g |
|--------|-------------|
|        |             |
| FEMALE | 3800        |
| MALE   | 3750        |
```

- здесь у нас популярная ошибка, мы просто выбираем `body_mass_g`, а не находим среднее, поэтому SQL выбирает любые значения из `body_mass_g`. Аккуратнее)

### Фильтрация агрегированных значений

[src/filter_aggregation.sql](https://gvwilson.github.io/sql-tutorial/src/filter_aggregation.sql)

```
SELECT sex,
       AVG(body_mass_g) AS average_mass_g
FROM penguins
GROUP BY sex
HAVING average_mass_g > 4000.0;
```

[out/filter_aggregation.out](https://gvwilson.github.io/sql-tutorial/out/filter_aggregation.out)

```
| sex  |  average_mass_g  |
|------|------------------|
|      | 4005.55555555556 |
| MALE | 4545.68452380952 |
```

- здесь мы используем `HAVING` вместо `WHERE` (эффект тот же самый), оставляем только те значения из `average_mass_g`, которые больше 4000

### Читабельный вывод

[src/readable_aggregation.sql](https://gvwilson.github.io/sql-tutorial/src/readable_aggregation.sql)

```
SELECT sex,
       ROUND(AVG(body_mass_g), 1) AS average_mass_g
FROM penguins
GROUP BY sex
HAVING average_mass_g > 4000.0;
```

[out/readable_aggregation.out](https://gvwilson.github.io/sql-tutorial/out/readable_aggregation.out)

```
| sex  | average_mass_g |
|------|----------------|
|      | 4005.6         |
| MALE | 4545.7         |
```

- округляем среднее `AVG(body_mass_g` до 1 знака после запятой, используя `ROUND`

### Фильтрация входных данных

[src/filter_aggregate_inputs.sql](https://gvwilson.github.io/sql-tutorial/src/filter_aggregate_inputs.sql)

```
SELECT sex,
       ROUND(
           AVG(body_mass_g) FILTER (WHERE body_mass_g < 4000.0), 
           1) 
       AS average_mass_g
FROM penguins
GROUP BY sex;
```

[out/filter_aggregate_inputs.out](https://gvwilson.github.io/sql-tutorial/out/filter_aggregate_inputs.out)

```
|  sex   | average_mass_g |
|--------|----------------|
|        | 3362.5         |
| FEMALE | 3417.3         |
| MALE   | 3752.5         |
```

- при помощи `FILTER` мы находим среднее только тех значений `body_mass_g`, которые меньше 4000
- округляем до 1 знака после запятой, сохраняем в столбец `average_mass_g`
- группируем по `sex`

**Вот так выглядит связь основных понятий, которые мы только что обсуждали:**

![concept map: aggregation](https://habrastorage.org/getpro/habr/upload_files/65f/bf3/79b/65fbf379b530f5bc0afc31cb7bc8051d.png "Огромная шпаргалка с готовыми запросами SQL (SQLite)")

concept map: aggregation

**Кстати, вот так выглядит создание БД в оперативной памяти:**

[src/in_memory_db.sh](https://gvwilson.github.io/sql-tutorial/src/in_memory_db.sh)

```
sqlite3 :memory:
```

- запускаем интерактивную оболочку SQLite, создаём новую базу данных в оперативной памяти для более быстрой работы

### Создание табличек

[src/create_work_job.sql](https://gvwilson.github.io/sql-tutorial/src/create_work_job.sql)

```
CREATE TABLE job (name text NOT NULL,
                  billable real NOT NULL);

CREATE TABLE work (person text NOT NULL,
                   job text NOT NULL);
```

- создаём таблицу `job` со столбцами: `name` — столбец текстовых значений, не может быть пустым (`NOT NULL`), `billable` — содержит вещественные числа, не может быть пустым
- создаём табличку `work` со столбцами: `person` — текстовый, не может быть пустым, `job` — текстовый, не может быть пустым

### Вставляем данные

[src/populate_work_job.sql](https://gvwilson.github.io/sql-tutorial/src/populate_work_job.sql)

```
INSERT INTO job
VALUES ('calibrate', 1.5),
       ('clean', 0.5);

INSERT INTO work
VALUES ('mik', 'calibrate'),
       ('mik', 'clean'),
       ('mik', 'complain'),
       ('po', 'clean'),
       ('po', 'complain'),
       ('tay', 'complain');
```

[out/insert_values.out](https://gvwilson.github.io/sql-tutorial/out/insert_values.out)

```
|   name    | billable |
|-----------|----------|
| calibrate | 1.5      |
| clean     | 0.5      |

| person |    job    |
|--------|-----------|
| mik    | calibrate |
| mik    | clean     |
| mik    | complain  |
| po     | clean     |
| po     | complain  |
| tay    | complain  |
```

- ничего особенного, заполняем табличку `job` парами `name`–`billable`, и так же заполняем табличку `work` парами `person`–`job`

### Обновляем строки

[src/update_work_job.sql](https://gvwilson.github.io/sql-tutorial/src/update_work_job.sql)

```
UPDATE work
SET person = "tae"
WHERE person = "tay";
```

[out/update_rows.out](https://gvwilson.github.io/sql-tutorial/out/update_rows.out)

```
| person |    job    |
|--------|-----------|
| mik    | calibrate |
| mik    | clean     |
| mik    | complain  |
| po     | clean     |
| po     | complain  |
| tae    | complain  |
```

- меняем все записи `"tay"` на `"tae"`

### Удаляем строки

[src/delete_rows.sql](https://gvwilson.github.io/sql-tutorial/src/delete_rows.sql)

```
DELETE FROM work
WHERE person = "tae";
SELECT *
FROM work;
```

[out/delete_rows.out](https://gvwilson.github.io/sql-tutorial/out/delete_rows.out)

```
| person |    job    |
|--------|-----------|
| mik    | calibrate |
| mik    | clean     |
| mik    | complain  |
| po     | clean     |
| po     | complain  |
```

- удаляем все строки, где значение `person` равно `"tae"`

### Резервное копирование

[src/backing_up.sql](https://gvwilson.github.io/sql-tutorial/src/backing_up.sql)

```
CREATE TABLE backup (person text NOT NULL,
                     job text NOT NULL);
                     
INSERT INTO backup
SELECT person,
       job
FROM work
WHERE person = 'tae';

DELETE FROM work
WHERE person = 'tae';

SELECT *
FROM backup;
```

[out/backing_up.out](https://gvwilson.github.io/sql-tutorial/out/backing_up.out)

```
| person |   job    |
|--------|----------|
| tae    | complain |
```

- создаём табличку `backup` c текстовыми столбцами `person` и `job`
- помещаем внутрь `backup` значения столбцов `person` и `job` из таблицы `work`, где значения столбца `person` равно `'tae'`
- удаляем из `work` все записи со значением `person` равным `'tae'`
- отображаем записи таблички `backup`

**Вот так выглядит связь основных понятий, которые мы только что обсуждали:**

![concept map: data definition and modification](https://habrastorage.org/getpro/habr/upload_files/0c6/324/9e5/0c63249e54e3b837d6a4736d80f7f12c.png "Огромная шпаргалка с готовыми запросами SQL (SQLite)")

concept map: data definition and modification

### Объединение табличек при помощи JOIN

[src/cross_join.sql](https://gvwilson.github.io/sql-tutorial/src/cross_join.sql)

```
SELECT *
FROM work
CROSS JOIN job;
```

[out/cross_join.out](https://gvwilson.github.io/sql-tutorial/out/cross_join.out)

```
| person |    job    |   name    | billable |
|--------|-----------|-----------|----------|
| mik    | calibrate | calibrate | 1.5      |
| mik    | calibrate | clean     | 0.5      |
| mik    | clean     | calibrate | 1.5      |
| mik    | clean     | clean     | 0.5      |
| mik    | complain  | calibrate | 1.5      |
| mik    | complain  | clean     | 0.5      |
| po     | clean     | calibrate | 1.5      |
| po     | clean     | clean     | 0.5      |
| po     | complain  | calibrate | 1.5      |
| po     | complain  | clean     | 0.5      |
| tay    | complain  | calibrate | 1.5      |
| tay    | complain  | clean     | 0.5      |
```

- делаем `CROSS JOIN` для 2 таблиц `work` и `job` — все возможные комбинации строк из этих таблиц (если в `work` 3 строки, а в `job` 4 строки, то результат будет иметь 4 ⋅ 3 = 12 строк)

### INNER JOIN

[src/inner_join.sql](https://gvwilson.github.io/sql-tutorial/src/inner_join.sql)

```
SELECT *
FROM work
INNER JOIN job ON work.job = job.name;
```

[out/inner_join.out](https://gvwilson.github.io/sql-tutorial/out/inner_join.out)

```
| person |    job    |   name    | billable |
|--------|-----------|-----------|----------|
| mik    | calibrate | calibrate | 1.5      |
| mik    | clean     | clean     | 0.5      |
| po     | clean     | clean     | 0.5      |
```

- объединяем 2 таблицы `work` и `job` — берём те записи, где значение `job` из `work` совпадает со значением `name` из `job`

### Агрегирование объединённых через JOIN записей

[src/aggregate_join.sql](https://gvwilson.github.io/sql-tutorial/src/aggregate_join.sql)

```
SELECT work.person,
       SUM(job.billable) AS pay
FROM work
INNER JOIN job ON work.job = job.name
GROUP BY work.person;
```

[out/aggregate_join.out](https://gvwilson.github.io/sql-tutorial/out/aggregate_join.out)

```
| person | pay |
|--------|-----|
| mik    | 2.0 |
| po     | 0.5 |
```

- объединяем те строки таблиц `work` и `job`, где значение `job` в таблице `work` соответствует значению `name` в `job`
- суммируем значения `billable` из таблицы `job` для каждого значения `person` из таблицы `work`
- группируем результаты по значениям `person` из `work`

### LEFT JOIN

[src/left_join.sql](https://gvwilson.github.io/sql-tutorial/src/left_join.sql)

```
SELECT *
FROM work
LEFT JOIN job ON work.job = job.name;
```

[out/left_join.out](https://gvwilson.github.io/sql-tutorial/out/left_join.out)

```
| person |    job    |   name    | billable |
|--------|-----------|-----------|----------|
| mik    | calibrate | calibrate | 1.5      |
| mik    | clean     | clean     | 0.5      |
| mik    | complain  |           |          |
| po     | clean     | clean     | 0.5      |
| po     | complain  |           |          |
| tay    | complain  |           |          |
```

- склеиваем таблицы `work` и `job` по соответствующим значениям столбца `job`
- если в таблице `work` есть строки, для которых нет совпадений в таблице `job`, то они все равно будут включены в результат с пустыми (`NULL`) значениями
- использование `LEFT JOIN` гарантирует, что все строки из левой таблицы `work` будут включены в результат, независимо от наличия совпадающих строк в правой таблице `job`

### Агрегирование данных, собранных через LEFT JOIN

[src/aggregate_left_join.sql](https://gvwilson.github.io/sql-tutorial/src/aggregate_left_join.sql)

```
SELECT work.person,
       sum(job.billable) AS pay
FROM work
LEFT JOIN job ON work.job = job.name
GROUP BY work.person;
```

[out/aggregate_left_join.out](https://gvwilson.github.io/sql-tutorial/out/aggregate_left_join.out)

```
| person | pay |
|--------|-----|
| mik    | 2.0 |
| po     | 0.5 |
| tay    |     |
```

- вычисляем сумму значений столбца `billable` из `job`, сохраняем как `pay`
- используем `LEFT JOIN`, чтобы гарантированно включить все строки из `work` в `job`
- группируем по столбцу `person` из `work`

**Вот так выглядит связь основных понятий, которые мы только что обсуждали:**

![concept map: join](https://habrastorage.org/getpro/habr/upload_files/fa9/d5d/d80/fa9d5dd80111e82f67d5e6ba9f249bf0.png "Огромная шпаргалка с готовыми запросами SQL (SQLite)")

concept map: join

### Объединение значений

[src/coalesce.sql](https://gvwilson.github.io/sql-tutorial/src/coalesce.sql)

```
SELECT work.person,
       COALESCE(SUM(job.billable), 0.0) AS pay
FROM work 
LEFT JOIN job ON work.job = job.name
GROUP BY work.person;
```

[out/coalesce.out](https://gvwilson.github.io/sql-tutorial/out/coalesce.out)

```
| person | pay |
|--------|-----|
| mik    | 2.0 |
| po     | 0.5 |
| tay    | 0.0 |
```

- `COALESCE` используется для замены `NULL` на 0.0, если сумма `billable` для данного `person` равна `NULL`
- `LEFT JOIN` включает все записи из `work` и только соответствующие записи из `job`
- группируем по значениям столбца `person` из `work`

### SELECT DISTINCT и условие WHERE

[src/negate_incorrectly.sql](https://gvwilson.github.io/sql-tutorial/src/negate_incorrectly.sql)

```
SELECT DISTINCT person
FROM work
WHERE job != 'calibrate';
```

[out/negate_incorrectly.out](https://gvwilson.github.io/sql-tutorial/out/negate_incorrectly.out)

```
| person |
|--------|
| mik    |
| po     |
| tay    |
```

- выбираем уникальные значения из столбца `person`, где поле `job` не равно `calibrate`

### Использование набора в условии WHERE при помощи IN

[src/set_membership.sql](https://gvwilson.github.io/sql-tutorial/src/set_membership.sql)

```
SELECT *
FROM work
WHERE person NOT IN ('mik',
                     'tay');
```

[out/set_membership.out](https://gvwilson.github.io/sql-tutorial/out/set_membership.out)

```
| person |   job    |
|--------|----------|
| po     | clean    |
| po     | complain |
```

- выбираем все строки из `work`, где `person` не равно `'mik'` и не равно `'tay'`

### Подзапросы

[src/subquery_set.sql](https://gvwilson.github.io/sql-tutorial/src/subquery_set.sql)

```
SELECT DISTINCT person
FROM work
WHERE person not in
    (SELECT DISTINCT person
     FROM work
     WHERE job = 'calibrate');
```

[out/subquery_set.out](https://gvwilson.github.io/sql-tutorial/out/subquery_set.out)

```
| person |
|--------|
| po     |
| tay    |
```

- внутренний подзапрос выбирает уникальные значения столбца `person` из `work`, где в поле `job` стоит `'calibrate'`
- внешний, главный запрос выбирает те уникальные значения `person`, где `person` не равно значениям из внутренного подзапроса

### Автоикремент и PRIMARY KEY

[src/autoincrement.sql](https://gvwilson.github.io/sql-tutorial/src/autoincrement.sql)

```
CREATE TABLE person (ident integer PRIMARY KEY autoincrement,
                     name text NOT NULL);
INSERT INTO person
VALUES (NULL, 'mik'),
       (NULL, 'po'),
       (NULL, 'tay');

SELECT *
FROM person;
INSERT INTO person
VALUES (1, "prevented");
```

[out/autoincrement.out](https://gvwilson.github.io/sql-tutorial/out/autoincrement.out)

```
| ident | name |
|-------|------|
| 1     | mik  |
| 2     | po   |
| 3     | tay  |
Runtime error near line 12: UNIQUE constraint failed: person.ident (19)
```

- создаём табличку `person` с 2 столбцами: `ident` с целочисленными значениями, `name` с текстовыми значениями; столбец `ident` устанавливаем как `PRIMARY KEY`, включаем автоматическое инкрементирование значений
- помещаем в таблицу `person` 3 пары `ident`–`name`
- при попытке добавить ещё одну пару `(1, "prevented")` возникает ошибка, поскольку уже существует строка с `indent` равным 1

**Внутренняя табличка:**

[src/sequence_table.sql](https://gvwilson.github.io/sql-tutorial/src/sequence_table.sql)

```
SELECT *
FROM sqlite_sequence;
```

[out/sequence_table.out](https://gvwilson.github.io/sql-tutorial/out/sequence_table.out)

```
|  name  | seq |
|--------|-----|
| person | 3   |
```

- выводим все текущие значения автоинкрементных счетчиков для таблиц в БД SQLite

### Изменение таблички при помощи ALTER

[src/alter_tables.sql](https://gvwilson.github.io/sql-tutorial/src/alter_tables.sql)

```
ALTER TABLE job ADD ident integer NOT NULL DEFAULT -1;

UPDATE job
SET ident = 1
WHERE name = 'calibrate';

UPDATE job
SET ident = 2
WHERE name = 'clean';

SELECT *
FROM job;
```

[out/alter_tables.out](https://gvwilson.github.io/sql-tutorial/out/alter_tables.out)

```
|   name    | billable | ident |
|-----------|----------|-------|
| calibrate | 1.5      | 1     |
| clean     | 0.5      | 2     |
```

- добавляем новый столбец `ident` в табличку `job`; столбец заполняется целыми числами, не может быть пустым; ставим значение по умолчанию `-1` для этого столбца
- делаем значение столбца `ident` равным 1 там, где `name` равен `'calibrate'`
- устанавливаем значение `ident` равным 2 для строки, где `name` равен `clean`

### Создание новой таблички на базе старой

[src/insert_select.sql](https://gvwilson.github.io/sql-tutorial/src/insert_select.sql)

```
CREATE TABLE new_work (person_id integer NOT NULL,
                       job_id integer NOT NULL,
                       FOREIGN key(person_id) REFERENCES person(ident),
                       FOREIGN key(job_id) REFERENCES job(ident));

INSERT INTO new_work
SELECT person.ident AS person_id,
       job.ident AS job_id
FROM (person
      JOIN work 
      ON person.name = work.person)
      
JOIN job ON job.name = work.job;

SELECT *
FROM new_work;
```

[out/insert_select.out](https://gvwilson.github.io/sql-tutorial/out/insert_select.out)

```
| person_id | job_id |
|-----------|--------|
| 1         | 1      |
| 1         | 2      |
| 2         | 2      |
```

- создаём таблицу `new_work` с 2 целочисленными столбцами: `person_id` и `job_id`; оба столбца не могут быть пустыми
- 2 `FOREIGN KEY` ограничения добавляются, чтобы связать столбцы `person_id` и `job_id` новой таблицы `new_work` с соответствующими столбцами `ident` в таблицах `person` и `job`
- добавляем данные в таблицу `new_work`, используя результат запроса `SELECT`
- `FROM (person JOIN work ON person.name = work.person)` — данные будут выбраны из результатов соединения таблиц `person` и `work` по условию равенства значений столбца `name` в таблице `person` и столбца `person` в таблице `work`
- `JOIN job ON job.name = work.job` — результаты предыдущего соединения будут дополнительно соединены с таблицей `job` по условию равенства значений столбца `name` в таблице `job` и столбца `job`в `work`

### Удаление таблички

[src/drop_table.sql](https://gvwilson.github.io/sql-tutorial/src/drop_table.sql)

```
DROP TABLE work;
ALTER TABLE new_work RENAME TO work;
```

- удаляем `work` из БД
- изменяем имя таблички `new_work` на `work`

[out/drop_table.out](https://gvwilson.github.io/sql-tutorial/out/drop_table.out)

```
CREATE TABLE job (ident integer PRIMARY KEY autoincrement,
                                            name text NOT NULL,
                                            billable real NOT NULL);
CREATE TABLE sqlite_sequence(name,
                             seq);

CREATE TABLE person (ident integer PRIMARY KEY autoincrement,
                                               name text NOT NULL);

CREATE TABLE IF NOT EXISTS "work" (person_id integer NOT NULL,
                                   job_id integer NOT NULL,
                                   FOREIGN key(person_id) REFERENCES person(ident),
                                   FOREIGN key(job_id) REFERENCES job(ident));
```

- создаём таблицу `job` с 3 колонками: `ident` хранит целые числа, используется в качестве первичного ключа (`PRIMARY KEY`) и автоматически увеличивается (autoincrement);`name` текстовый столбец, не может быть пустым (`NOT NULL`);`billable` — столбец вещественных чисел, не может быть пустым
- создаём `sqlite_sequence` с 2 колонками: `name` и `seq`
- создаём таблицу `person` с 2 колонками: `ident` — хранит целые числа, используется в качестве первичного ключа и автоматически увеличивается (autoincrement), `name` — хранит текст, не может быть пустым
- создаём `work` с 4 колонками: `person_id` – хранит целые числа, не может быть пустым; аналогичный столбец `job_id`
- устанавливаем внешние ключи, связывающие `person_id` с `ident` в таблице `person` и `job_id` с `ident` в таблице `job`

### Сравнение отдельных значений с агрегированными

[src/compare_individual_aggregate.sql](https://gvwilson.github.io/sql-tutorial/src/compare_individual_aggregate.sql)

```
SELECT body_mass_g
FROM   penguins
WHERE  body_mass_g > (SELECT AVG(body_mass_g)
                      FROM   penguins)
LIMIT  5; 
```

[out/compare_individual_aggregate.out](https://gvwilson.github.io/sql-tutorial/out/compare_individual_aggregate.out)

```
| body_mass_g |
|-------------|
| 4675        |
| 4250        |
| 4400        |
| 4500        |
| 4650        |
```

- выбираем только те строки, где значение в столбце `body_mass_g` больше, чем среднее значение `body_mass_g` по всем строкам в таблице `penguins`
- ну и выводим только первые 5 строк

### Сравнение отдельных значений с агрегированными внутри групп

[src/compare_within_groups.sql](https://gvwilson.github.io/sql-tutorial/src/compare_within_groups.sql)

```
SELECT penguins.species,
       penguins.body_mass_g,
       Round(averaged.avg_mass_g, 1) AS avg_mass_g
FROM   penguins
       JOIN (SELECT species,
                    Avg(body_mass_g) AS avg_mass_g
             FROM   penguins
             GROUP  BY species) AS averaged
         ON penguins.species = averaged.species
WHERE  penguins.body_mass_g > averaged.avg_mass_g
LIMIT  5; 
```

[out/compare_within_groups.out](https://gvwilson.github.io/sql-tutorial/out/compare_within_groups.out)

```
| species | body_mass_g | avg_mass_g |
|---------|-------------|------------|
| Adelie  | 3750        | 3700.7     |
| Adelie  | 3800        | 3700.7     |
| Adelie  | 4675        | 3700.7     |
| Adelie  | 4250        | 3700.7     |
| Adelie  | 3800        | 3700.7     |
```

- выбираем столбцы `species` и `body_mass_g` из таблицы `penguins`
- вычисляем среднюю массу для каждого вида пингвина, округляем до 1 знака после запятой, используя подзапрос, который связывается с исходной таблицей `penguins` по полю `species`
- используя результаты подзапроса, фильтруем только те записи, где масса пингвина больше средней массы для его вида
- выводим только первые 5 записей

### CTE — табличные выражения

[src/common_table_expressions.sql](https://gvwilson.github.io/sql-tutorial/src/common_table_expressions.sql)

```
WITH grouped AS
  (SELECT species,
          avg(body_mass_g) AS avg_mass_g
   FROM penguins
   GROUP BY species)
SELECT penguins.species,
       penguins.body_mass_g,
       round(grouped.avg_mass_g, 1) AS avg_mass_g
FROM penguins
JOIN grouped
WHERE penguins.body_mass_g > grouped.avg_mass_g
LIMIT 5;
```

[out/common_table_expressions.out](https://gvwilson.github.io/sql-tutorial/out/common_table_expressions.out)

```
| species | body_mass_g | avg_mass_g |
|---------|-------------|------------|
| Adelie  | 3750        | 3700.7     |
| Adelie  | 3800        | 3700.7     |
| Adelie  | 4675        | 3700.7     |
| Adelie  | 4250        | 3700.7     |
| Adelie  | 3800        | 3700.7     |
```

- создаём табличку `grouped` (с помощью `WITH`), которая содержит среднюю массу тела пингвинов (`AVG(body_mass_g)`) для каждого вида из `penguins` (`GROUP BY species`)
- из `penguins` выбираем такие столбцы: `species`, `body_mass_g`; и из из общей таблицы `grouped`выбираем `avg_mass_g`, округлённое до 1 знака
- объединяем `penguins` с общей таблицей `grouped` (через `JOIN`); для каждого пингвина будет найдена соответствующая средняя масса тела для его вида
- `WHERE` — фильтруем; оставляем только тех, у которых масса тела больше средней массы их вида
- выводим только первые 5 строк

### Смотрим план запроса с помощью EXPLAIN

[src/explain_query_plan.sql](https://gvwilson.github.io/sql-tutorial/src/explain_query_plan.sql)

```
EXPLAIN query PLAN
SELECT species,
       AVG(body_mass_g)
FROM penguins
GROUP BY species;
```

[out/explain_query_plan.out](https://gvwilson.github.io/sql-tutorial/out/explain_query_plan.out)

```
QUERY PLAN
|--SCAN penguins
`--USE TEMP B-TREE FOR GROUP BY
```

- `EXPLAIN query PLAN` — получаем план выполнения запроса, как будет выполнен запрос в базе данных
- выбираем столбец `species`, вычисляем среднее значение столбца body_mass_g для каждого вида из `penguins`
- `GROUP BY species` — группируем результаты по столбцу `species`

### Нумеруем строки

- каждая таблица имеет специальный столбец `rowid` с уникальными числовыми идентификаторами

[src/rowid.sql](https://gvwilson.github.io/sql-tutorial/src/rowid.sql)

```
SELECT rowid, species, island
FROM penguins
LIMIT 5;
```

[out/rowid.out](https://gvwilson.github.io/sql-tutorial/out/rowid.out)

```
| rowid | species |  island   |
|-------|---------|-----------|
| 1     | Adelie  | Torgersen |
| 2     | Adelie  | Torgersen |
| 3     | Adelie  | Torgersen |
| 4     | Adelie  | Torgersen |
| 5     | Adelie  | Torgersen |
```

### Условия if-else

[src/if_else.sql](https://gvwilson.github.io/sql-tutorial/src/if_else.sql)

```
WITH sized_penguins AS
  (SELECT species,
          iif(body_mass_g < 3500, 'small', 'large') AS size
   FROM penguins)
SELECT species,
       size,
       count(*) AS num
FROM sized_penguins
GROUP BY species,
         size
ORDER BY species,
         num;
```

[out/if_else.out](https://gvwilson.github.io/sql-tutorial/out/if_else.out)

```
|  species  | size  | num |
|-----------|-------|-----|
| Adelie    | small | 54  |
| Adelie    | large | 98  |
| Chinstrap | small | 17  |
| Chinstrap | large | 51  |
| Gentoo    | large | 124 |
```

- создаём временную таблицу `sized_penguins`, которая содержит два столбца: `species` и `size`
- `size` определяется на основе условия: если `body_mass_g` меньше 3500, то он считается `'small'`, в противном случае – `'large'`
- выбираем столбцы `species` и `size` из временной таблицы `sized_penguins`, а подсчитываем количество записей для каждой комбинации `species` и `size`, используя функцию `count(*)`
- группируем данные (`GROUP BY`) по `species` и `size`

### Выбираем с помощью SELECT и CASE

А если нам нужны маленькие, средние и большие?

Можно вложить if, но он быстро становится нечитаемым

[src/case_when.sql](https://gvwilson.github.io/sql-tutorial/src/case_when.sql)

```
WITH sized_penguins AS
  (SELECT species,
          CASE
              WHEN body_mass_g < 3500 THEN 'small'
              WHEN body_mass_g < 5000 THEN 'medium'
              ELSE 'large'
          END AS SIZE
   FROM penguins)
SELECT species,
       SIZE,
       count(*) AS num
FROM sized_penguins
GROUP BY species,
         SIZE
ORDER BY species,
         num;
```

[out/case_when.out](https://gvwilson.github.io/sql-tutorial/out/case_when.out)

```
|  species  |  size  | num |
|-----------|--------|-----|
| Adelie    | large  | 1   |
| Adelie    | small  | 54  |
| Adelie    | medium | 97  |
| Chinstrap | small  | 17  |
| Chinstrap | medium | 51  |
| Gentoo    | medium | 56  |
| Gentoo    | large  | 68  |
```

- в блоке `WITH` создаём набор данных с именем `sized_penguins`, где находится `species` и `size`, определенные на `body_mass_g`
- `CASE` разделяет пингвинов на 3 категории: `'small'`, `'medium'` и `'large'` в зависимости от их массы
- в основном блоке `SELECT` выбираются вид пингвина, его размер и количество пингвинов каждого размера (`num`) из набора `sized_penguins`
- результаты группируются по виду пингвина и их размеру с помощью `GROUP BY`
- в конце запроса результаты сортируются сначала по `species` в алфавитном порядке, а затем по `num`

### Работаем с диапазоном значений

[src/check_range.sql](https://gvwilson.github.io/sql-tutorial/src/check_range.sql)

```
WITH sized_penguins AS
  (SELECT species,
          CASE
              WHEN body_mass_g BETWEEN 3500 AND 5000 THEN 'normal'
              ELSE 'abnormal'
          END AS SIZE
   FROM penguins)
SELECT species,
       SIZE,
       count(*) AS num
FROM sized_penguins
GROUP BY species,
         SIZE
ORDER BY species,
         num;
```

[out/check_range.out](https://gvwilson.github.io/sql-tutorial/out/check_range.out)

```
|  species  |   size   | num |
|-----------|----------|-----|
| Adelie    | abnormal | 55  |
| Adelie    | normal   | 97  |
| Chinstrap | abnormal | 17  |
| Chinstrap | normal   | 51  |
| Gentoo    | abnormal | 62  |
| Gentoo    | normal   | 62  |
```

- создаём общую таблицу выражений (CTE) `sized_penguins`, она выбирает вид пингвина и определяет его размер в зависимости от массы тела; если масса в диапазоне от 3500 до 5000 г, это размер `normal`, в противном случае – `abnormal`
- затем из этой CTE извлекаем данные с указанием видов пингвинов, их размеров и количества пингвинов каждого вида и размера, используя `SELECT` с агрегирующей функцией `COUNT(*)`
- группируем по виду и размеру пингвина с помощью `GROUP BY`
- сортируем результат по виду и количеству пингвинов в порядке возрастания с помощью `ORDER BY`

**Ещё одна БД: **

ER-диаграмма показывает отношения между отдельными табличками и выглядит так:

![assay database table diagram](https://habrastorage.org/getpro/habr/upload_files/648/684/cd2/648684cd2e1abbf5bde4c04ac8d416d1.png "Огромная шпаргалка с готовыми запросами SQL (SQLite)")

assay database table diagram

![assay ER diagram](https://habrastorage.org/getpro/habr/upload_files/63a/122/f00/63a122f00a285e28e49eac6483666c7f.png "Огромная шпаргалка с готовыми запросами SQL (SQLite)")

assay ER diagram

[src/assay_staff.sql](https://gvwilson.github.io/sql-tutorial/src/assay_staff.sql)

```
SELECT *
FROM staff;
```

[out/assay_staff.out](https://gvwilson.github.io/sql-tutorial/out/assay_staff.out)

```
| ident | personal |  family   | dept | age |
|-------|----------|-----------|------|-----|
| 1     | Kartik   | Gupta     |      | 46  |
| 2     | Divit    | Dhaliwal  | hist | 34  |
| 3     | Indrans  | Sridhar   | mb   | 47  |
| 4     | Pranay   | Khanna    | mb   | 51  |
| 5     | Riaan    | Dua       |      | 23  |
| 6     | Vedika   | Rout      | hist | 45  |
| 7     | Abram    | Chokshi   | gen  | 23  |
| 8     | Romil    | Kapoor    | hist | 38  |
| 9     | Ishaan   | Ramaswamy | mb   | 35  |
| 10    | Nitya    | Lal       | gen  | 52  |
```

### Ищем по фрагменту с помощью LIKE

[src/like_glob.sql](https://gvwilson.github.io/sql-tutorial/src/like_glob.sql)

```
SELECT personal,
       family
FROM staff
WHERE personal LIKE '%ya%'
  OR family GLOB '*De*';
```

[out/like_glob.out](https://gvwilson.github.io/sql-tutorial/out/like_glob.out)

```
| personal | family |
|----------|--------|
| Nitya    | Lal    |
```

- `SELECT personal, family` — хотим выбрать столбцы `personal` и `family` из таблицы `staff`
- `FROM staff` — ну понятно, запрос будет выполнен в таблице `staff`
- `'%ya%'` — хотим выбрать строки, в которых значение столбца `personal` содержит подстроку `ya` (с помощью `LIKE`) или значение столбца `family` содержит `De` (с помощью `GLOB`)

### Выбираем первую и последнюю строки

[src/union_all.sql](https://gvwilson.github.io/sql-tutorial/src/union_all.sql)

```
SELECT *
FROM
  (SELECT *
   FROM
     (SELECT *
      FROM experiment
      ORDER BY started ASC
      LIMIT 5)
   UNION ALL SELECT *
   FROM
     (SELECT *
      FROM experiment
      ORDER BY started DESC
      LIMIT 5))
ORDER BY started ASC ;
```

[out/union_all.out](https://gvwilson.github.io/sql-tutorial/out/union_all.out)

```
| ident |    kind     |  started   |   ended    |
|-------|-------------|------------|------------|
| 17    | trial       | 2023-01-29 | 2023-01-30 |
| 35    | calibration | 2023-01-30 | 2023-01-30 |
| 36    | trial       | 2023-02-02 | 2023-02-03 |
| 25    | trial       | 2023-02-12 | 2023-02-14 |
| 2     | calibration | 2023-02-14 | 2023-02-14 |
| 40    | calibration | 2024-01-21 | 2024-01-21 |
| 12    | trial       | 2024-01-26 | 2024-01-28 |
| 44    | trial       | 2024-01-27 | 2024-01-29 |
| 34    | trial       | 2024-02-01 | 2024-02-02 |
| 14    | calibration | 2024-02-03 | 2024-02-03 |
```

- выбираем 5 самых старых записей из таблицы `experiment`, отсортированных по возрастанию даты начала (`started ASC`) с помощью подзапроса (внутренний `SELECT`)
- выбираем 5 самых новых записей из `experiment`, отсортированных по убыванию даты начала (`started DESC`) с помощью другого подзапроса
- объединяем эти 2 подзапроса с помощью `UNION ALL`, так мы получаем временную таблицу, содержащую 10 записей (5 самых старых и 5 самых новых)
- из временной таблицы выбираем все столбцы для каждой записи (`SELECT *`) и окончательно сортируем записи по возрастанию даты начала (`started ASC`) с помощью внешнего `ORDER BY`

### Пересечение отдельных табличек

[src/intersect.sql](https://gvwilson.github.io/sql-tutorial/src/intersect.sql)

```
SELECT personal,
       family,
       dept,
       age
FROM staff
WHERE dept = 'mb' INTERSECT
  SELECT personal,
         family,
         dept,
         age
  FROM staff WHERE age < 50 ;
```

[out/intersect.out](https://gvwilson.github.io/sql-tutorial/out/intersect.out)

```
| personal |  family   | dept | age |
|----------|-----------|------|-----|
| Indrans  | Sridhar   | mb   | 47  |
| Ishaan   | Ramaswamy | mb   | 35  |
```

- здесь мы используем `INTERSECT` для объединения результатов двух отдельных запросов
- вначале выбираем данные из таблицы `staff`, в которых значение поля `dept` равно `'mb'`
- потом выбираем данные из таблицы `staff`, в которых значение поля `age` меньше 50
- с помощью `INTERSECT` объединяем результаты этих двух запросов
- в результате будут выбраны строки, которые присутствуют в обоих результатах, то есть записи из `staff`, где значение `dept` равно `'mb'` и значение `age` меньше 50

### Исключение

[src/except.sql](https://gvwilson.github.io/sql-tutorial/src/except.sql)

```
SELECT personal,
       family,
       dept,
       age
FROM staff
WHERE dept = 'mb'
EXCEPT
SELECT personal,
       family,
       dept,
       age
FROM staff
WHERE age < 50 ;
```

[out/except.out](https://gvwilson.github.io/sql-tutorial/out/except.out)

```
| personal | family | dept | age |
|----------|--------|------|-----|
| Pranay   | Khanna | mb   | 51  |
```

- при помощи `SELECT` извлекаем 4 поля из `staff`: `personal`, `family`, `dept` и `age`
- затем используем `WHERE`, чтобы отфильтровать только те строки, в которых значение `dept` равно `'mb'`
- после этого при помощи `EXCEPT` удаляем из исходного результата любые строки, которые также присутствуют в результате второго запроса
- второй запрос `SELECT` также извлекает четыре поля из `staff`: `personal`, `family`, `dept` и `age`
- используем `WHERE`, чтобы отфильтровать только те строки, где значение `age` меньше 50

### Случайные значения в SQL

[src/random_numbers.sql](https://gvwilson.github.io/sql-tutorial/src/random_numbers.sql)

```
WITH decorated AS
  (SELECT random() AS rand,
          personal || ' ' || family AS name
   FROM staff)
SELECT rand,
       abs(rand) % 10 AS selector,
       name
FROM decorated
WHERE selector < 5;
```

[out/random_numbers.out](https://gvwilson.github.io/sql-tutorial/out/random_numbers.out)

```
|         rand         | selector |      name       |
|----------------------|----------|-----------------|
| 7176652035743196310  | 0        | Divit Dhaliwal  |
| -2243654635505630380 | 2        | Indrans Sridhar |
| -6940074802089166303 | 5        | Pranay Khanna   |
| 8882650891091088193  | 9        | Riaan Dua       |
| -45079732302991538   | 5        | Vedika Rout     |
| -8973877087806386134 | 2        | Abram Chokshi   |
| 3360598450426870356  | 9        | Romil Kapoor    |
```

- создаём временную таблицу `decorated`

- в этой таблице извлекается случайное число с помощью `random()`
- конкатенируем значения `personal` и `family` под именем `name` с помощью `' '` для разделения
- таким образом создаём временную таблицу, содержащую столбцы `rand` с случайными числами и `name` со значениями из столбцов `personal` и `family` таблицы `staff`
- делаем выборку из временной таблицы `decorated`; в выборку включаем столбцы `rand`, `name`
- `abs(rand) % 10` — это мы вычисляем остаток от деления абсолютного значения `rand` на 10
- ну и в конце оставляем только строки, где `selector` меньше 5

### Создание индексов

[src/create_use_index.sql](https://gvwilson.github.io/sql-tutorial/src/create_use_index.sql)

```
EXPLAIN query PLAN
SELECT filename
FROM plate
WHERE filename like '%07%';

CREATE INDEX plate_file ON plate(filename);

EXPLAIN query PLAN
SELECT filename
FROM plate
WHERE filename like '%07%';
```

[out/create_use_index.out](https://gvwilson.github.io/sql-tutorial/out/create_use_index.out)

```
QUERY PLAN
`--SCAN plate USING COVERING INDEX sqlite_autoindex_plate_1
QUERY PLAN
`--SCAN plate USING COVERING INDEX plate_file
```

- выбираем все значения столбца `filename` из таблицы `plate`, где значение столбца `filename` содержит подстроку `07`
- создаём индекс с именем `plate_file` для столбца `filename` в таблице `plate`
- запрашиваем план выполнения запроса (`EXPLAIN query PLAN`)

### Генерация последовательности значений

[src/generate_sequence.sql](https://gvwilson.github.io/sql-tutorial/src/generate_sequence.sql)

```
SELECT value
FROM generate_series(1, 5);
```

[out/generate_sequence.out](https://gvwilson.github.io/sql-tutorial/out/generate_sequence.out)

```
| value |
|-------|
| 1     |
| 2     |
| 3     |
| 4     |
| 5     |
```

- `generate_series(1, 5)` — генерируем ряд чисел от 1 до 5
- `SELECT value` — выбираем этот столбец `value` со сгенерированными числами от 1 до 5

### Генерируем последовательность на основе данных

[src/data_range_sequence.sql](https://gvwilson.github.io/sql-tutorial/src/data_range_sequence.sql)

```
CREATE TABLE temp (num integer NOT NULL);

INSERT INTO temp
VALUES (1),
       (5);

SELECT value
FROM generate_series ((SELECT min(num)
                       FROM TEMP),
                      (SELECT max(num)
                       FROM TEMP));
```

[out/data_range_sequence.out](https://gvwilson.github.io/sql-tutorial/out/data_range_sequence.out)

```
| value |
|-------|
| 1     |
| 2     |
| 3     |
| 4     |
| 5     |
```

- создаём временную таблицу `temp`, которая содержит 1 столбец с именем `num` типа integer; этот столбец не может быть пустым
- помещаем в `temp` значения 1 и 5 в столбец `num`
- используем `generate_series` для создания последовательности чисел между минимальным и максимальным значениями из столбца `num` в таблице `temp`

### Генерация последовательностей дат

[src/date_sequence.sql](https://gvwilson.github.io/sql-tutorial/src/date_sequence.sql)

```
SELECT date(
              (SELECT julianday(min(started))
               FROM experiment) + value) AS some_day
FROM
  (SELECT value
   FROM generate_series(
                          (SELECT 0),
                          (SELECT count(*) - 1
                           FROM experiment)))
LIMIT 5;
```

[out/date_sequence.out](https://gvwilson.github.io/sql-tutorial/out/date_sequence.out)

```
|  some_day  |
|------------|
| 2023-01-29 |
| 2023-01-30 |
| 2023-01-31 |
| 2023-02-01 |
| 2023-02-02 |
```

- `SELECT julianday(min(started)) FROM experiment` — находим минимальную дату в столбце `started` из `experiment`, преобразуем её в `Julian` день (числовое представление даты) и возвращаем этот `Julian` день
- внешним подзапросом вычисляем разницу между этим минимальным `Julian` днем и каждым `value` из `generate_series`
- затем складываем эти разницы с минимальным `Julian` днем, и конвертируем обратно в дату с помощью `date()`
- ну и выбираем только первые 5 результатов этого вычисления с помощью `LIMIT 5`

### Подсчитываем количество значений за день, без пропусков

[src/experiments_per_day.sql](https://gvwilson.github.io/sql-tutorial/src/experiments_per_day.sql)

```
     WITH -- complete sequence of days with 0 as placeholder for number of experiments
          all_days AS (
             SELECT DATE (
                    (
                       SELECT julianday (MIN(started))
                         FROM experiment
                    ) + VALUE
                    ) AS some_day,
                    0 AS zeroes
               FROM (
                       SELECT VALUE
                         FROM generate_series (
                              (
                                 SELECT 0
                              ),
                              (
                                 SELECT COUNT(*) - 1
                                   FROM experiment
                              )
                              )
                    )
          ), -- sequence of actual days with actual number of experiments started
          actual_days AS (
             SELECT started,
                    COUNT(started) AS num_exp
               FROM experiment
           GROUP BY started
          ) -- combined by joining on day and taking actual number (if available) or zero
   SELECT all_days.some_day AS DAY,
          COALESCE(actual_days.num_exp, all_days.zeroes) AS num_exp
     FROM all_days
LEFT JOIN actual_days ON all_days.some_day = actual_days.started
    LIMIT 5;
```

[out/experiments_per_day.out](https://gvwilson.github.io/sql-tutorial/out/experiments_per_day.out)

```
|    day     | num_exp |
|------------|---------|
| 2023-01-29 | 1       |
| 2023-01-30 | 1       |
| 2023-01-31 | 0       |
| 2023-02-01 | 0       |
| 2023-02-02 | 1       |
```

- создаём последовательность всех дней с нулевым значением в качестве заполнителя для количества экспериментов
- создаём последовательность фактических дней с реальным числом экспериментов
- объединяем эти последовательности, соединяя их по дням и беря реальное количество (если доступно) или ноль
- выводит результат, показывая дни (`all_days.some_day`) и соответствующее количество экспериментов (`COALESCE(actual_days.num_exp, all_days.zeroes) AS num_exp`), при этом используется функция `COALESCE`, чтобы использовать фактическое количество экспериментов, если оно доступно, или ноль, если нет

### JOIN таблички с собой же

[src/self_join.sql](https://gvwilson.github.io/sql-tutorial/src/self_join.sql)

```
     WITH person AS (
             SELECT ident,
                    personal || ' ' || family AS name
               FROM staff
          )
   SELECT LEFT.name,
          RIGHT.name
     FROM person AS
LEFT JOIN person AS RIGHT
    LIMIT 10;
```

[out/self_join.out](https://gvwilson.github.io/sql-tutorial/out/self_join.out)

```
|     name     |       name       |
|--------------|------------------|
| Kartik Gupta | Kartik Gupta     |
| Kartik Gupta | Divit Dhaliwal   |
| Kartik Gupta | Indrans Sridhar  |
| Kartik Gupta | Pranay Khanna    |
| Kartik Gupta | Riaan Dua        |
| Kartik Gupta | Vedika Rout      |
| Kartik Gupta | Abram Chokshi    |
| Kartik Gupta | Romil Kapoor     |
| Kartik Gupta | Ishaan Ramaswamy |
| Kartik Gupta | Nitya Lal        |
```

- создаём временную общую таблицу `person` с помощью WITH
- объединяем столбцы `personal` и `family` в один столбец `name`
- при помощи `SELECT` выбираем из `person` значения столбца `name` через алиасы `left` и `right`
- после этого происходит объединение `person` с собой с помощью оператора `LEFT JOIN`, при этом таблица алиасируется как `RIGHT`

Этот SQL-код, однако, содержит ошибку, правильный синтаксис должен быть следующим:

```
     WITH person AS (
             SELECT ident,
                    personal || ' ' || family AS name
               FROM staff
          )
   SELECT LEFT.name,
          RIGHT.name
     FROM person AS LEFT
LEFT JOIN person AS RIGHT ON < условие соединения >
    LIMIT 10;
```

В исходном примере условие соединения (`ON`) не было указано

### Генерируем уникальные пары значений

[src/unique_pairs.sql](https://gvwilson.github.io/sql-tutorial/src/unique_pairs.sql)

```
     WITH person AS (
             SELECT ident,
                    personal || ' ' || family AS name
               FROM staff
          )
   SELECT LEFT.name,
          RIGHT.name
     FROM person AS
LEFT JOIN person AS RIGHT ON LEFT.ident < RIGHT.ident
    WHERE LEFT.ident <= 4
      AND RIGHT.ident <= 4;
```

[out/unique_pairs.out](https://gvwilson.github.io/sql-tutorial/out/unique_pairs.out)

```
|      name       |      name       |
|-----------------|-----------------|
| Kartik Gupta    | Divit Dhaliwal  |
| Kartik Gupta    | Indrans Sridhar |
| Kartik Gupta    | Pranay Khanna   |
| Divit Dhaliwal  | Indrans Sridhar |
| Divit Dhaliwal  | Pranay Khanna   |
| Indrans Sridhar | Pranay Khanna   |
```

- создаём временную таблицу `person`, которая содержит результат выбора из таблицы `staff`
- из `person` выбираем значения `left.name` и `right.name` с использованием операции слияния (`JOIN`). В этом случае происходит слияние `person` с собой, причем каждая копия `person` используется в качестве левой и правой таблиц соответственно. Слияние выполняется по условию, что идентификатор слева меньше идентификатора справа.
- затем применяем дополнительное условие с помощью `WHERE`, которое фильтрует результаты `JOIN`-операции. Это условие проверяет, что идентификаторы слева и справа меньше или равны 4

### Фильтрация пар

[src/filter_pairs.sql](https://gvwilson.github.io/sql-tutorial/src/filter_pairs.sql)

```
     WITH person AS (
             SELECT ident,
                    personal || ' ' || family AS name
               FROM staff
          ),
          together AS (
             SELECT LEFT.staff AS left_staff,
                    RIGHT.staff AS right_staff
               FROM performed AS
          LEFT JOIN performed AS RIGHT ON LEFT.experiment = RIGHT.experiment
              WHERE left_staff < right_staff
          )
   SELECT LEFT.name AS person_1,
          RIGHT.name AS person_2
     FROM person AS
LEFT JOIN person AS
    RIGHT JOIN together ON LEFT.ident = left_staff
      AND RIGHT.ident = right_staff;
```

[out/filter_pairs.out](https://gvwilson.github.io/sql-tutorial/out/filter_pairs.out)

```
|    person_1     |     person_2     |
|-----------------|------------------|
| Kartik Gupta    | Vedika Rout      |
| Pranay Khanna   | Vedika Rout      |
| Indrans Sridhar | Romil Kapoor     |
| Abram Chokshi   | Ishaan Ramaswamy |
| Pranay Khanna   | Vedika Rout      |
| Kartik Gupta    | Abram Chokshi    |
| Abram Chokshi   | Romil Kapoor     |
| Kartik Gupta    | Divit Dhaliwal   |
| Divit Dhaliwal  | Abram Chokshi    |
| Pranay Khanna   | Ishaan Ramaswamy |
| Indrans Sridhar | Romil Kapoor     |
| Kartik Gupta    | Ishaan Ramaswamy |
| Kartik Gupta    | Nitya Lal        |
| Kartik Gupta    | Abram Chokshi    |
| Pranay Khanna   | Romil Kapoor     |
```

- во временной табличке `person` выбираем имена сотрудников из таблицы `staff`; используем `personal ' ' family AS name`, чтобы объединить значения из столбцов `personal` и `family`
- временная табличка `together` использует оператор `LEFT JOIN` для объединения таблицы `performed` с собой на основе столбца `experiment`. Затем выбираются пары сотрудников, участвовавших в одном и том же эксперименте, исключая случаи, когда идентификатор левого сотрудника (`left_staff`) больше идентификатора правого сотрудника (`right_staff`)
- затем выполняется основной `SELECT`, который использует `person` и `together` для объединения имен сотрудников на основе их идентификаторов. Он выполняет два `LEFT JOIN`, чтобы объединить `person` с самим собой и затем объединить результат с `together` на основе идентификаторов сотрудников.
- затем выбираются имена сотрудников для отображения в итоговом результате.

### EXISTS

[src/correlated_subquery.sql](https://gvwilson.github.io/sql-tutorial/src/correlated_subquery.sql)

```
SELECT name,
       building
FROM department
WHERE EXISTS
    (SELECT 1
     FROM staff
     WHERE dept = department.ident )
ORDER BY name;
```

[out/correlated_subquery.out](https://gvwilson.github.io/sql-tutorial/out/correlated_subquery.out)

```
|       name        |     building     |
|-------------------|------------------|
| Genetics          | Chesson          |
| Histology         | Fashet Extension |
| Molecular Biology | Chesson          |
```

- выбираем столбцы `name` и `building` из таблицы `department`
- `WHERE EXISTS (SELECT 1 FROM staff WHERE dept = department.ident )` — используем подзапрос, который проверяет существование хотя бы одной записи в таблице `staff`, для которой значение столбца `dept` совпадает с значением столбца `ident` из таблицы `department`
- `ORDER BY name` — устанавливаем порядок сортировки результатов по столбцу `name` в алфавитном порядке

### NOT EXISTS в SQL

[src/nonexistence.sql](https://gvwilson.github.io/sql-tutorial/src/nonexistence.sql)

```
SELECT name,
       building
FROM department
WHERE NOT EXISTS
    (SELECT 1
     FROM staff
     WHERE dept = department.ident )
ORDER BY name;
```

[out/nonexistence.out](https://gvwilson.github.io/sql-tutorial/out/nonexistence.out)

```
|     name      | building |
|---------------|----------|
| Endocrinology | TGVH     |
```

- выбираем столбцы `name` и `building` из таблицы `department`
- `WHERE NOT EXISTS` — выбираем только те записи из `department`, для которых не существует записей в таблице `staff`
- `SELECT 1 FROM staff WHERE dept = department.ident` — проверяем, существуют ли записи в таблице `staff`, связанные с отделом из таблицы `department`
- `ORDER BY name` — сортируем результат по столбцу name

**Избегание коррелированных подзапросов**

[src/avoid_correlated_subqueries.sql](https://gvwilson.github.io/sql-tutorial/src/avoid_correlated_subqueries.sql)

```
SELECT DISTINCT department.name AS name,
                department.building AS building
FROM department
JOIN staff ON department.ident = staff.dept
ORDER BY name;
```

[out/avoid_correlated_subqueries.out](https://gvwilson.github.io/sql-tutorial/out/avoid_correlated_subqueries.out)

```
|       name        |     building     |
|-------------------|------------------|
| Genetics          | Chesson          |
| Histology         | Fashet Extension |
| Molecular Biology | Chesson          |
```

- `SELECT DISTINCT` — выбираем уникальные значения `name` и `building` из таблицы `department`
- `JOIN staff ON department.ident = staff.dept` — объединяем таблицы `department` и `staff` на основе условия, что значение столбца `ident` из `department` равно значению `dept` из `staff`
- `ORDER BY name` — результаты выборки сортируем в алфавитном порядке по столбцу `name`

### Опережение и отставание

[src/lead_lag.sql](https://gvwilson.github.io/sql-tutorial/src/lead_lag.sql)

```
     WITH ym_num AS (
             SELECT strftime ('%Y-%m', started) AS ym,
                    COUNT(*) AS num
               FROM experiment
           GROUP BY ym
          )
   SELECT ym,
          lag (num) OVER (
           ORDER BY ym
          ) AS prev_num,
          num,
          lead (num) OVER (
           ORDER BY ym
          ) AS next_num
     FROM ym_num
 ORDER BY ym;
```

- в этой таблице извлекается случайное число с помощью `random()`
- конкатенируем значения `personal` и `family` под именем `name` с помощью `' '` для разделения
- таким образом создаём временную таблицу, содержащую столбцы `rand` с случайными числами и `name` со значениями из столбцов `personal` и `family` таблицы `staff`
- делаем выборку из временной таблицы `decorated`; в выборку включаем столбцы `rand`, `name`
- `abs(rand) % 10` — это мы вычисляем остаток от деления абсолютного значения `rand` на 10
- ну и в конце оставляем только строки, где `selector` меньше 5

### Создание индексов

[src/create_use_index.sql](https://gvwilson.github.io/sql-tutorial/src/create_use_index.sql)

```
EXPLAIN query PLAN
SELECT filename
FROM plate
WHERE filename like '%07%';

CREATE INDEX plate_file ON plate(filename);

EXPLAIN query PLAN
SELECT filename
FROM plate
WHERE filename like '%07%';
```

[out/create_use_index.out](https://gvwilson.github.io/sql-tutorial/out/create_use_index.out)

```
QUERY PLAN
`--SCAN plate USING COVERING INDEX sqlite_autoindex_plate_1
QUERY PLAN
`--SCAN plate USING COVERING INDEX plate_file
```

- выбираем все значения столбца `filename` из таблицы `plate`, где значение столбца `filename` содержит подстроку `07`
- создаём индекс с именем `plate_file` для столбца `filename` в таблице `plate`
- запрашиваем план выполнения запроса (`EXPLAIN query PLAN`)

### Генерация последовательности значений

[src/generate_sequence.sql](https://gvwilson.github.io/sql-tutorial/src/generate_sequence.sql)

```
SELECT value
FROM generate_series(1, 5);
```

[out/generate_sequence.out](https://gvwilson.github.io/sql-tutorial/out/generate_sequence.out)

```
| value |
|-------|
| 1     |
| 2     |
| 3     |
| 4     |
| 5     |
```

- `generate_series(1, 5)` — генерируем ряд чисел от 1 до 5
- `SELECT value` — выбираем этот столбец `value` со сгенерированными числами от 1 до 5

### Генерируем последовательность на основе данных

[src/data_range_sequence.sql](https://gvwilson.github.io/sql-tutorial/src/data_range_sequence.sql)

```
CREATE TABLE temp (num integer NOT NULL);

INSERT INTO temp
VALUES (1),
       (5);

SELECT value
FROM generate_series ((SELECT min(num)
                       FROM TEMP),
                      (SELECT max(num)
                       FROM TEMP));
```

[out/data_range_sequence.out](https://gvwilson.github.io/sql-tutorial/out/data_range_sequence.out)

```
| value |
|-------|
| 1     |
| 2     |
| 3     |
| 4     |
| 5     |
```

- создаём временную таблицу `temp`, которая содержит 1 столбец с именем `num` типа integer; этот столбец не может быть пустым
- помещаем в `temp` значения 1 и 5 в столбец `num`
- используем `generate_series` для создания последовательности чисел между минимальным и максимальным значениями из столбца `num` в таблице `temp`

### Генерация последовательностей дат

[src/date_sequence.sql](https://gvwilson.github.io/sql-tutorial/src/date_sequence.sql)

```
SELECT date(
              (SELECT julianday(min(started))
               FROM experiment) + value) AS some_day
FROM
  (SELECT value
   FROM generate_series(
                          (SELECT 0),
                          (SELECT count(*) - 1
                           FROM experiment)))
LIMIT 5;
```

[out/date_sequence.out](https://gvwilson.github.io/sql-tutorial/out/date_sequence.out)

```
|  some_day  |
|------------|
| 2023-01-29 |
| 2023-01-30 |
| 2023-01-31 |
| 2023-02-01 |
| 2023-02-02 |
```

- `SELECT julianday(min(started)) FROM experiment` — находим минимальную дату в столбце `started` из `experiment`, преобразуем её в `Julian` день (числовое представление даты) и возвращаем этот `Julian` день
- внешним подзапросом вычисляем разницу между этим минимальным `Julian` днем и каждым `value` из `generate_series`
- затем складываем эти разницы с минимальным `Julian` днем, и конвертируем обратно в дату с помощью `date()`
- ну и выбираем только первые 5 результатов этого вычисления с помощью `LIMIT 5`

### Подсчитываем количество значений за день, без пропусков

[src/experiments_per_day.sql](https://gvwilson.github.io/sql-tutorial/src/experiments_per_day.sql)

```
     WITH -- complete sequence of days with 0 as placeholder for number of experiments
          all_days AS (
             SELECT DATE (
                    (
                       SELECT julianday (MIN(started))
                         FROM experiment
                    ) + VALUE
                    ) AS some_day,
                    0 AS zeroes
               FROM (
                       SELECT VALUE
                         FROM generate_series (
                              (
                                 SELECT 0
                              ),
                              (
                                 SELECT COUNT(*) - 1
                                   FROM experiment
                              )
                              )
                    )
          ), -- sequence of actual days with actual number of experiments started
          actual_days AS (
             SELECT started,
                    COUNT(started) AS num_exp
               FROM experiment
           GROUP BY started
          ) -- combined by joining on day and taking actual number (if available) or zero
   SELECT all_days.some_day AS DAY,
          COALESCE(actual_days.num_exp, all_days.zeroes) AS num_exp
     FROM all_days
LEFT JOIN actual_days ON all_days.some_day = actual_days.started
    LIMIT 5;
```

[out/experiments_per_day.out](https://gvwilson.github.io/sql-tutorial/out/experiments_per_day.out)

```
|    day     | num_exp |
|------------|---------|
| 2023-01-29 | 1       |
| 2023-01-30 | 1       |
| 2023-01-31 | 0       |
| 2023-02-01 | 0       |
| 2023-02-02 | 1       |
```

- создаём последовательность всех дней с нулевым значением в качестве заполнителя для количества экспериментов
- создаём последовательность фактических дней с реальным числом экспериментов
- объединяем эти последовательности, соединяя их по дням и беря реальное количество (если доступно) или ноль
- выводит результат, показывая дни (`all_days.some_day`) и соответствующее количество экспериментов (`COALESCE(actual_days.num_exp, all_days.zeroes) AS num_exp`), при этом используется функция `COALESCE`, чтобы использовать фактическое количество экспериментов, если оно доступно, или ноль, если нет

### JOIN таблички с собой же

[src/self_join.sql](https://gvwilson.github.io/sql-tutorial/src/self_join.sql)

```
     WITH person AS (
             SELECT ident,
                    personal || ' ' || family AS name
               FROM staff
          )
   SELECT LEFT.name,
          RIGHT.name
     FROM person AS
LEFT JOIN person AS RIGHT
    LIMIT 10;
```

[out/self_join.out](https://gvwilson.github.io/sql-tutorial/out/self_join.out)

```
|     name     |       name       |
|--------------|------------------|
| Kartik Gupta | Kartik Gupta     |
| Kartik Gupta | Divit Dhaliwal   |
| Kartik Gupta | Indrans Sridhar  |
| Kartik Gupta | Pranay Khanna    |
| Kartik Gupta | Riaan Dua        |
| Kartik Gupta | Vedika Rout      |
| Kartik Gupta | Abram Chokshi    |
| Kartik Gupta | Romil Kapoor     |
| Kartik Gupta | Ishaan Ramaswamy |
| Kartik Gupta | Nitya Lal        |
```

- создаём временную общую таблицу `person` с помощью WITH
- объединяем столбцы `personal` и `family` в один столбец `name`
- при помощи `SELECT` выбираем из `person` значения столбца `name` через алиасы `left` и `right`
- после этого происходит объединение `person` с собой с помощью оператора `LEFT JOIN`, при этом таблица алиасируется как `RIGHT`

Этот SQL-код, однако, содержит ошибку, правильный синтаксис должен быть следующим:

```
     WITH person AS (
             SELECT ident,
                    personal || ' ' || family AS name
               FROM staff
          )
   SELECT LEFT.name,
          RIGHT.name
     FROM person AS LEFT
LEFT JOIN person AS RIGHT ON < условие соединения >
    LIMIT 10;
```

В исходном примере условие соединения (`ON`) не было указано

### Генерируем уникальные пары значений

[src/unique_pairs.sql](https://gvwilson.github.io/sql-tutorial/src/unique_pairs.sql)

```
     WITH person AS (
             SELECT ident,
                    personal || ' ' || family AS name
               FROM staff
          )
   SELECT LEFT.name,
          RIGHT.name
     FROM person AS
LEFT JOIN person AS RIGHT ON LEFT.ident < RIGHT.ident
    WHERE LEFT.ident <= 4
      AND RIGHT.ident <= 4;
```

[out/unique_pairs.out](https://gvwilson.github.io/sql-tutorial/out/unique_pairs.out)

```
|      name       |      name       |
|-----------------|-----------------|
| Kartik Gupta    | Divit Dhaliwal  |
| Kartik Gupta    | Indrans Sridhar |
| Kartik Gupta    | Pranay Khanna   |
| Divit Dhaliwal  | Indrans Sridhar |
| Divit Dhaliwal  | Pranay Khanna   |
| Indrans Sridhar | Pranay Khanna   |
```

- создаём временную таблицу `person`, которая содержит результат выбора из таблицы `staff`
- из `person` выбираем значения `left.name` и `right.name` с использованием операции слияния (`JOIN`). В этом случае происходит слияние `person` с собой, причем каждая копия `person` используется в качестве левой и правой таблиц соответственно. Слияние выполняется по условию, что идентификатор слева меньше идентификатора справа.
- затем применяем дополнительное условие с помощью `WHERE`, которое фильтрует результаты `JOIN`-операции. Это условие проверяет, что идентификаторы слева и справа меньше или равны 4

### Фильтрация пар

[src/filter_pairs.sql](https://gvwilson.github.io/sql-tutorial/src/filter_pairs.sql)

```
     WITH person AS (
             SELECT ident,
                    personal || ' ' || family AS name
               FROM staff
          ),
          together AS (
             SELECT LEFT.staff AS left_staff,
                    RIGHT.staff AS right_staff
               FROM performed AS
          LEFT JOIN performed AS RIGHT ON LEFT.experiment = RIGHT.experiment
              WHERE left_staff < right_staff
          )
   SELECT LEFT.name AS person_1,
          RIGHT.name AS person_2
     FROM person AS
LEFT JOIN person AS
    RIGHT JOIN together ON LEFT.ident = left_staff
      AND RIGHT.ident = right_staff;
```

[out/filter_pairs.out](https://gvwilson.github.io/sql-tutorial/out/filter_pairs.out)

```
|    person_1     |     person_2     |
|-----------------|------------------|
| Kartik Gupta    | Vedika Rout      |
| Pranay Khanna   | Vedika Rout      |
| Indrans Sridhar | Romil Kapoor     |
| Abram Chokshi   | Ishaan Ramaswamy |
| Pranay Khanna   | Vedika Rout      |
| Kartik Gupta    | Abram Chokshi    |
| Abram Chokshi   | Romil Kapoor     |
| Kartik Gupta    | Divit Dhaliwal   |
| Divit Dhaliwal  | Abram Chokshi    |
| Pranay Khanna   | Ishaan Ramaswamy |
| Indrans Sridhar | Romil Kapoor     |
| Kartik Gupta    | Ishaan Ramaswamy |
| Kartik Gupta    | Nitya Lal        |
| Kartik Gupta    | Abram Chokshi    |
| Pranay Khanna   | Romil Kapoor     |
```

- во временной табличке `person` выбираем имена сотрудников из таблицы `staff`; используем `personal ' ' family AS name`, чтобы объединить значения из столбцов `personal` и `family`
- временная табличка `together` использует оператор `LEFT JOIN` для объединения таблицы `performed` с собой на основе столбца `experiment`. Затем выбираются пары сотрудников, участвовавших в одном и том же эксперименте, исключая случаи, когда идентификатор левого сотрудника (`left_staff`) больше идентификатора правого сотрудника (`right_staff`)
- затем выполняется основной `SELECT`, который использует `person` и `together` для объединения имен сотрудников на основе их идентификаторов. Он выполняет два `LEFT JOIN`, чтобы объединить `person` с самим собой и затем объединить результат с `together` на основе идентификаторов сотрудников.
- затем выбираются имена сотрудников для отображения в итоговом результате.

### EXISTS

[src/correlated_subquery.sql](https://gvwilson.github.io/sql-tutorial/src/correlated_subquery.sql)

```
SELECT name,
       building
FROM department
WHERE EXISTS
    (SELECT 1
     FROM staff
     WHERE dept = department.ident )
ORDER BY name;
```

[out/correlated_subquery.out](https://gvwilson.github.io/sql-tutorial/out/correlated_subquery.out)

```
|       name        |     building     |
|-------------------|------------------|
| Genetics          | Chesson          |
| Histology         | Fashet Extension |
| Molecular Biology | Chesson          |
```

- выбираем столбцы `name` и `building` из таблицы `department`
- `WHERE EXISTS (SELECT 1 FROM staff WHERE dept = department.ident )` — используем подзапрос, который проверяет существование хотя бы одной записи в таблице `staff`, для которой значение столбца `dept` совпадает с значением столбца `ident` из таблицы `department`
- `ORDER BY name` — устанавливаем порядок сортировки результатов по столбцу `name` в алфавитном порядке

### NOT EXISTS в SQL

[src/nonexistence.sql](https://gvwilson.github.io/sql-tutorial/src/nonexistence.sql)

```
SELECT name,
       building
FROM department
WHERE NOT EXISTS
    (SELECT 1
     FROM staff
     WHERE dept = department.ident )
ORDER BY name;
```

[out/nonexistence.out](https://gvwilson.github.io/sql-tutorial/out/nonexistence.out)

```
|     name      | building |
|---------------|----------|
| Endocrinology | TGVH     |
```

- выбираем столбцы `name` и `building` из таблицы `department`
- `WHERE NOT EXISTS` — выбираем только те записи из `department`, для которых не существует записей в таблице `staff`
- `SELECT 1 FROM staff WHERE dept = department.ident` — проверяем, существуют ли записи в таблице `staff`, связанные с отделом из таблицы `department`
- `ORDER BY name` — сортируем результат по столбцу name

**Избегание коррелированных подзапросов**

[src/avoid_correlated_subqueries.sql](https://gvwilson.github.io/sql-tutorial/src/avoid_correlated_subqueries.sql)

```
SELECT DISTINCT department.name AS name,
                department.building AS building
FROM department
JOIN staff ON department.ident = staff.dept
ORDER BY name;
```

[out/avoid_correlated_subqueries.out](https://gvwilson.github.io/sql-tutorial/out/avoid_correlated_subqueries.out)

```
|       name        |     building     |
|-------------------|------------------|
| Genetics          | Chesson          |
| Histology         | Fashet Extension |
| Molecular Biology | Chesson          |
```

- `SELECT DISTINCT` — выбираем уникальные значения `name` и `building` из таблицы `department`
- `JOIN staff ON department.ident = staff.dept` — объединяем таблицы `department` и `staff` на основе условия, что значение столбца `ident` из `department` равно значению `dept` из `staff`
- `ORDER BY name` — результаты выборки сортируем в алфавитном порядке по столбцу `name`

### Опережение и отставание

[src/lead_lag.sql](https://gvwilson.github.io/sql-tutorial/src/lead_lag.sql)

```
     WITH ym_num AS (
             SELECT strftime ('%Y-%m', started) AS ym,
                    COUNT(*) AS num
               FROM experiment
           GROUP BY ym
          )
   SELECT ym,
          lag (num) OVER (
           ORDER BY ym
          ) AS prev_num,
          num,
          lead (num) OVER (
           ORDER BY ym
          ) AS next_num
     FROM ym_num
 ORDER BY ym;
```

[out/lead_lag.out](https://gvwilson.github.io/sql-tutorial/out/lead_lag.out)

```
|   ym    | prev_num | num | next_num |
|---------|----------|-----|----------|
| 2023-01 |          | 2   | 5        |
| 2023-02 | 2        | 5   | 5        |
| 2023-03 | 5        | 5   | 1        |
| 2023-04 | 5        | 1   | 6        |
| 2023-05 | 1        | 6   | 5        |
| 2023-06 | 6        | 5   | 3        |
| 2023-07 | 5        | 3   | 2        |
| 2023-08 | 3        | 2   | 4        |
| 2023-09 | 2        | 4   | 6        |
| 2023-10 | 4        | 6   | 4        |
| 2023-12 | 6        | 4   | 5        |
| 2024-01 | 4        | 5   | 2        |
| 2024-02 | 5        | 2   |          |
```

- создаём временную таблицу `ym_num` из 2 столбцов: `ym` (год-месяц ‘YYYY-MM’) и `num` (количество записей в каждом месяце)
- используем SQLite `strftime` для извлечения года и месяца из `started`, агрегируем результаты с помощью `GROUP BY`
- в основном запросе выбираем данные из `ym_num`, выполняем следующие операции и получаем год-месяц `ym`, количество записей в предыдущем месяце `(lag)num`, текущее количество записей `num` и количество записей в следующем месяце `(lead)num`
- результаты упорядочиваем по столбцу `ym` (год-месяц)

### Оконные функции

[src/window_functions.sql](https://gvwilson.github.io/sql-tutorial/src/window_functions.sql)

```
     WITH ym_num AS (
             SELECT strftime ('%Y-%m', started) AS ym,
                    COUNT(*) AS num
               FROM experiment
           GROUP BY ym
          )
   SELECT ym,
          num,
          SUM(num) OVER (
           ORDER BY ym
          ) AS num_done,
          CUME_DIST() OVER (
           ORDER BY ym
          ) AS progress
     FROM ym_num
 ORDER BY ym;
```

[out/window_functions.out](https://gvwilson.github.io/sql-tutorial/out/window_functions.out)

```
|   ym    | num | num_done |      progress      |
|---------|-----|----------|--------------------|
| 2023-01 | 2   | 2        | 0.0769230769230769 |
| 2023-02 | 5   | 7        | 0.153846153846154  |
| 2023-03 | 5   | 12       | 0.230769230769231  |
| 2023-04 | 1   | 13       | 0.307692307692308  |
| 2023-05 | 6   | 19       | 0.384615384615385  |
| 2023-06 | 5   | 24       | 0.461538461538462  |
| 2023-07 | 3   | 27       | 0.538461538461538  |
| 2023-08 | 2   | 29       | 0.615384615384615  |
| 2023-09 | 4   | 33       | 0.692307692307692  |
| 2023-10 | 6   | 39       | 0.769230769230769  |
| 2023-12 | 4   | 43       | 0.846153846153846  |
| 2024-01 | 5   | 48       | 0.923076923076923  |
| 2024-02 | 2   | 50       | 1.0                |
```

- создаём временную таблицу `ym_num`, которая содержит: `ym` — год и месяц, извлеченные из `started` в `experiment` с помощью `strftime('%Y-%m')`; `num` — количество записей в `experiment` для каждого сочетания года и месяца
- выбираем `ym` и `num` из таблицы `ym_num`, добавляем 2 дополнительных столбца: `num_done` — сумма количества экспериментов по всем предыдущим годам и месяцам (`sum(num) OVER (ORDER BY ym)`); `progress` — кумулятивное распределение количества экспериментов по всем предыдущим годам и месяцам (`cume_dist() OVER (ORDER BY ym)`)
- упорядочиваем результаты по столбцу `ym` (год и месяц)

**Внезапно небольшое задание: объясните, что делает запрос ниже**

[src/explain_window_function.sql](https://gvwilson.github.io/sql-tutorial/src/explain_window_function.sql)

```
EXPLAIN query PLAN
     WITH ym_num AS (
             SELECT strftime ('%Y-%m', started) AS ym,
                    COUNT(*) AS num
               FROM experiment
           GROUP BY ym
          )
   SELECT ym,
          num,
          SUM(num) OVER (
           ORDER BY ym
          ) AS num_done,
          CUME_DIST() OVER (
           ORDER BY ym
          ) AS progress
     FROM ym_num
 ORDER BY ym;
```

[out/explain_window_function.out](https://gvwilson.github.io/sql-tutorial/out/explain_window_function.out)

```
QUERY PLAN
|--CO-ROUTINE (subquery-3)
|  |--CO-ROUTINE (subquery-4)
|  |  |--CO-ROUTINE ym_num
|  |  |  |--SCAN experiment
|  |  |  `--USE TEMP B-TREE FOR GROUP BY
|  |  |--SCAN ym_num
|  |  `--USE TEMP B-TREE FOR ORDER BY
|  `--SCAN (subquery-4)
`--SCAN (subquery-3)
```

- создаём временную табличку `ym_num` с результатами агрегирования по месяцам, где данные из `started`преобразуются в формат год-месяц (`strftime('%Y-%m', started) AS ym`) и подсчитываем количество событий (`count(*) AS num`)
- группируем результаты по полю `ym`
- выбираем поля `ym` и `num` из `ym_num` и добавляем 2 дополнительных поля: `num_done` и `progress`; `num_done` — общее количество событий/мес, сгруппированных в порядке увеличения месяца; поле `progress` — прогресс в процентном соотношении относительно общего числа записей (`cume_dist()`)
- в итоге выводим данные в порядке увеличения значения `ym` (год-месяц)

### Используем PARTITION BY в SQL

[src/partition_window.sql](https://gvwilson.github.io/sql-tutorial/src/partition_window.sql)

```
WITH y_m_num AS
  (SELECT strftime('%Y', started) AS YEAR,
          strftime('%m', started) AS MONTH,
          count(*) AS num
   FROM experiment
   GROUP BY YEAR,
            MONTH)
SELECT YEAR,
       MONTH,
       num,
       sum(num) OVER (PARTITION BY YEAR
                      ORDER BY MONTH) AS num_done
FROM y_m_num
ORDER BY YEAR,
         MONTH;
```

[out/partition_window.out](https://gvwilson.github.io/sql-tutorial/out/partition_window.out)

```
| year | month | num | num_done |
|------|-------|-----|----------|
| 2023 | 01    | 2   | 2        |
| 2023 | 02    | 5   | 7        |
| 2023 | 03    | 5   | 12       |
| 2023 | 04    | 1   | 13       |
| 2023 | 05    | 6   | 19       |
| 2023 | 06    | 5   | 24       |
| 2023 | 07    | 3   | 27       |
| 2023 | 08    | 2   | 29       |
| 2023 | 09    | 4   | 33       |
| 2023 | 10    | 6   | 39       |
| 2023 | 12    | 4   | 43       |
| 2024 | 01    | 5   | 5        |
| 2024 | 02    | 2   | 7        |
```

- создаём временную таблицу `y_m_num` с тремя столбцами: `YEAR,` `MONTH` и `num`.
- временную табличку заполняем записями из `experiment`. Для каждой записи определяем год и месяц даты в столбце `started` (через `strftime`), считаем количество записей (`count(*)`) для каждого года и месяца, группируем результаты по году и месяцу
- выбираем данные из `y_m_num`, добавляем столбец `num_done` — накопительное значение для `num` в пределах каждого года `sum(num) OVER (PARTITION BY YEAR ORDER BY MONTH)` — суммируем значение `num` для каждого месяца при сортировке по месяцам внутри каждого года
- в итоге сортируем результаты по году и месяцу с помощью `ORDER BY YEAR, MONTH`

### Данные типа blob

[src/blob.sql](https://gvwilson.github.io/sql-tutorial/src/blob.sql)

```
CREATE TABLE images (name text NOT NULL,
                               content blob);

INSERT INTO images(name, content)
VALUES ("biohazard", readfile("img/biohazard.png")),
       ("crush", readfile("img/crush.png")),
       ("fire", readfile("img/fire.png")),
       ("radioactive", readfile("img/radioactive.png")),
       ("tripping", readfile("img/tripping.png"));

SELECT name,
       length(content)
FROM images;
```

[out/blob.out](https://gvwilson.github.io/sql-tutorial/out/blob.out)

```
|    name     | length(content) |
|-------------|-----------------|
| biohazard   | 19629           |
| crush       | 15967           |
| fire        | 18699           |
| radioactive | 16661           |
| tripping    | 17208           |
```

- создаём таблицу `images` со столбцами: `name` — текстовый столбец, не может быть пустым; `content` — столбец двоичных данных (`blob`)
- вставляем 5 пар `name`–`blob` в `images` с помощью `INSERT INTO`
- `readfile` читает содержимое файла `name` и возвращает его как двоичные данные `blob`
- выполняем выборку данных из `images` с помощью `SELECT`, получая значения `name` и вычисляя количество байт двоичных данных в `content`

**Ещё одна БД**

[src/lab_log_db.sh](https://gvwilson.github.io/sql-tutorial/src/lab_log_db.sh)

```
sqlite3 data/lab_log.db
```

[src/lab_log_schema.sql](https://gvwilson.github.io/sql-tutorial/src/lab_log_schema.sql)

```
.schema
```

[out/lab_log_schema.out](https://gvwilson.github.io/sql-tutorial/out/lab_log_schema.out)

```
CREATE TABLE sqlite_sequence(name,
                             seq);

CREATE TABLE person(ident integer PRIMARY KEY autoincrement,
                                              details text NOT NULL);

CREATE TABLE machine(ident integer PRIMARY KEY autoincrement,
                                               name text NOT NULL,
                                                         details text NOT NULL);

CREATE TABLE usage(ident integer PRIMARY KEY autoincrement,
                                             log text NOT NULL);
```

- создаём `sqlite_sequence` со столбцами `name` и `seq` (для значения счетчика, он используется в качестве `AUTOINCREMENT`)
- создаём `person` со столбцами `ident` (целочисленный, является первичным ключом (`PRIMARY KEY`), автоматически инкрементируется) и `details` (текстовый столбец, не может иметь значение `NULL`)
- создаём `machine` со столбцами `ident` (целочисленный, является первичным ключом (`PRIMARY KEY`), автоматически инкрементируется), `name` (текстовый, не может иметь значение `NULL`), `details`(текстовый, не может иметь значение `NULL`)
- создаём `usage` со столбцами `ident` (целочисленный, является первичным ключом (`PRIMARY KEY`), автоматически инкрементируется) и `log` (текстовый столбец, не может иметь значение `NULL`)

### Сохранение JSON

[src/json_in_table.sql](https://gvwilson.github.io/sql-tutorial/src/json_in_table.sql)

```
SELECT *
FROM machine;
```

[out/json_in_table.out](https://gvwilson.github.io/sql-tutorial/out/json_in_table.out)

```
| ident |      name      |                         details                         |
|-------|----------------|---------------------------------------------------------|
| 1     | WY401          | {"acquired": "2023-05-01"}                              |
| 2     | Inphormex      | {"acquired": "2021-07-15", "refurbished": "2023-10-22"} |
| 3     | AutoPlate 9000 | {"note": "needs software update"}                       |
```

### Выбираем отдельные поля в JSON

[src/json_field.sql](https://gvwilson.github.io/sql-tutorial/src/json_field.sql)

```
SELECT details->'$.acquired' AS single_arrow,
                details->>'$.acquired' AS double_arrow
FROM machine;
```

[out/json_field.out](https://gvwilson.github.io/sql-tutorial/out/json_field.out)

```
| single_arrow | double_arrow |
|--------------|--------------|
| "2023-05-01" | 2023-05-01   |
| "2021-07-15" | 2021-07-15   |
|              |              |
```

- `details->'$.acquired' AS single_arrow` — с помощью `->` извлекаем значение JSON поля `acquired` из столбца `details` для каждой строки из `machine`, обозначаем его как `single_arrow`
- `details->>'$.acquired' AS double_arrow` — оператор `->>` также используется для извлечения JSON по указанному пути, но возвращает текст, в отличие от `->`, который возвращает JSON значение; здесь мы извлекаем значение JSON поля `acquired` из столбца `details` для каждой строки из `machine`, обозначаем его как `double_arrow`

### Доступ к JSON-объекту

[src/json_array.sql](https://gvwilson.github.io/sql-tutorial/src/json_array.sql)

```
SELECT ident,
       json_array_length(log->'$') AS LENGTH,
       log->'$[0]' AS FIRST
FROM USAGE;
```

[out/json_array.out](https://gvwilson.github.io/sql-tutorial/out/json_array.out)

```
| ident | length |                            first                             |
|-------|--------|--------------------------------------------------------------|
| 1     | 4      | {"machine":"Inphormex","person":["Gabrielle","Dub\u00e9"]}   |
| 2     | 5      | {"machine":"Inphormex","person":["Marianne","Richer"]}       |
| 3     | 2      | {"machine":"sterilizer","person":["Josette","Villeneuve"]}   |
| 4     | 1      | {"machine":"sterilizer","person":["Maude","Goulet"]}         |
| 5     | 2      | {"machine":"AutoPlate 9000","person":["Brigitte","Michaud"]} |
| 6     | 1      | {"machine":"sterilizer","person":["Marianne","Richer"]}      |
| 7     | 3      | {"machine":"WY401","person":["Maude","Goulet"]}              |
| 8     | 1      | {"machine":"AutoPlate 9000"}                                 |
```

- `json_array_length(log->'$') AS LENGTH` — вычисляем длину массива, находящегося внутри JSON-объекта в столбце `log`; используем оператор `->`, чтобы получить массив из корневого уровня JSON-объекта, и `json_array_length` для подсчета количества элементов в этом массиве; результат помещаем в столбец с именем `LENGTH`
- `log->'$[0]' AS FIRST` — извлекаем первый элемент из массива, указанного в корневом уровне JSON-объекта в столбце `log`; используем оператор `->`, чтобы получить доступ к массиву, и указываем индекс элемента в квадратных скобках; результат сохраняем в столбец `FIRST`

### Распаковка JSON

[src/json_unpack.sql](https://gvwilson.github.io/sql-tutorial/src/json_unpack.sql)

```
SELECT ident,
       json_each.key AS KEY,
       json_each.value AS value
FROM USAGE,
     json_each(usage.log)
LIMIT 10;
```

[out/json_unpack.out](https://gvwilson.github.io/sql-tutorial/out/json_unpack.out)

```
| ident | key |                            value                             |
|-------|-----|--------------------------------------------------------------|
| 1     | 0   | {"machine":"Inphormex","person":["Gabrielle","Dub\u00e9"]}   |
| 1     | 1   | {"machine":"Inphormex","person":["Gabrielle","Dub\u00e9"]}   |
| 1     | 2   | {"machine":"WY401","person":["Gabrielle","Dub\u00e9"]}       |
| 1     | 3   | {"machine":"Inphormex","person":["Gabrielle","Dub\u00e9"]}   |
| 2     | 0   | {"machine":"Inphormex","person":["Marianne","Richer"]}       |
| 2     | 1   | {"machine":"AutoPlate 9000","person":["Marianne","Richer"]}  |
| 2     | 2   | {"machine":"sterilizer","person":["Marianne","Richer"]}      |
| 2     | 3   | {"machine":"AutoPlate 9000","person":["Monique","Marcotte"]} |
| 2     | 4   | {"machine":"sterilizer","person":["Marianne","Richer"]}      |
| 3     | 0   | {"machine":"sterilizer","person":["Josette","Villeneuve"]}   |
```

- `SELECT ident, json_each.key AS KEY, json_each.value AS value` — определяем, что нужно выбрать из таблицы `usage` и JSON-объектов, распарсенных с помощью функции `json_each`; из каждой строки выбираем идентификатор, а также ключ и его значение из каждого JSON-объекта в столбце `log`
- `FROM usage, json_each(usage.log)` — указываем источник данных для выборки; `usage` указывается после ключевого слова FROM, а `json_each` вызывается перед `log`, чтобы разобрать JSON-объекты из этого столбца
- `LIMIT 10` — выбираем только первые 10 строк

### Последний элемент в массиве

[src/json_array_last.sql](https://gvwilson.github.io/sql-tutorial/src/json_array_last.sql)

```
SELECT ident,
       log->'$[#-1].machine' AS FINAL
FROM USAGE
LIMIT 5;
```

[out/json_array_last.out](https://gvwilson.github.io/sql-tutorial/out/json_array_last.out)

```
| ident |    final     |
|-------|--------------|
| 1     | "Inphormex"  |
| 2     | "sterilizer" |
| 3     | "Inphormex"  |
| 4     | "sterilizer" |
| 5     | "sterilizer" |
```

- `SELECT ident, log->'$[#-1].machine' AS FINAL` — выбираем 2 столбца из `machine`; `ident`возвращается как есть, а столбец `log` обрабатывается так:
    - `log->'$[#-1].machine` — извлекаем данные из столбца `log` (`->` используется для доступа к JSON-полю в столбце `log`)
    - `$[#-1]` — обращаемся к последнему элементу массива, который хранится в `log`
    - `.machine` — хотим извлечь значение поля `machine` из объекта, находящегося в последнем элементе массива

### Модифицируем JSON

[src/json_modify.sql](https://gvwilson.github.io/sql-tutorial/src/json_modify.sql)

```
SELECT ident,
       name,
       json_set(details, '$.sold', json_quote('2024-01-25')) AS updated
FROM machine;
```

[out/json_modify.out](https://gvwilson.github.io/sql-tutorial/out/json_modify.out)

```
| ident |      name      |                           updated                            |
|-------|----------------|--------------------------------------------------------------|
| 1     | WY401          | {"acquired":"2023-05-01","sold":"2024-01-25"}                |
| 2     | Inphormex      | {"acquired":"2021-07-15","refurbished":"2023-10-22","sold":" |
|       |                | 2024-01-25"}                                                 |
| 3     | AutoPlate 9000 | {"note":"needs software update","sold":"2024-01-25"}         |
```

- `SELECT ident, name, ... FROM machine;` — выбираем значения столбцов `ident` и `name` из таблицы `machine`
- `json_set(details, '$.sold', json_quote('2024-01-25')) AS updated` — при помощи `json_set`обновляем JSON-объект в столбце `details`; функция добавляет/изменяет свойство `sold` в JSON-объекте в столбце `details`, присваивая ему новое значение, полученное с помощью функции `json_quote`; результат сохраняем как `updated`

**Обновляем табличку penguins:**

[src/count_penguins.sql](https://gvwilson.github.io/sql-tutorial/src/count_penguins.sql)

```
SELECT species,
       count(*) AS num
FROM penguins
GROUP BY species;
```

[out/count_penguins.out](https://gvwilson.github.io/sql-tutorial/out/count_penguins.out)

```
|  species  | num |
|-----------|-----|
| Adelie    | 152 |
| Chinstrap | 68  |
| Gentoo    | 124 |
```

### Immediate If в SQL

[src/make_active.sql](https://gvwilson.github.io/sql-tutorial/src/make_active.sql)

```
ALTER TABLE penguins ADD active integer NOT NULL DEFAULT 1;

UPDATE penguins
SET active = iif(species = 'Adelie', 0, 1);
```

- изменяем таблицу `penguins`, добавляя новый столбец `active` типа `integer`, который не может содержать значение `NULL`, и устанавливаем значение по умолчанию 1 для всех строк
- обновляем значения в столбце `active` в `penguins`; значение столбца `active` устанавливается на 0, если значение в `species` равно `'Adelie'`, иначе устанавливается на 1
- функция `IIF` (Immediate If) используется здесь для реализации условного выражения (1 аргумент – условие, 2 – результат, если условие истинно, и 3 – результат, если условие ложно)

[src/active_penguins.sql](https://gvwilson.github.io/sql-tutorial/src/active_penguins.sql)

```
SELECT species,
       count(*) AS num
FROM penguins
WHERE active
GROUP BY species;
```

[out/active_penguins.out](https://gvwilson.github.io/sql-tutorial/out/active_penguins.out)

```
|  species  | num |
|-----------|-----|
| Chinstrap | 68  |
| Gentoo    | 124 |
```

### Представление VIEW в SQL

[src/views.sql](https://gvwilson.github.io/sql-tutorial/src/views.sql)

```
CREATE VIEW IF NOT EXISTS active_penguins (species, island, bill_length_mm, bill_depth_mm, flipper_length_mm, body_mass_g, sex) AS
SELECT species,
       island,
       bill_length_mm,
       bill_depth_mm,
       flipper_length_mm,
       body_mass_g,
       sex
FROM penguins
WHERE active;

SELECT species,
       count(*) AS num
FROM active_penguins
GROUP BY species;
```

[out/views.out](https://gvwilson.github.io/sql-tutorial/out/views.out)

```
|  species  | num |
|-----------|-----|
| Chinstrap | 68  |
| Gentoo    | 124 |
```

- создаём представление (`VIEW`) с именем `active_penguins`, если его еще не существует
- представление содержит столбцы `species`, `island`, `bill_length_mm`, `bill_depth_mm`, `flipper_length_mm`, `body_mass_g`, и `sex`; данные для представления берутся из `penguins`, при условии, что пингвины являются активными (`WHERE active`)
- выполняем выборку из представления `active_penguins`: выбираем вид пингвина (`species`) и количество таких пингвинов (`num`), удовлетворяющих условиям, заданным в представлении `active_penguins`

**Напоминание о часах работы:**

[src/all_jobs.sql](https://gvwilson.github.io/sql-tutorial/src/all_jobs.sql)

```
CREATE TABLE job (name text NOT NULL,
                  billable real NOT NULL);

INSERT INTO job
VALUES ('calibrate', 1.5),
       ('clean', 0.5);

SELECT *
FROM job;
```

[out/all_jobs.out](https://gvwilson.github.io/sql-tutorial/out/all_jobs.out)

```
|   name    | billable |
|-----------|----------|
| calibrate | 1.5      |
| clean     | 0.5      |
```

### Добавляем проверку CHECK

[src/all_jobs_check.sql](https://gvwilson.github.io/sql-tutorial/src/all_jobs_check.sql)

```
CREATE TABLE job (name text NOT NULL,
                  billable real NOT NULL, CHECK (billable > 0.0));

INSERT INTO job
VALUES ('calibrate', 1.5);

INSERT INTO job
VALUES ('reset', -0.5);

SELECT *
FROM job;
```

[out/all_jobs_check.out](https://gvwilson.github.io/sql-tutorial/out/all_jobs_check.out)

```
Runtime error near line 9: CHECK constraint failed: billable > 0.0 (19)
|   name    | billable |
|-----------|----------|
| calibrate | 1.5      |
```

- создаём таблицу `job` с 2 столбцами, которые не могут быть пустыми: `name` (текстовый тип данных) и `billable` (вещественные тип данных)
- ограничение (`CHECK`) гарантирует, что значение столбца `billable` должно быть больше чем 0.0
- добавляем новую запись в `job` с указанными значениями `'calibrate'` для столбца `name` и 1.5 для столбца `billable` — сейчас под условие `CHECK` это попадает
- пытаемся добавить еще одну запись в таблицу `job` с указанными значениями `'reset'` для столбца `name`и -0.5 для столбца `billable`. Однако, так как -0.5 меньше либо равно 0.0, то это нарушает условие `CHECK`

**ACID**

ACID — это акроним, который описывает набор свойств транзакций баз данных, предназначенных для обеспечения целостности данных в случае ошибок, сбоев питания и других непредвиденных ситуаций:

1. **Атомарность (Atomicity)**: Транзакция должна быть атомарной, что означает, что она должна быть выполнена целиком или не выполнена вообще. Если одна часть транзакции не может быть выполнена, то все изменения, сделанные в рамках этой транзакции, должны быть отменены.
2. **Согласованность (Consistency)**: Транзакция должна приводить базу данных из одного согласованного состояния в другое согласованное состояние. Это означает, что все правила и ограничения, установленные на данные, должны быть соблюдены во время выполнения транзакции.
3. **Изолированность (Isolation)**: Транзакции должны быть изолированы друг от друга, чтобы предотвратить взаимное влияние. Каждая транзакция должна быть выполнена так, как если бы она была единственной выполняемой транзакцией в базе данных. Это гарантирует, что результаты одной транзакции не будут видны другим транзакциям до их завершения.
4. **Долговечность (Durability)**: Результаты выполненной транзакции должны быть постоянными и доступными даже в случае сбоя системы или перезагрузки. Это достигается путем записи изменений в постоянное хранилище, например SSD.

### TRANSACTION в SQL

[src/transaction.sql](https://gvwilson.github.io/sql-tutorial/src/transaction.sql)

```
CREATE TABLE job (name text NOT NULL,
                  billable real NOT NULL, CHECK (billable > 0.0));

INSERT INTO job
VALUES ('calibrate', 1.5);
BEGIN TRANSACTION;

INSERT INTO job
VALUES ('clean', 0.5);
ROLLBACK;

SELECT *
FROM job;
```

[out/transaction.out](https://gvwilson.github.io/sql-tutorial/out/transaction.out)

```
|   name    | billable |
|-----------|----------|
| calibrate | 1.5      |
```

- создаём таблицу `job` с 2 колонками, которые не могут быть пустыми:
    - `name` текстового типа
    - `billable` с типом данных `real` (вещественное число) и условием `CHECK (billable > 0.0)`, что гарантирует, что значение `billable` больше 0.0
- добавляем в `job` запись: `('calibrate', 1.5)`
- начинаем новую транзакцию.
- добавляем другую запись в таблицу `job`: `('clean', 0.5)`
- откатываем последнюю транзакцию, добавляя `'clean', 0.5`, поэтому данная строка не сохраняется

### ROLLBACK в SQL

[src/rollback_constraint.sql](https://gvwilson.github.io/sql-tutorial/src/rollback_constraint.sql)

```
   CREATE TABLE job (
          name text NOT NULL,
          billable real NOT NULL,
          CHECK (billable > 0.0) ON CONFLICT ROLLBACK
          );

   INSERT INTO job
   VALUES ('calibrate', 1.5);

   INSERT INTO job
   VALUES ('clean', 0.5),
          ('reset', -0.5);

   SELECT *
     FROM job;
```

[out/rollback_constraint.out](https://gvwilson.github.io/sql-tutorial/out/rollback_constraint.out)

```
Runtime error near line 11: CHECK constraint failed: billable > 0.0 (19)
|   name    | billable |
|-----------|----------|
| calibrate | 1.5      |
```

- создаём новую таблицу с именем `job` и 2 непустыми столбцами: текстовым `name` и вещественным `billable`
- значение в `billable` должно быть больше 0 (`CHECK (billable > 0.0)`)
- добавляем в `job` запись с именем `calibrate` со значением `billable` 1.5
- вторая запись с именем `clean` имеет значение `billable` равное 0.5
- третья запись с именем `reset` имеет значение `billable` равное -0.5 — тут возникает проблема с записью третьей строки, так как это нарушает ограничение `CHECK (billable > 0.0)`

### Откат с помощью ROLLBACK

[src/rollback_statement.sql](https://gvwilson.github.io/sql-tutorial/src/rollback_statement.sql)

```
CREATE TABLE job (name text NOT NULL,
                            billable real NOT NULL,
                                          CHECK (billable > 0.0));
INSERT OR ROLLBACK INTO job
VALUES ('calibrate', 1.5);

INSERT OR ROLLBACK INTO job
VALUES ('clean', 0.5),
       ('reset', -0.5);
       
SELECT *
FROM job;
```

[out/rollback_statement.out](https://gvwilson.github.io/sql-tutorial/out/rollback_statement.out)

```
Runtime error near line 11: CHECK constraint failed: billable > 0.0 (19)
|   name    | billable |
|-----------|----------|
| calibrate | 1.5      |
```

- создаём таблицу `job` с 2 непустыми столбцами:
    - текстовым столбцом `name`
    - вещественнозначным `billable` с ограничением `CHECK (billable > 0.0)` — значение в этом столбце всегда будет больше нуля
- вставляем данные в `job` с помощью оператора `INSERT OR ROLLBACK`, а именно одну запись с названием `calibrate` и значением `billable` равным 1.5
- вставляем ещё 2 записи в таблицу `job` с помощью оператора `INSERT OR ROLLBACK`: `clean` со значением 0.5 для `billable`, `reset` со значением -0.5 `billable` (что не подходит по условию `CHECK`)

### Вставка значений

[src/upsert.sql](https://gvwilson.github.io/sql-tutorial/src/upsert.sql)

```
CREATE TABLE jobs_done (person text UNIQUE,
                                    num integer DEFAULT 0);

INSERT INTO jobs_done
VALUES("zia", 1);

.print "after first"
SELECT *
FROM jobs_done;

.print
INSERT INTO jobs_done
VALUES("zia", 1);

.print "after failed"
SELECT *
FROM jobs_done;

INSERT INTO jobs_done
VALUES("zia", 1) ON conflict(person) DO
UPDATE
SET num = num + 1;

.print "\nafter upsert"
SELECT *
FROM jobs_done;
```

[out/upsert.out](https://gvwilson.github.io/sql-tutorial/out/upsert.out)

```
after first
| person | num |
|--------|-----|
| zia    | 1   |

Runtime error near line 14: UNIQUE constraint failed: jobs_done.person (19)
after failed
| person | num |
|--------|-----|
| zia    | 1   |

after upsert
| person | num |
|--------|-----|
| zia    | 2   |
```

- создаём `jobs_done` со столбцами `person` (текстовый тип данных с уникальными значениями) и `num`(целочисленный тип, по умолчанию равен 0)
- вставляем в `jobs_done` запись с именем `"zia"` и числом 1
- пытаемся снова вставить строку с тем же именем `"zia"` и числом 1 и снова выводим результаты запроса `SELECT`
- вставляем строку с тем же именем `"zia"` и числом 1 но уже указываем, чтобы в случае конфликта по столбцу `person`, обновить значение столбца `num`, увеличив его на 1

### Создание триггера

[src/trigger_setup.sql](https://gvwilson.github.io/sql-tutorial/src/trigger_setup.sql)

```
-- Track hours of lab work.
CREATE TABLE job (person text NOT NULL,
                              reported real NOT NULL CHECK (reported >= 0.0));

-- Explicitly store per-person total rather than using sum().
CREATE TABLE total (person text UNIQUE NOT NULL,
                                       hours real);

-- Initialize totals.
INSERT INTO total
VALUES ("gene", 0.0),
       ("august", 0.0);

-- Define a trigger.
CREATE TRIGGER total_trigger
BEFORE
INSERT ON job BEGIN -- Check that the person exists.

SELECT CASE
           WHEN NOT EXISTS
                  (SELECT 1
                   FROM total
                   WHERE person = new.person) THEN raise
                  (ROLLBACK, 'Unknown person ')
       END; -- Update their total hours (or fail if non-negative constraint violated).

UPDATE total
SET hours = hours + new.reported
WHERE total.person = new.person; END;
```

- создаём таблицу `job` со столбцами `person` и `reported`
- создаём `total` со столбцами `person` и `hours`
- устанавливаем значения `gene` и `august` в 0.0
- создаём триггер `total_trigger`, который срабатывает перед вставкой новых записей в таблицу `job`. Этот триггер:
    - проверяет, существует ли человек в таблице `total`, прежде чем разрешить вставку новых записей в таблицу `job`
    - обновляет общее количество отработанных часов для соответствующего человека в таблице `total`путем добавления нового количества отработанных часов из таблицы `job`

[src/trigger_successful.sql](https://gvwilson.github.io/sql-tutorial/src/trigger_successful.sql)

```
INSERT INTO job
VALUES ('gene', 1.5),
       ('august', 0.5),
       ('gene', 1.0);
```

[out/trigger_successful.out](https://gvwilson.github.io/sql-tutorial/out/trigger_successful.out)

```
| person | reported |
|--------|----------|
| gene   | 1.5      |
| august | 0.5      |
| gene   | 1.0      |

| person | hours |
|--------|-------|
| gene   | 2.5   |
| august | 0.5   |
```

**Срабатывание триггера**

[src/trigger_firing.sql](https://gvwilson.github.io/sql-tutorial/src/trigger_firing.sql)

```
INSERT INTO job
VALUES ('gene', 1.0),
       ('august', -1.0) ;
```

[out/trigger_firing.out](https://gvwilson.github.io/sql-tutorial/out/trigger_firing.out)

```
Runtime error near line 6: CHECK constraint failed: reported >= 0.0 (19)

| person | hours |
|--------|-------|
| gene   | 0.0   |
| august | 0.0   |
```

**Графическое представление**

[src/lineage_setup.sql](https://gvwilson.github.io/sql-tutorial/src/lineage_setup.sql)

```
CREATE TABLE lineage (parent text NOT NULL,
                      child text NOT NULL);
INSERT INTO lineage
VALUES ('Arturo', 'Clemente'),
       ('Darío', 'Clemente'),
       ('Clemente', 'Homero'),
       ('Clemente', 'Ivonne'),
       ('Ivonne', 'Lourdes'),
       ('Soledad', 'Lourdes'),
       ('Lourdes', 'Santiago');
```

[src/represent_graph.sql](https://gvwilson.github.io/sql-tutorial/src/represent_graph.sql)

```
SELECT *
FROM lineage;
```

[out/represent_graph.out](https://gvwilson.github.io/sql-tutorial/out/represent_graph.out)

```
|  parent  |  child   |
|----------|----------|
| Arturo   | Clemente |
| Darío    | Clemente |
| Clemente | Homero   |
| Clemente | Ivonne   |
| Ivonne   | Lourdes  |
| Soledad  | Lourdes  |
| Lourdes  | Santiago |
```

![lineage diagram](https://habrastorage.org/getpro/habr/upload_files/237/8cf/ccc/2378cfccc943d4d0c31e071280f5d279.png "Огромная шпаргалка с готовыми запросами SQL (SQLite)")

lineage diagram

### Рекурсивный запрос

[src/recursive_lineage.sql](https://gvwilson.github.io/sql-tutorial/src/recursive_lineage.sql)

```
     WITH RECURSIVE descendent AS (
             SELECT 'Clemente' AS person,
                    0 AS generations
          UNION ALL
             SELECT lineage.child AS person,
                    descendent.generations + 1 AS generations
               FROM descendent
               JOIN lineage ON descendent.person = lineage.parent
          )
   SELECT person,
          generations
     FROM descendent;
```

[out/recursive_lineage.out](https://gvwilson.github.io/sql-tutorial/out/recursive_lineage.out)

```
|  person  | generations |
|----------|-------------|
| Clemente | 0           |
| Homero   | 1           |
| Ivonne   | 1           |
| Lourdes  | 2           |
| Santiago | 3           |
```

- определяем общий термин `descendent` (потомок) как рекурсивное общее выражение. Начинаем с одной записи, где `'Clemente'` – это начальное имя, а 0 – это количество поколений.
- далее мы выполняем рекурсивное объединение с самим собой (с `descendent`) и таблицей `lineage`, чтобы найти всех потомков для каждого найденного человека. Выбираем потомка из таблицы `lineage`, увеличиваем количество поколений на 1 и продолжаем делать это для всех найденных потомков, пока они находятся
- если новых потомков больше не найдено, используем `SELECT` для выбора столбцов `person` и `generations` из `descendent`

**База данных отслеживания контактов**

[src/contact_person.sql](https://gvwilson.github.io/sql-tutorial/src/contact_person.sql)

```
SELECT *
FROM person;
```

[out/contact_person.out](https://gvwilson.github.io/sql-tutorial/out/contact_person.out)

```
| ident |         name          |
|-------|-----------------------|
| 1     | Juana Baeza           |
| 2     | Agustín Rodríquez     |
| 3     | Ariadna Caraballo     |
| 4     | Micaela Laboy         |
| 5     | Verónica Altamirano   |
| 6     | Reina Rivero          |
| 7     | Elias Merino          |
| 8     | Minerva Guerrero      |
| 9     | Mauro Balderas        |
| 10    | Pilar Alarcón         |
| 11    | Daniela Menéndez      |
| 12    | Marco Antonio Barrera |
| 13    | Cristal Soliz         |
| 14    | Bernardo Narváez      |
| 15    | Óscar Barrios         |
```

[src/contact_contacts.sql](https://gvwilson.github.io/sql-tutorial/src/contact_contacts.sql)

```
SELECT *
FROM contact;
```

[out/contact_contacts.out](https://gvwilson.github.io/sql-tutorial/out/contact_contacts.out)

```
|       left        |         right         |
|-------------------|-----------------------|
| Agustín Rodríquez | Ariadna Caraballo     |
| Agustín Rodríquez | Verónica Altamirano   |
| Juana Baeza       | Verónica Altamirano   |
| Juana Baeza       | Micaela Laboy         |
| Pilar Alarcón     | Reina Rivero          |
| Cristal Soliz     | Marco Antonio Barrera |
| Cristal Soliz     | Daniela Menéndez      |
| Daniela Menéndez  | Marco Antonio Barrera |
```

![contact diagram](https://habrastorage.org/getpro/habr/upload_files/9fd/0e1/86e/9fd0e186e42b4756c9242e4e349f7109.png "Огромная шпаргалка с готовыми запросами SQL (SQLite)")

contact diagram

### Продолжаем работать с bi_contact

[src/bidirectional.sql](https://gvwilson.github.io/sql-tutorial/src/bidirectional.sql)

```
CREATE TEMPORARY TABLE bi_contact (LEFT text, RIGHT text);

   INSERT INTO bi_contact
   SELECT LEFT,
          RIGHT
     FROM contact
UNION ALL
   SELECT RIGHT,
          LEFT
     FROM contact;
```

[out/bidirectional.out](https://gvwilson.github.io/sql-tutorial/out/bidirectional.out)

```
| original_count |
|----------------|
| 8              |

| num_contact |
|-------------|
| 16          |
```

- создаём временную табличку `bi_contact` с 2 столбцами: `LEFT` и `RIGHT`, оба текстовые
- вставляем в `bi_contact` данные из другой таблицы при помощи `SELECT`
- используем `UNION ALL` для объединения результатов 2 операций `SELECT` в один набор данных; данные из столбца `LEFT` и `RIGHT` таблицы `contact` вставляем в таблицу `bi_contact`. Первый набор данных берёт значения из столбцов `LEFT` и `RIGHT` таблицы `contact`, а второй набор данных берёт значения из столбцов `RIGHT` и `LEFT` таблицы `contact`
- в общем, вставляем в `bi_contact` комбинацию значений из столбцов `LEFT` и `RIGHT` таблицы `contact`и их перевёрнутые комбинации

### Обновляем идентификаторы групп

[src/update_group_ids.sql](https://gvwilson.github.io/sql-tutorial/src/update_group_ids.sql)

```
   SELECT left.name AS left_name,
          left.ident AS left_ident,
          right.name AS right_name,
          right.ident AS right_ident,
          min(left.ident, right.ident) AS new_ident
     FROM (
          person AS
LEFT JOIN bi_contact ON left.name = bi_contact.left
          )
     JOIN person AS RIGHT ON bi_contact.right = right.name;
```

[out/update_group_ids.out](https://gvwilson.github.io/sql-tutorial/out/update_group_ids.out)

```
|       left_name       | left_ident |      right_name       | right_ident | new_ident |
|-----------------------|------------|-----------------------|-------------|-----------|
| Juana Baeza           | 1          | Micaela Laboy         | 4           | 1         |
| Juana Baeza           | 1          | Verónica Altamirano   | 5           | 1         |
| Agustín Rodríquez     | 2          | Ariadna Caraballo     | 3           | 2         |
| Agustín Rodríquez     | 2          | Verónica Altamirano   | 5           | 2         |
| Ariadna Caraballo     | 3          | Agustín Rodríquez     | 2           | 2         |
| Micaela Laboy         | 4          | Juana Baeza           | 1           | 1         |
| Verónica Altamirano   | 5          | Agustín Rodríquez     | 2           | 2         |
| Verónica Altamirano   | 5          | Juana Baeza           | 1           | 1         |
| Reina Rivero          | 6          | Pilar Alarcón         | 10          | 6         |
| Pilar Alarcón         | 10         | Reina Rivero          | 6           | 6         |
| Daniela Menéndez      | 11         | Cristal Soliz         | 13          | 11        |
| Daniela Menéndez      | 11         | Marco Antonio Barrera | 12          | 11        |
| Marco Antonio Barrera | 12         | Cristal Soliz         | 13          | 12        |
| Marco Antonio Barrera | 12         | Daniela Menéndez      | 11          | 11        |
| Cristal Soliz         | 13         | Daniela Menéndez      | 11          | 11        |
| Cristal Soliz         | 13         | Marco Antonio Barrera | 12          | 12        |
```

### Рекурсивно устанавливаем метки

[src/recursive_labeling.sql](https://gvwilson.github.io/sql-tutorial/src/recursive_labeling.sql)

```
     WITH recursive labeled AS (
             SELECT person.NAME AS NAME,
                    person.ident AS label
               FROM person
              UNION -- not 'union all'
             SELECT person.NAME AS NAME,
                    labeled.label AS label
               FROM (
                    person
               JOIN bi_contact ON person.NAME = bi_contact.LEFT
                    )
               JOIN labeled ON bi_contact.RIGHT = labeled.NAME
              WHERE labeled.label < person.ident
          )
   SELECT NAME,
          min(label) AS group_id
     FROM labeled
 GROUP BY NAME
 ORDER BY label,
          NAME;
```

[out/recursive_labeling.out](https://gvwilson.github.io/sql-tutorial/out/recursive_labeling.out)

```
|         name          | group_id |
|-----------------------|----------|
| Agustín Rodríquez     | 1        |
| Ariadna Caraballo     | 1        |
| Juana Baeza           | 1        |
| Micaela Laboy         | 1        |
| Verónica Altamirano   | 1        |
| Pilar Alarcón         | 6        |
| Reina Rivero          | 6        |
| Elias Merino          | 7        |
| Minerva Guerrero      | 8        |
| Mauro Balderas        | 9        |
| Cristal Soliz         | 11       |
| Daniela Menéndez      | 11       |
| Marco Antonio Barrera | 11       |
| Bernardo Narváez      | 14       |
| Óscar Barrios         | 15       |
```

- определяем рекурсивное выражение `labeled` — оно начинается с базового запроса, который выбирает имена и идентификаторы из таблицы person,
    - затем используется `UNION` для объединения с другим запросом, который соединяет таблицы `person`и `bi_contact`, используя столбцы `name` и `left` в `bi_contact` и имена и метки из `person`
    - затем объединяет результаты этого соединения с ранее помеченными записями из `labeled`
    - `WHERE` устанавливает условие, что метка предыдущей записи должна быть меньше, чем идентификатор текущей записи `person`
- выполняем основной запрос — выбираем имена из `labeled` и вычисляем минимальную метку для каждого имени как `group_id` с помощью функции `min()`
- результат группируем по именам и сортируем сначала по метке, а затем по имени
- attention: только не используйте тут `UNION ALL`, иначе возникнет бесконечная рекурсия)

### Работа с SQL в Python при помощи sqlite3

[src/basic_python_query.py](https://gvwilson.github.io/sql-tutorial/src/basic_python_query.py)

```
import sqlite3

connection = sqlite3.connect("db/penguins.db")
cursor = connection.execute("SELECT count(*) FROM penguins;")
rows = cursor.fetchall()
print(rows)
```

[out/basic_python_query.out](https://gvwilson.github.io/sql-tutorial/out/basic_python_query.out)

```
[(344,)]
```

- импортируем библиотечку `sqlite3` (к слову, она является одной из стандартных библиотек) для работы с SQLite
- устанавливаем соединение с БД, расположенной в файле `"db/penguins.db"`, используя метод `sqlite3.connect()`. Если этого файл не существует, то он будет создан
- создаём объект `cursor` для выполнения SQL-запросов
- `select count(*) from penguins;` — подсчитываем количество всех записей в таблице `penguins`
- `fetchall()` — получаем результат выполнения запроса, сохраняем его в переменную `rows`

### Инкрементная выборка

[src/incremental_fetch.py](https://gvwilson.github.io/sql-tutorial/src/incremental_fetch.py)

```
import sqlite3

connection = sqlite3.connect("db/penguins.db")
cursor = connection.cursor()
cursor = cursor.execute("SELECT species, island FROM penguins LIMIT 5;")
while row := cursor.fetchone():
    print(row)
```

[out/incremental_fetch.out](https://gvwilson.github.io/sql-tutorial/out/incremental_fetch.out)

```
('Adelie', 'Torgersen')
('Adelie', 'Torgersen')
('Adelie', 'Torgersen')
('Adelie', 'Torgersen')
('Adelie', 'Torgersen')
```

- коннектимся к БД с помощью `sqlite3.connect("db/penguins.db")`
- `connection.cursor()` — создаём объект `cursor`, это указатель на результат выполнения запросов
- `select species, island from penguins limit 5;` — выбираем первые 5 записей из таблицы `penguins`, возвращая значения столбцов `species` и `island`
- пока переменная `row` из `cursor.fetchone()` непустая, печатаем её (мы сразу создаём переменную `row` и тут же используем её при помощи `:=` )

### Простые операции CREATE, INSERT, DELETE и другие с помощью sqlite3

[src/insert_delete.py](https://gvwilson.github.io/sql-tutorial/src/insert_delete.py)

```
import sqlite3

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()
cursor.execute("CREATE TABLE example(num integer);")

cursor.execute("INSERT INTO example VALUES (10),(20);")
print("after insertion", cursor.execute("SELECT * FROM example;").fetchall())

cursor.execute("DELETE FROM example WHERE num < 15;")
print("after deletion", cursor.execute("SELECT * FROM example;").fetchall())
```

[out/insert_delete.out](https://gvwilson.github.io/sql-tutorial/out/insert_delete.out)

```
after insertion [(10,), (20,)]
after deletion [(20,)]
```

- `connection = sqlite3.connect(":memory:")` — создаём подключение к БД SQLite, созданной в оперативной памяти
- `cursor = connection.cursor()` — создаём объект курсора, который используется для выполнения операций в БД
- `cursor.execute("CREATE TABLE example(num integer);")` — создаём новую таблицу с именем `example` и одним столбцом `num` для хранения целых чисел
- `cursor.execute("INSERT INTO example VALUES (10),(20);")` — вставляем 2 строки в `example` с числами 10 и 20 в столбец `num`
- `print("after insertion", cursor.execute("SELECT * FROM example;").fetchall())` — выводим содержимое таблицы `example` после вставки строк; выполняем операцию `SELECT`, чтобы выбрать все строки из таблицы, используя метод `.fetchall()` для извлечения результатов запроса
- `cursor.execute("DELETE FROM example WHERE num < 15;")` — удаляем строки из таблицы `example`, в которых значение столбца `num` меньше 15
- ну и в конце выводим содержимое таблицы `example` после удаления строк; также выполняем `SELECT`, чтобы выбрать все строки из таблицы, используя метод `.fetchall()` для извлечения результатов запроса

### Интерполируем значения

[src/interpolate.py](https://gvwilson.github.io/sql-tutorial/src/interpolate.py)

```
import sqlite3

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()
cursor.execute("CREATE TABLE example(num integer);")

cursor.executemany("insert into example values (?);", [(10,), (20,)])
print("after insertion", cursor.execute("SELECT * FROM example;").fetchall())
```

[out/interpolate.out](https://gvwilson.github.io/sql-tutorial/out/interpolate.out)

```
after insertion [(10,), (20,)]
```

![XKCD Exploits of a Mom](https://habrastorage.org/getpro/habr/upload_files/97d/5ba/947/97d5ba947d39291b99f978a29fa5638e.png "Огромная шпаргалка с готовыми запросами SQL (SQLite)")

XKCD Exploits of a Mom

- `connection = sqlite3.connect(":memory:")` — устанавливаем соединение с БД SQLite в оперативной памяти
- `cursor = connection.cursor()` — создаём объект курсора, который используется для выполнения операций БД
- `cursor.execute("create table example(num integer);")` — создаём таблицу `example` с одним столбцом `num` типа integer
- `cursor.executemany("insert into example values (?);", [(10,), (20,)])` — вставляем значения 10 и 20 в столбец `num` таблицы `example` с использованием параметризованного запроса

### Выполнение полноценных SQL-запросов в Python

[src/script_execution.py](https://gvwilson.github.io/sql-tutorial/src/script_execution.py)

```
import sqlite3

SETUP = """\
DROP TABLE IF EXISTS example;
CREATE TABLE example(num integer);
INSERT INTO example
VALUES (10),
       (20);
"""

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()
cursor.executescript(SETUP)
print("after insertion", cursor.execute("SELECT * FROM example;").fetchall())
```

[out/script_execution.out](https://gvwilson.github.io/sql-tutorial/out/script_execution.out)

```
after insertion [(10,), (20,)]
```

- удаляем таблицу `example`, если она существует
- создаём таблицу `example` с одним столбцом `num` типа integer
- вставляем 2 записи в таблицу `example` с числами 10 и 20
- выполняем `SETUP` с помощью метода курсора `executescript()`, который создает новую таблицу и вставляет данные
- выводим `after insertion` для обозначения того, что последующий запрос к базе данных будет относиться к состоянию после вставки данных
- выполняем запрос к БД для выбора всех записей из таблицы `example` с помощью метода `execute()` и `fetchall()` для извлечения результатов

### Исключения SQLite в Python

[src/exceptions.py](https://gvwilson.github.io/sql-tutorial/src/exceptions.py)

```
import sqlite3

SETUP = """\
CREATE TABLE example(num integer check(num > 0));

INSERT INTO example
VALUES (10);

INSERT INTO example
VALUES (-1);

INSERT INTO example
VALUES (20);
"""

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()
try:
    cursor.executescript(SETUP)
except sqlite3.Error as exc:
    print(f"SQLite exception: {exc}")
print("after execution", cursor.execute("SELECT * FROM example;").fetchall())
```

[out/exceptions.out](https://gvwilson.github.io/sql-tutorial/out/exceptions.out)

```
SQLite exception: CHECK constraint failed: num > 0
after execution [(10,)]
```

- устанавливаем соединение с БД в оперативной памяти с помощью `sqlite3.connect(":memory:")`
- создаём курсор для выполнения операци
- создаём таблицу `example` и вставляем в нее 3 значения с помощью `executescript()`
- в блоке `try-except` обрабатывается исключение `sqlite3.Error`, если произойдет какая-либо ошибка при выполнении запросов
- выводим содержимое таблицы `example` после выполнения запросов с помощью метода `fetchall()`

### Python и SQLite, ещё некоторые возможности

[src/embedded_python.py](https://gvwilson.github.io/sql-tutorial/src/embedded_python.py)

```
import sqlite3

SETUP = """\
CREATE TABLE example(num integer);
INSERT INTO example
VALUES (-10),
       (10),
       (20),
       (30);
"""

def clip(value):
    if value < 0:
        return 0
    if value > 20:
        return 20
    return value

connection = sqlite3.connect(":memory:")
connection.create_function("clip", 1, clip)
cursor = connection.cursor()
cursor.executescript(SETUP)
for row in cursor.execute("SELECT num, clip(num) FROM example;").fetchall():
    print(row)
```

[out/embedded_python.out](https://gvwilson.github.io/sql-tutorial/out/embedded_python.out)

```
(-10, 0)
(10, 10)
(20, 20)
(30, 20)
```

- создаём БД SQLite в оперативной памяти, создаём табличку `example`, заполняем её таблицу значениями (-10, 10, 20, 30)
- затем определяем функцию `clip`, которая принимает один аргумент и возвращает этот аргумент, если он находится между 0 и 20, или возвращает 0, если аргумент меньше 0, или возвращает 20, если аргумент больше 20
- выбираем значения из столбца `num` таблицы `example` и применяет функцию `clip` к каждому значению

### Работа с датой и временем

[src/dates_times.py](https://gvwilson.github.io/sql-tutorial/src/dates_times.py)

```
from datetime import date
import sqlite3

# Convert date to ISO-formatted string when writing to database
def _adapt_date_iso(val):
    return val.isoformat()

sqlite3.register_adapter(date, _adapt_date_iso)

# Convert ISO-formatted string to date when reading from database
def _convert_date(val):
    return date.fromisoformat(val.decode())

sqlite3.register_converter("date", _convert_date)

SETUP = """\
CREATE TABLE events(happened date NOT NULL,
                    description text NOT NULL);
"""

connection = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cursor = connection.cursor()
cursor.execute(SETUP)

cursor.executemany(
    "insert into events values (?, ?);",
    [(date(2024, 1, 10), "started tutorial"), (date(2024, 1, 29), "finished tutorial")],
)

for row in cursor.execute("SELECT * FROM EVENTS;").fetchall():
    print(row)
```

[out/dates_times.out](https://gvwilson.github.io/sql-tutorial/out/dates_times.out)

```
(datetime.date(2024, 1, 10), 'started tutorial')
(datetime.date(2024, 1, 29), 'finished tutorial')
```

- определяем функцию `_adapt_date_iso(val)` — она принимает дату и возвращает ее строковое представление в формате `ISO`
- определяем функцию `_convert_date(val)` — она принимает строку в формате `ISO` и возвращает объект типа `date`
- затем эти функции регистрируются в SQLite, чтобы обеспечить корректное преобразование данного типа данных при записи и чтении из базы данных
- после этого создается строка `SETUP`, которая содержит SQL-команду для создания таблицы `events` с двумя столбцами: `happened` типа `date` и `description` типа `text`
- с помощью `cursor.executemany` в таблицу `events` вставляются 2 записи с использованием значений типа `date` и строк
- с помощью `select * from events` и `cursor.execute` извлекаем значения всех строк из таблицы `events`

### SQL в Jupyter Notebooks

```
pip install jupysql
```

```
%load_ext sql
```

```
%sql sqlite:///data/penguins.db
```

```
Connecting to 'sqlite:///data/penguins.db'
```

- Подключение к БД:
    - `sqlite://` — протокол с 2 слэшами в конце
    - `/data/penguins.db` — 1 слэш спереди, это путь к локальной БД
- 1 знак процента `%sql` — для выполнения однострочных SQL-запросов
- 2 знака процента `%%sql` показывает, что вся ячейка будет восприниматься как один SQL-запрос

```
%%sql
SELECT species,
       count(*) AS num
FROM penguins
GROUP BY species;
```

```
Running query in 'sqlite:///data/penguins.db'
```

|species|num|
|---|---|
|Adelie|152|
|Chinstrap|68|
|Gentoo|124|

### Pandas и SQL

[src/install_pandas.sh](https://gvwilson.github.io/sql-tutorial/src/install_pandas.sh)

```
pip install pandas
```

[src/select_pandas.py](https://gvwilson.github.io/sql-tutorial/src/select_pandas.py)

```
import pandas as pd
import sqlite3

connection = sqlite3.connect("db/penguins.db")
query = "SELECT species, count(*) AS num FROM penguins GROUP BY species;"
df = pd.read_sql(query, connection)
print(df)
```

[out/select_pandas.out](https://gvwilson.github.io/sql-tutorial/out/select_pandas.out)

```
species  num
0     Adelie  152
1  Chinstrap   68
2     Gentoo  124
```

- `select species, count(*) as num from penguins group by species;` — извлекаем информацию о количестве пингвинов каждого вида из `penguins` и группируем результаты по видам
- выполняем запрос к БД с использованием метода `read_sql` библиотеки `pandas`, который читает результаты запроса и преобразует их в объект `DataFrame` (df)

### Polars и SQL

[src/install_polars.sh](https://gvwilson.github.io/sql-tutorial/src/install_polars.sh)

```
pip install polars pyarrow adbc-driver-sqlite
```

[src/select_polars.py](https://gvwilson.github.io/sql-tutorial/src/select_polars.py)

```
import polars as pl

query = "SELECT species, count(*) AS num FROM penguins GROUP BY species;"
uri = "sqlite:///db/penguins.db"
df = pl.read_database_uri(query, uri, engine="adbc")
print(df)
```

[out/select_polars.out](https://gvwilson.github.io/sql-tutorial/out/select_polars.out)

```
shape: (3, 2)
┌───────────┬─────┐
│ species   ┆ num │
│ ---       ┆ --- │
│ str       ┆ i64 │
╞═══════════╪═════╡
│ Adelie    ┆ 152 │
│ Chinstrap ┆ 68  │
│ Gentoo    ┆ 124 │
└───────────┴─────┘
```

- импортирует библиотеку Polars – она похожа на pandas, но с фокусом на параллельную обработку данных
- выбираем столбец `species` и вычисляем количество записей для каждого вида пингвинов из таблицы `penguins`; результат группируем по столбцу `species`
- устанавливаем строку подключения к базе данных SQLite в переменной `uri`
- используем `pl.read_database_uri` для выполнения SQL-запроса `query` к БД, указанной в `uri`, используя движок `adbc`
- выводим результат выполнения запроса в виде таблицы данных

### ORM

[src/orm.py](https://gvwilson.github.io/sql-tutorial/src/orm.py)

```
from sqlmodel import Field, Session, SQLModel, create_engine, select

class Department(SQLModel, table=True):
    ident: str = Field(default=None, primary_key=True)
    name: str
    building: str

engine = create_engine("sqlite:///db/assays.db")
with Session(engine) as session:
    statement = select(Department)
    for result in session.exec(statement).all():
        print(result)
```

[out/orm.out](https://gvwilson.github.io/sql-tutorial/out/orm.out)

```
building='Chesson' name='Genetics' ident='gen'
building='Fashet Extension' name='Histology' ident='hist'
building='Chesson' name='Molecular Biology' ident='mb'
building='TGVH' name='Endocrinology' ident='end'
```

- создаём класс `Department`, который представляет модель данных для отделов; каждый атрибут класса соответствует столбцу в таблице БД
- создаём объект `engine`, который представляет собой подключение к SQLite БД, где `assays.db` – это имя файла БД
- создаём `Session` для взаимодействия с базой данных через созданный `engine`
- формируем SQL-запрос с помощью `select(Department)`, который выбирает все данные из таблицы, представленной моделью `Department`
- выполняем запрос к БД через `session.exec(statement).all()`, который возвращает все строки, удовлетворяющие условию запроса

### Продолжаем работать с ORM

[src/orm_relation.py](https://gvwilson.github.io/sql-tutorial/src/orm_relation.py)

```
class Staff(SQLModel, table=True):
    ident: str = Field(default=None, primary_key=True)
    personal: str
    family: str
    dept: Optional[str] = Field(default=None, foreign_key="department.ident")
    age: int

engine = create_engine("sqlite:///db/assays.db")
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
    statement = select(Department, Staff).where(Staff.dept == Department.ident)
    for dept, staff in session.exec(statement):
        print(f"{dept.name}: {staff.personal} {staff.family}")
```

[out/orm_relation.out](https://gvwilson.github.io/sql-tutorial/out/orm_relation.out)

```
Histology: Divit Dhaliwal
Molecular Biology: Indrans Sridhar
Molecular Biology: Pranay Khanna
Histology: Vedika Rout
Genetics: Abram Chokshi
Histology: Romil Kapoor
Molecular Biology: Ishaan Ramaswamy
Genetics: Nitya Lal
```

- объявляем класс `Staff`; он использует SQLModel, что позволяет использовать этот класс как схему для создания таблицы в БД. Указание `table=True` в качестве аргумента класса говорит SQLModel о том, что данный класс должен отображаться в базу данных как таблица. У `Staff` есть несколько атрибутов :
    - `ident` – строковое поле, которое будет использоваться в качестве первичного ключа в базе данных. Оно имеет значение по умолчанию `None` и задается как первичный ключ (`primary_key=True`)
    - `personal` – строковое поле
    - `family` – строковое поле
    - `dept` – опциональное строковое поле; имеет значение по умолчанию `None` и устанавливается как внешний ключ (`foreign_key="department.ident"`)
    - `age` – целочисленное поле
- после определения `Staff`, создается экземпляр движка для работы с БД SQLite с помощью вызова функции `create_engine` из библиотеки SQLAlchemy
- затем вызываем метод `create_all` у метаданных SQLModel, что приводит к созданию всех таблиц, определенных в виде классов с помощью SQLModel, на основе ранее созданного движка базы данных
- далее устанавливаем сессия БД с использованием созданного ранее движка
- формируется SQL-запрос, который выбирает данные из таблиц `Department` и `Staff`, объединяя их по условию, что поле `Staff.dept` равно полю `Department.ident`
- выполняем этот запрос в сессии БД, и для каждой строки результата выводится название отдела и персональные данные сотрудника