Моя большая практическая шпаргалка SQL (SQLite) с готовыми запросами

Привет, Хабр)

Публикую шпаргалку по SQL, которая долгое время помогала мне, да и сейчас я периодически в неё заглядываю.

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

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

Большинство советов я публиковал в своем канале по анализу данных ^[1], где вы найдете большое количество советов, инструментов и примеров с кодом. А здесь большая полезная папка, ^[2] которую я собрал в которой куча полезного для работы с данными.

Кстати, все эти примеры SQL заботливо собраны в одной папке, можете скачать её ^[3] и экспериментировать локально. После скачивания и разархивирования, у вас будет 3 группы файлов:

assays.db9 — базы данных SQLite, которые используются в примерах ниже
assays.db8 — SQL-запросы, сценарии Python
assays.db7 — ожидаемый результат для примеров

Поехали!

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

assays.db6

assays.db5

ничего особенного, выбираем все записи из таблички assays.db4

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

src/admin_commands.sql ^[105]

assays.db3

out/admin_commands.out ^[106]

assays.db2

включаем заголовки и режим markdown; в assays.db1 подобные команды начинаются с assays.db0, а в PostgreSQL с Session9
кстати, для просмотра дополнительной инфы или чтобы узнать, какие команды есть, используйте Session8

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

src/specify_columns.sql ^[107]

Session7

out/specify_columns.out ^[108]

Session6

выбираем колонки Session5, Session4, Session3 из таблички Session2

Сортировка

src/sort.sql ^[109]

Session1

out/sort.out ^[110]

Session0

выбираем столбцы engine9, engine8, engine7 из таблички engine6
сортируем все значения из engine5 в возрастающем порядке (от A к Z)
строки с одинаковыми значениями engine4 дополнительно сортируем по их значениям engine3 в обратном порядке, от большего к меньшему (от Z к A)

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

src/limit.sql ^[111]

engine2

out/limit.out ^[112]

engine1

выбираем столбцы engine0, select(Department)9, select(Department)8 из таблички select(Department)7
сортируем по select(Department)6 в порядке возрастания, строки с одинаковым значением select(Department)5 сортируются по select(Department)4, с одинаковым select(Department)3 дополнительно сортируются по select(Department)2
ну и выводим только первые 10 строк

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

src/page.sql ^[113]

select(Department)1

out/page.out ^[114]

select(Department)0

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

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

src/distinct.sql ^[115]

Department7

out/distinct.out ^[116]

Department6

Department5 — выбираем уникальные комбинации из столбцов Department4, Department3, Department2

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

src/filter.sql ^[117]

Department1

out/filter.out ^[118]

Department0

выбираем уникальные комбинации значений session.exec(statement).all()9, session.exec(statement).all()8, session.exec(statement).all()7 из session.exec(statement).all()6, где значения поля session.exec(statement).all()5 равно session.exec(statement).all()4

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

src/filter_and.sql ^[119]

session.exec(statement).all()3

out/filter_and.out ^[120]

session.exec(statement).all()2

выбираем уникальные комбинации значений session.exec(statement).all()1, session.exec(statement).all()0, 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}") 9 из 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}") 8, где значения поля 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}") 7 равно 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}") 6, а значения поля 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}") 5 не равно 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}") 4

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

src/calculations.sql ^[121]

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}")
3

out/calculations.out ^[122]

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}")
2

выводим 3 первых строки значений 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}") 1, делённых на 10.0, и значений 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}") 0, делённых на 1000.0

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

src/rename_columns.sql ^[123]

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
9

out/rename_columns.out ^[124]

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
8

делим значения 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 7 на 10.0, делим значения 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 6 на 1000.0
переименовываем столбцы 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 5 — в 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 4, 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 3 — в 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 2, 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 1 — в 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 0
выводим первые 3 строки

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

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

src/show_missing_values.sql ^[125]

Staff9

out/show_missing_values.out ^[126]

Staff8

делим значения из Staff7 на 10, затем присваиваем результаты новому столбцу Staff6
делим значения из столбца Staff5 на 1000 и затем присваивание результатов новому столбцу Staff4
переименовываем Staff3 в Staff2

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

src/filter.sql ^[117]

Staff1

out/filter.out ^[118]

Staff0

выбираем столбцы table=True9, table=True8, table=True7
выводим все записи из table=True6, где значение table=True5 равно table=True4

src/null_equality.sql ^[127]

table=True3

out/null_equality.out ^[128]

table=True2

выводим все записи из table=True1, где значение table=True0 равно Staff9 и значение Staff8 равно Staff7

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

условие с оператором отрицания Staff6 тоже без проблем работает

src/null_inequality.sql ^[129]

Staff5

out/null_inequality.out ^[130]

Staff4

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

src/safe_null_equality.sql ^[131]

Staff3

out/safe_null_equality.out ^[132]

Staff2

выбираем строки со значениями Staff1, Staff0, ident9 из таблички ident8, где значения ident7 нет (ident6)

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

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

src/simple_sum.sql ^[133]

ident5

out/simple_sum.out ^[134]

ident4

суммируем все значения колонки ident3, сохраняем в новый столбец ident2

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

src/common_aggregations.sql ^[135]

ident1

out/common_aggregations.out ^[136]

ident0

находим максимальное значение из столбца None9, записываем это значение как None8
аналогично находим минимальное из None7, находим среднее из None6, среднее из None5

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

src/count_behavior.sql ^[137]

None4

out/count_behavior.out ^[138]

None3

None2 — считаем все значения из None1
None0 — считаем все значения из столбца primary_key=True9
primary_key=True8 — считаем уникальные значения из primary_key=True7 (очевидно их 2: primary_key=True6, primary_key=True5)
записываем эти 3 числа как primary_key=True4, primary_key=True3, primary_key=True2 соответственно

Группировка

src/simple_group.sql ^[139]

primary_key=True1

out/simple_group.out ^[140]

primary_key=True0

из таблички personal9 находим среднее всех значений personal8, сохраняем как personal7
группируем по значениям personal6 (группы personal5, personal4, personal3)

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

src/unaggregated_columns.sql ^[141]

personal2

out/unaggregated_columns.out ^[142]

personal1

для того, чтобы было видно названия отдельных групп, выбираем не только среднее personal0, но и family9
видим 3 группы: family8, family7, family6

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

src/arbitrary_in_aggregation.sql ^[143]

family5

out/arbitrary_in_aggregation.out ^[144]

family4

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

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

src/filter_aggregation.sql ^[145]

family1

out/filter_aggregation.out ^[146]

family0

здесь мы используем dept9 вместо dept8 (эффект тот же самый), оставляем только те значения из dept7, которые больше 4000

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

src/readable_aggregation.sql ^[147]

dept6

out/readable_aggregation.out ^[148]

dept5

округляем среднее dept4 до 1 знака после запятой, используя dept3

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

src/filter_aggregate_inputs.sql ^[149]

dept2

out/filter_aggregate_inputs.out ^[150]

dept1

при помощи dept0 мы находим среднее только тех значений None9, которые меньше 4000
округляем до 1 знака после запятой, сохраняем в столбец None8
группируем по None7

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

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

src/in_memory_db.sh ^[151]

None6

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

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

src/create_work_job.sql ^[152]

None5

создаём таблицу None4 со столбцами: None3 — столбец текстовых значений, не может быть пустым (None2), None1 — содержит вещественные числа, не может быть пустым
создаём табличку None0 со столбцами: foreign_key="department.ident"9 — текстовый, не может быть пустым, foreign_key="department.ident"8 — текстовый, не может быть пустым

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

src/populate_work_job.sql ^[153]

foreign_key="department.ident"7

out/insert_values.out ^[154]

foreign_key="department.ident"6

ничего особенного, заполняем табличку foreign_key="department.ident"5 парами foreign_key="department.ident"4-foreign_key="department.ident"3, и так же заполняем табличку foreign_key="department.ident"2 парами foreign_key="department.ident"1-foreign_key="department.ident"0

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

src/update_work_job.sql ^[155]

age9

out/update_rows.out ^[156]

age8

меняем все записи age7 на age6

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

src/delete_rows.sql ^[157]

age5

out/delete_rows.out ^[158]

age4

удаляем все строки, где значение age3 равно age2

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

src/backing_up.sql ^[159]

age1

out/backing_up.out ^[160]

age0

создаём табличку Staff9 c текстовыми столбцами Staff8 и Staff7
помещаем внутрь Staff6 значения столбцов Staff5 и Staff4 из таблицы Staff3, где значения столбца Staff2 равно Staff1
удаляем из Staff0 все записи со значением create_engine9 равным create_engine8
отображаем записи таблички create_engine7

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

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

src/cross_join.sql ^[161]

create_engine6

out/cross_join.out ^[162]

create_engine5

делаем create_engine4 для 2 таблиц create_engine3 и create_engine2 — все возможные комбинации строк из этих таблиц (если в create_engine1 3 строки, а в create_engine0 4 строки, то результат будет иметь 4 ⋅ 3 = 12 строк)

INNER JOIN

src/inner_join.sql ^[163]

create_all9

out/inner_join.out ^[164]

create_all8

объединяем 2 таблицы create_all7 и create_all6 — берём те записи, где значение create_all5 из create_all4 совпадает со значением create_all3 из create_all2

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

src/aggregate_join.sql ^[165]

create_all1

out/aggregate_join.out ^[166]

create_all0

объединяем те строки таблиц Department9 и Department8, где значение Department7 в таблице Department6 соответствует значению Department5 в Department4
суммируем значения Department3 из таблицы Department2 для каждого значения Department1 из таблицы Department0
группируем результаты по значениям Staff9 из Staff8

LEFT JOIN

src/left_join.sql ^[167]

Staff7

out/left_join.out ^[168]

Staff6

склеиваем таблицы Staff5 и Staff4 по соответствующим значениям столбца Staff3
если в таблице Staff2 есть строки, для которых нет совпадений в таблице Staff1, то они все равно будут включены в результат с пустыми (Staff0) значениями
использование Staff.dept9 гарантирует, что все строки из левой таблицы Staff.dept8 будут включены в результат, независимо от наличия совпадающих строк в правой таблице Staff.dept7

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

src/aggregate_left_join.sql ^[169]

Staff.dept6

out/aggregate_left_join.out ^[170]

Staff.dept5

вычисляем сумму значений столбца Staff.dept4 из Staff.dept3, сохраняем как Staff.dept2
используем Staff.dept1, чтобы гарантированно включить все строки из Staff.dept0 в Department.ident9
группируем по столбцу Department.ident8 из Department.ident7

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

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

src/coalesce.sql ^[171]

Department.ident6

out/coalesce.out ^[172]

Department.ident5

Department.ident4 используется для замены Department.ident3 на 0.0, если сумма Department.ident2 для данного Department.ident1 равна Department.ident0
more9 включает все записи из more8 и только соответствующие записи из more7
группируем по значениям столбца more6 из more5

SELECT DISTINCT и условие WHERE

src/negate_incorrectly.sql ^[173]

more4

out/negate_incorrectly.out ^[174]

more3

выбираем уникальные значения из столбца more2, где поле more1 не равно more0

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

src/set_membership.sql ^[175]

out/set_membership.out ^[176]

выбираем все строки из 4, где 3 не равно 2 и не равно 1

Подзапросы

src/subquery_set.sql ^[177]

out/subquery_set.out ^[178]

внутренний подзапрос выбирает уникальные значения столбца 8 из 7, где в поле 6 стоит 5
внешний, главный запрос выбирает те уникальные значения 4, где 3 не равно значениям из внутренного подзапроса

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

src/autoincrement.sql ^[179]

out/autoincrement.out ^[180]

создаём табличку 0 с 2 столбцами: 9 с целочисленными значениями, 8 с текстовыми значениями; столбец 7 устанавливаем как 6, включаем автоматическое инкрементирование значений
помещаем в таблицу 5 3 пары 4-3
при попытке добавить ещё одну пару 2 возникает ошибка, поскольку уже существует строка с 1 равным 1

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

src/sequence_table.sql ^[181]

out/sequence_table.out ^[182]

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

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

src/alter_tables.sql ^[183]

out/alter_tables.out ^[184]

добавляем новый столбец 6 в табличку 5; столбец заполняется целыми числами, не может быть пустым; ставим значение по умолчанию 4 для этого столбца
делаем значение столбца 3 равным 1 там, где 2 равен 1
устанавливаем значение 0 равным 2 для строки, где 9 равен 8

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

src/insert_select.sql ^[185]

out/insert_select.out ^[186]

создаём таблицу 5 с 2 целочисленными столбцами: 4 и 3; оба столбца не могут быть пустыми
2 2 ограничения добавляются, чтобы связать столбцы 1 и 0 новой таблицы 9 с соответствующими столбцами 8 в таблицах 7 и 6
добавляем данные в таблицу 5, используя результат запроса 4
3 — данные будут выбраны из результатов соединения таблиц 2 и 1 по условию равенства значений столбца 0 в таблице 9 и столбца 8 в таблице 7
6 — результаты предыдущего соединения будут дополнительно соединены с таблицей 5 по условию равенства значений столбца 4 в таблице 3 и столбца 2 в 1

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

src/drop_table.sql ^[187]

удаляем 9 из БД
изменяем имя таблички 8 на 7

out/drop_table.out ^[188]

создаём таблицу 5 с 3 колонками: 4 хранит целые числа, используется в качестве первичного ключа (3) и автоматически увеличивается (autoincrement);

2 текстовый столбец, не может быть пустым (1);

0 — столбец вещественных чисел, не может быть пустым
создаём 9 с 2 колонками: 8 и 7
создаём таблицу 6 с 2 колонками: 5 — хранит целые числа, используется в качестве первичного ключа и автоматически увеличивается (autoincrement), 4 — хранит текст, не может быть пустым
создаём 3 с 4 колонками: 2 - хранит целые числа, не может быть пустым; аналогичный столбец 1
устанавливаем внешние ключи, связывающие 0 с 9 в таблице 8 и 7 с 6 в таблице 5

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

src/compare_individual_aggregate.sql ^[189]

out/compare_individual_aggregate.out ^[190]

выбираем только те строки, где значение в столбце 2 больше, чем среднее значение 1 по всем строкам в таблице 0
ну и выводим только первые 5 строк

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

src/compare_within_groups.sql ^[191]

out/compare_within_groups.out ^[192]

выбираем столбцы 7 и 6 из таблицы 5
вычисляем среднюю массу для каждого вида пингвина, округляем до 1 знака после запятой, используя подзапрос, который связывается с исходной таблицей 4 по полю 3
используя результаты подзапроса, фильтруем только те записи, где масса пингвина больше средней массы для его вида
выводим только первые 5 записей

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

src/common_table_expressions.sql ^[193]

out/common_table_expressions.out ^[194]

создаём табличку 0 (с помощью 9), которая содержит среднюю массу тела пингвинов (8) для каждого вида из 7 (6)
из 5 выбираем такие столбцы: 4, 3; и из из общей таблицы 2 выбираем 1, округлённое до 1 знака
объединяем 0 с общей таблицей 9 (через 8); для каждого пингвина будет найдена соответствующая средняя масса тела для его вида
7 — фильтруем; оставляем только тех, у которых масса тела больше средней массы их вида
выводим только первые 5 строк

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

src/explain_query_plan.sql ^[195]

out/explain_query_plan.out ^[196]

4 — получаем план выполнения запроса, как будет выполнен запрос в базе данных
выбираем столбец 3, вычисляем среднее значение столбца body_mass_g для каждого вида из 2
1 — группируем результаты по столбцу 0

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

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

src/rowid.sql ^[197]

out/rowid.out ^[198]

Условия if-else

src/if_else.sql ^[199]

out/if_else.out ^[200]

создаём временную таблицу 4, которая содержит два столбца: 3 и 2
1 определяется на основе условия: если 0 меньше 3500, то он считается 9, в противном случае - 8
выбираем столбцы 7 и 6 из временной таблицы 5, а подсчитываем количество записей для каждой комбинации 4 и 3, используя функцию 2
группируем данные (1) по 0 и 9

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

src/case_when.sql ^[201]

out/case_when.out ^[202]

в блоке 6 создаём набор данных с именем 5, где находится 4 и 3, определенные на 2
1 разделяет пингвинов на 3 категории: 0, 9 и 8 в зависимости от их массы
в основном блоке 7 выбираются вид пингвина, его размер и количество пингвинов каждого размера (6) из набора 5
результаты группируются по виду пингвина и их размеру с помощью 4
в конце запроса результаты сортируются сначала по 3 в алфавитном порядке, а затем по 2

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

src/check_range.sql ^[203]

out/check_range.out ^[204]

создаём общую таблицу выражений (CTE) 9, она выбирает вид пингвина и определяет его размер в зависимости от массы тела; если масса в диапазоне от 3500 до 5000 г, это размер 8, в противном случае - 7
затем из этой CTE извлекаем данные с указанием видов пингвинов, их размеров и количества пингвинов каждого вида и размера, используя 6 с агрегирующей функцией 5
группируем по виду и размеру пингвина с помощью 4
сортируем результат по виду и количеству пингвинов в порядке возрастания с помощью 3

Ещё одна БД:

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

src/assay_staff.sql ^[205]

out/assay_staff.out ^[206]

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

src/like_glob.sql ^[207]

out/like_glob.out ^[208]

8 — хотим выбрать столбцы 7 и 6 из таблицы 5
4 — ну понятно, запрос будет выполнен в таблице 3
2 — хотим выбрать строки, в которых значение столбца 1 содержит подстроку 0 (с помощью 9) или значение столбца 8 содержит 7 (с помощью 6)

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

src/union_all.sql ^[209]

out/union_all.out ^[210]

выбираем 5 самых старых записей из таблицы 3, отсортированных по возрастанию даты начала (2) с помощью подзапроса (внутренний 1)
выбираем 5 самых новых записей из 0, отсортированных по убыванию даты начала (9) с помощью другого подзапроса
объединяем эти 2 подзапроса с помощью 8, так мы получаем временную таблицу, содержащую 10 записей (5 самых старых и 5 самых новых)
из временной таблицы выбираем все столбцы для каждой записи (7) и окончательно сортируем записи по возрастанию даты начала (6) с помощью внешнего 5

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

src/intersect.sql ^[211]

out/intersect.out ^[212]

здесь мы используем 2 для объединения результатов двух отдельных запросов
вначале выбираем данные из таблицы 1, в которых значение поля 0 равно 9
потом выбираем данные из таблицы 8, в которых значение поля 7 меньше 50
с помощью 6 мы находим пересечение, то есть строки, которые являются общими для результатов обоих запросов
в результате будут выбраны строки, которые присутствуют в обоих результатах, то есть записи из 5, где значение 4 равно 3 и значение 2 меньше 50

Исключение

src/except.sql ^[213]

out/except.out ^[214]

при помощи 9 извлекаем 4 поля из 8: 7, 6, 5 и 4
затем используем 3, чтобы отфильтровать только те строки, в которых значение 2 равно 1
после этого при помощи 0 удаляем из исходного результата любые строки, которые также присутствуют в результате второго запроса
второй запрос 9 также извлекает четыре поля из 8: 7, 6, 5 и 4
используем 3, чтобы отфильтровать только те строки, где значение 2 меньше 50

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

src/random_numbers.sql ^[215]

out/random_numbers.out ^[216]

создаём временную таблицу 9
в этой таблице извлекается случайное число с помощью 8
конкатенируем значения 7 и 6 под именем 5 с помощью 4 для разделения
таким образом создаём временную таблицу, содержащую столбцы 3 с случайными числами и 2 со значениями из столбцов 1 и 0 таблицы 9
делаем выборку из временной таблицы 8; в выборку включаем столбцы 7, 6
5 — это мы вычисляем остаток от деления абсолютного значения 4 на 10
ну и в конце оставляем только строки, где 3 меньше 5

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

src/create_use_index.sql ^[217]

out/create_use_index.out ^[218]

выбираем все значения столбца 0 из таблицы 9, где значение столбца 8 содержит подстроку 7
создаём индекс с именем 6 для столбца 5 в таблице 4
запрашиваем план выполнения запроса (3)

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

src/generate_sequence.sql ^[219]

out/generate_sequence.out ^[220]

0 — генерируем ряд чисел от 1 до 5
SELECT value — выбираем этот столбец value со сгенерированными числами от 1 до 5

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

src/data_range_sequence.sql ^[221]

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 ^[222]

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

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

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

src/date_sequence.sql ^[223]

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 ^[224]

|  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 ^[225]

     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 ^[226]

|    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 ^[227]

     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 ^[228]

|     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 ^[229]-код, однако, содержит ошибку, правильный синтаксис должен быть следующим:

     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 ^[230]

     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 ^[231]

|      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 ^[232]

     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 ^[233]

|    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 ^[234]

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

out/correlated_subquery.out ^[235]

|       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 ^[236]

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

out/nonexistence.out ^[237]

|     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

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

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

|       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 ^[240]

     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 ^[241]

|   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 ^[242]

     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 ^[243]

|   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 ^[244]

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 ^[245]

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 ^[246]

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 ^[247]

| 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 ^[248]

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 ^[249]

|    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 ^[250]

sqlite3 data/lab_log.db

src/lab_log_schema.sql ^[251]

.schema

out/lab_log_schema.out ^[252]

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 ^[253]

SELECT *
FROM machine;

out/json_in_table.out ^[254]

| 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 ^[255]

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

out/json_field.out ^[256]

| 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 ^[257]

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

out/json_array.out ^[258]

| ident | length |                            first                             |
|-------|--------|--------------------------------------------------------------|
| 1     | 4      | {"machine":"Inphormex","person":["Gabrielle","Dubu00e9"]}   |
| 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 ^[259]

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

out/json_unpack.out ^[260]

| ident | key |                            value                             |
|-------|-----|--------------------------------------------------------------|
| 1     | 0   | {"machine":"Inphormex","person":["Gabrielle","Dubu00e9"]}   |
| 1     | 1   | {"machine":"Inphormex","person":["Gabrielle","Dubu00e9"]}   |
| 1     | 2   | {"machine":"WY401","person":["Gabrielle","Dubu00e9"]}       |
| 1     | 3   | {"machine":"Inphormex","person":["Gabrielle","Dubu00e9"]}   |
| 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 ^[261]

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

out/json_array_last.out ^[262]

| 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 ^[263]

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

out/json_modify.out ^[264]

| 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 ^[265]

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

out/count_penguins.out ^[266]

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

Immediate If в SQL

src/make_active.sql ^[267]

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 ^[268]

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

out/active_penguins.out ^[269]

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

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

src/views.sql ^[270]

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 ^[271]

|  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 ^[272]

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 ^[273]

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

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

src/all_jobs_check.sql ^[274]

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 ^[275]

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 — это акроним, который описывает набор свойств транзакций баз данных, предназначенных для обеспечения целостности данных в случае ошибок, сбоев питания и других непредвиденных ситуаций:

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

TRANSACTION в SQL

src/transaction.sql ^[276]

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 ^[277]

|   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 ^[278]

   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 ^[279]

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 ^[280]

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 ^[281]

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 ^[282]

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 ^[283]

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 ^[284]

-- 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, прежде чем разрешить вставку новых записей в таблицу ^[285] job
- обновляет общее количество отработанных часов для соответствующего человека в таблице total путем добавления нового количества отработанных часов из таблицы job

src/trigger_successful.sql ^[286]

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

out/trigger_successful.out ^[287]

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

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

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

src/trigger_firing.sql ^[288]

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

out/trigger_firing.out ^[289]

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

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

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

src/lineage_setup.sql ^[290]

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 ^[291]

SELECT *
FROM lineage;

out/represent_graph.out ^[292]

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

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

src/recursive_lineage.sql ^[293]

     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 ^[294]

|  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 ^[295]

SELECT *
FROM person;

out/contact_person.out ^[296]

| 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 ^[297]

SELECT *
FROM contact;

out/contact_contacts.out ^[298]

|       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 |

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

src/bidirectional.sql ^[299]

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 ^[300]

| 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 ^[301]

   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 ^[302]

|       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 ^[303]

     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 ^[304]

|         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 ^[305]

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 ^[306]

[(344,)]

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

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

src/incremental_fetch.py ^[307]

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 ^[308]

('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 ^[309]

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 ^[310]

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 ^[311]

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 ^[312]

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

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 ^[313]

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 ^[314]

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

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

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

src/exceptions.py ^[315]

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 ^[316]

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 ^[317]

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 ^[318]

(-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 ^[319]

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 ^[320]

(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 ^[321]

pip install pandas

src/select_pandas.py ^[322]

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 ^[323]

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 ^[324]

pip install polars pyarrow adbc-driver-sqlite

src/select_polars.py ^[325]

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 ^[326]

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 ^[327]

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 ^[328]

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 ^[329]

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 ^[330]

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
выполняем этот запрос в сессии БД, и для каждой строки результата выводится название отдела и персональные данные сотрудника

The end

Что ж, пользуйтесь этими примерами SQL-запросов на здоровье; особенно эта подборка может быть полезной, если хочется кому-то объяснить что-то из SQL, и нужен подходящий пример

Всех с пятницей!

Автор: uproger

Источник ^[331]

Сайт-источник PVSM.RU: https://www.pvsm.ru

Путь до страницы источника: https://www.pvsm.ru/news/389993

Ссылки в тексте:

[1] канале по анализу данных: https://t.me/+dXZpK8lQ0lY4YjAy

[2] здесь большая полезная папка,: https://t.me/addlist/qht-ouKSGaQwNjcy

[3] скачать её: https://github.com/gvwilson/sql-tutorial/raw/main/sql-tutorial.zip

[4] Выбираем все значения из таблички: #0

[5] Дополнительные команды SQL: #1

[6] Выбираем нужные столбцы: #2

[7] Сортировка: #3

[8] Ограничение выводимых записей: #4

[9] Ещё некоторые параметры вывода: #5

[10] Удаляем дубликаты: #6

[11] Фильтруем результаты: #7

[12] Более сложные условия фильтрации: #8

[13] Некоторые математические действия: #9

[14] Переименовываем столбцы: #10

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

[16] Вывод с условием при помощи WHERE: #12

[17] Условие с отрицанием: #13

[18] Выбираем NULL значения: #14

[19] Агрегирование в SQL: #15

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

[21] Подсчёт значений при помощи COUNT: #17

[22] Группировка: #18

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

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

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

[26] Читабельный вывод: #22

[27] Фильтрация входных данных: #23

[28] Создание табличек: #24

[29] Вставляем данные: #25

[30] Обновляем строки: #26

[31] Удаляем строки: #27

[32] Резервное копирование: #28

[33] Объединение табличек при помощи JOIN: #29

[34] INNER JOIN: #30

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

[36] LEFT JOIN: #32

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

[38] Объединение значений: #34

[39] SELECT DISTINCT и условие WHERE: #35

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

[41] Подзапросы: #37

[42] Автоикремент и PRIMARY KEY: #38

[43] Изменение таблички при помощи ALTER: #39

[44] Создание новой таблички на базе старой: #40

[45] Удаление таблички: #41

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

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

[48] CTE — табличные выражения: #44

[49] Смотрим план запроса с помощью EXPLAIN: #45

[50] Нумеруем строки: #46

[51] Условия if-else: #47

[52] Выбираем с помощью SELECT и CASE: #48

[53] Работаем с диапазоном значений: #49

[54] Ищем по фрагменту с помощью LIKE: #50

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

[56] Пересечение отдельных табличек: #52

[57] Исключение : #53

[58] Случайные значения в SQL: #54

[59] Создание индексов: #55

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

[61] Генерируем последовательность на основе данных: #57

[62] Генерация последовательностей дат: #58

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

[64] JOIN таблички с собой же: #60

[65] Генерируем уникальные пары значений: #61

[66] Фильтрация пар: #62

[67] EXISTS: #63

[68] NOT EXISTS в SQL: #64

[69] Опережение и отставание : #65

[70] Оконные функции: #66

[71] Используем PARTITION BY в SQL: #67

[72] Данные типа blob: #68

[73] Сохранение JSON: #69

[74] Выбираем отдельные поля в JSON: #70

[75] Доступ к JSON-объекту: #71

[76] Распаковка JSON: #72

[77] Последний элемент в массиве: #73

[78] Модифицируем JSON: #74

[79] Immediate If в SQL: #75

[80] Представление VIEW в SQL: #76

[81] Добавляем проверку CHECK: #77

[82] TRANSACTION в SQL: #78

[83] ROLLBACK в SQL: #79

[84] Откат с помощью ROLLBACK: #80

[85] Вставка значений: #81

[86] Создание триггера: #82

[87] Рекурсивный запрос: #83

[88] Продолжаем работать с bi_contact: #84

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

[90] Рекурсивно устанавливаем метки: #86

[91] Работа с SQL в Python при помощи sqlite3: #87

[92] Инкрементная выборка : #88

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

[94] Интерполируем значения: #90

[95] Выполнение полноценных SQL-запросов в Python: #91

[96] Исключения SQLite в Python: #92

[97] Python и SQLite, ещё некоторые возможности: #93

[98] Работа с датой и временем: #94

[99] SQL в Jupyter Notebooks: #95

[100] Pandas и SQL: #96

[101] Polars и SQL: #97

[102] ORM: #98

[103] Продолжаем работать с ORM: #99

[104] The end: #100

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[229] SQL: https://uproger.com/gde-uchit-sql-besplatno-v-2024-godu/

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[285] таблицу: https://uproger.com/sql-dorozhnaya-karta-2024-goda/

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[326] out/select_polars.out: https://gvwilson.github.io/sql-tutorial/out/select_polars.out

[327] src/orm.py: https://gvwilson.github.io/sql-tutorial/src/orm.py

[328] out/orm.out: https://gvwilson.github.io/sql-tutorial/out/orm.out

[329] src/orm_relation.py: https://gvwilson.github.io/sql-tutorial/src/orm_relation.py

[330] out/orm_relation.out: https://gvwilson.github.io/sql-tutorial/out/orm_relation.out

[331] Источник: https://habr.com/ru/articles/792630/?utm_source=habrahabr&utm_medium=rss&utm_campaign=792630

Нажмите здесь для печати.