create index idx_partial on tab1(a, b) where a is not null and b = 5;
select * from tab1 where a is not null and b = 5; --> search table tab1 using index

create index idx_expression on tab1(a + b);
select * from tab1 where a + b > 10; --> search table tab1 using index ...
select * from tab1 where b + a > 10; --> scan table

create table tab1 (
  a integer primary key,
  b int,
  c text,
  d int generated always as (a * abs(b)) virtual,
  e text generated always as (substr(c, b, b + 1)) stored
);

create virtual table idx_rtree using rtree (
  id,              -- ключ
  minx, maxx,      -- мин и макc x координаты
  miny, maxy,      -- мин и макc y координаты
  data             -- дополнительные данные
);
insert into idx_rtree values (1, -80.7749, -80.7747, 35.3776, 35.3778);
insert into idx_rtree values (2, -81.0, -79.6, 35.0, 36.2);
select id from idx_rtree
where minx >= -81.08 and maxx <= -80.58 and miny >= 35.00  and maxy <= 35.44;

alter table tbl1 rename column a to b;

create table vocabulary (word text primary key, count int default 1);
insert into vocabulary (word) values ('jovial')
  on conflict (word) do update set count = count + 1;

update inventory
   set quantity = quantity - daily.amt
  from (select sum(quantity) as amt, itemid from sales group by 2) as daily
where inventory.itemid = daily.itemid;

with tab2 as (select * from tab1 where a > 10),
  tab3 as (select * from tab2 inner join ...)
select * from tab3;

-- Генерация значений
with recursive cnt(x) as (
  values(1) union all select x + 1 from cnt where x < 1000
)
select x from cnt;
-- Нахождения дочерних элементов или родителя в таблице с иерархией
create table tab1 (id, parent_id);
insert into tab1 values
  (1, null), (10, 1), (11, 1), (12, 10), (13, 10),
  (2, null), (20, 2), (21, 2), (22, 20), (23, 21);
-- Узлы ниже по иерархии
with recursive tc (id) as (
  select id from tab1 where id = 10
  union
  select tab1.id from tab1, tc where tab1.parent_id = tc.id
)
-- Узелы верхнего уровня для выбранных дочерних
with recursive tc (id, parent_id) as (
  select id, parent_id from tab1 where id in (12, 21)
  union
  select tc.parent_id, tab1.parent_id
  from tab1, tc where tab1.id = tc.parent_id
)
select distinct id from tc where parent_id is null order by 1;
-- Формирования отступов при выводе, напр. для структуры отделов
create table org(name text primary key, boss text references org);
insert into org values ('Alice', null),
  ('Bob', 'Alice'), ('Cindy', 'Alice'), ('Dave', 'Bob'),
  ('Emma', 'Bob'), ('Fred', 'Cindy'), ('Gail', 'Cindy');
with recursive
  under_alice (name, level) as (
    values('Alice', 0)
    union all
    select org.name, under_alice.level + 1
      from org join under_alice on org.boss = under_alice.name
     order by 2
  )
select substr('..........', 1, level * 3) || name from under_alice;

-- Номер строки в результате
create table tab1 (x integer primary key, y text);
insert into tab1 values (1, 'aaa'), (2, 'ccc'), (3, 'bbb');
select x, y, row_number() over (order by y) as row_number from tab1 order by x;
-- Таблица используется для следующих примеров
create table tab1 (a integer primary key, b, c);
insert into tab1 values (1, 'A', 'one'),
  (2, 'B', 'two'), (3, 'C', 'three'), (4, 'D', 'one'),
  (5, 'E', 'two'), (6, 'F', 'three'), (7, 'G', 'one');
-- Доступ к предыдущей и следующей записи в окне
select a, b, group_concat(b, '.') over (order by a rows between 1 preceding and 1 following) as prev_curr_next from tab1;
-- Значения в окне (группе, определяемой колонкой c)  от текущей строки до конца окна
select c, a, b, group_concat(b, '.') over (partition by c order by a range between current row and unbounded following) as curr_end from tab1 order by c, a;
-- Пропуск строк в окне по условию
select c, a, b, group_concat(b, '.') filter (where c <> 'two') over (order by a) as exceptTwo from t1 order by a;

vacuum into 'D:/backup/' || strftime('%Y-%M-%d', 'now') || '.sqlite';

select 'a' || ' 123 ' || null; --> null
select printf('%s %i %s', 'a', 123, null); --> 123 a
select printf('%s %i %i', 'a', 123, null); --> 123 a 0

select strftime('%Y-%M-%d %H:%m', 'now'); --> UTC время
select strftime('%Y-%M-%d %H:%m', 'now', 'localtime'); --> местное время
select strftime('%s', 'now'); -- текущее Unix-время
select strftime('%s', 'now', '+2 day'); --> текущее unix-время плюс два дня
-- Конвертация unix-времени в локальное для пользователя - 21-11-2020 15:25:14
select strftime('%d-%m-%Y %H:%M:%S', 1605961514, 'unixepoch', 'localtime')

select json_array(1, 2, 3); --> [1,2,3] (строка)
select json_array_length(json_array(1, 2, 3)); --> 3
select json_array_length('[1,2,3]'); --> 3
select json_object('a', json_array(2, 5), 'b', 10); --> {"a":[2,5],"b":10} (строка)
select json_extract('{"a":[2,5],"b":10}', '$.a[0]');  --> 2
select json_insert('{"a":[2,5]}', '$.c', 10); --> {"a":[2,5],"c":10} (строка)
select value from json_each(json_array(2, 5)); --> 2 строки 2, 5
select json_group_array(value) from json_each(json_array(2, 5)); --> [2,5] (строка)

create virtual table emails using fts5(sender, body);
SELECT * FROM emails WHERE emails = 'fts5'; -- sender или body содержит fts5

create virtual table temp.tab1 using csv(filename='thefile.csv');
select * from tab1;

select value from generate_series(5, 100, 5);