SQLize Online / PHPize Online  /  SQLtest Online

/* 1. Подготовьте DDL-скрипты создания объектов для приведённой модели: создание таблиц, первичных, уникальных, внешних ключей и т.д. 2. Заполните таблицы примерами из скрипта, приведенного в конце этого документа. 3. Подготовьте скрипты заполнения таблиц тестовыми данными, достаточными для выполнения заданий ниже. */ create table clients ( ID NUMBER(10) PRIMARY KEY NOT NULL, NAME VARCHAR2(1000) NOT NULL, PLACE_OF_BIRTH VARCHAR2(1000) NOT NULL, DATE_OF_BIRTH DATE NOT NULL, -- DATE_OF_BIRTH DATE NOT NULL CHECK (DATE_OF_BIRTH > '01-Jan-1900'), ADDRESS VARCHAR2(1000) NOT NULL, PASSPORT VARCHAR2(100) UNIQUE NOT NULL -- CONSTRAINT clients_chk_DATE_OF_BIRTH CHECK (DATE_OF_BIRTH > '01-Jan-1900'), ); 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 clients values (4, 'ХХХ ХХХ ХХХ', 'Россия, Московская облать, г. Балашиха', to_date('01.01.2001','DD.MM.YYYY'), 'Россия, Московская облать, г. Балашиха, ул. Пушкина, д. 7', '4454 666666, выдан ОВД г. Клин, 10.01.2015'); insert into clients values (5, 'Овечкин Александр Михайлович', 'Россия, Московская облать, г. Балашиха', to_date('17.10.1985','DD.MM.YYYY'), 'Россия, Московская облать, г. Москва, ул. Хоккейная, д. 1', '1234 123456, выдан ОВД г. Москва, 17.10.2011'); --------------------------------------------------------------------------------------------- create table tarifs ( ID NUMBER(10) PRIMARY KEY NOT NULL, NAME VARCHAR2(100) NOT NULL, COST NUMBER(10,2) ); insert into tarifs values (1,'Тариф за выдачу кредита', 10); insert into tarifs values (2,'Тариф за открытие счета', 10); insert into tarifs values (3,'Тариф за обслуживание карты', 10); --------------------------------------------------------------------------------------------- create table productype ( ID NUMBER(10) PRIMARY KEY NOT NULL, NAME VARCHAR2(100) unique NOT NULL, BEGIN_DATE DATE NOT NULL, END_DATE DATE, TARIF_REF NUMBER(10) NOT NULL, CONSTRAINT END_DATE_CHECK CHECK (END_DATE >= BEGIN_DATE), FOREIGN KEY (TARIF_REF) REFERENCES tarifs (ID) ); /* create or replace trigger aftinsttest after insert on productype for each row begin insert into tarifs (NAME) values(:new.NAME); end aftinsttest; ALTER TRIGGER aftinsttest ENABLE; */ 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); --------------------------------------------------------------------------------------------- create table products ( ID NUMBER(10) PRIMARY KEY NOT NULL, PRODUCT_TYPE_ID NUMBER(10) NOT NULL, NAME VARCHAR2(100) NOT NULL, CLIENT_REF NUMBER(10) NOT NULL, OPEN_DATE DATE NOT NULL, -- OPEN_DATE DATE NOT NULL CHECK (OPEN_DATE > '01-Jan-1900'), CLOSE_DATE DATE, CONSTRAINT CLOSE_DATE_CHECK CHECK (CLOSE_DATE >= OPEN_DATE), FOREIGN KEY (CLIENT_REF) REFERENCES clients (ID), FOREIGN KEY (PRODUCT_TYPE_ID) REFERENCES productype (ID) ); 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, 1, 'Кредитный договор с ХХХ Х.Х.', 4, to_date('01.06.2015','DD.MM.YYYY'), null); insert into products values (5, 1, 'Кредитный договор с Овечкиным А.М.', 5, to_date('01.06.2015','DD.MM.YYYY'), null); --for task 4 insert into products values (6, 2, 'Депозитный договор с Сидоровым И.П.', 1, to_date('01.06.2014','DD.MM.YYYY'), null); insert into products values (7, 1, 'Кредитный договор с Ивановым П.С.', 2, to_date('01.08.2017','DD.MM.YYYY'), to_date('01.08.2018','DD.MM.YYYY')); --------------------------------------------------------------------------------------------- create table accounts ( ID NUMBER(10) PRIMARY KEY NOT NULL, NAME VARCHAR2(100) NOT NULL, SALDO NUMBER (10,2) NOT NULL, CLIENT_REF NUMBER(10) NOT NULL, OPEN_DATE DATE NOT NULL, -- OPEN_DATE DATE NOT NULL CHECK (OPEN_DATE > '01-Jan-1900'), CLOSE_DATE DATE, PRODUCT_REF NUMBER(10) NOT NULL, ACC_NUM VARCHAR2(25) NOT NULL, CONSTRAINT ACC_CLOSE_DATE_CHECK CHECK (CLOSE_DATE >= OPEN_DATE), FOREIGN KEY (CLIENT_REF) REFERENCES clients (ID), FOREIGN KEY (PRODUCT_REF) REFERENCES products (ID) ); 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, 'Кредитный счет для Сидоровым И.П.', -2222, 1, to_date('01.07.2015','DD.MM.YYYY'), null, 1, '45502810401020000023'); insert into accounts values (4, 'Кредитный счет1 для ХХХ Х.Х.', 4444, 4, to_date('01.07.2015','DD.MM.YYYY'), null, 4, '45502810401020000055'); insert into accounts values (5, 'Кредитный счет2 для ХХХ Х.Х.', 0, 4, to_date('01.07.2015','DD.MM.YYYY'), null, 4, '45502810401020000088'); insert into accounts values (6, 'Кредитный счет для Овечкиным А.М.', 0, 5, to_date('01.07.2015','DD.MM.YYYY'), null, 5, '11111111111111111111'); --for task 4 insert into accounts values (7, 'Депозитный счет для Сидоровым И.П.', 0, 1, to_date('01.06.2014','DD.MM.YYYY'), null, 2, '45502810401020000044'); insert into accounts values (8, 'Кредитный счет для Ивановым П.С.', 0, 2, to_date('01.08.2017','DD.MM.YYYY'), to_date('01.08.2018','DD.MM.YYYY'), 1, '42301810400000000044'); --------------------------------------------------------------------------------------------- --create sequence rec_seq start with 1 increment by 1; create table records ( ID NUMBER(10) generated always as identity (start with 1 increment by 1) PRIMARY KEY NOT NULL, DT NUMBER (1) NOT NULL, SUM NUMBER(10,2) NOT NULL, ACC_REF NUMBER(10) NOT NULL, OPER_DATE DATE NOT NULL, FOREIGN KEY (ACC_REF) REFERENCES accounts (ID) ); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (1, 5000, 1, to_date('01.06.2015','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (0, 1000, 1, to_date('01.07.2015','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (0, 2000, 1, to_date('01.08.2015','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (0, 3000, 1, to_date('01.09.2015','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (1, 5000, 1, to_date('01.10.2015','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (0, 3000, 1, to_date('01.10.2015','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (0, 10000, 2, to_date('01.08.2017','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (1, 1000, 2, to_date('05.08.2017','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (1, 2000, 2, to_date('21.09.2017','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (1, 5000, 2, to_date('24.10.2017','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (0, 6000, 2, to_date('26.11.2017','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (0, 120000, 3, to_date('08.09.2017','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (1, 1000, 3, to_date('05.10.2017','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (1, 2000, 3, to_date('21.10.2017','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (1, 5000, 3, to_date('24.10.2017','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (1, 5000, 4, to_date('21.10.2017','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (0, 5000, 4, to_date('24.10.2017','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (1, 5000, 4, to_date('24.10.2017','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (1, 4000, 6, to_date('24.10.2017','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (0, 4000, 6, to_date('24.11.2017','DD.MM.YYYY')); --for task 5 insert into records (DT, SUM, ACC_REF, OPER_DATE) values (1, 5555, 1, to_date('01.10.2015','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (0, 5555, 1, to_date('01.10.2015','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (1, 5555, 2, to_date('01.10.2015','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (0, 5555, 2, to_date('01.10.2015','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (1, 5555, 3, to_date('01.10.2015','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (0, 5555, 3, to_date('01.10.2015','DD.MM.YYYY')); --for task 6 insert into records (DT, SUM, ACC_REF, OPER_DATE) values (1, 6666, 1, to_date('01.03.2025','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (0, 6666, 1, to_date('01.03.2025','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (1, 6666, 2, to_date('01.03.2025','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (0, 6666, 2, to_date('01.03.2025','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (1, 6666, 3, to_date('01.03.2025','DD.MM.YYYY')); insert into records (DT, SUM, ACC_REF, OPER_DATE) values (0, 6666, 3, to_date('01.03.2025','DD.MM.YYYY')); --------------------------------------------------------------------------------------------- SELECT * from clients; SELECT * FROM tarifs; SELECT * from productype; SELECT * from products; SELECT * from accounts; SELECT * from records; --------------------------------------------------------------------------------------------- /*4. Сформируйте выборку, которая содержит все счета, относящиеся к продуктам типа ДЕПОЗИТ, принадлежащих клиентам, у которых нет открытых продуктов типа КРЕДИТ.*/ select * from accounts where CLIENT_REF not in ( select CLIENT_REF from accounts where (PRODUCT_REF = 1 and CLOSE_DATE is null) --проверка в счетах union all select CLIENT_REF from products where (PRODUCT_TYPE_ID = 1 and CLOSE_DATE is null) ----проверка в продуктах ) and PRODUCT_REF = 2 order by CLIENT_REF, PRODUCT_REF; --------------------------------------------------------------------------------------------- /*5. Сформируйте выборку, которая выведет сумму движений по счетам (дебетовые и кредитовые) в рамках одного произвольного дня, в разрезе типа продукта.*/ -- с общим результатом - all_prod with cte as( select coalesce(TO_CHAR(DT), 'total') as DTT, coalesce(TO_CHAR(ACC_REF), 'all_acc') as ACC_REFF, sum(SUM) as agg from records where OPER_DATE = to_date('01.10.2015','DD.MM.YYYY') group by rollup(DT, ACC_REF) ) select distinct coalesce(productype.NAME, 'all_prod') as PROD_NAME, DT_0.agg as SUM_DT_0, DT_1.agg as SUM_DT_1, (NVL(DT_0.agg, 0) - NVL(DT_1.agg, 0)) as RESULT from cte left join cte DT_0 on cte.ACC_REFF = DT_0.ACC_REFF and DT_0.DTT = '0' left join cte DT_1 on cte.ACC_REFF = DT_1.ACC_REFF and DT_1.DTT = '1' left join accounts on TO_CHAR(accounts.ID) = cte.ACC_REFF left join products on products.ID = PRODUCT_REF left join productype on productype.ID = PRODUCT_TYPE_ID; -- без общего результата with cte as ( select DT, ACC_REF, sum(SUM) as agg from records where OPER_DATE = to_date('01.10.2015','DD.MM.YYYY') group by rollup(DT, ACC_REF) ) select distinct productype.NAME as PROD_NAME, DT_0.agg as SUM_DT_0, DT_1.agg as SUM_DT_1, (NVL(DT_0.agg, 0) - NVL(DT_1.agg, 0)) as RESULT from cte left join cte DT_0 on cte.ACC_REF = DT_0.ACC_REF and DT_0.DT = 0 left join cte DT_1 on cte.ACC_REF = DT_1.ACC_REF and DT_1.DT = 1 join accounts on accounts.ID = cte.ACC_REF join products on products.ID = PRODUCT_REF join productype on productype.ID = PRODUCT_TYPE_ID;