SQLize | PHPize | SQLtest

-- Создание таблицы country CREATE TABLE country ( countryID INT PRIMARY KEY, name VARCHAR(50) NOT NULL ); -- Создание таблицы artist CREATE TABLE artist ( artistID INT PRIMARY KEY, countryID INT, surname VARCHAR(50) NOT NULL, name VARCHAR(50) NOT NULL, birthday DATE, death_date DATE, FOREIGN KEY (countryID) REFERENCES country(countryID) ); -- Создание таблицы artwork CREATE TABLE artwork ( artworkID INT PRIMARY KEY, artistID INT, title VARCHAR(200) NOT NULL, created INT, FOREIGN KEY (artistID) REFERENCES artist(artistID) ); -- Создание таблицы exhibition CREATE TABLE exhibition ( exhibitionID INT PRIMARY KEY, title VARCHAR(200) NOT NULL, date DATE, address VARCHAR(200) ); -- Создание таблицы artwork_exhibition CREATE TABLE artwork_exhibition ( artexhID INT PRIMARY KEY, exhibitionID INT, artworkID INT, FOREIGN KEY (exhibitionID) REFERENCES exhibition(exhibitionID), FOREIGN KEY (artworkID) REFERENCES artwork(artworkID) ); -- Заполнение таблицы country INSERT INTO country (countryID, name) VALUES (1, 'Россия'), (2, 'Франция'), (3, 'Италия'); -- Заполнение таблицы artist INSERT INTO artist (artistID, countryID, surname, name, birthday, death_date) VALUES (1, 1, 'Репин', 'Илья', '1844-08-05', '1930-09-29'), (2, 1, 'Кандинский', 'Василий', '1866-12-16', '1944-12-13'), (3, 2, 'Моне', 'Клод', '1840-11-14', '1926-12-05'), (4, 3, 'Да Винчи', 'Леонардо', '1452-04-15', '1519-05-02'), (5, 3, 'Тицман', 'Тицнано', '1488-02-20', '1576-08-27'); -- Заполнение таблицы artwork INSERT INTO artwork (artworkID, artistID, title, created) VALUES (1, 1, 'Иван Грозный и сын его Иван', 1885), (2, 1, 'Не ждали', 1884), (3, 2, 'Композиция VIII', 1923), (4, 2, 'Синий всадник', 1903), (5, 2, 'Синяя гора', 1908), (6, 3, 'Камилла', 1866), (7, 4, 'Тайная вечеря', 1498), (8, 4, 'Витрувианский человек', 1492), (9, 5, 'Успение Богородицы', 1518), (10, 5, 'Венера Урбинская', 1538); -- Заполнение таблицы exhibition INSERT INTO exhibition (exhibitionID, title, date, address) VALUES (1, 'Русская живопись', '2024-03-15', 'ГМИИ имени Пушкина'), (2, 'Эпоха Возрождения', '2024-06-25', 'Новая Третьяковка'), (3, 'Романтизм в искусстве', '2024-09-16', 'Государственный музей изобразительных искусств имени А.С. Пушкина'); -- Заполнение таблицы artwork_exhibition INSERT INTO artwork_exhibition (artexhID, exhibitionID, artworkID) VALUES (1, 1, 1), (2, 1, 2), (3, 1, 3), (4, 1, 4), (5, 1, 5), (6, 2, 6), (7, 2, 7), (8, 2, 8), (9, 2, 9), (10, 2, 10), (11, 3, 3), (12, 3, 9), (13, 3, 10); WITH ArtistArtworkCount AS ( SELECT CONCAT(a.name, ' ', a.surname) AS artist_name, COUNT(ae.artworkID) AS artwork_count FROM artist a JOIN artwork aw ON a.artistID = aw.artistID JOIN artwork_exhibition ae ON aw.artworkID = ae.artworkID GROUP BY a.name, a.surname ) SELECT SUM(CASE WHEN artist_name = 'Илья Репин' THEN artwork_count ELSE 0 END) AS "Илья Репин", SUM(CASE WHEN artist_name = 'Василий Кандинский' THEN artwork_count ELSE 0 END) AS "Василий Кандинский", SUM(CASE WHEN artist_name = 'Клод Моне' THEN artwork_count ELSE 0 END) AS "Клод Моне", SUM(CASE WHEN artist_name = 'Леонардо Да Винчи' THEN artwork_count ELSE 0 END) AS "Леонардо Да Винчи", SUM(CASE WHEN artist_name = 'Тициано Тициан' THEN artwork_count ELSE 0 END) AS "Тициано Тициан" FROM ArtistArtworkCount;