2024-12-22 15:52:30 +03:00
|
|
|
|
--- Зарегистрировать нового пользователя
|
|
|
|
|
insert into clients (forename, surname, patronymic, age, phone_number, email)
|
2024-12-23 14:32:05 +03:00
|
|
|
|
values ('Обласов', 'Алексей', 'Сергеевич', 20, '89308136075', 'oblasovas53879@mail.ru');
|
2024-12-22 15:52:30 +03:00
|
|
|
|
|
|
|
|
|
--- Вывести доступные категории настольных игр
|
|
|
|
|
select name from sections;
|
|
|
|
|
|
|
|
|
|
--- Вывод названия игры и цена при условии что цена меньше 2000 руб (для незарегистрированного пользователя)
|
|
|
|
|
set lc_monetary to 'ru_RU.UTF-8';
|
|
|
|
|
select name, price
|
|
|
|
|
from board_games
|
|
|
|
|
where price::numeric < 2000;
|
|
|
|
|
|
|
|
|
|
--- Вывод самых дорогих игр с жанрами и издателями
|
|
|
|
|
set lc_monetary to 'ru_RU.UTF-8';
|
|
|
|
|
select
|
|
|
|
|
board_games.name as name,
|
|
|
|
|
genres.name as genre,
|
|
|
|
|
publishers.name as publisher,
|
|
|
|
|
board_games.price as price
|
|
|
|
|
from
|
|
|
|
|
board_games
|
|
|
|
|
join genres using(genre_id)
|
|
|
|
|
join publishers using(publisher_id)
|
|
|
|
|
order by
|
|
|
|
|
price desc
|
|
|
|
|
limit 10;
|
|
|
|
|
|
|
|
|
|
--- Вывод количества продаж за период
|
|
|
|
|
set lc_monetary to 'ru_RU.UTF-8';
|
|
|
|
|
select
|
|
|
|
|
board_games.name as name,
|
|
|
|
|
publishers.name as publisher,
|
|
|
|
|
board_games.price as price,
|
|
|
|
|
sum(orders_games.count) as sells
|
|
|
|
|
from
|
|
|
|
|
board_games
|
|
|
|
|
join publishers using(publisher_id)
|
|
|
|
|
join orders_games using(board_game_id)
|
|
|
|
|
join orders using(order_id)
|
|
|
|
|
where
|
2024-12-23 12:51:08 +03:00
|
|
|
|
orders.order_date between '2024-12-16' and '2024-12-20'
|
2024-12-22 15:52:30 +03:00
|
|
|
|
group by
|
|
|
|
|
board_games.name, publishers.name, board_games.price
|
|
|
|
|
order by
|
|
|
|
|
publishers.name
|