---

set search_path to home;


select authors.last_name, authors.name, books.title
from authors
join books on authors.id = books.id_author;


select distinct users.last_name, users.name
from users
join tickets on users.id = tickets.user_id
join books on tickets.book_id = books.bshifr
join authors on books.id_author = authors.id
where authors.last_name = 'Толстой'
and authors.name = 'Лев';


select distinct authors.last_name
from authors
join books on authors.id = books.id_author
where books.publishing = 'Дрофа';


select distinct authors.last_name, authors.name
from authors
join books on authors.id = books.id_author
where books.price > 10000
and books.price < 20000;


select books.*
from books
join authors on books.id_author = authors.id
where authors.last_name = 'Пушкин';


select distinct users.last_name, users.name
from users
join tickets on users.id = tickets.user_id
join books on tickets.book_id = books.bshifr
join authors on books.id_author = authors.id
where authors.last_name like 'К%';


select distinct users.last_name
from users
join tickets on users.id = tickets.user_id
where tickets.issue_date between date_trunc('year', current_date)::date and current_date;


select
    authors.last_name автор,
    books.title книга,
    coalesce(books.price::text, 'бесплатно') стоимость
from authors
join books on authors.id = books.id_author;


select
    users.last_name,
    users.name,
    tickets.book_id
from users
left join tickets on users.id = tickets.user_id;


select users.last_name, users.name
from users
left join tickets on users.id = tickets.user_id
where tickets.id is null;


select books.*
from books
left join tickets on books.bshifr = tickets.book_id
where tickets.id is null;


select
    books.title,
    case
        when count(tickets.id) = 0 then 'книга не выдавалась'
        else count(tickets.id)::text
    end количество_выдач
from books
left join tickets on books.bshifr = tickets.book_id
group by books.bshifr, books.title;


select books.*
from books
where price > 250

union

select books.*
from books
join authors on books.id_author = authors.id
where authors.last_name = 'Толстой';


select authors.last_name, authors.name
from authors
join books on authors.id = books.id_author
where books.price > 10000

union all

select authors.last_name, authors.name
from authors
join books on authors.id = books.id_author
where books.price < 20000;


select
    books.bshifr,
    books.title,
    tickets.id ticket_id,
    tickets.user_id
from books
left join tickets on books.bshifr = tickets.book_id

union

select
    books.bshifr,
    books.title,
    tickets.id ticket_id,
    tickets.user_id
from books
right join tickets on books.bshifr = tickets.book_id;


drop table if exists users_copy;

create table users_copy as
select *
from users;


select count(*)
from (
    select *
    from users

    union

    select *
    from users_copy
) result;


select count(*)
from (
    select *
    from users

    union all

    select *
    from users_copy
) result;


select books.*
from books
where price > 250

intersect

select books.*
from books
join authors on books.id_author = authors.id
where authors.last_name = 'Толстой';


select books.*
from books
where price > 250

except

select books.*
from books
join authors on books.id_author = authors.id
where authors.last_name = 'Толстой';


select
    authors.last_name,
    authors.name,
    avg(books.price) средняя_цена
from authors
join books on authors.id = books.id_author
group by authors.id, authors.last_name, authors.name;


select
    users.last_name,
    users.name,
    count(tickets.id) количество_книг
from users
left join tickets on users.id = tickets.user_id
group by users.id, users.last_name, users.name;


select count(books.bshifr) количество_книг
from authors
join books on authors.id = books.id_author
where authors.last_name like 'Р%';


select
    authors.last_name,
    authors.name,
    count(books.bshifr) количество_книг,
    sum(books.price) стоимость_книг
from authors
join books on authors.id = books.id_author
group by authors.id, authors.last_name, authors.name;


select
    section,
    avg(price) средняя_цена
from books
group by section;


select
    authors.last_name,
    authors.name,
    books.publishing,
    count(books.bshifr) количество_книг
from authors
join books on authors.id = books.id_author
group by
    authors.id,
    authors.last_name,
    authors.name,
    books.publishing;


select
    authors.last_name,
    authors.name,
    count(distinct books.bshifr) количество_книг,
    count(distinct tickets.user_id) количество_читателей
from authors
left join books on authors.id = books.id_author
left join tickets on books.bshifr = tickets.book_id
group by authors.id, authors.last_name, authors.name;


select
    authors.last_name,
    authors.name,
    books.section,
    count(books.bshifr) количество_книг
from authors
join books on authors.id = books.id_author
group by
    authors.last_name,
    authors.name,
    books.section;


select
    books.section,
    authors.last_name,
    authors.name,
    count(books.bshifr) количество_книг
from books
join authors on books.id_author = authors.id
group by grouping sets (
    (books.section, authors.last_name, authors.name),
    (books.section),
    (authors.last_name, authors.name),
    ()
);


select *
from generate_series(
    1::numeric,
    10::numeric,
    0.5::numeric
);


select generate_series(
    current_date,
    current_date + interval '30 days',
    interval '1 day'
)::date;


drop table if exists events;

create table events (
    id integer,
    predid integer,
    postid integer,
    descr varchar(50)
);


select
    id * 10 id,
    (id - 1) * 10 predid,
    (id + 1) * 10 postid,
    'Event ' || id * 10 descr
from generate_series(1, 10) as g(id);


insert into events
select
    id * 10,
    (id - 1) * 10,
    (id + 1) * 10,
    'Event ' || id * 10
from generate_series(1, 10) as g(id);


select *
from events;


with recursive event_tree as (
    select
        id,
        predid,
        postid,
        descr
    from events
    where id = 10

    union all

    select
        events.id,
        events.predid,
        events.postid,
        events.descr
    from events
    join event_tree on events.id = event_tree.postid
)
select *
from event_tree;


alter table users
add column if not exists level integer;

alter table users
add column if not exists manager_id integer;


update users
set level = 0, manager_id = null
where id = 1;

update users
set level = 1, manager_id = 1
where id = 2;

update users
set level = 2, manager_id = 2
where id = 3;


with recursive user_tree as (
    select
        id,
        last_name,
        name,
        manager_id,
        level
    from users
    where id = 1

    union all

    select
        users.id,
        users.last_name,
        users.name,
        users.manager_id,
        users.level
    from users
    join user_tree on users.manager_id = user_tree.id
)
select *
from user_tree
where id <> 1;