SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE clients( ID number(10), NAME varchar2(1000), PLACE_OF_BIRTH varchar2(1000), DATE_OF_BIRTH date, ADDRESS varchar2(1000), PASSPORT varchar2(100), CONSTRAINT CLIENTS_PK primary key (ID) ); CREATE TABLE products( ID number(10), PRODUCT_TYPE_ID number(10), NAME varchar2(100), CLIENT_REF number(10), OPEN_DATE date, CLOSE_DATE date, CONSTRAINT PRODUCTS_PK primary key (ID) ); CREATE TABLE product_type( ID number(10), NAME varchar2(100), BEGIN_DATE date, END_DATE date, TARIF_REF number(10), CONSTRAINT PRODUCT_TYPE_PK primary key (ID) ); CREATE TABLE accounts( ID number(10), NAME varchar2(100), SALDO number(10,2), CLIENT_REF number(10), OPEN_DATE date, CLOSE_DATE date, PRODUCT_REF number(10), ACC_NUM varchar2(25), CONSTRAINT ACCOUNTS_PK primary key (ID) ); CREATE TABLE records( ID number(10), DT number(1), SUM number(10,2), ACC_REF number(10), OPER_DATE date, CONSTRAINT RECORDS_PK primary key (ID) ); CREATE TABLE tarifs( ID number(10), NAME varchar2(100), COST number(10,2), CONSTRAINT TARIFS_PK primary key (ID) ); ALTER TABLE products ADD CONSTRAINT PROD_Cl_FK FOREIGN KEY (CLIENT_REF) references clients(ID); ALTER TABLE products ADD CONSTRAINT PROD_PRODTYPE_FK FOREIGN KEY (PRODUCT_TYPE_ID) references product_type(ID); ALTER TABLE product_type ADD CONSTRAINT PROD_TYPE_TAR_FK FOREIGN KEY (TARIF_REF) references tarifs(ID); ALTER TABLE accounts ADD CONSTRAINT ACC_Cl_FK FOREIGN KEY (CLIENT_REF) references clients(ID); ALTER TABLE accounts ADD CONSTRAINT ACC_PROD_FK FOREIGN KEY (PRODUCT_REF) references products(ID); 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 products values (4, 3, 'Карточный договор с Петровым С.И.', 3, to_date('02.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 accounts values (4, 'Карточный счет для Петровым С.И.', 822000, 3, to_date('01.08.2017','DD.MM.YYYY'), null, 4, '40817810700000000012'); 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')); select * from products; select * from records; CREATE OR REPLACE Function Repair() IS cursor c1 is select g2.name, g1.ACC_NUM, COALESCE(tt1.DEBET, 0) as DEBET, COALESCE(tt2.KREDIT, 0) as KREDIT from accounts g1 left join (select t1.name, t3.acc_num, sum(t4.sum) as DEBET from product_type t1 right join products t2 on(t1.ID = t2.PRODUCT_TYPE_ID) right join accounts t3 on(t2.ID = t3.PRODUCT_REF) right join (select * from records where DT = 1) t4 on(t3.ID = t4.ACC_REF) where t4.OPER_DATE = to_date('01.08.2017','DD.MM.YYYY') group by t3.acc_num, t1.name) tt1 on(g1.ACC_NUM = tt1.ACC_NUM) left join (select t1.name, t3.acc_num, sum(t4.sum) as KREDIT from product_type t1 right join products t2 on(t1.ID = t2.PRODUCT_TYPE_ID) right join accounts t3 on(t2.ID = t3.PRODUCT_REF) right join (select * from records where DT = 0) t4 on(t3.ID = t4.ACC_REF) where t4.OPER_DATE = to_date('01.08.2017','DD.MM.YYYY') group by t3.acc_num, t1.name) tt2 on(g1.ACC_NUM = tt2.ACC_NUM) left join products on(g1.product_ref=products.id) left join product_type g2 on(products.product_type_id=g2.id); /* 5 task */