SQLize Online / PHPize Online  /  SQLtest Online

/*первое задание*/ create table clients( id number(10) primary key, name varchar2(1000) not null, place_of_birth varchar2(1000), date_of_birth date, address varchar2(1000), passport varchar2(100) ); create table accounts( id number(10) primary key, name varchar2(100) not null, saldo number(10,2) defaul 0, client_ref number(10) not null, open_date date, close_date date, product_ref number(10), acc_num varchar2(25), constraint acc_cl_fk foreign key (client_ref) references client(id), constraint acc_prod_fk foreign key (product_ref) references product(id) ); create table product( id number(10) primary key, product_type_id number(10), name varchar2(100) not null, client_ref number(10) not null, open_date date, close_date date, constraint prod_cl_fk foreign key (client_ref) references client(id), constraint prod_prodtype_fk foreign key (product_type_id) references product_type(id) ); create table product_type( id number(10) primary key, name varchar2(100) not null, begin_date date, end_date date, tarif ref number(10), constraint prod_type_tar_fk foreign key (tarif_ref) references tarifs(id) ); create table records( id number(10) primary key, dt number(1) CHECK (DT IN (0, 1)), sum number(10,2) not null, acc_ref number(10) not null, oper_date date not null, constraint rec_acc_fk foreign key (acc_ref) references accounts(id) ); create table tarifs( id number(10) primary key, name varchar2(100) not null, cost (10,2) ); /*второе задание*/ insert into tarifs values (1,'Тариф за выдачу кредита', 10); insert into tarifs values (2,'Тариф за открытие счета', 10); insert into tarifs values (3,'Тариф за обслуживание карты', 10); insert into productype values (1, 'КРЕДИТ', to_date('01.01.2018','DD.MM.YYYY'), null, 1); insert into productype values (2, 'ДЕПОЗИТ', to_date('01.01.2018','DD.MM.YYYY'), null, 2); insert into productype 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')); /*третье задание*/ -- Клиенты без кредитов (для задания 4) insert into clients values (4, 'Кузнецов Андрей Алексеевич', 'Россия, Санкт-Петербург', to_date('01.01.1990','DD.MM.YYYY'), 'Россия, Санкт-Петербург, ул. Ленина, д. 10', '3333 777777, выдан ОВД г. Санкт-Петербург, 10.01.2010'); insert into products values (4, 2, 'Депозитный договор с Кузнецовым А.А.', 4, to_date('01.01.2023','DD.MM.YYYY'), null); insert into accounts values (4, 'Депозитный счет для Кузнецова А.А.', 15000, 4, to_date('01.01.2023','DD.MM.YYYY'), null, 2, '42301810400000000002'); -- Движения в рамках одного дня (для задания 5) insert into records values (16, 1, 5000, 4, to_date('15.12.2024','DD.MM.YYYY')); insert into records values (17, 0, 2000, 4, to_date('15.12.2024','DD.MM.YYYY')); -- Операции за прошедший месяц (для задания 6) insert into records values (18, 0, 3000, 2, to_date('15.11.2024','DD.MM.YYYY')); insert into records values (19, 1, 1000, 2, to_date('20.11.2024','DD.MM.YYYY')); -- Несоответствие балансов и операций (для задания 7) insert into accounts values (5, 'Счет с некорректным балансом', 1000, 1, to_date('01.01.2022','DD.MM.YYYY'), null, 1, '40817810700000000005'); insert into records values (20, 1, 2000, 5, to_date('02.01.2022','DD.MM.YYYY')); -- Баланс не учитывает это движение -- Погашенные кредиты с незакрытыми продуктами (для задания 8) insert into accounts values (6, 'Погашенный кредитный счет', 0, 1, to_date('01.06.2020','DD.MM.YYYY'), null, 1, '45502810401020000023'); insert into products values (5, 1, 'Кредитный договор с Петровым А.Н.', 6, to_date('01.06.2020','DD.MM.YYYY'), null); -- Продукты без движения более месяца (для задания 10) insert into accounts values (7, 'Счет без движения', 5000, 1, to_date('01.06.2022','DD.MM.YYYY'), null, 3, '40817810700000000007'); -- Последняя операция по счету более месяца назад insert into records values (21, 1, 1000, 7, to_date('01.10.2024','DD.MM.YYYY')); /*четвертое задание*/ select * from accounts a join products p on a.product_ref = p.id join product_type pt on p.product_type_id = pt.id where pt.name = 'ДЕПОЗИТ' and p.client_ref not in ( select distinct p.client_ref from products p join product_type pt on p.product_type_id = pt.id where pt.name = 'КРЕДИТ' and p.close_date is null ); /*пятое задание*/ select pt.name as product_type, r.oper_date, sum(case when r.dt = 1 then r.sum else 0 end) as debit_sum, sum(case when r.dt = 0 then r.sum else 0 end) as credit_sum from records r join accounts as a on r.account_id = a.id join products as p on a.product_id = p.id join product_type as pt on p.product_type_id = pt.id where r.oper_date = ('2024-12-15') group by pt.name, r.oper_date; /*шестое задание*/ select c.id as client_id, c.name as client_name, r.oper_date, SUM(r.sum) from clients c join accounts a on c.id = a.client_ref join records r on a.id = r.acc_ref where r.dt = 1 and r.oper_date >= ADD_MONTHS(TRUNC(SYSDATE), -1) and r.oper_date < TRUNC(SYSDATE) group by c.id, c.name, r.oper_date order by r.oper_date, c.name; /*седьмое задание это я не знаю*/ CREATE OR REPLACE PROCEDURE normalize_account_balances IS BEGIN -- Цикл по всем счетам FOR acc IN (SELECT id, saldo FROM accounts) LOOP -- Вычисляем реальный остаток по счёту на основе операций DECLARE real_balance NUMBER; BEGIN SELECT NVL(SUM(CASE WHEN r.dt = 0 THEN r.sum ELSE 0 END), 0) - -- Сумма кредитовых операций NVL(SUM(CASE WHEN r.dt = 1 THEN r.sum ELSE 0 END), 0) -- Сумма дебетовых операций INTO real_balance FROM records r WHERE r.acc_ref = acc.id; -- Ссылка на текущий счёт -- Если рассчитанный остаток не совпадает с текущим IF acc.saldo != real_balance THEN -- Обновляем остаток в таблице ACCOUNTS UPDATE accounts SET saldo = real_balance WHERE id = acc.id; DBMS_OUTPUT.PUT_LINE('Обновлен счет ID: ' || acc.id || ', новый остаток: ' || real_balance); END IF; END; END LOOP; -- Фиксируем изменения COMMIT; DBMS_OUTPUT.PUT_LINE('Нормализация остатков по счетам завершена.'); END; / /*восьмое задание*/ select distinct c.id as client_id, c.name as client_name, p.id as product_id, p.name as product_name, p.close_date as product_close_date, a.id as account_id, a.name as account_name, a.saldo from clients c join products p on c.id = p.client_ref join product_type pt on p.product_type_id = pt.id join accounts a on a.product_ref = p.id where pt.name = 'КРЕДИТ' and a.saldo = 0 and p.close_date is null; /*девятое задание*/ update products p set close_date = SYSDATE where id in ( select p.id from products p join product_type pt on p.product_type_id = pt.id join accounts a on a.product_ref = p.id left join records r on r.acc_ref = a.id and r.dt = 1 where pt.name = 'КРЕДИТ' and a.saldo = 0 and p.close_date is null and r.id is null ); /*десятое задание*/ update product_type pt set end_date = SYSDATE where id in ( select distinct pt.id from product_type pt join products p on p.product_type_id = pt.id join accounts a on a.product_ref = p.id left join records r on r.acc_ref = a.id where r.oper_date is null or r.oper_date < ADD_MONTHS(SYSDATE, -1) ); /*11 задание*/