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;

update species
set species_name='Древесный дрозд'
where species_name='Дрозд лесной'

update species
set species_name='Дрозд лесной'
where species_name='Древесный дрозд'

-- Создание схемы
drop schema if exists birds cascade;
create schema birds;

table genus;
select genus_id as crows_id from genus where genus_name='Ворон';
select * from species;

create table crows as 
select * from species where genus_id=(select genus_id from genus where genus_name='Ворон');

table crows;
truncate table crows;
drop table crows;


create table smth (a int);
insert into smth(a) values (1), (2), (3);
table smth;
update smth set a=a*2;

select pg_column_size('привеееет');

create temp table game_genre (
    genre_id bigint primary key,
    genre_name text
);

create temp table game (
    game_id int generated always as identity primary key,
    genre_id int not null references game_genre(genre_id)
);

insert into game_genre(genre_id, genre_name) values (1234567890123456789, 'шутер');

insert into game(genre_id) values (1234567890123456789);

comment on table family is 'Семейство птиц';
comment on column family.family_id is 'Уникальный идентификатор семейства';
comment on column family.family_name is 'Название семейства';
comment on column family.description is 'Описание семейства';

comment on table genus is 'Род птиц';
comment on column genus.genus_id is 'Уникальный идентификатор рода';
comment on column genus.genus_name is 'Название рода';
comment on column genus.family_id is 'Ссылка на семейство';
comment on column genus.description is 'Описание рода';

comment on table species is 'Вид птиц';
comment on column species.species_id is 'Уникальный идентификатор вида';
comment on column species.species_name is 'Название вида';
comment on column species.genus_id is 'Ссылка на род';
comment on column species.description is 'Описание вида';
comment on column species.average_length is 'Средняя длина в см';
comment on column species.average_weight is 'Средний вес в г';
comment on column species.primary_color is 'Цвет оперения';