Miravild

Запросы SQL по схеме БД

Создадим необходимые объекты (таблицы, ограничения, индексы):

CREATE TABLE "DEPARTMENT" 
("DEPARTMENT_ID" NUMERIC(11),
 "DEPARTMENT_NAME" VARCHAR2(256)
) TABLESPACE MIRAVILD_DATA;
 
COMMENT ON COLUMN "DEPARTMENT"."DEPARTMENT_ID" IS 'Идентификатор отдела';
COMMENT ON COLUMN "DEPARTMENT"."DEPARTMENT_NAME" IS 'Наименование отдела';
COMMENT ON TABLE "DEPARTMENT" IS 'Отдел';

CREATE UNIQUE INDEX "U_IDX_DEPARTMENT" ON "DEPARTMENT" ("DEPARTMENT_ID") TABLESPACE MIRAVILD_INDEX;

ALTER TABLE "DEPARTMENT" ADD CONSTRAINT "PK_DEPARTMENT" PRIMARY KEY ("DEPARTMENT_ID") 
USING INDEX "U_IDX_DEPARTMENT" ENABLE VALIDATE;
  
ALTER TABLE "DEPARTMENT" MODIFY ("DEPARTMENT_NAME" CONSTRAINT NN_DEPARTMENT_NAME NOT NULL ENABLE);
/

CREATE TABLE "EMPLOYEE" 
("EMPLOYEE_ID" NUMERIC(11),
 "DEPARTMENT_ID" NUMERIC(11),
 "EMPLOYEE_SURNAME" VARCHAR2(256),
 "EMPLOYEE_SALARY" NUMERIC(18, 2)
) TABLESPACE MIRAVILD_DATA;
 
COMMENT ON COLUMN "EMPLOYEE"."EMPLOYEE_ID" IS 'Идентификатор сотрудника';
COMMENT ON COLUMN "EMPLOYEE"."DEPARTMENT_ID" IS 'Идентификатор отдела';
COMMENT ON COLUMN "EMPLOYEE"."EMPLOYEE_SURNAME" IS 'Фамилия сотрудника';
COMMENT ON COLUMN "EMPLOYEE"."EMPLOYEE_SALARY" IS 'Зарплата сотрудника';
COMMENT ON TABLE "EMPLOYEE" IS 'Сотрудник';

CREATE UNIQUE INDEX "U_IDX_EMPLOYEE" ON "EMPLOYEE" ("EMPLOYEE_ID") TABLESPACE MIRAVILD_INDEX;

ALTER TABLE "EMPLOYEE" ADD CONSTRAINT "PK_EMPLOYEE" PRIMARY KEY ("EMPLOYEE_ID")
  USING INDEX "U_IDX_EMPLOYEE" ENABLE VALIDATE;
  
ALTER TABLE "EMPLOYEE" MODIFY ("EMPLOYEE_SURNAME" CONSTRAINT NN_EMPLOYEE_SURNAME NOT NULL ENABLE);
ALTER TABLE "EMPLOYEE" MODIFY ("EMPLOYEE_SALARY" CONSTRAINT NN_EMPLOYEE_SALARY NOT NULL ENABLE);

ALTER TABLE "EMPLOYEE" ADD CONSTRAINT "FK_DEPARTMENT_EMPLOYEE" FOREIGN KEY ("DEPARTMENT_ID") 
REFERENCES "DEPARTMENT" ("DEPARTMENT_ID") ENABLE;
/

Заполним таблицы тестовыми данными:

INSERT INTO department d
  (d.department_id, d.department_name)
VALUES
  (1, 'отдел №1');

INSERT INTO department d
  (d.department_id, d.department_name)
VALUES
  (2, 'отдел №2');

INSERT INTO department d
  (d.department_id, d.department_name)
VALUES
  (3, 'отдел №3');

COMMIT WORK;
/

INSERT INTO employee e
  (e.employee_id, e.department_id, e.employee_surname, e.employee_salary)
VALUES
  (1, 1, 'Петров', 500);

INSERT INTO employee e
  (e.employee_id, e.department_id, e.employee_surname, e.employee_salary)
VALUES
  (2, 1, 'Петров', 700);

INSERT INTO employee e
  (e.employee_id, e.department_id, e.employee_surname, e.employee_salary)
VALUES
  (3, 1, 'Петров', 500);
  
INSERT INTO employee e
  (e.employee_id, e.department_id, e.employee_surname, e.employee_salary)
VALUES
  (4, 2, 'Петров', 100);
  
INSERT INTO employee e
  (e.employee_id, e.department_id, e.employee_surname, e.employee_salary)
VALUES
  (5, 2, 'Петров', 300);
  
INSERT INTO employee e
  (e.employee_id, e.department_id, e.employee_surname, e.employee_salary)
VALUES
  (6, 2, 'Сидоров', 200);
  
INSERT INTO employee e
  (e.employee_id, e.department_id, e.employee_surname, e.employee_salary)
VALUES
  (7, 2, 'Иванов', 100);
  
INSERT INTO employee e
  (e.employee_id, e.department_id, e.employee_surname, e.employee_salary)
VALUES
  (8, 1, 'Иванов', 1500);

INSERT INTO employee e
  (e.employee_id, e.department_id, e.employee_surname, e.employee_salary)
VALUES
  (9, 1, 'Иванов', 1500);

COMMIT WORK;
/

1. Вывести список всех отделов, указав для каждого отдела суммарную зарплату и отсортировав по этой суммарной зарплате по убыванию.

SELECT d.department_name, SUM(e.employee_salary) AS sum_salary
  FROM department d
  LEFT JOIN employee e
    ON e.department_id = d.department_id
 GROUP BY d.department_name
 ORDER BY sum_salary DESC NULLS LAST;
/

SELECT DISTINCT d.department_name,
                SUM(e.employee_salary) over(PARTITION BY d.department_name) AS sum_salary
  FROM department d
  LEFT JOIN employee e
    ON e.department_id = d.department_id
 ORDER BY sum_salary DESC NULLS LAST;
/

2. Вывести по убыванию список всех отделов, указав для каждого отдела количество сотрудников.

SELECT d.department_name, COUNT(e.employee_id) AS cnt_employee
  FROM department d
  LEFT JOIN employee e
    ON e.department_id = d.department_id
 GROUP BY d.department_name
 ORDER BY d.department_name DESC;
/

SELECT DISTINCT d.department_name,
                COUNT(e.employee_id) over(PARTITION BY d.department_name) AS cnt_employee
  FROM department d
  LEFT JOIN employee e
    ON e.department_id = d.department_id
 ORDER BY d.department_name DESC;
/

3. Вывести список всех отделов, где нет сотрудников.

SELECT d.department_name
  FROM department d
  LEFT JOIN employee e
    ON e.department_id = d.department_id
 WHERE e.employee_id IS NULL;
/

SELECT d.department_name
  FROM department d
 WHERE NOT EXISTS
 (SELECT 1 FROM employee e WHERE e.department_id = d.department_id);
/

SELECT d.department_id
  FROM department d
MINUS
SELECT e.department_id
  FROM employee e;
/

4. Вывести список отделов, в названии которых присутствует слово «отдел» и, где только у двоих сотрудников зарплата больше 1000.

SELECT d.department_name
  FROM department d
  JOIN employee e
    ON e.department_id = d.department_id
 WHERE d.department_name LIKE '%отдел%'
   AND e.employee_salary > 1000
 GROUP BY d.department_name
HAVING COUNT(*) = 2;
/

5. Вывести список отделов с указанием суммы зарплаты, где в каждом отделе не менее двух сотрудников с разными фамилиями.

SELECT d.department_name, SUM(e.employee_salary) AS sum_salary
  FROM department d
  JOIN employee e
    ON e.department_id = d.department_id
 GROUP BY d.department_name
HAVING COUNT(DISTINCT e.employee_name) >= 2;
/

P.S.
Конфигурация СУБД — Oracle Database 11g Express Edition Release 11.2.0.2.0
IDE — PL/SQL Developer Version 11.0.6.1776

Tags: ERwin, ERwin Data Modeler, Oracle, SQL