obsidian/hardcore web development/11. SQL/quiz_1.sql

-- -----------------------------------------------------------------
-- Задания

-- -----------------------------------------------------------------
-- 11.16 Выборка данных из других источников

-- Выборка из подзапроса
select genus_name from (
    select * from genus
);


-- Выборка анбора дат с интервалом в 2 месяца
select date
from generate_series('2020-01-01'::date,
                    '2024-06-01'::date,
                    '2 month'::interval) date;

--
select * from (values
    ('The Shawshank Redemption', 9.3, 1994),
    ('The Godfather', 9.2, 1972),
    ('The Dark Knight', 9.1, 2008),
    ('Inception', 8.8, 2010)
) t(movie, imdb_rating, year);


-- -----------------------------------------------------------------
-- 11.18 Фильтрация данных

table family;

select * from family where family_name='Дроздовые';

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

select * from family where family_name in ('Дроздовые', 'Синицевые');

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

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

select * from family where family_name ilike '%а%';


-- -----------------------------------------------------------------
-- 11.19 Особое значение NULL, coalesce

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

select * from family where description is null;

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


-- -----------------------------------------------------------------
-- 11.20 Сортировка данных

select * from family order by family_id;

select * from family order by description nulls first;

select * from family order by sescription nulls last;

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

-- -----------------------------------------------------------------
-- 11.21 Ограничение количества результатов запроса

table species;

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

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

-- -----------------------------------------------------------------
-- 11.22 Измененяем данные в БД

select 'Птичка: ' || species_name птичка from species order by species_name;

-- или через функцию format
select format('Птичка: %s', species_name) as 'птичка' 
from species order by species_name;

-- Обновить записа
update species set species_name='Древесный дрозд'
where species_name='Дрозд лесной';

-- Удалить таблицу. Но команда упадет с ошибкой если таблицы нет или если
-- на таблицу ссылается другая таблица.
drop table something;

-- Удалить таблицу. Но команда упадет если на таблицу ссылается другая таблица.
drop table if exists something;

-- Удалить таблицу.
drop table if exists something cascade;

-- Создание таблицы из данных другой таблицы
create temp table crows as
select * from species where genus_id=(
	select genus_id from genus where genus_name='Ворон');

-- Создание таблицы из данных другой таблицы
select * into crows from species s where s.genus_id=(
	select genus_id from genus g where g.genus_name='Ворон'
);

-- Обновление данных в таблице
create table smth (a int);

update smth set a = a * 2;

-- -----------------------------------------------------------------
-- 11.26 Работа со строками

select species_name from species 
order by length(species_name) desc, species_name limit 5;

-- -----------------------------------------------------------------
-- 11.28 Работа со перечислениями

create table color (
	name varchar(22) primary key check (name in (
		'адовый красный',
		'ядерный зеленый',
		'подозрительная мурена'
	))
);

create table color (
	name text check (length(name) between 4 and 20)
);

-- -----------------------------------------------------------------
-- 11.29 Работа со временем

select to_char(created_at, 'DD.MM.YYYY HH24:MI:SS') "дата инцидента", 
	message "сообщение" from log;

insert into log (created_at, message) values 
	('2024-07-31 20:19:51'::timestamp, 'Произошло страшное: сломался приём платежа #1128'),
    ('2024-07-31 17:19:52'::timestamp, 'Трындец с отправкой почты, похоже, почтовый сервер недоступен!'),
    ('2024-07-31 17:20:53'::timestamp, 'Ужас, всё работает!');


drop table if exists log;
create temp table log(
	log_id bigint generated always as identity,
	created_at timestamp not null,
	message text not null
);

insert into log (created_at, message) values
	('2024-06-02 20:20:20'::timestamp, 'Это событие за прошлый год'),
	('2025-06-02 20:20:20'::timestamp, 'Это событие за текущий год');

table log;

select * from log where extract(month from(created_at)) = extract(month from now()) 
and extract(year from(created_at)) = extract(year from now());

-- Вернуть данные за этот месяц
select * from log
where date_trunc('month', created_at) = date_trunc('month', current_timestamp)
order by log_id;

-- Вернуть данные за последнюю неделю
select * from log where created_at >= current_date - interval '7 days'

-- Удалить данные за последние 30 дней
delete from log
where created_at < current_date - intercal '30 days';

-- Прибавить месяц
select '2024-01-31'::date + '1 month'::interval;

-- Прибавить год
select '2024-02-29'::date + '1 year'::interval;

-- Эти запросы вернут одинаковое значение
select '2024-02-28'::date - '1 year'::interval;
select '2024-02-29'::date - '1 year'::interval;

-- Запрос вернет ответ типа integer
select '2024-02-01'::date - '2023-01-01'::date d;

-- Запрос вернет ответи типа interval
select '2024-02-01'::timestamp - '2023-01-01'::timestamp d;

-- Форматирование даты
select 
	to_char(start, 'DD.MM.YYYY') start,
	to_char(finish, 'DD.MM.YYYY') finish
	extract(day from (finish - start) delta_in_days
from records order by start, finish;

-- С помощью функции age можно вывести дельту
select 
	to_char(start, 'DD.MM.YYYY') start,
	to_char(finish, 'DD.MM.YYYY') finish
	age(finish, start) delta
from records order by start, finish;

-- Достает email тех у кого в апреле день рождения
select email from employees 
where extract('month' from birthday)=4
order by email;

-- -----------------------------------------------------------------
-- 11.31 Работа с массивами

drop table if exists log;
table log;

create temp table log (
	log_id bigint generated always as identity,
	created_at timestamp not null default current_timestamp,
	messages text not null
);

insert into log (created_at, messages) values(
	'2024-08-01 00:47:28.821', 'кажется, всё пропало|или нет, пытаемся восстановить связь|нет, точно всё пропало'
);

insert into log (created_at, messages) values(
	'2024-08-01 01:06:21.581', 'id=128|ничоси, опять всё сломалось, никогда такого не было и вот опять'
);

select log_id, created_at, (string_to_array(messages, '|'))
[array_length(string_to_array(messages, '|'), 1)] as last_message 
from log;

select log_id, created_at,
	split_part(messages, '|', array_length(string_to_array(messages, '|'), 1)) as last_message
from log;

select log_id, created_at, split_part(messages, '|', -1) as last_message from log;

--
select unnest(string_to_array(messages, '|')) as messages from log;

select int_array from some_entity where 777=any(int_array);

select unnest(string_to_array(messages, '|')) from log;

select unnest as messages from (select unnest(string_to_array(messages, '|')) from log) 
where unnest like '%всё%';

-- более короткое решение
select t.* from (
	select unnest(string_to_array(messages, '|') messages
	from log
) t where t.messages '%всё%';

-- -----------------------------------------------------------------
-- 11.33 Работа с JSON и JSONB

drop table if exists book_json;

create temp table book_json (
	book_id bigint generated always as identity primary key,
	data jsonb not null
);

-- Данные в поле data (jsonb) могут различаться
insert into book_json (data) values 
	('{"name": null}'),
	('{"name": ""}'),
	('{"name": "Тихий Дон"}'),
	('{"name": 777}'),
	('{}');

table book_json;

select book_id, data from book_json where data ? 'name';

select book_id, data from book_json where data->>'name' is not null;

select book_id, data from book_json where length(data->>'name') > 0;

-- или вот такой вариант
select * from book_json
where data->>'name' is not null and data->>'name' != '';

-- Создание таблицы с ограничениями по jsonb
create table book_json(
	book_id bigint generated always as identity primary key,
	data jsonb not null check (
		data->>'name' is not null and
		data->>'pages' is not null and
		(data->>'pages')::int > 0
	)
);

-- -----------------------------------------------------------------
-- 11.33 Изменение структуры таблиц

-- Добавить ограничение на таблицу
alter table book_json add check (
	data->>'name' is not null and
	data->>'pages' is not null and
	(data->>'pages')::int > 0
);

-- Добавить колонку к таблице
alter table book_json
add column created_at timestamp not null default current_timestamp;

alter table book_json
add column created_at timestamp not null default now();

-- -----------------------------------------------------------------
-- 11.38 INNER JOIN

table species;
table genus;

select species_name as вид, genus_name as род from species
join genus using(genus_id)
order by species_name;

-- Другие условия
select species_name вид, genus_name род
from species s join genus g on s.genus = g.genus_id
order by species_name;

--
drop table if exists client, payment cascade;

create temp table client (
	client_id bigint generated always as identity primary key,
	email varchar(360) not null
);

create temp table payment (
	payment_id bigint generated always as identity primary key,
	payment_time timestamp not null default current_timestamp,
	amount int not null check (amount > 0),
	client_id bigint not null references client(client_id)
);

insert into client (email) values
	('client1@mail.ru'),
	('client2@mail.ru'),
	('client3@mail.ru');

insert into payment (payment_time, amount, client_id) values
	((now() - '1 month'::interval), 10, 1),
	((now() - '2 month'::interval), 11, 2),
	((now() - '3 month'::interval), 12, 3);



select email from client
join payment using(client_id)
where payment_time::date + interval '1 month' = current_date
order by email

table species;
table genus;
table family;

select s.species_name вид, g.genus_name род
from species s
join genus g on s.genus_id = g.genus_id
order by s.species_name;

-- -----------------------------------------------------------------
-- 11.39 LEFT И RIGHT OUTER JOIN

table species;
table genus;

select species_name вид, genus_name род 
from species s left join genus g using(genus_id)
order by species_name;

-- -----------------------------------------------------------------
-- 11.40 FULL OUTER JOIN

select species_name вид, genus_name род
from species s full join genus g using(genus_id)
order by species_name;

select s.species_name вид, g.genus_name род
from species s
full join genus g using(genus_id)
order by s.species_name;

-- -----------------------------------------------------------------
-- 11.44 JOIN LATERAL

table species;
table genus;
table family;
table observations;

select
	species_name,
	to_char(last_observation.date, 'DD.MM.YYYY') last_observation
from species left join lateral (
	select observation_date as date
	from observations
	where observations.species_id = species.species_id
	order by observation_date desc
	limit 1
) as last_observation on true
order by species_name;

-- -----------------------------------------------------------------
-- 11.44 SEMI JOIN

select species_name from species s
where exists (
	select 1
	from observations o
	where o.species_id=s.species_id
		and extract('year' from observation_date)=2019
) order by species_name;

select observer_name, observation_date
from observations o, species s, genus g, family f
where o.species_id = s.species_id and
	s.genus_id = g.genus_id and
	g.family_id = f.family_id and
	f.family_name = 'Синицевые'
order by observer_name;


--
select family_id from family where family_name='Синицевые'

table genus;

select genus_id from genus g where exists (
	select 1
	from family f
	where family_name='Синицевые'
		and f.family_id=g.family_id
);

select species_id from species s where exists (
	select genus_id from genus g where exists (
	select 1
	from family f
	where family_name='Синицевые'
		and f.family_id=g.family_id
	) and g.genus_id=s.genus_id
);

select observer_name, observation_date from observations o
where exists (
	select 1 from species s where exists (
		select 1 from genus g where exists (
			select 1
			from family f
			where family_name='Синицевые'
				and f.family_id=g.family_id
		) and g.genus_id=s.genus_id
	) and s.species_id = o.species_id
) order by observer_name;

select observer_name, observation_date from observations
join species s using(species_id)
join genus g using(genus_id)
join family f using(family_id)
where family_name = 'Синицевые'
order by observer_name;


-- -----------------------------------------------------------------
-- Разное

select div(11, 5);
select extract(years from '2234-02-23'::date) as year;
SELECT LENGTH('sql-academy') AS str_length;