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 person ( id bigint primary key , name varchar not null, age integer not null default 10, gender varchar default 'female' not null , address varchar ); alter table person add constraint ch_gender check ( gender in ('female','male') ); insert into person values (1, 'Anna', 16, 'female', 'Moscow'); insert into person values (2, 'Andrey', 21, 'male', 'Moscow'); insert into person values (3, 'Kate', 33, 'female', 'Kazan'); insert into person values (4, 'Denis', 13, 'male', 'Kazan'); insert into person values (5, 'Elvira', 45, 'female', 'Kazan'); insert into person values (6, 'Irina', 21, 'female', 'Saint-Petersburg'); insert into person values (7, 'Peter', 24, 'male', 'Saint-Petersburg'); insert into person values (8, 'Nataly', 30, 'female', 'Novosibirsk'); insert into person values (9, 'Dmitriy', 18, 'male', 'Samara'); create table pizzeria (id bigint primary key , name varchar not null , rating numeric not null default 0); alter table pizzeria add constraint ch_rating check ( rating between 0 and 5); insert into pizzeria values (1,'Pizza Hut', 4.6); insert into pizzeria values (2,'Dominos', 4.3); insert into pizzeria values (3,'DoDo Pizza', 3.2); insert into pizzeria values (4,'Papa Johns', 4.9); insert into pizzeria values (5,'Best Pizza', 2.3); insert into pizzeria values (6,'DinoPizza', 4.2); create table person_visits (id bigint primary key , person_id bigint not null , pizzeria_id bigint not null , visit_date date not null default current_date, constraint uk_person_visits unique (person_id, pizzeria_id, visit_date), constraint fk_person_visits_person_id foreign key (person_id) references person(id), constraint fk_person_visits_pizzeria_id foreign key (pizzeria_id) references pizzeria(id) ); insert into person_visits values (1, 1, 1, '2022-01-01'); insert into person_visits values (2, 2, 2, '2022-01-01'); insert into person_visits values (3, 2, 1, '2022-01-02'); insert into person_visits values (4, 3, 5, '2022-01-03'); insert into person_visits values (5, 3, 6, '2022-01-04'); insert into person_visits values (6, 4, 5, '2022-01-07'); insert into person_visits values (7, 4, 6, '2022-01-08'); insert into person_visits values (8, 5, 2, '2022-01-08'); insert into person_visits values (9, 5, 6, '2022-01-09'); insert into person_visits values (10, 6, 2, '2022-01-09'); insert into person_visits values (11, 6, 4, '2022-01-01'); insert into person_visits values (12, 7, 1, '2022-01-03'); insert into person_visits values (13, 7, 2, '2022-01-05'); insert into person_visits values (14, 8, 1, '2022-01-05'); insert into person_visits values (15, 8, 2, '2022-01-06'); insert into person_visits values (16, 8, 4, '2022-01-07'); insert into person_visits values (17, 9, 4, '2022-01-08'); insert into person_visits values (18, 9, 5, '2022-01-09'); insert into person_visits values (19, 9, 6, '2022-01-10'); create table menu (id bigint primary key , pizzeria_id bigint not null , pizza_name varchar not null , price numeric not null default 1, constraint fk_menu_pizzeria_id foreign key (pizzeria_id) references pizzeria(id)); insert into menu values (1,1,'cheese pizza', 900); insert into menu values (2,1,'pepperoni pizza', 1200); insert into menu values (3,1,'sausage pizza', 1200); insert into menu values (4,1,'supreme pizza', 1200); insert into menu values (5,6,'cheese pizza', 950); insert into menu values (6,6,'pepperoni pizza', 800); insert into menu values (7,6,'sausage pizza', 1000); insert into menu values (8,2,'cheese pizza', 800); insert into menu values (9,2,'mushroom pizza', 1100); insert into menu values (10,3,'cheese pizza', 780); insert into menu values (11,3,'supreme pizza', 850); insert into menu values (12,4,'cheese pizza', 700); insert into menu values (13,4,'mushroom pizza', 950); insert into menu values (14,4,'pepperoni pizza', 1000); insert into menu values (15,4,'sausage pizza', 950); insert into menu values (16,5,'cheese pizza', 700); insert into menu values (17,5,'pepperoni pizza', 800); insert into menu values (18,5,'supreme pizza', 850); create table person_order ( id bigint primary key , person_id bigint not null , menu_id bigint not null , order_date date not null default current_date, constraint fk_order_person_id foreign key (person_id) references person(id), constraint fk_order_menu_id foreign key (menu_id) references menu(id) ); insert into person_order values (1,1, 1, '2022-01-01'); insert into person_order values (2,1, 2, '2022-01-01'); insert into person_order values (3,2, 8, '2022-01-01'); insert into person_order values (4,2, 9, '2022-01-01'); insert into person_order values (5,3, 16, '2022-01-04'); insert into person_order values (6,4, 16, '2022-01-07'); insert into person_order values (7,4, 17, '2022-01-07'); insert into person_order values (8,4, 18, '2022-01-07'); insert into person_order values (9,4, 6, '2022-01-08'); insert into person_order values (10,4, 7, '2022-01-08'); insert into person_order values (11,5, 6, '2022-01-09'); insert into person_order values (12,5, 7, '2022-01-09'); insert into person_order values (13,6, 13, '2022-01-01'); insert into person_order values (14,7, 3, '2022-01-03'); insert into person_order values (15,7, 9, '2022-01-05'); insert into person_order values (16,7, 4, '2022-01-05'); insert into person_order values (17,8, 8, '2022-01-06'); insert into person_order values (18,8, 14, '2022-01-07'); insert into person_order values (19,9, 18, '2022-01-09'); insert into person_order values (20,9, 6, '2022-01-10'); CREATE TABLE person_audit ( created timestamptz NOT NULL DEFAULT(CURRENT_TIMESTAMP), type_event char(1) NOT NULL DEFAULT('I'), row_id bigint NOT NULL, name varchar, age integer, gender varchar, address varchar, CONSTRAINT ch_type_event CHECK (type_event IN ('I', 'U', 'D')) ); CREATE OR REPLACE FUNCTION fnc_trg_person_insert_audit() RETURNS trigger AS $trg_person_insert_audit$ BEGIN IF (TG_OP = 'INSERT') THEN INSERT INTO person_audit SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Yakutsk', 'I', NEW.*; END IF; RETURN NULL; END; $trg_person_insert_audit$ LANGUAGE plpgsql; CREATE TRIGGER trg_person_insert_audit AFTER INSERT ON person FOR EACH ROW EXECUTE FUNCTION fnc_trg_person_insert_audit(); INSERT INTO person(id, name, age, gender, address) VALUES (10,'Damir', 22, 'male', 'Irkutsk'); CREATE OR REPLACE FUNCTION fnc_trg_person_update_audit() RETURNS trigger AS $trg_person_update_audit$ BEGIN IF (TG_OP = 'UPDATE') THEN INSERT INTO person_audit SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Yakutsk', 'U', OLD.*; END IF; RETURN NULL; END; $trg_person_update_audit$ LANGUAGE plpgsql; CREATE TRIGGER trg_person_update_audit AFTER UPDATE ON person FOR EACH ROW EXECUTE FUNCTION fnc_trg_person_update_audit(); UPDATE person SET name = 'Bulat' WHERE id = 10; UPDATE person SET name = 'Damir' WHERE id = 10; CREATE OR REPLACE FUNCTION fnc_trg_person_delete_audit() RETURNS trigger AS $trg_person_delete_audit$ BEGIN IF (TG_OP = 'DELETE') THEN INSERT INTO person_audit SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Yakutsk', 'D', OLD.*; END IF; RETURN NULL; END; $trg_person_delete_audit$ LANGUAGE plpgsql; CREATE TRIGGER trg_person_delete_audit AFTER DELETE ON person FOR EACH ROW EXECUTE FUNCTION fnc_trg_person_delete_audit(); DELETE FROM person WHERE id = 10; DROP TRIGGER trg_person_delete_audit ON person; DROP TRIGGER trg_person_insert_audit ON person; DROP TRIGGER trg_person_update_audit ON person; DROP FUNCTION fnc_trg_person_delete_audit(); DROP FUNCTION fnc_trg_person_insert_audit(); DROP FUNCTION fnc_trg_person_update_audit(); TRUNCATE person_audit; CREATE OR REPLACE FUNCTION fnc_trg_person_audit() RETURNS TRIGGER AS $trg_person_audit$ BEGIN IF (TG_OP = 'INSERT') THEN INSERT INTO person_audit SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Yakutsk', 'I', NEW.*; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO person_audit SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Yakutsk', 'U', OLD.*; ELSIF (TG_OP = 'DELETE') THEN INSERT INTO person_audit SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Yakutsk', 'D', OLD.*; END IF; RETURN NULL; END; $trg_person_audit$ LANGUAGE plpgsql; CREATE TRIGGER trg_person_audit AFTER INSERT OR UPDATE OR DELETE ON person FOR EACH ROW EXECUTE FUNCTION fnc_trg_person_audit(); INSERT INTO person(id, name, age, gender, address) VALUES (10,'Damir', 22, 'male', 'Irkutsk'); UPDATE person SET name = 'Bulat' WHERE id = 10; UPDATE person SET name = 'Damir' WHERE id = 10; DELETE FROM person WHERE id = 10; CREATE FUNCTION fnc_persons_female() RETURNS TABLE ( id bigint, name varchar, age integer, gender varchar, address varchar ) AS $persons_female$ SELECT * FROM person WHERE gender = 'female' $persons_female$ LANGUAGE SQL; CREATE FUNCTION fnc_persons_male() RETURNS TABLE ( id bigint, name varchar, age integer, gender varchar, address varchar ) AS $persons_male$ SELECT * FROM person WHERE gender = 'male' $persons_male$ LANGUAGE SQL; DROP FUNCTION fnc_persons_female(), fnc_persons_male(); CREATE FUNCTION fnc_persons(IN pgender varchar DEFAULT 'female') RETURNS TABLE ( id bigint, name varchar, age integer, gender varchar, address varchar ) AS $persons$ SELECT * FROM person WHERE pgender = person.gender $persons$ LANGUAGE SQL; CREATE FUNCTION fnc_person_visits_and_eats_on_date( IN pperson varchar DEFAULT 'Dmitriy', IN pprice numeric DEFAULT 500, IN pdate date DEFAULT '2022-01-08') RETURNS TABLE ( name_of_pizzerias varchar ) AS $person_visits_and_eats_on_date$ BEGIN RETURN QUERY ((SELECT pizzeria.name FROM (SELECT * FROM person_visits WHERE person_visits.visit_date = pdate) query1 JOIN (SELECT * FROM person WHERE person.name = pperson) query2 ON query1.person_id = query2.id JOIN pizzeria ON query1.pizzeria_id = pizzeria.id JOIN (SELECT * FROM menu WHERE menu.price < pprice) query3 ON pizzeria.id = query3.pizzeria_id ) INTERSECT (SELECT pizzeria.name FROM (SELECT * FROM person_order WHERE person_order.order_date = pdate) query1 JOIN (SELECT * FROM person WHERE person.name = pperson) query2 ON query1.person_id = query2.id JOIN (SELECT * FROM menu WHERE menu.price < pprice) query3 ON query1.menu_id = query3.id JOIN pizzeria ON query3.pizzeria_id = pizzeria.id)); END; $person_visits_and_eats_on_date$ LANGUAGE plpgsql; CREATE FUNCTION func_minimum(VARIADIC arr numeric[]) RETURNS numeric AS $minimum$ SELECT MIN($1[i]) FROM GENERATE_SUBSCRIPTS($1, 1) g(i); $minimum$ LANGUAGE SQL; SELECT func_minimum(VARIADIC arr => ARRAY[10.0, -1.0, 5.0, 4.4]);
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