obsidian/docs/Script-1.sql

insert into family (family_name) values (
	'Синицевые'
);

insert into family (family_name, description) values (
	'Врановые',
	'Семейство птиц, включающее воронов, грачей, сорок и других'
), (
	'Дроздовые',
	'Семейство певчих птиц, включающее дроздов, дрозд-рябинников и других'
);

insert into genus (genus_name, family_id) values (
	'Синица',
	1
), (
	'Гаичка',
	1
), (
	'Ворон',
	2
), (
	'Сорока',
	2
), (
	'Дрозд',
	3
);


table genus;

select genus_name from genus;

select genus_name, genus_id from genus;

select genus_name as bird_genus, genus_id from genus;

select genus_name as bird_genus, genus_id from public.genus;
	
select * from family where family_name='Дроздовые' or family_name='Синицевые';
select * from family where family_name in ('Дроздовые', 'Синицевые');

select * from family where family_name='Дроздовые' and family_name='Синицевые';

select * from family where family_name like '%а%' or family_name like '%с%';

select * from family;

-- Используем функцию coalesce для полей со значением null

select family_id, family_name, coalesce(description, 'не заполнено') description from family;

select * from family where description is null or family_name like '%Д%';


-- Временная таблица для сортировка
create temp table author_tmp(
    author_id bigint generated always as identity primary key,
    first_name varchar(150) not null,
    last_name varchar(150) not null
);

insert into author_tmp (first_name, last_name) values
   ('Михаил', 'Шишкин'),
   ('Михаил', 'Веллер'),
   ('Михаил', 'Шолохов'),
   ('Михаил', 'Зощенко'),
   ('Михаил', 'Булгаков'),
   ('Александр', 'Беляев'),
   ('Александр', 'Пушкин'),
   ('Лусиану', 'Рамальо');

select * from author_tmp;
select * from author_tmp order by first_name;

select * from family order by family_id desc;

select * from family order by description nulls first;

select * from family;
select family_name from family where family_name like 'Д%' order by family_name;

select * from (
    values (2, 3), (3, 20), (2, 4), (1, 20), (3, 4), (1, 19)
) as something(a, b)


select * from (
    values (2, 3), (3, 20), (2, 4), (1, 20), (3, 4), (1, 19)
) as something(a, b)
order by a, b;

select * from (
    values (3, 20), (2, 4), (1, 20), (3, 4), (3, null), (1, 19), (2, 1), (2, null)
) as something(a, b)
order by a, b desc nulls last;


table genus;
table species;


select * from species 
order by species_name, description 
limit 10 offset 10;


select species_id, species_name, primary_color from species where primary_color ilike '%бел%'
order by species_name
limit 5;

select 'sadfs' || 'sadfasdf' || now() as message;

drop schema if exists tests cascade;
create schema tests;


select format('Птичка: %s', species_name) as "птичка" from species
order by species_name;