SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
create schema if not exists workspace; set search_path = workspace; create table if not exists CLIENTS( ID smallint unique primary key, NAME varchar not null, PLACE_OF_BIRTH varchar not null, DATE_OF_BIRTH date not null, ADDRESS varchar not null, PASSPORT varchar not null ); create table if not exists TARIFS( ID smallint unique primary key, NAME varchar not null, COST integer not null ); create table if not exists PRODUCT_TYPE( ID smallint unique primary key, NAME varchar not null, BEGIN_DATE date, END_DATE date, TARIF_REF integer REFERENCES TARIFS(ID) ); create table if not exists PRODUCTS( ID smallint unique primary key, PRODUCT_TYPE_ID smallint REFERENCES PRODUCT_TYPE(ID), NAME varchar not null, CLIENT_REF smallint REFERENCES CLIENTS(ID), OPEN_DATE date, CLOSE_DATE date ); create table if not exists ACCOUNTS( ID smallint unique primary key, NAME varchar not null, SALDO integer not null, CLIENT_REF smallint REFERENCES CLIENTS(ID), OPEN_DATE date, CLOSE_DATE date, PRODUCT_REF smallint REFERENCES PRODUCTS(ID), ACC_NUM varchar not null ); create table if not exists RECORDS( ID smallint unique primary key, DT smallint, SUM integer not null, ACC_REF smallint REFERENCES ACCOUNTS(ID), OPER_DATE date ); insert into tarifs values (1,'Тариф за выдачу кредита', 10); insert into tarifs values (2,'Тариф за открытие счета', 10); insert into tarifs values (3,'Тариф за обслуживание карты', 10); insert into product_type values (1, 'КРЕДИТ', to_date('01.01.2018','DD.MM.YYYY'), null, 1); insert into product_type values (2, 'ДЕПОЗИТ', to_date('01.01.2018','DD.MM.YYYY'), null, 2); insert into product_type values (3, 'КАРТА', to_date('01.01.2018','DD.MM.YYYY'), null, 3); insert into clients values (1, 'Сидоров Иван Петрович', 'Россия, Московская облать, г. Пушкин', to_date('01.01.2001','DD.MM.YYYY'), 'Россия, Московская облать, г. Пушкин, ул. Грибоедова, д. 5', '2222 555555, выдан ОВД г. Пушкин, 10.01.2015'); insert into clients values (2, 'Иванов Петр Сидорович', 'Россия, Московская облать, г. Клин', to_date('01.01.2001','DD.MM.YYYY'), 'Россия, Московская облать, г. Клин, ул. Мясникова, д. 3', '4444 666666, выдан ОВД г. Клин, 10.01.2015'); insert into clients values (3, 'Петров Сиодр Иванович', 'Россия, Московская облать, г. Балашиха', to_date('01.01.2001','DD.MM.YYYY'), 'Россия, Московская облать, г. Балашиха, ул. Пушкина, д. 7', '4444 666666, выдан ОВД г. Клин, 10.01.2015'); insert into products values (1, 1, 'Кредитный договор с Сидоровым И.П.', 1, to_date('01.06.2015','DD.MM.YYYY'), null); insert into products values (2, 2, 'Депозитный договор с Ивановым П.С.', 2, to_date('01.08.2017','DD.MM.YYYY'), null); insert into products values (3, 3, 'Карточный договор с Петровым С.И.', 3, to_date('01.08.2017','DD.MM.YYYY'), null); insert into accounts values (1, 'Кредитный счет для Сидоровым И.П.', -2000, 1, to_date('01.06.2015','DD.MM.YYYY'), null, 1, '45502810401020000022'); insert into accounts values (2, 'Депозитный счет для Ивановым П.С.', 6000, 2, to_date('01.08.2017','DD.MM.YYYY'), null, 2, '42301810400000000001'); insert into accounts values (3, 'Карточный счет для Петровым С.И.', 8000, 3, to_date('01.08.2017','DD.MM.YYYY'), null, 3, '40817810700000000001'); insert into records values (1, 1, 5000, 1, to_date('01.06.2015','DD.MM.YYYY')); insert into records values (2, 0, 1000, 1, to_date('01.07.2015','DD.MM.YYYY')); insert into records values (3, 0, 2000, 1, to_date('01.08.2015','DD.MM.YYYY')); insert into records values (4, 0, 3000, 1, to_date('01.09.2015','DD.MM.YYYY')); insert into records values (5, 1, 5000, 1, to_date('01.10.2015','DD.MM.YYYY')); insert into records values (6, 0, 3000, 1, to_date('01.10.2015','DD.MM.YYYY')); insert into records values (7, 0, 10000, 2, to_date('01.08.2017','DD.MM.YYYY')); insert into records values (8, 1, 1000, 2, to_date('05.08.2017','DD.MM.YYYY')); insert into records values (9, 1, 2000, 2, to_date('21.09.2017','DD.MM.YYYY')); insert into records values (10, 1, 5000, 2, to_date('24.10.2017','DD.MM.YYYY')); insert into records values (11, 0, 6000, 2, to_date('26.11.2017','DD.MM.YYYY')); insert into records values (12, 0, 120000, 3, to_date('08.09.2017','DD.MM.YYYY')); insert into records values (13, 1, 1000, 3, to_date('05.10.2017','DD.MM.YYYY')); insert into records values (14, 1, 2000, 3, to_date('21.10.2017','DD.MM.YYYY')); insert into records values (15, 1, 5000, 3, to_date('24.10.2017','DD.MM.YYYY')); insert into tarifs values(4,'Тариф за выдачу кредита', 15); insert into clients values (4, 'Алексеев Николай Александрович', 'Россия, Новосибирская облать, г. Новосибирск', to_date('01.08.2002','DD.MM.YYYY'), 'Россия, Новосибирская облать, г. Новосибирск, ул. Народная, д. 26', '5017 888888, выдан ОВД г. Новосибирск, 18.04.2015'); insert into clients values (5, 'Семенов Никита Александрович', 'Россия, Омская область, г. Омск', to_date('03.06.2002','DD.MM.YYYY'), 'Россия, Омская облать, г. Омск, ул. Некрасова, д. 1', '4412 663665, выдан ОВД г. Омск, 10.01.2016'); insert into clients values (6, 'Еникеев Тимур Иванович', 'Россия, Московская облать, г. Москва', to_date('04.11.2001','DD.MM.YYYY'), 'Россия, Московская облать, г. Москва, ул. Багровая, д. 5', '4444 666666, выдан ОВД г. Москва, 06.05.2015'); insert into products values (4, 1, 'Кредитный договор с Алексеевым Н.А.', 4, to_date('01.06.2015','DD.MM.YYYY'), null); insert into products values (5, 2, 'Депозитный договор с Семеновым Н.А.', 5, to_date('01.08.2017','DD.MM.YYYY'), null); insert into products values (6, 2, 'Депозитный договор с Еникеевым Т.И.', 6, to_date('01.08.2017','DD.MM.YYYY'), null); insert into products values (7, 1, 'Кредитный договор с Еникеевым Т.И.', 6, to_date('01.08.2017','DD.MM.YYYY'), null); insert into accounts values (4, 'Кредитный счет для Алексеева Н.А.', -5000, 4, to_date('01.06.2015','DD.MM.YYYY'), null, 4, '45502810401020000022'); insert into accounts values (5, 'Депозитный счет для Семенова Н.А.', 3000, 5, to_date('01.08.2017','DD.MM.YYYY'), null, 5, '45203141540000000001'); insert into accounts values (6, 'Депозитный счет для Еникеева Т.И.', 7000, 6, to_date('01.08.2017','DD.MM.YYYY'), null, 6, '49081781070000000001'); insert into accounts values (7, 'Кредитный счет для Еникеева Т.И.', 0, 6, to_date('01.08.2017','DD.MM.YYYY'), null, 7, '49081781070123400001'); insert into records values (16, 1, 5000, 6, to_date('01.06.2015','DD.MM.YYYY')); insert into records values (17, 0, 1000, 6, to_date('01.07.2015','DD.MM.YYYY')); insert into records values (18, 0, 2000, 6, to_date('24.10.2017','DD.MM.YYYY')); insert into records values (19, 0, 3000, 7, to_date('01.09.2015','DD.MM.YYYY')); insert into records values (20, 1, 5000, 7, to_date('24.10.2017','DD.MM.YYYY')); insert into records values (21, 0, 2000, 7, to_date('24.10.2017','DD.MM.YYYY')); insert into records values (22, 0, 11000, 4, to_date('01.08.2017','DD.MM.YYYY')); insert into records values (23, 1, 2000, 4, to_date('05.08.2017','DD.MM.YYYY')); insert into records values (24, 1, 2000, 4, to_date('21.09.2017','DD.MM.YYYY')); insert into records values (25, 1, 5000, 4, to_date('24.10.2017','DD.MM.YYYY')); insert into records values (26, 0, 6000, 4, to_date('26.11.2017','DD.MM.YYYY')); insert into records values (27, 0, 130000, 5, to_date('08.09.2017','DD.MM.YYYY')); insert into records values (28, 1, 4000, 5, to_date('05.10.2017','DD.MM.YYYY')); insert into records values (30, 1, 3000, 5, to_date('02.07.2022','DD.MM.YYYY')); insert into records values (29, 1, 2000, 5, to_date('24.10.2017','DD.MM.YYYY')); select* from records; select* from products; select acc.name, saldo, acc.client_ref, acc.open_date, acc.close_date, acc.product_ref, acc_num from( select accounts.client_ref from accounts inner join products pr ON pr.id = accounts.product_ref where product_type_id = 2 Except select accounts.client_ref from accounts inner join products pr ON pr.id = accounts.product_ref where product_type_id = 1) x inner join accounts acc ON x.client_ref = acc.client_ref; select Distinct x.oper_date, x.product_type_id, avg(x.sum) OVER (PARTITION BY x.product_type_id) from( select new_t.oper_date, new_t.sum, pr.product_type_id from( select records.oper_date, records.sum, acc.product_ref from records inner join accounts acc ON records.acc_ref = acc.id) new_t inner join products pr ON new_t.product_ref = pr.id where oper_date = to_date('24.10.2017','DD.MM.YYYY')) x; select cl.name, x.sum, x.oper_date from( select oper_date, records.sum, acc.client_ref from records inner join accounts acc ON acc.id = records.acc_ref Where records.oper_date + 30 > to_date('08.07.2022','DD.MM.YYYY')) x inner join clients cl ON cl.id = x.client_ref; create view Operations As select cl.id as client_id, y.dt, y.sum, y.saldo, y.id as product_id, y.product_type_id from( select x.dt, x.sum, x.client_ref, x.saldo, pr.id, pr.product_type_id from( select records.dt, records.sum, acc.product_ref, acc.client_ref, acc.saldo from records inner join accounts acc ON acc.id = records.acc_ref) x inner join products pr ON pr.id = x.product_ref) y inner join clients cl ON cl.id = y.client_ref; create view Operations_debet as select o.client_id, o.sum, o.saldo, o.product_id, o.product_type_id from operations o where dt = 1; create view Operations_credit as select o.client_id, o.sum, o.saldo, o.product_id, o.product_type_id from operations o where dt = 0; UPDATE products SET close_date = CASE WHEN products.id IN (select acc.product_ref as product_id from records inner join accounts acc ON acc.id = records.acc_ref Where records.oper_date + 30 > to_date('08.07.2022','DD.MM.YYYY')) THEN to_date('08.07.2022','DD.MM.YYYY') ELSE close_date END; UPDATE products SET close_date = CASE WHEN products.id IN ( select products.id from products inner join accounts acc ON acc.product_ref = products.id where product_type_id = 1 AND saldo = 0) THEN to_date('08.07.2022','DD.MM.YYYY') ELSE close_date END; create view real_sums_accounts as select ACC_REF, sum(sum) from ( select ACC_REF, (case when dt = 1 then -sum else sum end) AS sum from RECORDS ) x group by ACC_REF order by acc_ref; select* from accounts; select*from real_sums_accounts; create or replace procedure set_real_sum() LANGUAGE 'plpgsql' as $$ BEGIN UPDATE accounts acc SET acc.saldo = CASE when real_sums_accounts.acc_ref = accounts.id THEN real_sums_accounts.sum ELSE acc.saldo END; END;$$ select* from operations; ALTER TABLE products ADD COLUMN contract_sum integer; create view max_sum_debet as select Distinct Operations_debet.product_type_id, max(operations_debet.sum) OVER (PARTITION BY operations_debet.product_type_id) as max_s from operations_debet; create view max_sum_credit as select Distinct Operations_credit.product_type_id, max(operations_credit.sum) OVER (PARTITION BY operations_credit.product_type_id) as max_s from operations_credit; UPDATE max_sum_credit SET max_s = 13000 where product_type_id = 3; select* from max_sum_credit;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear