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 ('Паспорт', 'Доверенность')), -- CHECK-constraint для ограничения допустимых значений DOC_TYPE 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); -- Вставка данных в таблицу 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')); -- Вставка данных в таблицу 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', 'Основной счет Сидорова'); -- Вставка данных в таблицу 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); --Сколько денег у каждого клиента? Отсортировать по имени клиента по алфавиту SELECT P.PPL_NAME, A.ACCNT_ACNT, B.BLNC_OSTATOK 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 ORDER BY P.PPL_NAME; --Сколько клиентов без паспорта? SELECT P.PPL_NAME FROM PEOPLE P LEFT JOIN DOCS D ON P.PPL_CODE = D.DOC_PPL_CODE WHERE D.DOC_TYPE IS NULL; --Сколько денег у поручителя с конкретным номером паспорта? SELECT B.BLNC_OSTATOK 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 = '111111'; --Где живет самый богатый человек? 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 P.PPL_NAME, A.ACCNT_ACNT, B.BLNC_OSTATOK, 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 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 A.ACCNT_CRNC = 'RUB' -- Убедимся, что работаем только с рублями ORDER BY BALANCE_GROUP, P.PPL_NAME; -- Добавление столбца даты рождения ALTER TABLE PEOPLE ADD PPL_BIRTH_DATE DATE; -- Обновление дат рождения UPDATE PEOPLE SET PPL_BIRTH_DATE = TO_DATE('1990/01/01', 'yyyy/mm/dd') WHERE PPL_CODE = 1; -- Иванов Иван Иванович - 33 года UPDATE PEOPLE SET PPL_BIRTH_DATE = TO_DATE('1988/06/15', 'yyyy/mm/dd') WHERE PPL_CODE = 2; -- Петров Петр Петрович - 35 лет UPDATE PEOPLE SET PPL_BIRTH_DATE = TO_DATE('1992/11/20', 'yyyy/mm/dd') WHERE PPL_CODE = 3; -- Сидоров Сидор Сидорович - 30 лет UPDATE PEOPLE SET PPL_BIRTH_DATE = TO_DATE('1995/05/10', 'yyyy/mm/dd') WHERE PPL_CODE = 4; -- Абдрахманов Сергей Юрьевич - 28 лет -- Обновление балансов UPDATE BLNC SET BLNC_OSTATOK = 35000 WHERE BLNC_ACCNT_CODE = 2; -- Петров Петр Петрович - 35000 руб. UPDATE BLNC SET BLNC_OSTATOK = 40000 WHERE BLNC_ACCNT_CODE = 3; -- Сидоров Сидор Сидорович - 40000 руб. -- Убедимся, что все счета в рублях UPDATE ACCNT SET ACCNT_CRNC = 'RUB' WHERE ACCNT_CODE IN (1, 2, 3); -- Запрос 2: Клиенты в возрасте от 20 до 35 с остатком на счетах от 25 000 до 100 000 руб. SELECT P.PPL_NAME, A.ACCNT_ACNT, B.BLNC_OSTATOK, FLOOR(MONTHS_BETWEEN(SYSDATE, P.PPL_BIRTH_DATE) / 12) AS AGE 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 FLOOR(MONTHS_BETWEEN(SYSDATE, P.PPL_BIRTH_DATE) / 12) BETWEEN 20 AND 35 AND A.ACCNT_CRNC = 'RUB' AND B.BLNC_OSTATOK BETWEEN 25000 AND 100000 ORDER BY P.PPL_NAME; -- Запрос 3: Клиенты по городу жительства с остатками на счетах от 30 000 до 50 000 руб. SELECT P.PPL_NAME, A.ACCNT_ACNT, B.BLNC_OSTATOK, AD.ADDR_CITY 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 JOIN ADDRESS AD ON P.PPL_CODE = AD.ADDR_PPL_CODE WHERE AD.ADDR_CITY = 'Санкт-Петербург' AND A.ACCNT_CRNC = 'RUB' AND B.BLNC_OSTATOK BETWEEN 30000 AND 50000 ORDER BY P.PPL_NAME;