Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --books--
- DROP TABLE IF EXISTS books cascade;
- CREATE TABLE books (
- id SERIAL PRIMARY KEY,
- name VARCHAR(255) NOT NULL
- );
- INSERT INTO books (name) VALUES
- ('Война и мир'),
- ('Преступление и наказание'),
- ('Мастер и Маргарита'),
- ('Тихий дон'),
- ('Бойцовский Клуб');
- --book_reg--
- DROP TABLE IF EXISTS book_reg cascade;
- CREATE TABLE book_reg (
- id INT PRIMARY KEY,
- DATE DATE NOT NULL
- );
- INSERT INTO book_reg (id, DATE) VALUES
- (1, TO_DATE('01.05.2006', 'DD.MM.YYYY')),
- (3, TO_DATE('05.07.2004', 'DD.MM.YYYY')),
- (5, TO_DATE('30.09.2015', 'DD.MM.YYYY'));
- --genres--
- DROP TABLE IF EXISTS genres cascade;
- CREATE TABLE genres (
- id SERIAL PRIMARY KEY,
- name VARCHAR(255) NOT NULL
- );
- INSERT INTO genres (name) VALUES
- ('Русская классика'),
- ('Зарубежные романы'),
- ('Драма');
- --book_genres--
- DROP TABLE IF EXISTS book_genres cascade;
- CREATE TABLE book_genres (
- book_id INT NOT NULL,
- genre_id INT NOT NULL,
- UNIQUE (book_id, genre_id),
- FOREIGN KEY (book_id) REFERENCES books (id),
- FOREIGN KEY (genre_id) REFERENCES genres (id)
- );
- INSERT INTO book_genres (book_id, genre_id) VALUES
- (1, 1),
- (1, 3),
- (2, 1),
- (3, 1),
- (4, 1),
- (5, 2),
- (5, 3);
- --проверки
- SELECT * FROM books;
- SELECT * FROM book_reg;
- SELECT * FROM genres;
- SELECT * FROM book_genres;
- --запрос
- SELECT g.name AS genre_name, COUNT(DISTINCT b.id) AS book_count
- FROM book_reg b_r
- JOIN books b ON b_r.id = b.id
- JOIN book_genres b_g ON b.id = b_g.book_id
- JOIN genres g ON b_g.genre_id = g.id
- WHERE b_r.DATE > TO_DATE('12.04.2003', 'DD.MM.YYYY')
- GROUP BY g.name
- ORDER BY book_count DESC;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement