Тема 24 Информационная система рекламного агенства

CREATE TABLE "Сотрудник" (
    "Код_сотр" NUMBER(3) NOT NULL,
    "Имя_сотр" VARCHAR2(20) NOT NULL,
    "Фам_сотр" VARCHAR2(20) NOT NULL,
    "Отч_сотр" VARCHAR2(20),
    "Дат_р" DATE NOT NULL,
    "Тел_сотр" VARCHAR2(12) NOT NULL,
    "Пас_дан" VARCHAR2(20) NOT NULL,
    "Дата_выд_пас" DATE NOT NULL,
    "Мест_выд_пас" VARCHAR2(100) NOT NULL,
    "Код_отдел" NUMBER(3) NOT NULL,
    "Дат_н_р" DATE NOT NULL,
    constraint СОТРУДНИК_PK PRIMARY KEY ("Код_сотр"));


CREATE TABLE "Отдел" (
    "Код_отдел" NUMBER(3) NOT NULL,
    "Назв_отдел_фил" VARCHAR2(100) NOT NULL,
    "Оклад" NUMBER(8,3) NOT NULL,
    "Код_фил" NUMBER(3) NOT NULL,
    constraint ОТДЕЛ_PK PRIMARY KEY ("Код_отдел"));


CREATE TABLE "Филиал" (
    "Код_фил" NUMBER(3) NOT NULL,
    "Рег_фил" VARCHAR2(100) NOT NULL,
    "Гор_фил" VARCHAR2(100) NOT NULL,
    "Ул_фил" VARCHAR2(100) NOT NULL,
    constraint ФИЛИАЛ_PK PRIMARY KEY ("Код_фил"));


CREATE TABLE "Материал" (
    "Код_мат" NUMBER(3) NOT NULL,
    "Назв" VARCHAR2(100) NOT NULL,
    "Кол_во" NUMBER(10) NOT NULL,
    "Цен_за_ед" NUMBER(8,2) NOT NULL,
    "Код_зав" NUMBER(3) NOT NULL,
    constraint МАТЕРИАЛ_PK PRIMARY KEY ("Код_мат"));


CREATE TABLE "Щит" (
    "Код_щит" NUMBER(3) NOT NULL,
    "Назв" VARCHAR2(100) NOT NULL,
    "Занят" VARCHAR2(10) NOT NULL,
    "Выс" NUMBER(7,2) NOT NULL,
    "Шир" NUMBER(7,2) NOT NULL,
    "Код_зав" NUMBER(3) NOT NULL,
    "Код_фил" NUMBER(3) NOT NULL,
    "Код_дог" NUMBER(3) NOT NULL,
    constraint ЩИТ_PK PRIMARY KEY ("Код_щит"));


CREATE TABLE "Клиент" (
    "Код_кл" NUMBER(3) NOT NULL,
    "Имя_кл" VARCHAR2(20) NOT NULL,
    "Фам_кл" VARCHAR2(20) NOT NULL,
    "Отч_кл" VARCHAR2(20),
    "Дат_р" DATE NOT NULL,
    "Эле_почт" VARCHAR2(50),
    "Тел_кл" VARCHAR2(12) NOT NULL,
    "Пас_дан" VARCHAR2(50) NOT NULL,
    "Дат_выда_пас" DATE NOT NULL,
    "Мест_выд_пас" VARCHAR2(50) NOT NULL,
    "Мест_жит" VARCHAR2(100) NOT NULL,
    constraint КЛИЕНТ_PK PRIMARY KEY ("Код_кл"));



CREATE TABLE "Договор" (
    "Код_дог" NUMBER(3) NOT NULL,
    "Дата_зак" DATE NOT NULL,
    "Мес_сум_опл" NUMBER(8,2) NOT NULL,
    "Срок" DATE NOT NULL,
    "Шир_щит" NUMBER(7,2) NOT NULL,
    "Выс_щит" NUMBER(7,2) NOT NULL,
    "Код_фил" NUMBER(3) NOT NULL,
    "Код_кл" NUMBER(3) NOT NULL,
    "Цен_за_пер" NUMBER(8,2) NOT NULL,
    constraint ДОГОВОР_PK PRIMARY KEY ("Код_дог"));


CREATE TABLE "Оплата" (
    "Код_опл" NUMBER(3) NOT NULL,
    "Сум" NUMBER(8,2) NOT NULL,
    "Код_дог" NUMBER(3) NOT NULL,
    constraint ОПЛАТА_PK PRIMARY KEY ("Код_опл"));


CREATE TABLE "ЩитМатериал" (
    "Код" NUMBER(3) NOT NULL,
    "Код_щит" NUMBER(3) NOT NULL,
    "Код_мат" NUMBER(3) NOT NULL,
    "Кол_во" NUMBER(7,2) NOT NULL,
    constraint ЩИТМАТЕРИАЛ_PK PRIMARY KEY ("Код"));

ALTER TABLE "Сотрудник" ADD CONSTRAINT "Сотрудник_fk0" FOREIGN KEY ("Код_отдел") REFERENCES "Отдел"("Код_отдел");

ALTER TABLE "Отдел" ADD CONSTRAINT "Отдел_fk0" FOREIGN KEY ("Код_фил") REFERENCES "Филиал"("Код_фил");


ALTER TABLE "Материал" ADD CONSTRAINT "Материал_fk0" FOREIGN KEY ("Код_зав") REFERENCES "Филиал"("Код_фил");

ALTER TABLE "Щит" ADD CONSTRAINT "Щит_fk0" FOREIGN KEY ("Код_зав") REFERENCES "Филиал"("Код_фил");
ALTER TABLE "Щит" ADD CONSTRAINT "Щит_fk1" FOREIGN KEY ("Код_фил") REFERENCES "Филиал"("Код_фил");
ALTER TABLE "Щит" ADD CONSTRAINT "Щит_fk2" FOREIGN KEY ("Код_дог") REFERENCES "Договор"("Код_дог");


ALTER TABLE "Договор" ADD CONSTRAINT "Договор_fk0" FOREIGN KEY ("Код_фил") REFERENCES "Филиал"("Код_фил");
ALTER TABLE "Договор" ADD CONSTRAINT "Договор_fk1" FOREIGN KEY ("Код_кл") REFERENCES "Клиент"("Код_кл");

ALTER TABLE "Оплата" ADD CONSTRAINT "Оплата_fk0" FOREIGN KEY ("Код_дог") REFERENCES "Договор"("Код_дог");

ALTER TABLE "ЩитМатериал" ADD CONSTRAINT "ЩитМатериал_fk0" FOREIGN KEY ("Код_щит") REFERENCES "Щит"("Код_щит");
ALTER TABLE "ЩитМатериал" ADD CONSTRAINT "ЩитМатериал_fk1" FOREIGN KEY ("Код_мат") REFERENCES "Материал"("Код_мат");

SELECT * FROM "Сотрудник";
INSERT INTO "Сотрудник" ("Код_сотр","Имя_сотр","Фам_сотр","Отч_сотр","Дат_р","Тел_сотр","Пас_дан" ,"Дата_выд_пас" ,"Мест_выд_пас" ,"Код_отдел" ,"Дат_н_р" )
VALUES (1, 'Артемий', 'Лебедев', 'Татьянович', '13-фев-75','+79220940202', '7293 445512', '25-фев-10', 'Москва', 1, '2-Май-16');

INSERT INTO "Сотрудник" ("Код_сотр","Имя_сотр","Фам_сотр","Отч_сотр","Дат_р","Тел_сотр","Пас_дан" ,"Дата_выд_пас" ,"Мест_выд_пас" ,"Код_отдел" ,"Дат_н_р" )
VALUES (2, 'Никита', 'Сергеевич ', 'Хрущёв', '11-Май-01','+79220020202', '1221 141516', '25-фев-10', 'Москва', 2, '2-Май-16');

INSERT INTO "Сотрудник" ("Код_сотр","Имя_сотр","Фам_сотр","Отч_сотр","Дат_р","Тел_сотр","Пас_дан" ,"Дата_выд_пас" ,"Мест_выд_пас","Код_отдел" ,"Дат_н_р" )
VALUES (3, 'Максим', 'Анатольевич', 'Собчак', '12-Май-01','+79112221314', '7612 566123', '25-фев-11', 'Москва', 5,'5-Май-17');


SELECT * FROM "Отдел";
INSERT INTO "Отдел" ("Код_отдел", "Назв_отдел_фил", "Оклад", "Код_фил")
VALUES (1, 'Дизайн', '75000', 1);
INSERT INTO "Отдел" ("Код_отдел", "Назв_отдел_фил", "Оклад", "Код_фил")
VALUES (2, 'Производство', '55000', 5);
INSERT INTO "Отдел" ("Код_отдел", "Назв_отдел_фил", "Оклад", "Код_фил")
VALUES (5, 'Логистика', '55000', 5);
INSERT INTO "Отдел" ("Код_отдел", "Назв_отдел_фил", "Оклад", "Код_фил")
VALUES (4, 'Реклама', '75000', 1);

SELECT * FROM "Филиал";
INSERT INTO "Филиал" ("Код_фил", "Рег_фил", "Гор_фил", "Ул_фил")
VALUES (1, 'Москва', 'Москва', 'Льва Толстова');
INSERT INTO "Филиал" ("Код_фил", "Рег_фил", "Гор_фил", "Ул_фил")
VALUES (2, 'Санкт Петербург', 'Санкт Петербург', 'Пискарёвский просп, 2');
INSERT INTO "Филиал" ("Код_фил", "Рег_фил", "Гор_фил", "Ул_фил")
VALUES (3, 'Владимерская обл', 'Ковров', 'Лопатина,2');
INSERT INTO "Филиал" ("Код_фил", "Рег_фил", "Гор_фил", "Ул_фил")
VALUES (4, 'Нижний Новгород', 'Нижний Новгород', 'ул. Родионова, 23');
INSERT INTO "Филиал" ("Код_фил", "Рег_фил", "Гор_фил", "Ул_фил")
VALUES (5, 'Cвердловская об', 'Екатиренбург', 'ул. Хохрякова, 10');

SELECT * FROM "Материал";
INSERT INTO "Материал" ("Код_мат", "Назв", "Кол_во", "Цен_за_ед", "Код_зав")
VALUES (1, 'армированный винил', 100, 100.5, 2);
INSERT INTO "Материал" ("Код_мат", "Назв", "Кол_во", "Цен_за_ед", "Код_зав")
VALUES (2, ' виниловое полотно', 1000,150, 2);
INSERT INTO "Материал" ("Код_мат", "Назв", "Кол_во", "Цен_за_ед", "Код_зав")
VALUES (3, 'Пластичны полиуретановые', 25, 120, 150, 2);
INSERT INTO "Материал" ("Код_мат", "Назв", "Кол_во", "Цен_за_ед", "Код_зав")
VALUES (4, 'Светодиодные ленты', 100, 200, 2);
INSERT INTO "Материал" ("Код_мат", "Назв", "Кол_во", "Цен_за_ед", "Код_зав")
VALUES (5, 'алюминий', 1000, 1000, 2);
INSERT INTO "Материал" ("Код_мат", "Назв", "Кол_во", "Цен_за_ед", "Код_зав")
VALUES (6, 'Тентовая ткань', 1000, 125.25, 2);

SELECT * FROM "Щит";
INSERT INTO "Щит" ("Код_щит", "Назв", "Занят", "Выс", "Шир", "Код_зав", "Код_фил", "Код_дог")
VALUES (1,'Билборд', 'до 25.02.21', 3, 6, 2, 1, 1);
INSERT INTO "Щит" ("Код_щит", "Назв", "Занят", "Выс", "Шир", "Код_зав", "Код_фил", "Код_дог")
VALUES (2,'Суперборд', 'Свободен', 4, 14, 2, 1, 2);
INSERT INTO "Щит" ("Код_щит", "Назв", "Занят", "Выс", "Шир", "Код_зав", "Код_фил", "Код_дог")
VALUES (3,'Суперсайт','до 02.02.21', 5, 15, 2, 2, 3);
INSERT INTO "Щит" ("Код_щит", "Назв", "Занят", "Выс", "Шир", "Код_зав", "Код_фил", "Код_дог")
VALUES (4,'Пиллар','до 04.01.22', 2.7, 3.7, 2, 3, 4);
INSERT INTO "Щит" ("Код_щит", "Назв", "Занят", "Выс", "Шир", "Код_зав", "Код_фил", "Код_дог")
VALUES (5,'Ситиформат','до 22.10.20', 1.2, 1.8, 2, 4, 5);

SELECT * FROM "Клиент";
INSERT INTO "Клиент" ("Код_кл", "Имя_кл", "Фам_кл", "Отч_кл", "Дат_р", "Эле_почт", "Тел_кл", "Пас_дан", "Дат_выда_пас", "Мест_выд_пас", "Мест_жит")
VALUES (1,'Сергей','Аксаков','Дмитриеевич', '02-май-95', 'MoscowCity@city.ru', '+7499209032', '1210 729300', '12-май-16', 'Санкт Петербург', 'Москва');
INSERT INTO "Клиент" ("Код_кл", "Имя_кл", "Фам_кл", "Отч_кл", "Дат_р", "Эле_почт", "Тел_кл", "Пас_дан", "Дат_выда_пас", "Мест_выд_пас", "Мест_жит")
VALUES (2,'Александр','Бродский','Иванович', '22-май-90', 'rtspb@rt.ru', '+78126701022', '1010 101983', '12-май-16', 'Санкт Петербург', 'Санкт Петербург');
INSERT INTO "Клиент" ("Код_кл", "Имя_кл", "Фам_кл", "Отч_кл", "Дат_р", "Эле_почт", "Тел_кл", "Пас_дан", "Дат_выда_пас", "Мест_выд_пас", "Мест_жит")
VALUES (3,'Алексей','Любимов','Александрович', '25-май-94', 'merlow@gmail.com', '+74996711422', '2020 202923', '25-май-16', 'Москва', 'Москва');

SELECT * FROM "Договор";
INSERT INTO "Договор" ("Код_дог", "Дата_зак", "Мес_сум_опл", "Срок", "Шир_щит", "Выс_щит", "Код_фил", "Код_кл", "Цен_за_пер")
VALUES (1,'11-Апр-21','3500','11-Май-22', 4, 14, 1, 1, 42000);
INSERT INTO "Договор" ("Код_дог", "Дата_зак", "Мес_сум_опл", "Срок", "Шир_щит", "Выс_щит", "Код_фил", "Код_кл", "Цен_за_пер")
VALUES (2,'11-Фев-21','4200','11-Май-21', 5, 15, 1, 2, 12600);
INSERT INTO "Договор" ("Код_дог", "Дата_зак", "Мес_сум_опл", "Срок", "Шир_щит", "Выс_щит", "Код_фил", "Код_кл", "Цен_за_пер")
VALUES (3,'20-Фев-21','1200','11-Март-21', 2.7, 3.7, 1, 3, 1335);
INSERT INTO "Договор" ("Код_дог", "Дата_зак", "Мес_сум_опл", "Срок", "Шир_щит", "Выс_щит", "Код_фил", "Код_кл", "Цен_за_пер")
VALUES (4,'20-Фев-21','1000','11-Март-21', 1.2, 1.8, 2, 3, 1035);

SELECT * FROM "ЩитМатериал";
INSERT INTO "ЩитМатериал" ("Код", "Код_щит", "Код_мат", "Кол_во")
VALUES (1,1,1,8);
INSERT INTO "ЩитМатериал" ("Код", "Код_щит", "Код_мат", "Кол_во")
VALUES (2,1,2,2);
INSERT INTO "ЩитМатериал"("Код", "Код_щит", "Код_мат", "Кол_во")
VALUES (3,2,2,5);
INSERT INTO "ЩитМатериал"("Код", "Код_щит", "Код_мат", "Кол_во")
VALUES (4,2,3,1);