SQLize Online / PHPize Online  /  SQLtest Online

DROP SCHEMA IF EXISTS ss; CREATE SCHEMA ss; CREATE TABLE ss.user ( user_id serial NOT NULL PRIMARY KEY, user_nm varchar(32) NOT NULL, birth_dt date NOT NULL, sex varchar(1) CHECK (sex IN ('M', 'F', 'X', NULL)) ); CREATE TABLE ss.playlist ( playlist_id serial NOT NULL PRIMARY KEY, user_id integer NOT NULL, playlist_nm varchar(32) NOT NULL, playlist_desc varchar(256), FOREIGN KEY (user_id) REFERENCES ss.user(user_id) ON DELETE CASCADE ); CREATE TABLE ss.music ( music_id serial NOT NULL PRIMARY KEY, music_title varchar(32) NOT NULL, release_dt date ); CREATE TABLE ss.genre ( genre_id serial NOT NULL PRIMARY KEY, genre_nm varchar(32) NOT NULL ); CREATE TABLE ss.music_genre ( music_id integer NOT NULL, genre_id integer NOT NULL, PRIMARY KEY (music_id, genre_id), FOREIGN KEY (music_id) REFERENCES ss.music(music_id) ON DELETE CASCADE, FOREIGN KEY (genre_id) REFERENCES ss.genre(genre_id) ON DELETE CASCADE ); CREATE TABLE ss.playlist_content ( playlist_id integer NOT NULL, music_id integer NOT NULL, PRIMARY KEY (playlist_id, music_id), FOREIGN KEY (playlist_id) REFERENCES ss.playlist(playlist_id) ON DELETE CASCADE, FOREIGN KEY (music_id) REFERENCES ss.music(music_id) ON DELETE CASCADE ); CREATE TABLE ss.performer ( performer_id serial NOT NULL PRIMARY KEY, performer_nm varchar(32) NOT NULL ); CREATE TABLE ss.music_performed ( music_id integer NOT NULL, performer_id integer NOT NULL, PRIMARY KEY (music_id, performer_id), FOREIGN KEY (music_id) REFERENCES ss.music(music_id) ON DELETE CASCADE, FOREIGN KEY (performer_id) REFERENCES ss.performer(performer_id) ON DELETE CASCADE ); CREATE TABLE ss.musician ( musician_id serial NOT NULL PRIMARY KEY, musician_nm varchar(32) NOT NULL ); CREATE TABLE ss.participation ( musician_id integer NOT NULL, performer_id integer NOT NULL, start_valid_dt date NOT NULL, end_valid_dt date NOT NULL, PRIMARY KEY (musician_id, performer_id, start_valid_dt), FOREIGN KEY (musician_id) REFERENCES ss.musician(musician_id) ON DELETE CASCADE, FOREIGN KEY (performer_id) REFERENCES ss.performer(performer_id) ON DELETE CASCADE ); CREATE TABLE ss.chart ( chart_num integer CHECK (chart_num BETWEEN 1 AND 10), start_valid_dt date NOT NULL, end_valid_dt date NOT NULL, music_id integer NOT NULL, PRIMARY KEY (chart_num, start_valid_dt) ); INSERT INTO ss.user (user_nm, birth_dt, sex) VALUES ('Dujin', '1993-09-21', 'F'), ('Burillador', '1993-10-06', 'F'), ('Shakagrel', '1991-02-23', 'F'), ('Fedwyn', '1995-05-25', 'M'), ('Thetalis', '2000-04-26', 'F'), ('Buzadar', '2003-08-04', 'M'), ('Burilis', '2002-04-16', 'M'), ('Agamarim', '1993-09-03', 'X'), ('Stonefont', '1997-05-18', NULL), ('Chillray', '1994-01-25', NULL); INSERT INTO ss.playlist (user_id, playlist_nm, playlist_desc) VALUES (1, 'Saved', NULL), (2, 'Saved', NULL), (3, 'Saved', NULL), (4, 'Saved', NULL), (5, 'Saved', NULL), (6, 'Saved', NULL), (7, 'Saved', NULL), (8, 'Saved', NULL), (9, 'Saved', NULL), (10, 'Saved', NULL), (2, 'Sport', NULL), (2, 'Study', NULL), (3, 'Walk', NULL), (5, 'Relax', NULL), (7, 'Favourite', NULL); INSERT INTO ss.music (music_title, release_dt) VALUES ('Counting Stars', '2013-05-31'), ('Apologize', '2006-04-30'), ('All The Right Moves', '2009-09-29'), ('Smells Like Teen Spirit', '1991-09-10'), ('What I''ve done', '2007-04-02'), ('New Divide', '2009-05-18'), ('Invisible', '2017-05-10'), ('Pump It', '2006-01-17'), ('Let''s get it started', '2004-06-22'), ('Scream & Shout', '2012-11-20'), ('Heartbreaker', '2008-05-05'), ('Meet Me Halfway', '2009-09-22'), ('Can''t Hold us', '2011-08-16'), ('Stitches', '2015-05-05'), ('Shape Of You', '2017-01-30'), ('Thinking Out Loud', '2014-09-24'), ('Boulevard of Broken Dreams', '2004-11-29'), ('Single Ladies', '2008-10-13'), ('If I Were a Boy', '2008-10-08'), ('Take It Off', '2010-07-13'), ('TiK ToK', '2009-08-07'), ('Bring Me To Life', '2003-04-22'), ('Going Under', '2003-09-09'), ('My Immortal', '2003-12-08'), ('Say It Right', '2006-10-31'), ('All Good Things', '2006-11-17'), ('Blinding Lights', '2019-11-29'), ('Starboy', '2016-09-21'), ('We Found Love', '2011-09-22'), ('This Is What You Came For', '2016-04-29'), ('Diamonds', '2012-09-27'), ('Umbrella', '2007-03-29'), ('Fergalicious', '2006-10-23'), ('London Bridge', '2006-07-18'), ('Unstoppable', '2016-01-21'), ('Flames', '2018-03-22'), ('Wake Me Up', '2013-06-17'), ('Levels', '2011-10-28'), ('Somebody That I Used To Know', '2011-07-05'), ('Instant Crush', '2013-11-22'), ('Harder, Better, Faster, Stronger', '2001-10-13'), ('Get Lucky', '2013-04-19'), ('Outside', '2014-10-20'), ('Lights', '2010-02-26'), ('Cameo Lover', '2011-03-29'), ('Happy', '2013-11-21'); INSERT INTO ss.genre (genre_nm) VALUES ('pop'), ('dance-pop'), ('folk-pop'), ('pop rock'), ('hip-hop'), ('folk'), ('rock'), ('hard rock'), ('metal'), ('EDM'), ('R&B'), ('emo'), ('electropop'), ('country'), ('house'), ('funk'), ('disco'); INSERT INTO ss.music_genre (music_id, genre_id) VALUES (1, 1), (1, 2), (1, 3), (1, 4), (2, 1), (2, 4), (2, 5), (3, 1), (3, 6), (3, 7), (4, 7), (4, 8), (5, 7), (5, 8), (5, 9), (6, 7), (6, 9), (7, 1), (8, 1), (8, 5), (9, 5), (10, 1), (10, 5), (10, 10), (11, 1), (12, 1),(12, 2),(12, 5), (13, 5), (13, 11), (14, 1), (15, 1), (16, 1), (17, 8), (17, 12), (18, 1), (18, 11), (19, 1), (19, 10), (20, 1), (20, 2), (21, 2), (21, 13), (22, 8), (22, 9), (23, 1), (23, 6), (23, 7), (23, 9), (24, 1), (24, 6), (24, 7), (24, 9), (25, 1), (25, 5), (25, 11), (26, 10), (26, 11), (27, 13), (28, 10), (28, 11), (29, 1), (29, 11), (30, 2), (30, 11), (31, 1), (31, 5), (31, 11), (32, 1), (32, 10), (32, 11), (33, 1), (33, 5), (34, 1), (34, 5), (35, 1), (35, 11), (36, 1), (36, 11), (37, 11), (37, 14), (37, 15), (38, 11), (38, 15), (39, 1), (39, 7), (40, 10), (40, 13), (41, 10), (42, 16), (42, 17), (43, 1), (44, 1), (44, 10), (45, 1), (46, 1); INSERT INTO ss.playlist_content (playlist_id, music_id) VALUES (1, 19), (1, 29), (1, 42), (1, 26), (1, 6), (1, 43), (1, 15), (2, 11), (2, 23), (2, 42), (2, 7), (2, 9), (3, 29), (3, 27), (3, 12), (3, 23), (3, 45), (3, 16), (3, 36), (4, 30), (4, 3), (4, 29), (4, 46), (4, 5), (4, 7), (4, 9), (4, 39), (5, 27), (5, 34), (5, 6), (5, 12), (5, 9), (6, 31), (6, 10), (6, 45), (6, 33), (6, 37), (7, 32), (7, 4), (7, 38), (7, 31), (7, 27), (7, 36), (7, 16), (7, 11), (8, 17), (8, 41), (8, 36), (8, 7), (8, 9), (8, 27), (8, 1), (8, 31), (9, 20), (9, 22), (9, 34), (9, 41), (9, 33), (9, 3), (10, 37), (10, 24), (10, 40), (10, 11), (10, 44), (11, 42), (11, 34), (11, 44), (12, 39), (12, 6), (12, 20), (12, 10), (13, 10), (13, 5), (13, 25), (13, 15), (13, 28), (13, 46), (13, 34), (13, 13), (14, 25), (14, 38), (14, 10), (14, 30), (15, 39), (15, 6), (15, 14); INSERT INTO ss.performer (performer_nm) VALUES ('OneRepublic'), ('Alesso'), ('Nirvana'), ('Linkin Park'), ('The Black Eyed Peas'), ('Alan Walker'), ('Macklemore'), ('Ryan Lewis'), ('Ray Dalton'), ('Shawn Mendes'), ('Ed Sheeran'), ('Green Day'), ('Beyonce'), ('Ke$ha'), ('Evanescence'), ('Nelly Furtado'), ('The Weeknd'), ('Rihanna'), ('will.i.am'), ('Fergie'), ('apl.de.ap'), ('Britney Spears'), ('J. Rey Soul'), ('Taboo'), ('Avicii'), ('Gotye'), ('Daft Punk'), ('Calvin Harris'), ('David Guetta'), ('Sia'), ('Ellie Goulding'), ('Pharrell Williams'), ('Nile Rodgers'), ('Kimbra'); INSERT INTO ss.music_performed (music_id, performer_id) VALUES (1, 1), (2, 1), (3, 1), (4, 3), (5, 4), (6, 4), (7, 4), (8, 5), (9, 5), (10, 19), (10, 22), (11, 19), (12, 5), (13, 7), (13, 8), (13, 9), (14, 10), (15, 11), (16, 11), (17, 12), (18, 13), (19, 13), (20, 14), (21, 14), (22, 15), (23, 15), (24, 15), (25, 16), (26, 16), (27, 17), (27, 27), (28, 17), (28, 28), (29, 18), (19, 28), (30, 18), (31, 18), (32, 18), (33, 20), (34, 19), (34, 20), (35, 30), (36, 30), (36, 29), (37, 25), (38, 25), (39, 26), (39, 34), (40, 27), (41, 27), (42, 27), (42, 33), (43, 31), (43, 28), (44, 31), (45, 34), (46, 32); INSERT INTO ss.musician (musician_nm) VALUES ('William Adams'), ('Allan Pinedo Lindo'), ('Jaime Luis Gomez'), ('Fergie Duhamel'), ('Amy Lynn Lee'), ('Chester Bennington'), ('Tim Bergling'), ('Abel Makkonen Tesfaye'), ('Robyn Rihanna Fenty'), ('Jessica Joy Seria Reynoso'); INSERT INTO ss.participation (musician_id, performer_id, start_valid_dt, end_valid_dt) VALUES (1, 19, '1988-01-01', '9999-12-31'), (1, 5, '1992-01-01', '9999-12-31'), (2, 5, '1992-01-01', '9999-12-31'), (2, 21, '1996-01-01', '9999-12-31'), (3, 5, '1995-01-01', '9999-12-31'), (3, 24, '1995-01-01', '9999-12-31'), (4, 5, '2002-01-01', '2016-12-31'), (4, 20, '1984-01-01', '9999-12-31'), (5, 15, '1995-01-01', '9999-12-31'), (6, 4, '1999-01-01', '2017-07-20'), (7, 25, '2006-01-01', '2016-12-31'), (8, 17, '2009-01-01', '9999-12-31'), (9, 18, '2005-01-01', '9999-12-31'), (10, 5, '2018-01-01', '9999-12-31'), (10, 23, '2007-01-01', '2009-12-31'), (10, 23, '2013-01-01', '9999-12-31'); INSERT INTO ss.chart (chart_num, start_valid_dt, end_valid_dt, music_id) VALUES (1,'2015-01-01','2015-12-31', 28), (2,'2015-01-01','2015-12-31', 15), (3,'2015-01-01','2015-12-31', 32), (4,'2015-01-01','2015-12-31', 39), (5,'2015-01-01','2015-12-31', 28), (6,'2015-01-01','2015-12-31', 1), (7,'2015-01-01','2015-12-31', 4), (8,'2015-01-01','2015-12-31', 36), (9,'2015-01-01','2015-12-31', 35), (10,'2015-01-01','2015-12-31', 22), (1,'2016-01-01','2016-12-31', 15), (2,'2016-01-01','2016-12-31', 27), (3,'2016-01-01','2016-12-31', 1), (4,'2016-01-01','2016-12-31', 39), (5,'2016-01-01','2016-12-31', 36), (6,'2016-01-01','2016-12-31', 22), (7,'2016-01-01','2016-12-31', 35), (8,'2016-01-01','2016-12-31', 37), (9,'2016-01-01','2016-12-31', 4), (10,'2016-01-01','2016-12-31', 28), (1,'2017-01-01','2017-12-31', 28), (2,'2017-01-01','2017-12-31', 22), (3,'2017-01-01','2017-12-31', 39), (4,'2017-01-01','2017-12-31', 1), (5,'2017-01-01','2017-12-31', 36), (6,'2017-01-01','2017-12-31', 27), (7,'2017-01-01','2017-12-31', 37), (8,'2017-01-01','2017-12-31', 4), (9,'2017-01-01','2017-12-31', 35), (10,'2017-01-01','2017-12-31', 32); -- Создадим в базе данных песню All the Things She Said, записанную группой t.A.T.u. и выпущенную 18 августа 2002 insert into ss.performer (performer_nm) values('t.A.T.u.'); insert into ss.music (music_title, release_dt) values('All the Things She Said', '2002-08-18'); -- В ее составе с 1999 две девушки - Юлия Волкова и Елена Катина insert into ss.musician (musician_nm) values('Yulia Volkova'); insert into ss.musician (musician_nm) values('Elena Katina'); insert into ss.participation select *, '1999-01-01', '9999-12-31' from (select musician_id from ss.musician where musician_nm = 'Yulia Volkova' or musician_nm = 'Elena Katina') m cross join (select performer_id from ss.performer where performer_nm = 't.A.T.u.') p; -- Проверим, что получилось select * from ss.participation; -- Созданные записи - 11 и 12 певцы состоят в 47-ой группе insert into ss.music_performed select * from (select music_id from ss.music where music_title = 'All the Things She Said') m cross join (select performer_id from ss.performer where performer_nm = 't.A.T.u.') p; select * from ss.music_performed where music_id = (select music_id from ss.music where music_title = 'All the Things She Said'); -- Здесь мы создали запись, что эта группа исполнила песню -- Группа распалась в 2011 году, обновим соответствующую таблицу update ss.participation set end_valid_dt = '2011-12-31' where musician_id in (select musician_id from ss.musician where musician_nm = 'Yulia Volkova' or musician_nm = 'Elena Katina'); select * from ss.participation; -- Увидели, что год окончания участия в составе изменился -- Удалим все delete from ss.musician cascade where musician_nm = 'Yulia Volkova' or musician_nm = 'Elena Katina'; delete from ss.music cascade where music_title = 'All the Things She Said'; delete from ss.performer cascade where performer_nm = 't.A.T.u.'; -- CRUD - акроним от операций create, read, update, delete, в postgres им соответствуют insert, select, update, delete