INSERT INTO SRI_Departaments (Name,Address,Foundation_date) 
 select 'НИИЧАВО','ул. Братьев Стругацких д. 2',to_date('01.01.2001','DD.MM.YYYY') from dual 
 union all
 select 'Линейного счастья','ул. Мира д. 1',to_date('01.01.1812','DD.MM.YYYY') from dual 
 union all
 select 'Смысла жизни','ул. Мира д. 1',to_date('07.04.1612','DD.MM.YYYY') from dual
 union all 
 select 'Абсолютного знания','ул. Войны д. 1945',to_date('22.06.1945','DD.MM.YYYY') from dual
 union all 
 select 'ИЗНУКАРНОЖ','ул. Музейная д. 0',to_date('28.03.1953','DD.MM.YYYY') from dual
 union all 
 select 'Вивариум','ул. Странная д. 17',to_date('01.01.2001','DD.MM.YYYY') from dual
 union all 
 select 'Воинствующего атеизма','ул. НеЖанныДарк д. 666',to_date('6.06.666','DD.MM.YYYY') from dual;

INSERT INTO Races (Race) 
 select 'Человек' from dual 
 union all
 select 'Джинн' from dual 
 union all
 select 'Домовой' from dual 
 union all
 select 'Чупакабра' from dual 
 union all
 select 'Дубль' from dual 
 union all
 select 'Химера' from dual
 union all
 select 'Бог' from dual
 union all
 select 'Вурдалак' from dual
 union all
 select 'Гигантохейер' from dual
 union all
 select 'Конь волшебный' from dual
 union all
 select 'Хлебобулочное изделие' from dual
 union all
 select 'Мышь' from dual;
 
INSERT INTO Staff (Surname,Name,Middle_name,Birth_date,Placement_date,Departament_id,Race_id,Sex) 
 select 'Киврин','Федор','Симеонович',to_date('01.12.1500','DD.MM.YYYY'),to_date('01.12.1501','DD.MM.YYYY'),
 	(select id from SRI_Departaments where upper(Name)=upper('Линейного счастья')),
 	(select id from Races where upper(Race)=upper('Человек')),'М' from dual 
 union all
 select 'Хунта','Кристобаль','Хозеивич',to_date('11.11.1111','DD.MM.YYYY'),to_date('12.12.1212','DD.MM.YYYY'),
 	(select id from SRI_Departaments where upper(Name)=upper('Воинствующего атеизма')) ,
 	(select id from Races where upper(Race)=upper('Человек')),'М' from dual
 union all
 select 'Ойра-Ойра','Роман','Петрович',to_date('18.08.1920','DD.MM.YYYY'),to_date('07.07.1949','DD.MM.YYYY'),
 	(select id from SRI_Departaments where upper(Name)=upper('Смысла жизни')) ,
 	(select id from Races where upper(Race)=upper('Человек')),'М' from dual
 union all
 select 'Один','Саваоф','Баалович',to_date('21.11.1000','DD.MM.YYYY'),to_date('01.01.1325','DD.MM.YYYY'),
 	(select id from SRI_Departaments where upper(Name)=upper('Абсолютного знания')) ,
 	(select id from Races where upper(Race)=upper('Бог')),'М' from dual
 union all
 select 'Эйты','Идисюда','Быстрович',to_date('01.01.0001','DD.MM.YYYY'),to_date('31.12.1311','DD.MM.YYYY'),
 	(select id from SRI_Departaments where upper(Name)=upper('Воинствующего атеизма')) ,
 	(select id from Races where upper(Race)=upper('Джинн')),'М' from dual
 union all
 select NULL,'Альфред',NULL,to_date('02.02.1798','DD.MM.YYYY'),to_date('02.06.1917','DD.MM.YYYY'),
 	(select id from SRI_Departaments where upper(Name)=upper('Вивариум')) ,
 	(select id from Races where upper(Race)=upper('Вурдалак')),'М' from dual
 union all
 select 'Сталин','Гермиона','Адольфовна',to_date('22.06.1941','DD.MM.YYYY'),to_date('09.05.1945','DD.MM.YYYY'),
 	(select id from SRI_Departaments where upper(Name)=upper('НИИЧАВО')) ,
 	(select id from Races where upper(Race)=upper('Человек')),'Ж' from dual
 union all
 select 'Гитлер','Русь','Иосифовна',to_date('22.06.1941','DD.MM.YYYY'),to_date('09.05.1945','DD.MM.YYYY'),
 	(select id from SRI_Departaments where upper(Name)=upper('Воинствующего атеизма')) ,
 	(select id from Races where upper(Race)=upper('Человек')),'Ж' from dual;

INSERT INTO Positions (Position) 
 select 'Заведующий отделом' from dual 
 union all
 select 'Стажер' from dual 
 union all
 select 'Научный сотрудник' from dual 
 union all
 select 'Лаборант' from dual 
 union all
 select 'Технический работник' from dual 
 union all
 select 'Сторож' from dual
 union all
 select 'Дворник' from dual
 union all
 select 'Консультант' from dual
 union all
 select 'Младший научный сотрудник' from dual;

 INSERT INTO Degrees (Degree) 
 select 'Бакалавр' from dual 
 union all
 select 'Магистр' from dual 
 union all
 select 'Кандидат наук' from dual 
 union all
 select 'Доктор наук' from dual;

 INSERT INTO Scientists (Staff_id,Position_id,Degree_id) 
 select
 (select id from Staff where upper(Surname)=upper('Киврин') and upper(Name)=upper('Федор') and upper(Middle_name)=upper('Симеонович')),
 (select id from Positions where upper(Position)=upper('Заведующий отделом')),
 (select id from Degrees where upper(Degree)=upper('Доктор наук')) from dual
 union all
 select
 (select id from Staff where upper(Surname)=upper('Хунта') and upper(Name)=upper('Кристобаль') and upper(Middle_name)=upper('Хозеивич')),
 (select id from Positions where upper(Position)=upper('Научный сотрудник')),
 (select id from Degrees where upper(Degree)=upper('Кандидат наук')) from dual
 union all
 select
 (select id from Staff where upper(Surname)=upper('Ойра-Ойра') and upper(Name)=upper('Роман') and upper(Middle_name)=upper('Петрович')),
 (select id from Positions where upper(Position)=upper('Младший научный сотрудник')),
 (select id from Degrees where upper(Degree)=upper('Бакалавр')) from dual
  union all
  select
 (select id from Staff where upper(Surname)=upper('Один') and upper(Name)=upper('Саваоф') and upper(Middle_name)=upper('Баалович')),
 (select id from Positions where upper(Position)=upper('Консультант')),
 (select id from Degrees where upper(Degree)=upper('Магистр')) from dual;


 INSERT INTO Service_Staff (Staff_id,Position_id) 
 select
 (select id from Staff where upper(Surname)=upper('Эйты') and upper(Name)=upper('Идисюда') and upper(Middle_name)=upper('Быстрович')),
 (select id from Positions where upper(Position)=upper('Дворник')) from dual
 union all
 select
 (select id from Staff where upper(Name)=upper('Альфред') and Middle_name is null and Surname is null),
 (select id from Positions where upper(Position)=upper('Сторож')) from dual
 union all
 select
 (select id from Staff where upper(Surname)=upper('Сталин') and upper(Name)=upper('Гермиона') and upper(Middle_name)=upper('Адольфовна')),
 (select id from Positions where upper(Position)=upper('Технический работник')) from dual
 union all
 select
 (select id from Staff where upper(Surname)=upper('Гитлер') and upper(Name)=upper('Русь') and upper(Middle_name)=upper('Иосифовна')),
 (select id from Positions where upper(Position)=upper('Технический работник')) from dual;

 INSERT INTO Doctypes (Doctype) 
 select 'Заявление о приеме на работу' from dual 
 union all
 select 'Заявление на отпуск' from dual 
 union all
 select 'Жалоба' from dual  
 union all
 select 'Отчет' from dual 
 union all
 select 'Служебная записка' from dual 
 union all
 select 'Объяснительная' from dual;


INSERT INTO  Documents_Flow (Doctype_id,in_Date,By_pers_id,To_pers_id) 
 select
 (select id from Doctypes where upper(Doctype)=upper('Отчет')), 
 to_date('07.08.2009','DD.MM.YYYY'),
 (select id from Staff where upper(Surname)=upper('Эйты') and upper(Name)=upper('Идисюда') and upper(Middle_name)=upper('Быстрович')),
 (select id from Staff where upper(Surname)=upper('Киврин') and upper(Name)=upper('Федор') and upper(Middle_name)=upper('Симеонович')) from dual
 union all
 select
 (select id from Doctypes where upper(Doctype)=upper('Отчет')), 
 to_date('09.08.2007','DD.MM.YYYY'),
 (select id from Staff where Surname is null and upper(Name)=upper('Альфред') and Middle_name is null),
 (select id from Staff where upper(Surname)=upper('Один') and upper(Name)=upper('Саваоф') and upper(Middle_name)=upper('Баалович')) from dual
 union all
 select
 (select id from Doctypes where upper(Doctype)=upper('Заявление на отпуск')), 
 to_date('02.03.2004','DD.MM.YYYY'),
 (select id from Staff where upper(Surname)=upper('Ойра-Ойра') and upper(Name)=upper('Роман') and upper(Middle_name)=upper('Петрович')),
 (select id from Staff where upper(Surname)=upper('Киврин') and upper(Name)=upper('Федор') and upper(Middle_name)=upper('Симеонович')) from dual
 union all
 select
 (select id from Doctypes where upper(Doctype)=upper('Объяснительная')), 
 to_date('02.03.2004','DD.MM.YYYY'),
 (select id from Staff where upper(Surname)=upper('Гитлер') and upper(Name)=upper('Русь') and upper(Middle_name)=upper('Иосифовна')),
 (select id from Staff where upper(Surname)=upper('Хунта') and upper(Name)=upper('Кристобаль') and upper(Middle_name)=upper('Хозеивич')) from dual;

 INSERT INTO Research_Topics (Topic) 
 select 'Биологическая магия' from dual 
 union all
 select 'Химическая магия' from dual 
 union all
 select 'Счастье' from dual 
 union all
 select 'Несчастье' from dual 
 union all
 select 'Историческая магия' from dual 
 union all
 select 'Мифология' from dual;

 INSERT INTO Research_Roles (Research_role) 
 select 'Консультант' from dual 
 union all
 select 'Автор исследования' from dual 
 union all
 select 'Рецензиент' from dual 
 union all
 select 'Научный руководитель' from dual;

 INSERT INTO Research (Departament_id,Title,Topic_id,Start_date) 
 select
 (select id from SRI_Departaments where upper(Name)=upper('Линейного счастья')),
 'Счастье идиотов и его ценность',
 (select id from Research_Topics where upper(Topic)=upper('Счастье')),
 to_date('07.11.1917','DD.MM.YYYY') from dual
 union all
 select
 (select id from SRI_Departaments where upper(Name)=upper('Смысла жизни')),
 'Несчастье как повод задуматься',
 (select id from Research_Topics where upper(Topic)=upper('Несчастье')),
 to_date('24.08.0079','DD.MM.YYYY') from dual
 union all
 select
 (select id from SRI_Departaments where upper(Name)=upper('Воинствующего атеизма')),
 'Роль огня в лечении шизофрении',
 (select id from Research_Topics where upper(Topic)=upper('Биологическая магия')),
 to_date('30.05.1431','DD.MM.YYYY') from dual;

INSERT INTO Researchers (Research_id,Scien_id,Research_role_id) 
select
(select id from Research where upper(Title)=upper('Роль огня в лечении шизофрении')),
(select id from Staff where upper(Surname)=upper('Хунта') and upper(Name)=upper('Кристобаль') and upper(Middle_name)=upper('Хозеивич')),
(select id from Research_Roles where upper(Research_role)=upper('Автор исследования')) from dual
union all
select
(select id from Research where upper(Title)=upper('Роль огня в лечении шизофрении')),
(select id from Staff where upper(Surname)=upper('Ойра-Ойра') and upper(Name)=upper('Роман') and upper(Middle_name)=upper('Петрович')),
(select id from Research_Roles where upper(Research_role)=upper('Консультант')) from dual
union all
select
(select id from Research where upper(Title)=upper('Несчастье как повод задуматься')),
(select id from Staff where upper(Surname)=upper('Один') and upper(Name)=upper('Саваоф') and upper(Middle_name)=upper('Баалович')),
(select id from Research_Roles where upper(Research_role)=upper('Научный руководитель')) from dual
union all
select
(select id from Research where upper(Title)=upper('Счастье идиотов и его ценность')),
(select id from Staff where upper(Surname)=upper('Один') and upper(Name)=upper('Саваоф') and upper(Middle_name)=upper('Баалович')),
(select id from Research_Roles where upper(Research_role)=upper('Рецензиент')) from dual;

 INSERT INTO Food (Food) 
 select 'Греча' from dual 
 union all
 select 'Печень трески' from dual 
 union all
 select 'Треска' from dual 
 union all
 select 'Мясо на кости' from dual 
 union all
 select 'Мозг млекопитающих' from dual 
 union all
 select 'Яйца куриные' from dual 
 union all
 select 'Каучук' from dual 
 union all
 select 'Овес' from dual;


 INSERT INTO Vivarium (Race_id,Name) 
 select
 (select id from Races where upper(Race)=upper('Хлебобулочное изделие')), 'Колобок' from dual
 union all
 select
 (select id from Races where upper(Race)=upper('Конь волшебный')), 'Конек Горбунок' from dual
 union all
 select
 (select id from Races where upper(Race)=upper('Вурдалак')), 'Дракула' from dual
 union all
 select
 (select id from Races where upper(Race)=upper('Химера')), 'Тёща' from dual
 union all
 select
 (select id from Races where upper(Race)=upper('Мышь')), 'Первый' from dual
 union all
 select
 (select id from Races where upper(Race)=upper('Мышь')), 'Второй' from dual
 union all
 select
 (select id from Races where upper(Race)=upper('Мышь')), 'Третий' from dual;



 INSERT INTO Ration (Food_id,Creature_id) 
 select
 (select id from Food where upper(Food)=upper('Мясо на кости')), 
 (select id from Vivarium where upper(Name)=upper('Дракула')) from dual
 union all
 select
 (select id from Food where upper(Food)=upper('Мозг млекопитающих')), 
 (select id from Vivarium where upper(Name)=upper('Тёща')) from dual
 union all
 select
 (select id from Food where upper(Food)=upper('Овес')), 
 (select id from Vivarium where upper(Name)=upper('Конек Горбунок')) from dual
 union all
 select
 (select id from Food where upper(Food)=upper('Овес')), 
 (select id from Vivarium where upper(Name)=upper('Первый')) from dual
 union all
 select
 (select id from Food where upper(Food)=upper('Овес')), 
 (select id from Vivarium where upper(Name)=upper('Второй')) from dual
 union all
 select
 (select id from Food where upper(Food)=upper('Овес')), 
 (select id from Vivarium where upper(Name)=upper('Третий')) from dual;


 INSERT INTO Library (Title,Author,Subject,Box_number) 
 select 'Хоббит туда и обратно','Дж.Р.Р. Толкиен', 'Фэнтэзи',3 from dual 
 union all
 select 'Гнойные нарывы: методы лечения','Дж.Дж. Абрамс', 'Медицина',7 from dual 
 union all
 select 'Роль Владиславов в мировой истории','Кудряшов А.А.', 'История',1 from dual 
 union all
 select 'Мои 146%','Чуров В.Е.', 'Математика',146 from dual; 


 INSERT INTO Books_Delivery_List (ISBN,Staff_id,Get_date,Return_date) 
 select
 (select id from Library where upper(Title)=upper('Хоббит туда и обратно')),
 (select id from Staff where upper(Surname)=upper('Один') and upper(Name)=upper('Саваоф') and upper(Middle_name)=upper('Баалович')),
 to_date('01.01.1325','DD.MM.YYYY'),
 to_date('07.12.1598','DD.MM.YYYY') from dual
 union all
 select
 (select id from Library where upper(Title)=upper('Хоббит туда и обратно')),
 (select id from Staff where upper(Surname)=upper('Ойра-Ойра') and upper(Name)=upper('Роман') and upper(Middle_name)=upper('Петрович')),
 to_date('07.12.1598','DD.MM.YYYY'),
 to_date('08.12.1598','DD.MM.YYYY') from dual
 union all
 select
 (select id from Library where upper(Title)=upper('Гнойные нарывы: методы лечения')),
 (select id from Staff where upper(Name)=upper('Альфред') and Middle_name is null and Surname is null),
 to_date('07.12.1943','DD.MM.YYYY'),
 to_date('08.12.1965','DD.MM.YYYY') from dual
 union all
 select
 (select id from Library where upper(Title)=upper('Роль Владиславов в мировой истории')),
 (select id from Staff where upper(Surname)=upper('Гитлер') and upper(Name)=upper('Русь') and upper(Middle_name)=upper('Иосифовна')),
 to_date('19.12.1985','DD.MM.YYYY'),
 to_date('01.01.1986','DD.MM.YYYY') from dual;


INSERT INTO Dyte_Time (SS_id,Departament_id,Dyte_date) 
select 
(select Staff.id as id from Staff join Service_Staff on Staff.id=Service_Staff.Staff_id where upper(Surname)=upper('Эйты') and upper(Name)=upper('Идисюда') and upper(Middle_name)=upper('Быстрович')),
(select id from SRI_Departaments where upper(Name)=upper('Линейного счастья')),
to_date('31.12.1993','DD.MM.YYYY') from dual
union all
select
(select Staff.id from Staff join Service_Staff on Staff.id=Service_Staff.Staff_id where upper(Surname)=upper('Гитлер') and upper(Name)=upper('Русь') and upper(Middle_name)=upper('Иосифовна')),
(select id from SRI_Departaments where upper(Name)=upper('Абсолютного знания')),
to_date('01.01.1994','DD.MM.YYYY') from dual
union all
select
(select Staff.id from Staff join Service_Staff on Staff.id=Service_Staff.Staff_id where upper(Surname)=upper('Сталин') and upper(Name)=upper('Гермиона') and upper(Middle_name)=upper('Адольфовна')),
(select id from SRI_Departaments where upper(Name)=upper('ИЗНУКАРНОЖ')),
to_date('01.01.1994','DD.MM.YYYY') from dual;

 INSERT INTO Artifact_status (Status) 
 select 'В наличии' from dual 
 union all
 select 'Нет в наличии' from dual 
 union all
 select 'Зарезервировано' from dual 
 union all
 select 'Утерян' from dual;

 INSERT INTO Artifacts_Storage (Name,Description,Entrance_date,Creation_date,Status_id)
 select
 'Меч кладенец','Лучшее произведение отечественной кузнечной промышленности',
 to_date('25.04.1329','DD.MM.YYYY'),
 to_date('31.01.1000','DD.MM.YYYY'),
 (select id from Artifact_status where upper(Status)=upper('В наличии')) from dual
 union all
  select
 'Кольцо всевластия','СМ. Властелин колец',
 to_date('04.03.1001','DD.MM.YYYY'),
 to_date('31.01.1820','DD.MM.YYYY'),
 (select id from Artifact_status where upper(Status)=upper('Зарезервировано')) from dual
 union all
  select
 'Ковер самолет','Подарок Самаркандских коллег, летает, может висеть на стене',
 to_date('07.03.0301','DD.MM.YYYY'),
 to_date('08.03.1969','DD.MM.YYYY'),
 (select id from Artifact_status where upper(Status)=upper('Нет в наличии')) from dual
  union all
  select
 'Снитч','Бесполезный летающий шарик',
 to_date('09.11.1949','DD.MM.YYYY'),
 to_date('09.09.2006','DD.MM.YYYY'),
 (select id from Artifact_status where upper(Status)=upper('Утерян')) from dual;



 INSERT INTO Artifacts_Delivery_List (Artifact_id,Scien_id,Get_date,Return_date) 
 select
 (select id from Artifacts_Storage where upper(Name)=upper('Меч кладенец')),
 (select Scientists.id from Scientists join Staff on Scientists.Staff_id =Staff.id  where upper(Surname)=upper('Один') and upper(Name)=upper('Саваоф') and upper(Middle_name)=upper('Баалович')),
 to_date('01.01.1025','DD.MM.YYYY'),
 to_date('07.12.1599','DD.MM.YYYY') from dual
 union all
 select
 (select id from Artifacts_Storage where upper(Name)=upper('Кольцо всевластия')),
 (select Scientists.id from Scientists  join Staff on Scientists.Staff_id =Staff.id where upper(Surname)=upper('Ойра-Ойра') and upper(Name)=upper('Роман') and upper(Middle_name)=upper('Петрович')),
 to_date('07.12.1668','DD.MM.YYYY'),
 to_date('08.12.2008','DD.MM.YYYY') from dual
 union all
 select
 (select id from Artifacts_Storage where upper(Name)=upper('Ковер самолет')),
 (select Scientists.id from Scientists  join Staff on Scientists.Staff_id =Staff.id where upper(Surname)=upper('Ойра-Ойра') and upper(Name)=upper('Роман') and upper(Middle_name)=upper('Петрович')),
 to_date('07.12.1118','DD.MM.YYYY'),
 to_date('08.12.2023','DD.MM.YYYY') from dual;

 INSERT INTO Hostel_Contracts (Staff_id,Room_Number,In_date,Out_date) 
 select
 (select id from Staff where upper(Surname)=upper('Один') and upper(Name)=upper('Саваоф') and upper(Middle_name)=upper('Баалович')),
 23,
 to_date('01.01.1025','DD.MM.YYYY'),
 to_date('07.12.1599','DD.MM.YYYY') from dual
 union all
 select
 (select id from Staff where upper(Surname)=upper('Ойра-Ойра') and upper(Name)=upper('Роман') and upper(Middle_name)=upper('Петрович')),
 29,
 to_date('07.12.1668','DD.MM.YYYY'),
 to_date('08.12.2008','DD.MM.YYYY') from dual
 union all
 select
 (select id from Staff where upper(Surname)=upper('Гитлер') and upper(Name)=upper('Русь') and upper(Middle_name)=upper('Иосифовна')),
 10,
 to_date('07.12.1118','DD.MM.YYYY'),
 to_date('08.12.2023','DD.MM.YYYY') from dual;