Департамент и сотрудник

Даны две SQL-таблицы:

EMPLOYEE — сотрудник
— EMPLOYEE_ID
— EMPLOYEE_NAME — имя
— lEMPLOYEE_SURNAME — фамилия
— EMPLOYEE_SALARY — размер зарплаты
— DEPARTMENT_ID — отдел

DEPARTMENT — отдел
— DEPARTMENT_ID
— DEPARTMENT_NAME — название отдела

Напишите запросы к ним, которые будут выводить:

  1. Список имён и фамилий всех сотрудников с названиями отделов, к которым каждый из них привязан
  2. Список отделов, к которым привязано более двух сотрудников


Тестовые таблицы:

CREATE TABLE "DEPARTMENT" 
("DEPARTMENT_ID" INTEGER,
 "DEPARTMENT_NAME" VARCHAR2(256)
);
 
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");

ALTER TABLE "DEPARTMENT" ADD CONSTRAINT "PK_DEPARTMENT" PRIMARY KEY ("DEPARTMENT_ID") 
USING INDEX "U_IDX_DEPARTMENT" ENABLE VALIDATE;
/

CREATE TABLE "EMPLOYEE" 
("EMPLOYEE_ID" INTEGER,
 "EMPLOYEE_NAME" VARCHAR2(256),
 "EMPLOYEE_SURNAME" VARCHAR2(256),
 "EMPLOYEE_SALARY" NUMBER,
 "DEPARTMENT_ID" INTEGER
);
 
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_SURNAME" IS 'Фамилия сотрудника';
COMMENT ON COLUMN "EMPLOYEE"."EMPLOYEE_SALARY" IS 'Зарплата сотрудника';
COMMENT ON TABLE "EMPLOYEE" IS 'Сотрудник';

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

ALTER TABLE "EMPLOYEE" ADD CONSTRAINT "PK_EMPLOYEE" PRIMARY KEY ("EMPLOYEE_ID")
  USING INDEX "U_IDX_EMPLOYEE" ENABLE VALIDATE;

ALTER TABLE "EMPLOYEE" MODIFY ("EMPLOYEE_NAME" CONSTRAINT NN_EMPLOYEE_NAME NOT NULL ENABLE);  
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;
/

Список имён и фамилий всех сотрудников с названиями отделов, к которым каждый из них привязан:

select e.employee_name, e.employee_surname, d.department_name
  from employee e
  left join department d
    on d.department_id = e.department_id;
/

Список отделов, к которым привязано более двух сотрудников:

select d.department_name
  from employee e
  join department d
    on d.department_id = e.department_id
 group by d.department_name
having count(*) > 2;
/

Tags: ,

Comments are closed.