2YarCEA2

1--books--
2DROP TABLE IF EXISTS books cascade;
3CREATE TABLE books (
4    id SERIAL PRIMARY KEY,
5    name VARCHAR(255) NOT NULL
6);
7
8INSERT INTO books (name) VALUES
9('Война и мир'),
10('Преступление и наказание'),
11('Мастер и Маргарита'),
12('Тихий дон'),
13('Бойцовский Клуб');
14
15--book_reg--
16DROP TABLE IF EXISTS book_reg cascade;
17CREATE TABLE book_reg (
18    id INT PRIMARY KEY,
19    date DATE NOT NULL
20);
21
22INSERT INTO book_reg (id, date) VALUES
23(1, TO_DATE('01.05.2006', 'DD.MM.YYYY')),
24(3, TO_DATE('05.07.2004', 'DD.MM.YYYY')),
25(5, TO_DATE('30.09.2015', 'DD.MM.YYYY'));
26
27--genres--
28DROP TABLE IF EXISTS genres cascade;
29CREATE TABLE genres (
30    id SERIAL PRIMARY KEY,
31    name VARCHAR(255) NOT NULL
32);
33
34INSERT INTO genres (name) VALUES
35('Русская классика'),
36('Зарубежные романы'),
37('Драма');
38
39--book_genres--
40DROP TABLE IF EXISTS book_genres cascade;
41CREATE TABLE book_genres (
42    book_id INT NOT NULL,
43    genre_id INT NOT NULL,
44    UNIQUE (book_id, genre_id),
45    FOREIGN KEY (book_id) REFERENCES books (id),
46    FOREIGN KEY (genre_id) REFERENCES genres (id)
47);
48
49INSERT INTO book_genres (book_id, genre_id) VALUES
50(1, 1),
51(1, 3),
52(2, 1),
53(3, 1),
54(4, 1),
55(5, 2),
56(5, 3);
57
58--проверки
59SELECT * FROM books;
60SELECT * FROM book_reg;
61SELECT * FROM genres;
62SELECT * FROM book_genres;
63
64--запрос
65SELECT g.name AS genre_name, COUNT(DISTINCT b.id) AS book_count
66FROM book_reg b_r
67JOIN books b ON b_r.id = b.id
68JOIN book_genres b_g ON b.id = b_g.book_id
69JOIN genres g ON b_g.genre_id = g.id
70WHERE b_r.date > TO_DATE('12.04.2003', 'DD.MM.YYYY')
71GROUP BY g.name
72ORDER BY book_count DESC;