SQLize
Online
/
PHPize Online
/
SQLtest Online
A
A
A
Share
Donate
Blog
Popular
Donate
A
A
A
Share
Blog
Popular
SQLize.online is a free online SQL environment for quickly running, experimenting with and sharing code.
You can run your SQL code on top of the most popular RDBMS including MySQL, MariaDB, SQLite, PostgreSQL, Oracle and Microsoft SQL Server.
SQL code:
Upload
Copy
Format
Clear
CREATE TABLE genre ( id bigint PRIMARY KEY, name varchar(100) NOT NULL, parent_genre_id bigint, FOREIGN KEY (parent_genre_id) REFERENCES genre(id) ); CREATE TABLE track ( id bigint PRIMARY KEY , name varchar(100) NOT NULL ); CREATE TABLE track_genre ( track_id bigint, genre_id bigint, PRIMARY KEY(track_id, genre_id), FOREIGN KEY (track_id) REFERENCES track(id), FOREIGN KEY (genre_id) REFERENCES genre(id) ); INSERT INTO genre(id, name, parent_genre_id) VALUES (1, 'pop', null), (2, 'rock', null), (3, 'blues', null), (4, 'russian pop', 1), (5, 'k-pop', 1), (6, 'euro pop', 1), (7, 'hard rock', 2), (8, 'metal', 2), (9, 'punk rock', 2), (10, 'delta blues', 3), (11, 'electric blues', 3), (12, 'heavy metal', 8), (13, 'trash metal', 8), (14, 'post punk', 9), (15, 'horror punk', 9); INSERT INTO track(id, name) VALUES (1, 'Hallowed Be Thy Name'), (2, 'Boys Don''t Cry'), (3, 'Riding With The King'), (4, 'You Give Love A Bad Name'), (5, 'Since I''ve Been Loving You'); INSERT INTO track_genre(track_id, genre_id) VALUES (1, 12), (2, 14), (3, 3), (4, 2), (4, 7), (5, 3), (5, 7); CREATE TABLE t ( track_id INT, genre_id INT, branch INT[] ); WITH RECURSIVE genre_tree AS ( SELECT id, name, parent_genre_id, ARRAY[id] AS branch -- Заменяем id на массив [id] FROM genre WHERE parent_genre_id IS NULL UNION ALL SELECT child.id, child.name, child.parent_genre_id, g.branch || child.id -- Конкатенируем текущий id с массивом branch FROM genre AS child JOIN genre_tree g ON g.id = child.parent_genre_id ) INSERT INTO t (track_id, genre_id, branch) SELECT track_id, genre_id, genre_tree.branch FROM track_genre JOIN genre_tree ON track_genre.genre_id = genre_tree.id; SELECT * FROM t; --CREATE TABLE t_temp AS --SELECT track_id, genre_id, unnest(branch) AS branch_new --FROM t; --DROP TABLE t; --ALTER TABLE t_temp RENAME TO t; --ALTER TABLE t ADD COLUMN branch_new INT; --UPDATE t SET branch_new = unnest(branch); --ALTER TABLE t DROP COLUMN branch; ALTER TABLE t ADD COLUMN branch_new INT; UPDATE t SET branch_new = ANY(SELECT unnest(branch) FROM t WHERE t.track_id = t.track_id); DELETE FROM T WHERE array_length(branches, 1) > 1; ALTER TABLE t DROP COLUMN branch; SELECT * FROM t; ALTER TABLE t DROP COLUMN genre_id; SELECT * FROM t; ALTER TABLE t RENAME COLUMN branch_new TO genre_id; WITH del AS (DELETE FROM t RETURNING *) INSERT INTO t SELECT DISTINCT * FROM del; SELECT * FROM t; --CREATE TEMPORARY TABLE temp_table AS --SELECT DISTINCT ON (track_id, genre_id) -- * --FROM t; --DROP TABLE t; --ALTER TABLE temp_table RENAME TO t; ALTER TABLE t ADD COLUMN track_name varchar(100); UPDATE t SET track_name = track.name FROM track WHERE t.track_id = track.id; ALTER TABLE t ADD COLUMN genre_name varchar(100); UPDATE t SET genre_name = genre.name FROM genre WHERE t.genre_id = genre.id; SELECT * FROM t ORDER BY track_id ASC;
SQL
Server:
MariaDB 11.4
MariaDB 11.5
MariaDB 10
MariaDB 10 Sakila (ReadOnly)
MySQL 5.7
MySQL 5.7 Sakila (ReadOnly)
MySQL 8.0
MySQL 8.0 Sakila (ReadOnly)
SQLite 3
PostgreSQL 10 Bookings (ReadOnly)
PostgreSQL 11
PostgreSQL 12
PostgreSQL 13
PostgreSQL 14
PostgreSQL 15
MS SQL Server 2017
MS SQL Server 2019
MS SQL Server 2022
MS SQL Server 2022 AdventureWorks (ReadOnly)
Firebird 4.0
Firebird 4.0 (Employee)
Oracle Database 19c (HR)
Oracle Database 21c
Oracle Database 23c Free
SOQOL
Version
ER Diagram
Preserve result
Stuck with a problem?
Got Error?
Ask ChatGPT!
Result:
Copy
Clear