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;