DROP TABLE IF EXISTS bd6_departments;
DROP TABLE IF EXISTS bd6_employees;


CREATE TABLE bd6_departments(
  id integer PRIMARY KEY,
  name varchar(64),
  postal_code varchar(6),
  street varchar(64),
  building varchar(16),
  city varchar(32)
);

INSERT INTO bd6_departments 
VALUES(10, 'Administration', '109658', 'Leningradskoe shosse', '1', 'Moscow'),
(20, 'Marketing', '107701', 'Lenina', '22a', 'Volgograd'),
(30, 'Purchasing', '109901', 'Mikluho-Maklaya', '8', 'Bryansk'),
(40, 'Human Resources', '10967', '5-ya parkovaya', '16', 'Moscow'),
(50, 'Shipping', '109659', '38 Bakinskih komissarov', '77', 'Moscow'),
(60, 'IT', '109902', 'Pervomajskaya', '33', 'Kirov');

CREATE TABLE bd6_employees(
  id integer PRIMARY KEY,
  last_name varchar(64) NOT NULL,
  first_name varchar(64) NOT NULL,
  phone_number varchar(18),
  email varchar(32),
  department_id integer NOT NULL,
  manager_id integer,
  salary_in_euro numeric(8, 2)  DEFAULT 0 NOT NULL,
  UNIQUE(last_name, first_name, department_id)
);


INSERT INTO bd6_employees VALUES(1, 'Radygin', 'Victor', '8-(495)-567-7788', 'vr@e.mail.mephi.ru', 10, NULL, 8000),
(2, 'Kuprijanov', 'Dmitrij', '8-(495)-567-7794', 'kd@e.mail.mephi.ru', 60, 1, 6534.33),
(3, 'Ivanov-Skladovskij', 'Ivan', '8-(495)-567-7799', 'ii1@mail.mephi.ru', 20, 1, 4404.14),
(4, 'Petrov', 'Petr', '8-(495)-567-7794', 'petrovpetr@m.gmail.ru', 60, 2, 3456.43),
(5, 'kozlov', 'Konstantin', '8-(495)-567-7794', 'kkozlov@mephi.ru', 60, 2, 2300),
(6, 'Abramov', 'Abram', '8-(495)-567-7794', 'abramova@k75.mephi.ru', 60, 2, 2200.11),
(7, 'IvanovпїЅ-SkladovskпїЅya-Petrova', 'Ivanka', '8-(495)-567-7794', 'ii2@mail.mephi.ru', 60, 4, 3756.33),
(8, 'Petrov', 'Ivan', '8-(495)-567-7799', 'petrovivan@m.gmail.ru', 20, 3, 4850),
(9, 'kozlov', 'Ivan', '8-(495)-567-7799', 'ikozlov@mephi.ru', 20, 3, 3460),
(10, 'Abramov', 'Moisej', '8-(495)-567-7794', 'abramovm@k75.mephi.ru', 60, 4, 2345),
(11, 'Petrov', 'Alex', '8-(495)-567-7799', 'petroval@m.gmail.ru', 20, 3, 2465),
(12, 'kozlov', 'Maxim', '8-(495)-567-7799', 'mkozlov@mephi.ru', 20, 3, 2788),
(13, 'Abramov', 'Isyaslav', '8-(495)-567-7738', 'abramovi@k75.mephi.ru', 30, 1, 6500);