SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE book(book_id SERIAL PRIMARY KEY, title VARCHAR(30)); CREATE TABLE author(author_id SERIAL PRIMARY KEY, name VARCHAR(30)); CREATE TABLE books_authors(id SERIAL PRIMARY KEY, book_id INT, author_id INT, FOREIGN KEY (book_id) REFERENCES book(book_id), FOREIGN key (author_id) REFERENCES author(author_id)); INSERT INTO book(title) VALUES('Книга 1'); INSERT INTO book(title) VALUES('Книга 2'); INSERT INTO book(title) VALUES('Книга 3'); INSERT INTO author(name) VALUES('Пушкин'); INSERT INTO author(name) VALUES('Гоголь'); INSERT INTO author(name) VALUES('Толстой'); INSERT INTO author(name) VALUES('Лермонтов'); INSERT INTO books_authors(book_id, author_id) VALUES(1, 1); INSERT INTO books_authors(book_id, author_id) VALUES(1, 2); INSERT INTO books_authors(book_id, author_id) VALUES(1, 3); INSERT INTO books_authors(book_id, author_id) VALUES(2, 1); INSERT INTO books_authors(book_id, author_id) VALUES(2, 3); INSERT INTO books_authors(book_id, author_id) VALUES(2, 4); CREATE OR REPLACE FUNCTION array_sort (ANYARRAY) RETURNS ANYARRAY LANGUAGE SQL AS $$ SELECT ARRAY(SELECT unnest($1) ORDER BY 1) $$; WITH authors_ids AS ( SELECT author_id FROM author WHERE author.name IN ('Пушкин', 'Гоголь', 'Толстой') ORDER BY author_id ) SELECT book_id, array_sort(array_agg(author_id)) AS authors FROM books_authors GROUP BY book_id HAVING array_agg(author_id) = ARRAY( SELECT * FROM authors_ids ) ORDER BY book_id;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear