В таблице «ИНЦИДЕНТ»/«INCIDENT» содержится информация по произошедшим в банке инцидентам с 2003 по 2006 года.
- Поле «INCIDENT_NUMBER» — уникальный номер инцидента.
- Поле «INCIDENT_DATE» — дата, когда инцидент был обнаружен сотрудником банка.
- Поле «INCIDENT_EMPLOYEE» — сотрудник банка, обнаруживший инцидент.
В таблице «ФИНАНСОВЫЙ ЭФФЕКТ»/«FINANCIAL_EFFECT» представлена информация по потерям по каждому из инцидентов. Поле «INCIDENT_NUMBER» таблицы «INCIDENT» является primary key поля «INCIDENT_NUMBER» таблицы «FINANCIAL_EFFECT».
Создадим необходимые таблицы:
CREATE TABLE "INCIDENT" ("INCIDENT_NUMBER" VARCHAR2(64), "INCIDENT_DATE" DATE, "INCIDENT_EMPLOYEE" VARCHAR2(256)) TABLESPACE MIRAVILD_DATA; COMMENT ON TABLE "INCIDENT" IS 'Информация по произошедшим в банке инцидентам с 2003 по 2006 года'; COMMENT ON COLUMN "INCIDENT"."INCIDENT_NUMBER" IS 'Уникальный номер инцидента'; COMMENT ON COLUMN "INCIDENT"."INCIDENT_DATE" IS 'Дата, когда инцидент был обнаружен сотрудником банка'; COMMENT ON COLUMN "INCIDENT"."INCIDENT_EMPLOYEE" IS 'Cотрудник банка, обнаруживший инцидент'; CREATE UNIQUE INDEX "INCIDENT_U_IDX" ON "INCIDENT" ("INCIDENT_NUMBER") TABLESPACE MIRAVILD_INDEX; ALTER TABLE "INCIDENT" ADD CONSTRAINT "INCIDENT_PK" PRIMARY KEY ("INCIDENT_NUMBER") USING INDEX "INCIDENT_U_IDX" ENABLE VALIDATE; ALTER TABLE "INCIDENT" MODIFY ("INCIDENT_DATE" CONSTRAINT INCIDENT_DATE_NN NOT NULL ENABLE); ALTER TABLE "INCIDENT" MODIFY ("INCIDENT_EMPLOYEE" CONSTRAINT INCIDENT_EMPLOYEE_NN NOT NULL ENABLE); /
CREATE TABLE "FINANCIAL_EFFECT" ("INCIDENT_NUMBER" VARCHAR2(64), "LOSS" NUMERIC(11)) TABLESPACE MIRAVILD_DATA; COMMENT ON TABLE "FINANCIAL_EFFECT" IS 'Финансовый эффект'; COMMENT ON COLUMN "FINANCIAL_EFFECT"."LOSS" IS 'Информация о потери по каждому из инцидентов'; ALTER TABLE "FINANCIAL_EFFECT" ADD CONSTRAINT "INCIDENT_EFFECT_FK" FOREIGN KEY ("INCIDENT_NUMBER") REFERENCES "INCIDENT" ("INCIDENT_NUMBER") ENABLE; ALTER TABLE "FINANCIAL_EFFECT" MODIFY ("INCIDENT_NUMBER" CONSTRAINT EFFECT_INCIDENT_NUMBER_NN NOT NULL ENABLE); ALTER TABLE "FINANCIAL_EFFECT" MODIFY ("LOSS" CONSTRAINT FINANCIAL_EFFECT_LOSS_NN NOT NULL ENABLE); /
Добавим тестовые данные:
INSERT INTO incident i (i.incident_number, i.incident_date, i.incident_employee) VALUES ('EVE-01215', to_date('07.12.2003', 'dd.mm.yyyy'), 'Heather Ainley'); INSERT INTO incident i (i.incident_number, i.incident_date, i.incident_employee) VALUES ('EVE-01216', to_date('14.02.2005', 'dd.mm.yyyy'), 'Sacha Galloway'); INSERT INTO incident i (i.incident_number, i.incident_date, i.incident_employee) VALUES ('EVE-01217', to_date('29.06.2004', 'dd.mm.yyyy'), 'Sacha Galloway'); INSERT INTO incident i (i.incident_number, i.incident_date, i.incident_employee) VALUES ('EVE-01218', to_date('19.01.2003', 'dd.mm.yyyy'), 'Patricia Pittaway'); INSERT INTO incident i (i.incident_number, i.incident_date, i.incident_employee) VALUES ('EVE-01219', to_date('22.09.2003', 'dd.mm.yyyy'), 'Peyman Mestchian'); INSERT INTO incident i (i.incident_number, i.incident_date, i.incident_employee) VALUES ('EVE-01220', to_date('29.01.2004', 'dd.mm.yyyy'), 'Patricia Pittaway'); INSERT INTO incident i (i.incident_number, i.incident_date, i.incident_employee) VALUES ('EVE-01221', to_date('11.10.2004', 'dd.mm.yyyy'), 'Raj Ray'); INSERT INTO incident i (i.incident_number, i.incident_date, i.incident_employee) VALUES ('EVE-01222', to_date('07.08.2005', 'dd.mm.yyyy'), 'Raj Ray'); INSERT INTO incident i (i.incident_number, i.incident_date, i.incident_employee) VALUES ('EVE-01223', to_date('21.07.2003', 'dd.mm.yyyy'), 'Peyman Mestchian'); INSERT INTO incident i (i.incident_number, i.incident_date, i.incident_employee) VALUES ('EVE-01224', to_date('16.05.2004', 'dd.mm.yyyy'), 'Peyman Mestchian'); INSERT INTO incident i (i.incident_number, i.incident_date, i.incident_employee) VALUES ('EVE-01225', to_date('18.11.2003', 'dd.mm.yyyy'), 'Peyman Mestchian'); INSERT INTO incident i (i.incident_number, i.incident_date, i.incident_employee) VALUES ('EVE-01226', to_date('20.02.2004', 'dd.mm.yyyy'), 'Peyman Mestchian'); INSERT INTO incident i (i.incident_number, i.incident_date, i.incident_employee) VALUES ('EVE-01227', to_date('18.10.2005', 'dd.mm.yyyy'), 'Heather Ainley'); INSERT INTO incident i (i.incident_number, i.incident_date, i.incident_employee) VALUES ('EVE-01228', to_date('01.03.2005', 'dd.mm.yyyy'), 'Patricia Pittaway'); INSERT INTO incident i (i.incident_number, i.incident_date, i.incident_employee) VALUES ('EVE-01229', to_date('22.12.2002', 'dd.mm.yyyy'), 'Patricia Pittaway'); INSERT INTO incident i (i.incident_number, i.incident_date, i.incident_employee) VALUES ('EVE-01230', to_date('28.03.2004', 'dd.mm.yyyy'), 'Raj Ray'); COMMIT WORK; /
INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01215', to_number('208690')); INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01216', to_number('235270')); INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01217', to_number('25960')); INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01215', to_number('22280')); INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01218', to_number('588570')); INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01218', to_number('29970')); INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01218', to_number('27570')); INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01221', to_number('38760')); INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01215', to_number('8980')); INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01222', to_number('15030')); INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01225', to_number('6090')); INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01224', to_number('16400')); INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01221', to_number('319740')); INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01227', to_number('78380')); INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01227', to_number('19430')); INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01228', to_number('979190')); INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01228', to_number('11760')); INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01229', to_number('20960')); INSERT INTO financial_effect fe (fe.incident_number, fe.loss) VALUES ('EVE-01230', to_number('11960')); COMMIT WORK; /
Задание №1
Вывести всех сотрудников («INCIDENT_NUMBER» | «INCIDENT_NUMBER» | «INCIDENT_EMPLOYEE»), которые обнаружили инциденты после 01.01.2003.
SELECT i.* FROM incident i WHERE i.incident_date > to_date('01.01.2003', 'dd.mm.yyyy'); /
Задание №2
Вывести список сотрудников, которые чаще других встречаются в таблице «INCIDENT».
SELECT i1.incident_employee AS "Сотрудник", COUNT(*) AS "Количество" FROM incident i1 GROUP BY i1.incident_employee HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM incident i2 GROUP BY i2.incident_employee); /
Вариант исполнения предыдущего запрос
SELECT i.incident_employee AS "Сотрудник", i.cnt_employee AS "Количество" FROM (SELECT i.incident_employee, i.cnt_employee, rank() over(ORDER BY i.cnt_employee DESC) AS rank, dense_rank() over(ORDER BY i.cnt_employee DESC) AS dense_rank FROM (SELECT i.incident_employee, COUNT(*) AS cnt_employee FROM incident i GROUP BY i.incident_employee) i) i WHERE i.rank = 1; /
Задание №3
Вывести список уникальных идентификаторов инцидентов и суммы потерь по каждому из них.
SELECT DISTINCT fe.incident_number AS "Номер инцидента", SUM(fe.loss) over(PARTITION BY fe.incident_number) AS "Сумма потерь" FROM financial_effect fe ORDER BY "Сумма потерь" DESC; /
Задание №4
Вывести сотрудника, обнаружившего наибольшую сумму потерь с 2003 по 2006 год.
SELECT i.incident_employee AS "Сотрудник" FROM (SELECT fe.incident_number, SUM(fe.loss) AS sum_loss FROM financial_effect fe GROUP BY fe.incident_number HAVING SUM(fe.loss) = (SELECT MAX(SUM(fe.loss)) AS max_sum_loss FROM financial_effect fe GROUP BY fe.incident_number)) fe JOIN incident i ON i.incident_number = fe.incident_number; /
SELECT i.incident_employee AS "Сотрудник" FROM (SELECT fe.incident_number, rank() over(ORDER BY fe.sum_loss DESC) AS rank, dense_rank() over(ORDER BY fe.sum_loss DESC) AS dense_rank FROM (SELECT DISTINCT fe.incident_number, SUM(fe.loss) over(PARTITION BY fe.incident_number) AS sum_loss FROM financial_effect fe) fe) fe JOIN incident i ON i.incident_number = fe.incident_number WHERE fe.rank = 1; /