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 P.PPL_NAME, SUM(B.BLNC_OSTATOK) AS TOTAL_BALANCE, CASE WHEN SUM(B.BLNC_OSTATOK) < &MIN_BALANCE THEN 'До ' || &MIN_BALANCE || ' руб.' WHEN SUM(B.BLNC_OSTATOK) BETWEEN &MIN_BALANCE AND &MID_BALANCE THEN 'От ' || &MIN_BALANCE || ' до ' || &MID_BALANCE || ' руб.' WHEN SUM(B.BLNC_OSTATOK) BETWEEN &MID_BALANCE AND &MAX_BALANCE THEN 'От ' || &MID_BALANCE || ' до ' || &MAX_BALANCE || ' руб.' ELSE 'Свыше ' || &MAX_BALANCE || ' руб.' END AS BALANCE_GROUP 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 P.PPL_NAME ORDER BY P.PPL_NAME; -- Выбрать клиентов в возрасте от 20 до 35 с остатком на счетах от 25 000 до 100 000 руб. SELECT P.PPL_NAME, SUM(B.BLNC_OSTATOK) AS TOTAL_BALANCE 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 WHERE TRUNC(MONTHS_BETWEEN(SYSDATE, P.PPL_BIRTHDATE)/12) BETWEEN &MIN_AGE AND &MAX_AGE GROUP BY P.PPL_NAME HAVING SUM(B.BLNC_OSTATOK) BETWEEN 25000 AND 100000; -- Выбрать клиентов по городу жительства с остатками на счетах от 30 000 до 50 000 руб. SELECT P.PPL_NAME, A.ADDR_CITY, SUM(B.BLNC_OSTATOK) AS TOTAL_BALANCE FROM PEOPLE P JOIN ACCNT AC ON P.PPL_CODE = AC.ACCNT_PPL_CODE JOIN BLNC B ON AC.ACCNT_CODE = B.BLNC_ACCNT_CODE JOIN ADDRESS A ON P.PPL_CODE = A.ADDR_PPL_CODE WHERE A.ADDR_CITY = '&CITY' GROUP BY P.PPL_NAME, A.ADDR_CITY HAVING SUM(B.BLNC_OSTATOK) BETWEEN 30000 AND 50000; -- Сколько денег у каждого клиента? Отсортировать по имени клиента по алфавиту (учесть, что у людей может быть несколько счетов) SELECT P.PPL_NAME, SUM(B.BLNC_OSTATOK) AS TOTAL_BALANCE 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 P.PPL_NAME ORDER BY P.PPL_NAME; -- Список клиентов без паспорта? (учесть, что у людей может быть несколько паспортов) SELECT P.PPL_NAME FROM PEOPLE P LEFT JOIN DOCS D ON P.PPL_CODE = D.DOC_PPL_CODE AND D.DOC_TYPE = 'Паспорт' WHERE D.DOC_PPL_CODE IS NULL; -- Сколько денег у поручителя человека с конкретным номером паспортом? (сделать номер паспорта бинд-переменной) SELECT SUM(B.BLNC_OSTATOK) AS TOTAL_BALANCE FROM DOCS D JOIN PEOPLE P ON D.DOC_PPL_CODE = P.PPL_CODE JOIN PEOPLE P2 ON P.PPL_PPL_CODE = P2.PPL_CODE JOIN ACCNT A ON P2.PPL_CODE = A.ACCNT_PPL_CODE JOIN BLNC B ON A.ACCNT_CODE = B.BLNC_ACCNT_CODE WHERE D.DOC_TYPE = 'Паспорт' AND D.DOC_NUM = '&PASSPORT_NUMBER'; -- Где живет самый богатый человек? 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;