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
--https://sqlize.online/sql/oracle21/c3d60ebfbfdb2829afc312408113bf7c/ -- DROP -- DROP TABLE clients; -- DROP TABLE accounts; ------------------------ -- CREATE INSERT CREATE TABLE clients (client_id VARCHAR(1), FIO VARCHAR(255) , Region VARCHAR(50), account_num INT); INSERT INTO clients VALUES ('A','Иванов','Москва',111); INSERT INTO clients VALUES ('A','Иванов','Москва',222); INSERT INTO clients VALUES ('B','Петров','Иваново',333); INSERT INTO clients VALUES ('C','Сидоров','Москва',444); INSERT INTO clients VALUES ('D','Новиков','Москва',555); INSERT INTO clients VALUES ('E','Шварценеггер','Лос-Анджелес',666); INSERT INTO clients VALUES ('D','Новиков','Москва',777); INSERT INTO clients VALUES ('F','Морозов','г.Москва',888); INSERT INTO clients VALUES ('G','Цветков','Москва',999); INSERT INTO clients VALUES ('G','Цветков','Москва',1111); INSERT INTO clients VALUES ('H','Шубин','Монтевидео',2222); INSERT INTO clients VALUES ('K','Крюков','Москва',3333); CREATE TABLE accounts (value_day varchar(25), Summa_USD int, Account_num INT); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-01-01',15000,111); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-02-01',10000,111) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-03-01',5000,222) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-03-01',30000,333) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-04-01',20000,444) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-01-01',200000,666); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-02-01',400000,666); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-03-01',200000,666); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-04-01',198000,666) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-01-01',5000,555); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-02-01',15000,555); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-03-01',10000,555) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-01-01',2000,777); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-03-01',20000,777); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-08-01',10000,777) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-02-01',10000,888); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-04-01',2000,888); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-07-01',20500,888) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-01-01',45000,999); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-02-01',545000,999); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-03-01',0,999) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-01-01',1000,1111); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-02-01',10000,1111); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-03-01',21000,1111); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-04-01',19500,1111) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-01-01',10000,2222); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-02-01',20000,2222); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-03-01',60000,2222) ; COMMIT; ------------------------ -- SELECT * FROM clients; -- SELECT * FROM accounts; ------------------------ ------------------------ -- Задание 1 -- Вывести список клиентов из Москвы с суммарными остатками по клиенту -- и проставить флаг превышения средств свыше 20 000 -- на последнюю дату. with acc_last_sum as ( select distinct Account_num, last_value(Summa_USD) over (partition by Account_num order by value_day rows between unbounded preceding and unbounded following) "last_sum" from accounts ) select FIO ,sum("last_sum") "сумм ост" ,case -- when sum("last_sum") > 20000 then true else false when sum("last_sum") > 20000 then 1 else 0 end "флаг" from clients cl join acc_last_sum ac on ac.Account_num = cl.Account_num where -- lower(Region) like '%м%с%к%' --ilike без в постгре например -- replace(Region, 'г.') like 'Москва' Region in ('Москва','г.Москва') group by FIO order by "флаг" desc ; explain analyze with acc_last_sum2 as ( select Account_num ,max(value_day) "last_day" from accounts group by Account_num ) select FIO ,sum(Summa_USD) "сумм ост" ,case when sum(Summa_USD) > 20000 then 1 else 0 end "флаг" from acc_last_sum2 a2 join accounts ac on ac.Account_num = a2.Account_num and ac.value_day = a2."last_day" join clients cl on cl.Account_num = ac.Account_num where Region in ('Москва','г.Москва') group by FIO order by 3 desc
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