SQLize Online / PHPize Online  /  SQLtest Online

create schema cinema; CREATE TABLE cinema.film_studio ( film_studio_id INTEGER PRIMARY KEY, film_studio_nm VARCHAR(128) NOT NULL, foundation_dt DATE NOT NULL, location VARCHAR(128) NOT NULL ); CREATE TABLE cinema.director ( director_id INTEGER PRIMARY KEY, first_nm VARCHAR(128) NOT NULL, last_nm VARCHAR(128) NOT NULL, sex VARCHAR(128) NOT NULL, birth_dt DATE NOT NULL, nationality VARCHAR(128) NOT NULL ); CREATE TABLE cinema.film ( film_id INTEGER PRIMARY KEY, film_nm VARCHAR(128) NOT NULL, release_dt DATE NOT NULL, film_studio_id INTEGER NOT NULL, FOREIGN KEY (film_studio_id) REFERENCES cinema.film_studio (film_studio_id), director_id INTEGER NOT NULL, FOREIGN KEY (director_id) REFERENCES cinema.director (director_id), budget NUMERIC NOT NULL, box_office_amt NUMERIC, duration_min NUMERIC NOT NULL, country VARCHAR(128) NOT NULL, rating NUMERIC ); CREATE TABLE cinema.actor ( actor_id INTEGER PRIMARY KEY, first_nm VARCHAR(128) NOT NULL, last_nm VARCHAR(128) NOT NULL, sex VARCHAR(128) NOT NULL, birth_dt DATE NOT NULL, height NUMERIC NOT NULL, nationality VARCHAR(128) NOT NULL ); CREATE TABLE cinema.genre ( genre_id INTEGER PRIMARY KEY, genre_nm VARCHAR(128) NOT NULL ); CREATE TABLE cinema.user ( user_id INTEGER PRIMARY KEY, first_nm VARCHAR(128) NOT NULL, last_nm VARCHAR(128) NOT NULL, phone_no VARCHAR(128) NOT NULL ); CREATE TABLE cinema.user_review ( film_id INTEGER NOT NULL, FOREIGN KEY (film_id) REFERENCES cinema.film (film_id), user_id INTEGER NOT NULL, FOREIGN KEY (user_id) REFERENCES cinema.user (user_id), PRIMARY KEY (film_id, user_id), rating NUMERIC NOT NULL, comment_txt TEXT, change_dttm TIMESTAMP NOT NULL ); CREATE TABLE cinema.film_actor ( film_id INTEGER NOT NULL, FOREIGN KEY (film_id) REFERENCES cinema.film (film_id), actor_id INTEGER NOT NULL, FOREIGN KEY (actor_id) REFERENCES cinema.actor (actor_id), PRIMARY KEY (film_id, actor_id) ); CREATE TABLE cinema.film_genre ( film_id INTEGER NOT NULL, FOREIGN KEY (film_id) REFERENCES cinema.film (film_id), genre_id INTEGER NOT NULL, FOREIGN KEY (genre_id) REFERENCES cinema.genre (genre_id), PRIMARY KEY (film_id, genre_id) ); INSERT INTO cinema.genre VALUES(1, 'action'); INSERT INTO cinema.genre VALUES(2, 'adventure'); INSERT INTO cinema.genre VALUES(3, 'animation'); INSERT INTO cinema.genre VALUES(4, 'biography'); INSERT INTO cinema.genre VALUES(5, 'comedy'); INSERT INTO cinema.genre VALUES(6, 'crime'); INSERT INTO cinema.genre VALUES(7, 'detective'); INSERT INTO cinema.genre VALUES(8, 'documentary'); INSERT INTO cinema.genre VALUES(9, 'drama'); INSERT INTO cinema.genre VALUES(10, 'family'); INSERT INTO cinema.genre VALUES(11, 'fantasy'); INSERT INTO cinema.genre VALUES(12, 'horror'); INSERT INTO cinema.genre VALUES(13, 'musical'); INSERT INTO cinema.genre VALUES(14, 'post-apocalyptic'); INSERT INTO cinema.genre VALUES(15, 'romance'); INSERT INTO cinema.genre VALUES(16, 'sci-fi'); INSERT INTO cinema.genre VALUES(17, 'thriller'); INSERT INTO cinema.genre VALUES(18, 'war'); INSERT INTO cinema.genre VALUES(19, 'western'); INSERT INTO cinema.genre VALUES(20, 'history'); INSERT INTO cinema.genre VALUES(21, 'neo-noir'); INSERT INTO cinema.director VALUES(1, 'Ridley', 'Scott', 'male', '1937-11-30', 'british'); INSERT INTO cinema.director VALUES(2, 'Denis', 'Villeneuve', 'male', '1967-10-03', 'french'); INSERT INTO cinema.director VALUES(3, 'Christopher', 'Nolan', 'male', '1970-07-30', 'british'); INSERT INTO cinema.director VALUES(4, 'Martin', 'Scorsese', 'male', '1942-11-17', 'american'); INSERT INTO cinema.director VALUES(5, 'Peter', 'Jackson', 'male', '1961-10-31', 'new zealand'); INSERT INTO cinema.director VALUES(6, 'Robert', 'Zemeckis', 'male', '1952-05-14', 'american'); INSERT INTO cinema.director VALUES(7, 'George', 'Lucas', 'male', '1944-05-14', 'american'); INSERT INTO cinema.director VALUES(8, 'Stanley', 'Kubrick', 'male', '1928-07-26', 'american'); INSERT INTO cinema.director VALUES(9, 'Ron', 'Howard', 'male', '1954-03-01', 'american'); INSERT INTO cinema.director VALUES(10, 'Francis Ford', 'Coppola', 'male', '1939-04-07', 'american'); INSERT INTO cinema.director VALUES(11, 'David', 'Fincher', 'male', '1962-08-28', 'american'); INSERT INTO cinema.director VALUES(12, 'Quentin', 'Tarantino', 'male', '1963-03-27', 'american'); INSERT INTO cinema.director VALUES(13, 'Guy', 'Ritchie', 'male', '1968-09-10', 'british'); INSERT INTO cinema.director VALUES(14, 'Damien', 'Chazelle', 'male', '1985-01-19', 'american'); INSERT INTO cinema.director VALUES(15, 'Luc', 'Besson', 'male', '1959-03-18', 'french'); INSERT INTO cinema.director VALUES(16, 'Nicolas Winding', 'Refn', 'male', '1970-09-29', 'danish'); INSERT INTO cinema.director VALUES(17, 'Mel', 'Gibson', 'male', '1956-01-03', 'australian'); INSERT INTO cinema.actor VALUES(1, 'Hugh', 'Jackman', 'male', '1968-10-12', 188, 'australian'); INSERT INTO cinema.actor VALUES(2, 'Jake', 'Gyllenhaal', 'male', '1980-12-19', 183, 'american'); INSERT INTO cinema.actor VALUES(3, 'Ryan', 'Gosling', 'male', '1980-11-19', 184, 'canadian'); INSERT INTO cinema.actor VALUES(4, 'Brad', 'Pitt', 'male', '1963-12-18', 180, 'american'); INSERT INTO cinema.actor VALUES(5, 'Leonardo', 'DiCaprio', 'male', '1974-11-11', 183, 'american'); INSERT INTO cinema.actor VALUES(6, 'Oscar', 'Isaac', 'male', '1979-03-09', 174, 'american'); INSERT INTO cinema.actor VALUES(7, 'Robert', 'De Niro', 'male', '1943-08-17', 175, 'american'); INSERT INTO cinema.actor VALUES(8, 'Al', 'Pacino', 'male', '1940-04-25', 168, 'american'); INSERT INTO cinema.actor VALUES(9, 'Matthew', 'McConaughey', 'male', '1969-11-04', 182, 'american'); INSERT INTO cinema.actor VALUES(10, 'Christian', 'Bale', 'male', '1974-01-30', 183, 'british'); INSERT INTO cinema.actor VALUES(11, 'Viggo', 'Mortensen', 'male', '1958-10-20', 180, 'danish'); INSERT INTO cinema.actor VALUES(12, 'Samuel', 'Jackson', 'male', '1948-12-21', 189, 'american'); INSERT INTO cinema.actor VALUES(13, 'Tim', 'Roth', 'male', '1961-05-14', 170, 'british'); INSERT INTO cinema.actor VALUES(14, 'Christoph', 'Waltz', 'male', '1956-10-04', 170, 'austrian'); INSERT INTO cinema.actor VALUES(15, 'Andrew', 'Garfield', 'male', '1983-08-20', 179, 'british'); INSERT INTO cinema.actor VALUES(16, 'Jason', 'Statham', 'male', '1967-07-26', 178, 'british'); INSERT INTO cinema.actor VALUES(17, 'Sigourney', 'Weaver', 'female', '1949-10-08', 182, 'american'); INSERT INTO cinema.actor VALUES(18, 'Harrison', 'Ford', 'male', '1942-07-13', 185, 'american'); INSERT INTO cinema.actor VALUES(19, 'Russell', 'Crowe', 'male', '1964-04-07', 182, 'new zealand'); INSERT INTO cinema.actor VALUES(20, 'Matt', 'Damon', 'male', '1970-10-08', 178, 'american'); INSERT INTO cinema.actor VALUES(21, 'Ewan', 'McGregor', 'male', '1971-03-31', 179, 'scottish'); INSERT INTO cinema.actor VALUES(22, 'Emma', 'Stone', 'female', '1988-11-06', 168, 'american'); INSERT INTO cinema.actor VALUES(23, 'Jack', 'Nicholson', 'male', '1937-04-22', 177, 'american'); INSERT INTO cinema.actor VALUES(24, 'Jean', 'Reno', 'male', '1948-07-30', 191, 'french'); INSERT INTO cinema.actor VALUES(25, 'Natalie', 'Portman', 'female', '1981-06-09', 160, 'american'); INSERT INTO cinema.actor VALUES(26, 'Jennifer', 'Connelly', 'female', '1970-12-12', 169, 'american'); INSERT INTO cinema.actor VALUES(27, 'Michael J.', 'Fox', 'male', '1961-06-09', 163, 'canadian'); INSERT INTO cinema.film_studio VALUES(1, 'Warner Bros.', '1923-04-04', 'USA'); INSERT INTO cinema.film_studio VALUES(2, 'Paramount Pictures', '1912-05-08', 'USA'); INSERT INTO cinema.film_studio VALUES(3, 'Universal Pictures', '1912-04-30', 'USA'); INSERT INTO cinema.film_studio VALUES(4, 'Walt Disney Pictures', '1923-10-16', 'USA'); INSERT INTO cinema.film_studio VALUES(5, 'New Line Cinema', '1967-06-18', 'USA'); INSERT INTO cinema.film_studio VALUES(6, 'Columbia Pictures', '1924-01-10', 'USA'); INSERT INTO cinema.film_studio VALUES(7, '20th Century Fox', '1935-05-31', 'USA'); INSERT INTO cinema.film_studio VALUES(8, 'Sony Pictures', '1987-01-01', 'USA'); INSERT INTO cinema.film_studio VALUES(9, 'MGM', '1924-04-17', 'USA'); INSERT INTO cinema.film_studio VALUES(10, 'Miramax Films', '1979-12-19', 'USA'); INSERT INTO cinema.film_studio VALUES(11, 'Legendary Pictures', '2000-01-01', 'USA'); INSERT INTO cinema.film_studio VALUES(12, 'Lionsgate Films', '1962-01-01', 'USA'); INSERT INTO cinema.film_studio VALUES(13, 'Summit Entertainment', '2007-04-19', 'USA'); INSERT INTO cinema.film_studio VALUES(14, 'Lucasfilm', '1971-12-10', 'USA'); INSERT INTO cinema.film VALUES(1, 'The Lord of the Rings: The Fellowship of the Ring', '2001-12-19', 5, 5, 93000000, 880839846, 178, 'USA', 8.8); INSERT INTO cinema.film VALUES(2, 'The Lord of the Rings: The Two Towers', '2002-12-18', 5, 5, 94000000, 936689735, 179, 'USA', 8.8); INSERT INTO cinema.film VALUES(3, 'The Lord of the Rings: The Return of the King', '2003-12-01', 5, 5, 94000000, 1120424614, 201, 'USA', 9); INSERT INTO cinema.film VALUES(4, 'Prisoners', '2013-08-30', 1, 2, 46000000, 122126687, 153, 'USA', 8.1); INSERT INTO cinema.film VALUES(5, 'Dune', '2021-09-03', 1, 2, 165000000, 400600000, 156, 'USA', 8.1); INSERT INTO cinema.film VALUES(6, 'Blade Runner 2049', '2017-10-03', 6, 2, 150000000, 259239658, 164, 'USA', 8.0); INSERT INTO cinema.film VALUES(7, 'Interstellar', '2014-10-26', 1, 3, 165000000, 677463813, 169, 'USA', 8.6); INSERT INTO cinema.film VALUES(8, 'Inception', '2010-07-08', 1, 3, 160000000, 828322032, 148, 'USA', 8.8); INSERT INTO cinema.film VALUES(9, 'The Prestige', '2006-10-17', 1, 3, 40000000, 109676311, 125, 'USA', 8.5); INSERT INTO cinema.film VALUES(10, 'The Dark Knight', '2008-07-14', 1, 3, 185000000, 1003045358, 152, 'USA', 9.0); INSERT INTO cinema.film VALUES(11, 'Pulp Fiction', '1994-10-14', 10, 12, 8000000, 213928762, 154, 'USA', 8.9); INSERT INTO cinema.film VALUES(12, 'Reservoir Dogs', '1992-10-09', 10, 12, 1200000, 2832029, 99, 'USA', 8.3); INSERT INTO cinema.film VALUES(13, 'Django Unchained', '2012-12-25', 6, 12, 100000000, 425368238, 165, 'USA', 8.4); INSERT INTO cinema.film VALUES(14, 'Inglourious Basterds', '2009-05-20', 3, 12, 70000000, 321455689, 153, 'USA', 8.3); INSERT INTO cinema.film VALUES(15, 'Fight Club', '1999-10-15', 7, 11, 63000000, 100853753, 139, 'USA', 8.8); INSERT INTO cinema.film VALUES(16, 'Seven', '1995-09-15', 5, 11, 33000000, 327311859, 127, 'USA', 8.6); INSERT INTO cinema.film VALUES(17, 'The Social Network', '2010-09-14', 6, 11, 50000000, 224920315, 121, 'USA', 7.8); INSERT INTO cinema.film VALUES(18, 'Zodiac', '2007-02-28', 1, 11, 65000000, 84785914, 158, 'USA', 7.7); INSERT INTO cinema.film VALUES(19, 'Lock, Stock and Two Smoking Barrels', '1998-08-28', 13, 13, 960000, 25000000, 107, 'UK', 8.2); INSERT INTO cinema.film VALUES(20, 'Snatch', '2000-09-01', 6, 13, 10000000, 83557872, 104, 'UK', 8.3); INSERT INTO cinema.film VALUES(21, 'The Gentlemen', '2019-12-03', 10, 13, 22000000, 114996853, 113, 'UK', 7.8); INSERT INTO cinema.film VALUES(22, 'Taxi Driver', '1976-02-08', 6, 4, 1900000, 28262574, 114, 'USA', 8.3); INSERT INTO cinema.film VALUES(23, 'Shutter Island', '2010-02-13', 2, 4, 80000000, 294804195, 138, 'USA', 8.2); INSERT INTO cinema.film VALUES(24, 'The Departed', '2006-09-26', 1, 4, 90000000, 291465034, 151, 'USA', 8.5); INSERT INTO cinema.film VALUES(25, 'The Wolf of Wall Street', '2013-12-09', 2, 4, 100000000, 392000694, 180, 'USA', 8.2); INSERT INTO cinema.film VALUES(26, 'Alien', '1979-05-25', 7, 1, 11000000, 99932689, 116, 'USA', 8.5); INSERT INTO cinema.film VALUES(27, 'Blade Runner', '1982-06-25', 1, 1, 28000000, 27615743, 117, 'USA', 8.1); INSERT INTO cinema.film VALUES(28, 'Gladiator', '2000-05-01', 3, 1, 103000000, 460583960, 155, 'USA', 8.5); INSERT INTO cinema.film VALUES(29, 'The Martian', '2015-09-11', 7, 1, 108000000, 630161890, 144, 'USA', 8); INSERT INTO cinema.film VALUES(30, 'Drive', '2011-09-16', 3, 16, 15000000, 76900000, 100, 'USA', 7.8); INSERT INTO cinema.film VALUES(31, 'Star Wars. Episode III. Revenge of the Sith', '2005-05-19', 14, 7, 113000000, 848754768, 140, 'USA', 7.6); INSERT INTO cinema.film VALUES(32, 'La La Land', '2016-08-31', 13, 14, 30000000, 447407695, 128, 'USA', 8.0); INSERT INTO cinema.film VALUES(33, 'The Shining', '1980-05-23', 1, 8, 19000000, 44035750, 144, 'USA', 8.4); INSERT INTO cinema.film VALUES(34, 'Back to the Future', '1985-07-03', 3, 6, 19000000, 381109762, 116, 'USA', 8.5); INSERT INTO cinema.film VALUES(35, 'Hacksaw Ridge', '2016-09-04', 13, 17, 40000000, 180563636, 139, 'USA', 8.1); INSERT INTO cinema.film VALUES(36, 'The Godfather', '1972-03-14', 2, 10, 6000000, 243862778, 175, 'USA', 9.2); INSERT INTO cinema.film VALUES(37, 'Léon', '1994-09-14', 6, 15, 16000000, 46100000, 110, 'France', 8.5); INSERT INTO cinema.film VALUES(38, 'A Beautiful Mind', '2001-12-13', 3, 9, 58000000, 313542341, 135, 'USA', 8.2); INSERT INTO cinema.film_actor VALUES(1, 11); INSERT INTO cinema.film_actor VALUES(2, 11); INSERT INTO cinema.film_actor VALUES(3, 11); INSERT INTO cinema.film_actor VALUES(4, 1); INSERT INTO cinema.film_actor VALUES(4, 2); INSERT INTO cinema.film_actor VALUES(5, 6); INSERT INTO cinema.film_actor VALUES(6, 3); INSERT INTO cinema.film_actor VALUES(6, 18); INSERT INTO cinema.film_actor VALUES(7, 9); INSERT INTO cinema.film_actor VALUES(8, 5); INSERT INTO cinema.film_actor VALUES(9, 1); INSERT INTO cinema.film_actor VALUES(9, 10); INSERT INTO cinema.film_actor VALUES(10, 10); INSERT INTO cinema.film_actor VALUES(11, 12); INSERT INTO cinema.film_actor VALUES(11, 13); INSERT INTO cinema.film_actor VALUES(12, 13); INSERT INTO cinema.film_actor VALUES(13, 12); INSERT INTO cinema.film_actor VALUES(13, 14); INSERT INTO cinema.film_actor VALUES(14, 4); INSERT INTO cinema.film_actor VALUES(14, 14); INSERT INTO cinema.film_actor VALUES(15, 4); INSERT INTO cinema.film_actor VALUES(16, 4); INSERT INTO cinema.film_actor VALUES(17, 15); INSERT INTO cinema.film_actor VALUES(18, 2); INSERT INTO cinema.film_actor VALUES(19, 16); INSERT INTO cinema.film_actor VALUES(20, 4); INSERT INTO cinema.film_actor VALUES(20, 16); INSERT INTO cinema.film_actor VALUES(21, 9); INSERT INTO cinema.film_actor VALUES(22, 7); INSERT INTO cinema.film_actor VALUES(23, 5); INSERT INTO cinema.film_actor VALUES(24, 5); INSERT INTO cinema.film_actor VALUES(24, 20); INSERT INTO cinema.film_actor VALUES(24, 23); INSERT INTO cinema.film_actor VALUES(25, 5); INSERT INTO cinema.film_actor VALUES(26, 17); INSERT INTO cinema.film_actor VALUES(27, 18); INSERT INTO cinema.film_actor VALUES(28, 19); INSERT INTO cinema.film_actor VALUES(29, 20); INSERT INTO cinema.film_actor VALUES(30, 3); INSERT INTO cinema.film_actor VALUES(30, 6); INSERT INTO cinema.film_actor VALUES(31, 12); INSERT INTO cinema.film_actor VALUES(31, 21); INSERT INTO cinema.film_actor VALUES(31, 25); INSERT INTO cinema.film_actor VALUES(32, 3); INSERT INTO cinema.film_actor VALUES(32, 22); INSERT INTO cinema.film_actor VALUES(33, 23); INSERT INTO cinema.film_actor VALUES(34, 27); INSERT INTO cinema.film_actor VALUES(35, 15); INSERT INTO cinema.film_actor VALUES(36, 8); INSERT INTO cinema.film_actor VALUES(37, 24); INSERT INTO cinema.film_actor VALUES(37, 25); INSERT INTO cinema.film_actor VALUES(38, 19); INSERT INTO cinema.film_actor VALUES(38, 26); INSERT INTO cinema.film_genre VALUES(1, 11); INSERT INTO cinema.film_genre VALUES(1, 2); INSERT INTO cinema.film_genre VALUES(1, 9); INSERT INTO cinema.film_genre VALUES(2, 11); INSERT INTO cinema.film_genre VALUES(2, 2); INSERT INTO cinema.film_genre VALUES(2, 9); INSERT INTO cinema.film_genre VALUES(3, 11); INSERT INTO cinema.film_genre VALUES(3, 2); INSERT INTO cinema.film_genre VALUES(3, 9); INSERT INTO cinema.film_genre VALUES(4, 17); INSERT INTO cinema.film_genre VALUES(4, 9); INSERT INTO cinema.film_genre VALUES(4, 6); INSERT INTO cinema.film_genre VALUES(4, 7); INSERT INTO cinema.film_genre VALUES(5, 16); INSERT INTO cinema.film_genre VALUES(5, 1); INSERT INTO cinema.film_genre VALUES(5, 9); INSERT INTO cinema.film_genre VALUES(5, 2); INSERT INTO cinema.film_genre VALUES(6, 16); INSERT INTO cinema.film_genre VALUES(6, 1); INSERT INTO cinema.film_genre VALUES(6, 17); INSERT INTO cinema.film_genre VALUES(6, 9); INSERT INTO cinema.film_genre VALUES(6, 7); INSERT INTO cinema.film_genre VALUES(7, 16); INSERT INTO cinema.film_genre VALUES(7, 9); INSERT INTO cinema.film_genre VALUES(7, 2); INSERT INTO cinema.film_genre VALUES(8, 16); INSERT INTO cinema.film_genre VALUES(8, 1); INSERT INTO cinema.film_genre VALUES(8, 17); INSERT INTO cinema.film_genre VALUES(8, 9); INSERT INTO cinema.film_genre VALUES(8, 7); INSERT INTO cinema.film_genre VALUES(9, 17); INSERT INTO cinema.film_genre VALUES(9, 16); INSERT INTO cinema.film_genre VALUES(9, 9); INSERT INTO cinema.film_genre VALUES(9, 7); INSERT INTO cinema.film_genre VALUES(10, 16); INSERT INTO cinema.film_genre VALUES(10, 1); INSERT INTO cinema.film_genre VALUES(10, 17); INSERT INTO cinema.film_genre VALUES(10, 6); INSERT INTO cinema.film_genre VALUES(10, 9); INSERT INTO cinema.film_genre VALUES(11, 6); INSERT INTO cinema.film_genre VALUES(11, 9); INSERT INTO cinema.film_genre VALUES(12, 6); INSERT INTO cinema.film_genre VALUES(12, 9); INSERT INTO cinema.film_genre VALUES(12, 17); INSERT INTO cinema.film_genre VALUES(13, 19); INSERT INTO cinema.film_genre VALUES(13, 1); INSERT INTO cinema.film_genre VALUES(13, 9); INSERT INTO cinema.film_genre VALUES(13, 5); INSERT INTO cinema.film_genre VALUES(14, 1); INSERT INTO cinema.film_genre VALUES(14, 9); INSERT INTO cinema.film_genre VALUES(14, 5); INSERT INTO cinema.film_genre VALUES(14, 18); INSERT INTO cinema.film_genre VALUES(15, 17); INSERT INTO cinema.film_genre VALUES(15, 9); INSERT INTO cinema.film_genre VALUES(15, 6); INSERT INTO cinema.film_genre VALUES(16, 17); INSERT INTO cinema.film_genre VALUES(16, 9); INSERT INTO cinema.film_genre VALUES(16, 6); INSERT INTO cinema.film_genre VALUES(16, 7); INSERT INTO cinema.film_genre VALUES(17, 9); INSERT INTO cinema.film_genre VALUES(17, 4); INSERT INTO cinema.film_genre VALUES(18, 17); INSERT INTO cinema.film_genre VALUES(18, 7); INSERT INTO cinema.film_genre VALUES(18, 9); INSERT INTO cinema.film_genre VALUES(18, 6); INSERT INTO cinema.film_genre VALUES(19, 1); INSERT INTO cinema.film_genre VALUES(19, 5); INSERT INTO cinema.film_genre VALUES(19, 6); INSERT INTO cinema.film_genre VALUES(20, 6); INSERT INTO cinema.film_genre VALUES(20, 5); INSERT INTO cinema.film_genre VALUES(20, 1); INSERT INTO cinema.film_genre VALUES(21, 6); INSERT INTO cinema.film_genre VALUES(21, 5); INSERT INTO cinema.film_genre VALUES(21, 1); INSERT INTO cinema.film_genre VALUES(22, 17); INSERT INTO cinema.film_genre VALUES(22, 9); INSERT INTO cinema.film_genre VALUES(22, 6); INSERT INTO cinema.film_genre VALUES(23, 17); INSERT INTO cinema.film_genre VALUES(23, 7); INSERT INTO cinema.film_genre VALUES(23, 9); INSERT INTO cinema.film_genre VALUES(24, 17); INSERT INTO cinema.film_genre VALUES(24, 9); INSERT INTO cinema.film_genre VALUES(24, 6); INSERT INTO cinema.film_genre VALUES(25, 9); INSERT INTO cinema.film_genre VALUES(25, 6); INSERT INTO cinema.film_genre VALUES(25, 4); INSERT INTO cinema.film_genre VALUES(25, 5); INSERT INTO cinema.film_genre VALUES(26, 12); INSERT INTO cinema.film_genre VALUES(26, 16); INSERT INTO cinema.film_genre VALUES(26, 17); INSERT INTO cinema.film_genre VALUES(27, 16); INSERT INTO cinema.film_genre VALUES(27, 17); INSERT INTO cinema.film_genre VALUES(27, 9); INSERT INTO cinema.film_genre VALUES(28, 20); INSERT INTO cinema.film_genre VALUES(28, 1); INSERT INTO cinema.film_genre VALUES(28, 9); INSERT INTO cinema.film_genre VALUES(28, 2); INSERT INTO cinema.film_genre VALUES(29, 16); INSERT INTO cinema.film_genre VALUES(29, 2); INSERT INTO cinema.film_genre VALUES(30, 6); INSERT INTO cinema.film_genre VALUES(30, 9); INSERT INTO cinema.film_genre VALUES(30, 17); INSERT INTO cinema.film_genre VALUES(30, 21); INSERT INTO cinema.film_genre VALUES(31, 16); INSERT INTO cinema.film_genre VALUES(31, 1); INSERT INTO cinema.film_genre VALUES(31, 11); INSERT INTO cinema.film_genre VALUES(31, 2); INSERT INTO cinema.film_genre VALUES(32, 13); INSERT INTO cinema.film_genre VALUES(32, 9); INSERT INTO cinema.film_genre VALUES(32, 5); INSERT INTO cinema.film_genre VALUES(32, 15); INSERT INTO cinema.film_genre VALUES(33, 17); INSERT INTO cinema.film_genre VALUES(33, 9); INSERT INTO cinema.film_genre VALUES(33, 12); INSERT INTO cinema.film_genre VALUES(33, 7); INSERT INTO cinema.film_genre VALUES(34, 16); INSERT INTO cinema.film_genre VALUES(34, 5); INSERT INTO cinema.film_genre VALUES(34, 2); INSERT INTO cinema.film_genre VALUES(35, 9); INSERT INTO cinema.film_genre VALUES(35, 18); INSERT INTO cinema.film_genre VALUES(35, 4); INSERT INTO cinema.film_genre VALUES(35, 20); INSERT INTO cinema.film_genre VALUES(36, 9); INSERT INTO cinema.film_genre VALUES(36, 6); INSERT INTO cinema.film_genre VALUES(37, 1); INSERT INTO cinema.film_genre VALUES(37, 17); INSERT INTO cinema.film_genre VALUES(37, 9); INSERT INTO cinema.film_genre VALUES(37, 6); INSERT INTO cinema.film_genre VALUES(38, 9); INSERT INTO cinema.film_genre VALUES(38, 4); INSERT INTO cinema.user VALUES(1, 'Anthony', 'Stone','+79999999999'); INSERT INTO cinema.user VALUES(2, 'Joseph', 'Freeman', '+78888888888'); INSERT INTO cinema.user VALUES(3, 'Charles', 'Hansen', '+77777777777'); INSERT INTO cinema.user VALUES(4, 'Earl', 'Jones', '+76666666666'); INSERT INTO cinema.user VALUES(5, 'Todd', 'Castro', '+75555555555'); INSERT INTO cinema.user VALUES(6, 'James', 'Doyle', '+74444444444'); INSERT INTO cinema.user VALUES(7, 'Richard', 'Ruiz', '+73333333333'); INSERT INTO cinema.user VALUES(8, 'Richard', 'Newman', '+72222222222'); INSERT INTO cinema.user VALUES(9, 'Ivan', 'Strogalshchikov', '+71111111111'); INSERT INTO cinema.user VALUES(10, 'Lawrence', 'Brown', '+70000000000'); INSERT INTO cinema.user_review VALUES(1, 1, 10, 'This is the best trilogy in the world.', '2020-07-03 20:42:36'); INSERT INTO cinema.user_review VALUES(2, 1, 10, 'This is the best trilogy in the world.', '2020-07-03 20:43:54'); INSERT INTO cinema.user_review VALUES(3, 1, 10, 'This is the best trilogy in the world.', '2020-07-03 20:45:02'); INSERT INTO cinema.user_review VALUES(7, 3, 9, '', '2021-05-12 14:01:16'); INSERT INTO cinema.user_review VALUES(11, 3, 8, '', '2018-11-16 19:00:34'); INSERT INTO cinema.user_review VALUES(22, 7, 9, '', '2015-08-23 02:27:51'); INSERT INTO cinema.user_review VALUES(30, 9, 10, 'Ryan Gosling is my favorite actor. I really liked his character, it is literally me. As always, he played very well, shows so many emotions. In addition, the film has a wonderful atmosphere and soundtrack. I liked this movie, but I still did not understand whether he died at the end or not. I give 10 to this movie.', '2016-08-30 10:54:23'); INSERT INTO cinema.user_review VALUES(32, 9, 10, 'I have never watched musicals before, and I decided to watch La la Land only because of Ryan Gosling - and I did not lose. The film is excellent, for a strong 9. Ryan Gosling is as beautiful as ever.', '2016-09-20 15:36:43'); INSERT INTO cinema.user_review VALUES(6, 10, 7, 'I do not like Ryan Gosling. He always plays characters without emotions, who are silent throughout the film and walk with one facial expression. The film is not bad, but because of Gosling, I reduce it to 7.', '2019-02-11 21:44:34'); INSERT INTO cinema.user_review VALUES(27, 10, 9, 'Excellent science fiction from Master Riddley Scott.', '2018-10-03 23:58:11'); INSERT INTO cinema.film VALUES(100, 'AAAAA', '1979-03-25', 4, 1, 11001000, 99932689, 116, 'USA', 8.5); INSERT INTO cinema.film_actor VALUES(100, 2); INSERT INTO cinema.film_genre VALUES(100, 2); CREATE VIEW films as SELECT film_nm as film, string_agg(genre_nm, ', ') as genres, film_studio, director, actors, release_dt, budget, box_office_amt, duration_min, country, rating FROM ( SELECT * FROM cinema.film_genre fg LEFT JOIN cinema.genre g ON fg.genre_id = g.genre_id) g LEFT JOIN ( SELECT f.*, string_agg(CONCAT(first_nm, ' ', last_nm), ', ') as actors FROM ( SELECT * FROM cinema.film_actor fa LEFT JOIN cinema.actor a ON fa.actor_id = a.actor_id) a LEFT JOIN ( SELECT f.*, film_studio_nm as film_studio FROM ( SELECT f.*, CONCAT(first_nm, ' ', last_nm) as director FROM cinema.film f LEFT JOIN cinema.director d ON f.director_id = d.director_id) f LEFT JOIN cinema.film_studio fs ON f.film_studio_id = fs.film_studio_id) f ON f.film_id = a.film_id GROUP BY f.film_id, f.film_nm, director, film_studio, release_dt, film_studio_id, director_id, budget, box_office_amt, duration_min, country, rating ) f ON f.film_id = g.film_id GROUP BY f.film_id, f.film_nm, director, film_studio, actors, release_dt, film_studio_id, director_id, budget, box_office_amt, duration_min, country, rating; SELECT * FROM films; DELETE FROM cinema.film_actor WHERE film_id = 100 AND actor_id = 2; DELETE FROM cinema.film_genre WHERE film_id = 100 AND genre_id = 2; SELECT * FROM films; CREATE OR REPLACE FUNCTION update_view() RETURNS TRIGGER AS $$ BEGIN IF (TG_OP = 'DELETE') THEN DELETE FROM cinema.film WHERE film_nm = OLD.film; RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN UPDATE cinema.film SET film_nm = NEW.film, film_studio_id = (SELECT film_studio_id FROM cinema.film_studio WHERE film_studio_nm = NEW.film_studio), director_id = (SELECT director_id FROM cinema.director d WHERE CONCAT(d.first_nm, ' ', d.last_nm) = NEW.director), release_dt = NEW.release_dt, budget = NEW.budget, box_office_amt = NEW.box_office_amt, duration_min = NEW.duration_min, country = NEW.country, rating = NEW.rating WHERE film_nm = OLD.film; RETURN NEW; END IF; END; $$ LANGUAGE plpgsql; CREATE TRIGGER update_film INSTEAD OF UPDATE OR DELETE ON films FOR EACH ROW EXECUTE PROCEDURE update_view(); SELECT * FROM films; DELETE FROM films WHERE film = 'AAAAA'; SELECT * FROM films