SQLize Online / PHPize Online  /  SQLtest Online

-- Создание таблицы PEOPLE CREATE TABLE PEOPLE ( PPL_CODE NUMBER PRIMARY KEY, PPL_NAME VARCHAR2(2000) NOT NULL, PPL_BIRTHDATE DATE NOT NULL, -- Добавим поле даты рождения для расчета возраста PPL_PPL_CODE NUMBER REFERENCES PEOPLE(PPL_CODE) -- Поручитель (может быть null) ); -- Создание таблицы DOCS 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, PRIMARY KEY (DOC_PPL_CODE, DOC_NUM) ); -- Создание таблицы ADDRESS 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, PRIMARY KEY (ADDR_PPL_CODE) ); -- Создание таблицы ACCNT 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 ); -- Создание таблицы BLNC CREATE TABLE BLNC ( BLNC_ACCNT_CODE NUMBER REFERENCES ACCNT(ACCNT_CODE), BLNC_OSTATOK NUMBER NOT NULL, PRIMARY KEY (BLNC_ACCNT_CODE) ); -- Вставка данных в таблицу PEOPLE INSERT INTO PEOPLE (PPL_CODE, PPL_NAME, PPL_BIRTHDATE, PPL_PPL_CODE) VALUES (1, 'Иванов Иван Иванович', TO_DATE('1990/01/01','yyyy/mm/dd'), NULL); INSERT INTO PEOPLE (PPL_CODE, PPL_NAME, PPL_BIRTHDATE, PPL_PPL_CODE) VALUES (2, 'Петров Петр Петрович', TO_DATE('1985/06/15','yyyy/mm/dd'), NULL); INSERT INTO PEOPLE (PPL_CODE, PPL_NAME, PPL_BIRTHDATE, PPL_PPL_CODE) VALUES (3, 'Сидоров Сидор Сидорович', TO_DATE('1992/03/20','yyyy/mm/dd'), 1); -- Поручитель для Иванова INSERT INTO PEOPLE (PPL_CODE, PPL_NAME, PPL_BIRTHDATE, PPL_PPL_CODE) VALUES (4, 'Абдрахманов Сергей Юрьевич', TO_DATE('1987/11/25','yyyy/mm/dd'), 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 (1, '222222', 'DD', 'Паспорт', TO_DATE('2010/01/01','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'); -- Вставка данных в таблицу 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', 1, '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); -- Баланс второго счета Иванова -- Сколько денег у каждого клиента? Отсортировать по имени клиента по алфавиту (учесть, что у людей может быть несколько счетов) SELECT A.ADDR_CITY FROM ADDRESS A JOIN PEOPLE P ON A.ADDR_PPL_CODE = P.PPL_CODE JOIN ACCNT ACC ON P.PPL_CODE = ACC.ACCNT_PPL_CODE JOIN BLNC B ON ACC.ACCNT_CODE = B.BLNC_ACCNT_CODE ORDER BY B.BLNC_OSTATOK DESC FETCH FIRST 1 ROW ONLY; SELECT ADDR.ADDR_CITY, P.PPL_NAME, P.PPL_CODE, SUM(B.BLNC_OSTATOK) FROM PEOPLE P JOIN ACCNT A ON P.PPL_CODE = A.ACCNT_PPL_CODE JOIN ADDRESS ADDR ON P.PPL_CODE = ADDR.ADDR_PPL_CODE JOIN BLNC B ON A.ACCNT_CODE = B.BLNC_ACCNT_CODE GROUP BY ADDR.ADDR_CITY, P.PPL_NAME, P.PPL_CODE ORDER BY SUM(B.BLNC_OSTATOK) DESC FETCH FIRST 1 ROW ONLY;