SQLize Online / PHPize Online  /  SQLtest Online

-- Создание таблиц CREATE TABLE PEOPLE ( PPL_CODE NUMBER PRIMARY KEY, PPL_NAME VARCHAR2(2000) NOT NULL, PPL_PPL_CODE NUMBER REFERENCES PEOPLE(PPL_CODE) UNIQUE ); CREATE TABLE DOCS ( DOC_PPL_CODE NUMBER REFERENCES PEOPLE(PPL_CODE), DOC_NUM VARCHAR2(30) NOT NULL, DOC_SERIES VARCHAR2(30) NOT NULL, DOC_TYPE VARCHAR2(30) CHECK (DOC_TYPE IN ('Паспорт', 'Доверенность')), DOC_DATE DATE NOT NULL ); CREATE TABLE ADDRESS ( ADDR_PPL_CODE NUMBER REFERENCES PEOPLE(PPL_CODE), ADDR_CITY VARCHAR2(200) NOT NULL, ADDR_STREET VARCHAR2(200) NOT NULL, ADDR_HOUSE VARCHAR2(200) NOT NULL, ADDR_FLAT VARCHAR2(20) NOT NULL ); CREATE TABLE ACCNT ( ACCNT_CODE NUMBER PRIMARY KEY, ACCNT_ACNT VARCHAR2(30) NOT NULL, ACCNT_PPL_CODE NUMBER REFERENCES PEOPLE(PPL_CODE), ACCNT_CRNC VARCHAR2(3) NOT NULL, ACCNT_NAME VARCHAR2(2000) NOT NULL ); CREATE TABLE BLNC ( BLNC_ACCNT_CODE NUMBER REFERENCES ACCNT(ACCNT_CODE), BLNC_OSTATOK NUMBER NOT NULL ); -- Вставка данных в таблицу PEOPLE INSERT INTO PEOPLE (PPL_CODE, PPL_NAME, PPL_PPL_CODE) VALUES (1, 'Иванов Иван Иванович', NULL); INSERT INTO PEOPLE (PPL_CODE, PPL_NAME, PPL_PPL_CODE) VALUES (2, 'Петров Петр Петрович', NULL); INSERT INTO PEOPLE (PPL_CODE, PPL_NAME, PPL_PPL_CODE) VALUES (3, 'Сидоров Сидор Сидорович', 1); INSERT INTO PEOPLE (PPL_CODE, PPL_NAME, PPL_PPL_CODE) VALUES (4, 'Абдрахманов Сергей Юрьевич', NULL); INSERT INTO PEOPLE (PPL_CODE, PPL_NAME, PPL_PPL_CODE) VALUES (5, 'Кузнецов Алексей Викторович', NULL); INSERT INTO PEOPLE (PPL_CODE, PPL_NAME, PPL_PPL_CODE) VALUES (6, 'Смирнова Анна Сергеевна', NULL); INSERT INTO PEOPLE (PPL_CODE, PPL_NAME, PPL_PPL_CODE) VALUES (7, 'Лебедев Дмитрий Александрович', 1); INSERT INTO PEOPLE (PPL_CODE, PPL_NAME, PPL_PPL_CODE) VALUES (8, 'Григорьев Сергей Владимирович', NULL); -- Вставка данных в таблицу DOCS INSERT INTO DOCS (DOC_PPL_CODE, DOC_NUM, DOC_SERIES, DOC_TYPE, DOC_DATE) VALUES (1, '123456', 'AA', 'Паспорт', TO_DATE('2004/08/13','yyyy/mm/dd')); INSERT INTO DOCS (DOC_PPL_CODE, DOC_NUM, DOC_SERIES, DOC_TYPE, DOC_DATE) VALUES (2, '654321', 'BB', 'Доверенность', TO_DATE('2004/08/13','yyyy/mm/dd')); INSERT INTO DOCS (DOC_PPL_CODE, DOC_NUM, DOC_SERIES, DOC_TYPE, DOC_DATE) VALUES (3, '111111', 'CC', 'Паспорт', TO_DATE('2004/08/13','yyyy/mm/dd')); INSERT INTO DOCS (DOC_PPL_CODE, DOC_NUM, DOC_SERIES, DOC_TYPE, DOC_DATE) VALUES (4, '222222', 'DD', 'Паспорт', TO_DATE('2005/01/15','yyyy/mm/dd')); INSERT INTO DOCS (DOC_PPL_CODE, DOC_NUM, DOC_SERIES, DOC_TYPE, DOC_DATE) VALUES (5, '333333', 'EE', 'Паспорт', TO_DATE('2003/02/20','yyyy/mm/dd')); INSERT INTO DOCS (DOC_PPL_CODE, DOC_NUM, DOC_SERIES, DOC_TYPE, DOC_DATE) VALUES (6, '444444', 'FF', 'Доверенность', TO_DATE('2002/03/25','yyyy/mm/dd')); INSERT INTO DOCS (DOC_PPL_CODE, DOC_NUM, DOC_SERIES, DOC_TYPE, DOC_DATE) VALUES (7, '555555', 'GG', 'Паспорт', TO_DATE('2001/04/30','yyyy/mm/dd')); -- Вставка данных в таблицу ADDRESS INSERT INTO ADDRESS (ADDR_PPL_CODE ,ADDR_CITY ,ADDR_STREET ,ADDR_HOUSE ,ADDR_FLAT )VALUES(1,'Москва','Ленинский проспект','100','1'); INSERT INTO ADDRESS (ADDR_PPL_CODE ,ADDR_CITY ,ADDR_STREET ,ADDR_HOUSE ,ADDR_FLAT )VALUES(2,'Санкт-Петербург','Невский проспект','150','2'); INSERT INTO ADDRESS (ADDR_PPL_CODE ,ADDR_CITY ,ADDR_STREET ,ADDR_HOUSE ,ADDR_FLAT )VALUES(3,'Новосибирск','Красный проспект','200','3'); INSERT INTO ADDRESS (ADDR_PPL_CODE ,ADDR_CITY ,ADDR_STREET ,ADDR_HOUSE ,ADDR_FLAT )VALUES(5,'Екатеринбург','Ленина ул.','50','5'); INSERT INTO ADDRESS (ADDR_PPL_CODE ,ADDR_CITY ,ADDR_STREET ,ADDR_HOUSE ,ADDR_FLAT )VALUES(6,'Казань','Кремлевская ул.','10','3'); INSERT INTO ADDRESS (ADDR_PPL_CODE ,ADDR_CITY ,ADDR_STREET ,ADDR_HOUSE ,ADDR_FLAT )VALUES(7,'Челябинск','Тракторостроителей ул.','20','7'); -- Вставка данных в таблицу ACCNT INSERT INTO ACCNT (ACCNT_CODE, ACCNT_ACNT, ACCNT_PPL_CODE, ACCNT_CRNC, ACCNT_NAME) VALUES (1,'111111',1,'RUB','Основной счет Иванова'); INSERT INTO ACCNT (ACCNT_CODE, ACCNT_ACNT, ACCNT_PPL_CODE, ACCNT_CRNC, ACCNT_NAME) VALUES (2,'222222',2,'USD','Основной счет Петрова'); INSERT INTO ACCNT (ACCNT_CODE, ACCNT_ACNT, ACCNT_PPL_CODE, ACCNT_CRNC, ACCNT_NAME) VALUES (3,'333333',3,'EUR','Основной счет Сидорова'); INSERT INTO ACCNT (ACCNT_CODE, ACCNT_ACNT, ACCNT_PPL_CODE, ACCNT_CRNC, ACCNT_NAME) VALUES (4,'444444',4,'RUB','Основной счет Абдрахманова'); INSERT INTO ACCNT (ACCNT_CODE, ACCNT_ACNT, ACCNT_PPL_CODE, ACCNT_CRNC, ACCNT_NAME) VALUES (5,'555555',5,'USD','Основной счет Кузнецова'); INSERT INTO ACCNT (ACCNT_CODE, ACCNT_ACNT, ACCNT_PPL_CODE, ACCNT_CRNC, ACCNT_NAME) VALUES (6,'666666',6,'EUR','Основной счет Смирновой'); INSERT INTO ACCNT (ACCNT_CODE, ACCNT_ACNT, ACCNT_PPL_CODE ,ACCNT_CRNC ,ACCNT_NAME )VALUES(7,'777777' ,7 ,'RUB' ,'Основной счет Лебедева'); -- Вставка данных в таблицу BLNC INSERT INTO BLNC(BLNC_ACCNT_CODE ,BLNC_OSTATOK )VALUES(1 ,10000); INSERT INTO BLNC(BLNC_ACCNT_CODE ,BLNC_OSTATOK )VALUES(2 ,20000); INSERT INTO BLNC(BLNC_ACCNT_CODE ,BLNC_OSTATOK )VALUES(3 ,30000); INSERT INTO BLNC(BLNC_ACCNT_CODE ,BLNC_OSTATOK )VALUES(4 ,40000); INSERT INTO BLNC(BLNC_ACCNT_CODE ,BLNC_OSTATOK )VALUES(5 ,60000); INSERT INTO BLNC(BLNC_ACCNT_CODE ,BLNC_OSTATOK )VALUES(6 ,25000); INSERT INTO BLNC(BLNC_ACCNT_CODE ,BLNC_OSTATOK )VALUES(7 ,35000); -- Разбивка клиентов на группы по остаткам на счетах SELECT CASE WHEN B.BLNC_OSTATOK < 10000 THEN '< 10 000 руб.' WHEN B.BLNC_OSTATOK BETWEEN 10000 AND 50000 THEN '10 000 - 50 000 руб.' WHEN B.BLNC_OSTATOK BETWEEN 50000 AND 100000 THEN '50 000 - 100 000 руб.' ELSE '> 100 000 руб.' END AS Balance_Group, COUNT(P.PPL_NAME) AS Client_Count FROM PEOPLE P JOIN ACCNT A ON P.PPL_CODE = A.ACCNT_PPL_CODE JOIN BLNC B ON A.ACCNT_CODE = B.BLNC_ACCNT_CODE GROUP BY CASE WHEN B.BLNC_OSTATOK < 10000 THEN '< 10 000 руб.' WHEN B.BLNC_OSTATOK BETWEEN 10000 AND 50000 THEN '10 000 - 50 000 руб.' WHEN B.BLNC_OSTATOK BETWEEN 50000 AND 100000 THEN '50 000 - 100 000 руб.' ELSE '> 100 000 руб.' END; -- Выбор клиентов в возрасте от min_age до max_age с остатком на счетах от min_balance до max_balance SELECT P.PPL_NAME FROM PEOPLE P JOIN DOCS D ON P.PPL_CODE = D.DOC_PPL_CODE JOIN ACCNT A ON P.PPL_CODE = A.ACCNT_PPL_CODE JOIN BLNC B ON A.ACCUNT CODE = B.BLNC_ACCANT CODE WHERE EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM D.DOC_DATE) BETWEEN :min_age AND :max_age AND B.BLNC_OSTATOK BETWEEN :min_balance AND :max_balance; -- Выбор клиентов по городу жительства с остатками на счетах от min_balance до max_balance SELECT P.PFL_NAME FROM PEOPLE P JOIN ADDRESS A ON P.PFL_COD = A.ADDR_COD JOIN ACCOUNT AC ON P.PFL_COD = AC.ACCOUNT_COD JOIN BALANCE B ON AC.ACCOUNT_COD = B.BALANCE_COD WHERE A.ADDR_CITY = :city AND B.BALANCE_AMOUNT BETWEEN :min_balance AND :max_balance;