TestBank

an anonymous user · January 15, 2022 · SQL
Create table CLIENTS (
    client_cd primary_key number,
    FIO	varchar2(300),
    BIRTH_DATE date,
    GENDER varchar2(2),
    BIRTH_PLACE	varchar2(300),
    SOCIAL_STATUS varchar2(30),
    MONTH_INCOME number,
    J_ORG_NAME varchar2(300))
                    ;
insert into CLIENTS(CLIENT_CD, FIO, BIRTH_DATE, GENDER, BIRTH_PLACE, 
SOCIAL_STATUS, MONTH_INCOME, J_ORG_NAME)
values (9569614, 'ШИЛОВА ЛЮБОВЬ ИЛЬИНИЧНА', '19.01.1996', 'F', 'ГОР. УЛЬЯНОВСК', 'STUDENT', 10000, NULL),
        (176278, 'ПОГРЕБНОЙ МАКСИМ ПАВЛОВИЧ', '11.02.1984', 'M', 'Г. САРАТОВ', 'WORK', 18000, 'ЗАО "АГРОТЕХСНАБ"'),
        (9569612, 'МАКАРОВ АЛЕКСЕЙ АЛЕКСЕЕВИЧ', '13.05.1983', 'M', 'ГОРОД ТУЛА', 'WORK', 27000, 'ООО "ТРУБОПЛАСТ"'),
        (9569658, 'ГАНИЧКИНА ЕКАТЕРИНА СЕРГЕЕВНА', '23.01.1969', 'F', 'ГОРОД ВОЛЬСК ', 'PENSIONER', 17000, NULL);
create table APPLICATIONS (
  application_cd PRIMARY_KEY number,
  created_date date,
  macroprod_dsc varchar2(100),
  client_cd number,
  deal_loan_cd number,
  approved_flg number,
  tt_region varchar2(30),
  req_loan_amt number,
  approv_loan_amt number,
  credit_rate number)
  ;
  
insert into APPLICATIONS
(
APPLICATION_CD,
CREATED_DATE,
MACROPROD_DSC,
CLIENT_CD,
DEAL_LOAN_CD,
APPROVED_FLG,
TT_REGION,
REQ_LOAN_AMT,
APPROV_LOAN_AMT,
CREDIT_RATE)
values (105840706, '02.07.2019', 'CASH', 42617872, NULL, 0, 'Дальний Восток', 200000, NULL, 12.91),
    (105880134, '02.07.2019', 'CASH', 27549261, 29340620, 1, 'Центр', 353000, 240000, 17.91),
    (115449073, '14.10.2019', 'CASH', 6905203, NULL, 0, 'Поволжье', 453000, NULL, 12.90),
    (90574832, '16.01.2019', 'POS', 39038117, NULL, 1, 'Урал', 59000, 59000, 14.90),
    (101231735, '13.05.2019', 'CASH', 29090926, NULL, 1, 'Юг', 376000, 299900, 14.92)
    ;

select truncate(CREATED_DATE, 'mm_yyyy'), count(DEAL_LOAN_CD) from applications
where DEAL_LOAN_CD is not null
group by truncate(CREATED_DATE, 'mm_yyyy')









Comments

Please sign up or log in to contribute to the discussion.