SQLize Online / PHPize Online  /  SQLtest Online

drop TABLE Access_to_the_storehouse; drop TABLE Pigs; drop TABLE Hangar_sector; drop TABLE Hangar; drop TABLE Equipment; drop TABLE Pigs; drop TABLE Storehouse; drop TABLE Farm_worker; drop SEQUENCE seq_farm_worker_id; drop SEQUENCE seq_storehouse_id; drop SEQUENCE seq_equipment_id; drop SEQUENCE seq_hangar_id; drop SEQUENCE seq_hangar_sector_id; drop SEQUENCE seq_pigs_id; drop SEQUENCE seq_Access_to_the_storehouse_id; CREATE TABLE Farm_worker ( Pass_id NUMBER PRIMARY KEY, Full_name VARCHAR2(100), Date_of_birth DATE, Phone_number VARCHAR2(15), Job1 VARCHAR2(50) ); CREATE TABLE Storehouse ( Storehouse_id NUMBER PRIMARY KEY, Pig_feed VARCHAR2(50), Capacity_Store NUMBER, Building_area NUMBER, Number_of_floors NUMBER ); CREATE TABLE Equipment ( ID_Equipment NUMBER PRIMARY KEY, Storehouse NUMBER, Destination VARCHAR2(100), Price NUMBER, Useful_life NUMBER, CONSTRAINT Equipment_Storehouse_fk FOREIGN KEY (Storehouse) REFERENCES Storehouse(Storehouse_id) ); CREATE TABLE Hangar ( Building_id NUMBER PRIMARY KEY, Manager1 NUMBER, Feed_from_the_storehouse VARCHAR2(50), Building_area NUMBER, Wall_material VARCHAR2(50), CONSTRAINT Hangar_Farm_worker_fk FOREIGN KEY (Manager1) REFERENCES Farm_worker(Pass_id) ); CREATE TABLE Hangar_sector ( Sector_id NUMBER PRIMARY KEY, Type1 VARCHAR2(50), Equipment NUMBER, Responsible_worker NUMBER, Hangar NUMBER, CONSTRAINT Hangar_sector_Equipment_fk FOREIGN KEY (Equipment) REFERENCES Equipment(ID_Equipment), CONSTRAINT Hangar_sector_Farm_worker_fk FOREIGN KEY (Responsible_worker) REFERENCES Farm_worker(Pass_id), CONSTRAINT Hangar_sector_Hangar_fk FOREIGN KEY (Hangar) REFERENCES Hangar(Building_id) ); CREATE TABLE Pigs ( ID_Pigs NUMBER PRIMARY KEY, Breed VARCHAR2(50), Date_of_birth DATE, Weigh CHAR(10), Sector NUMBER, CONSTRAINT Pigs_Hangar_sector_fk FOREIGN KEY (Sector) REFERENCES Hangar_sector(Sector_id) ); CREATE TABLE Access_to_the_storehouse ( Pass_id NUMBER, Storehouse_id NUMBER, CONSTRAINT Access_to_the_storehouse_pk PRIMARY KEY (Pass_id, Storehouse_id), CONSTRAINT Access_to_the_storehouse_Farm_worker_fk FOREIGN KEY (Pass_id) REFERENCES Farm_worker(Pass_id), CONSTRAINT Access_to_the_storehouse_Storehouse_fk FOREIGN KEY (Storehouse_id) REFERENCES Storehouse(Storehouse_id) ); CREATE SEQUENCE seq_farm_worker_id START WITH 1 INCREMENT BY 1; CREATE SEQUENCE seq_storehouse_id START WITH 1 INCREMENT BY 1; CREATE SEQUENCE seq_equipment_id START WITH 1 INCREMENT BY 1; CREATE SEQUENCE seq_hangar_id START WITH 1 INCREMENT BY 1; CREATE SEQUENCE seq_hangar_sector_id START WITH 1 INCREMENT BY 1; CREATE SEQUENCE seq_pigs_id START WITH 1 INCREMENT BY 1; CREATE SEQUENCE seq_Access_to_the_storehouse_id START WITH 1 INCREMENT BY 1; INSERT INTO Storehouse (Storehouse_id, Pig_feed, Capacity_Store, Building_area, Number_of_floors) VALUES (seq_storehouse_id.NEXTVAL, 'Сено', 1000, 200, 1); INSERT INTO Storehouse (Storehouse_id, Pig_feed, Capacity_Store, Building_area, Number_of_floors) VALUES (seq_storehouse_id.NEXTVAL, 'Желуди', 700, 150, 2); INSERT INTO Storehouse (Storehouse_id, Pig_feed, Capacity_Store, Building_area, Number_of_floors) VALUES (seq_storehouse_id.NEXTVAL, 'Пшеница', 1200, 250, 3); INSERT INTO Storehouse (Storehouse_id, Pig_feed, Capacity_Store, Building_area, Number_of_floors) VALUES (seq_storehouse_id.NEXTVAL, 'Ячмень', 900, 180, 2); INSERT INTO Storehouse (Storehouse_id, Pig_feed, Capacity_Store, Building_area, Number_of_floors) VALUES (seq_storehouse_id.NEXTVAL, 'Сухари', 800, 170, 1); INSERT INTO Equipment (ID_Equipment, Storehouse, Destination, Price, Useful_life) VALUES (seq_equipment_id.NEXTVAL, 1, 'Система кормления', 5000, 10); INSERT INTO Equipment (ID_Equipment, Storehouse, Destination, Price, Useful_life) VALUES (seq_equipment_id.NEXTVAL, 2, 'Система автоматической подачи воды', 3000, 8); INSERT INTO Equipment (ID_Equipment, Storehouse, Destination, Price, Useful_life) VALUES (seq_equipment_id.NEXTVAL, 3, 'Система охлаждения', 7000, 15); INSERT INTO Equipment (ID_Equipment, Storehouse, Destination, Price, Useful_life) VALUES (seq_equipment_id.NEXTVAL, 4, 'Система отопления', 1500, 5); INSERT INTO Equipment (ID_Equipment, Storehouse, Destination, Price, Useful_life) VALUES (seq_equipment_id.NEXTVAL, 5, 'Вентиляция', 4000, 12); INSERT INTO Farm_worker (Pass_id, Full_name, Date_of_birth, Phone_number, Job1) VALUES (seq_farm_worker_id.NEXTVAL, 'Дария Александрова', TO_DATE('1999-09-09', 'YYYY-MM-DD'), '88005', 'Фермер'); INSERT INTO Farm_worker (Pass_id, Full_name, Date_of_birth, Phone_number, Job1) VALUES (seq_farm_worker_id.NEXTVAL, 'Ксения Дорофеева', TO_DATE('2000-01-01', 'YYYY-MM-DD'), '88006', 'Менеджер'); INSERT INTO Farm_worker (Pass_id, Full_name, Date_of_birth, Phone_number, Job1) VALUES (seq_farm_worker_id.NEXTVAL, 'Валерия Шибанова', TO_DATE('2004-03-03', 'YYYY-MM-DD'), '88007', 'Технический работник'); INSERT INTO Farm_worker (Pass_id, Full_name, Date_of_birth, Phone_number, Job1) VALUES (seq_farm_worker_id.NEXTVAL, 'Кристина Реброва', TO_DATE('1990-04-04', 'YYYY-MM-DD'), '88008', 'Ветеринар'); INSERT INTO Farm_worker (Pass_id, Full_name, Date_of_birth, Phone_number, Job1) VALUES (seq_farm_worker_id.NEXTVAL, 'Мария Сидорова', TO_DATE('1998-05-05', 'YYYY-MM-DD'), '88009', 'Работник склада'); INSERT INTO Hangar (Building_id, Manager1, Feed_from_the_storehouse, Building_area, Wall_material) VALUES (seq_hangar_id.NEXTVAL, 2, 'Ручное', 1000, 'Бетонный'); INSERT INTO Hangar (Building_id, Manager1, Feed_from_the_storehouse, Building_area, Wall_material) VALUES (seq_hangar_id.NEXTVAL, 2, 'Автоматическое', 1200, 'Железный'); INSERT INTO Hangar (Building_id, Manager1, Feed_from_the_storehouse, Building_area, Wall_material) VALUES (seq_hangar_id.NEXTVAL, 1, 'Ручное', 800, 'Оловянный'); INSERT INTO Hangar (Building_id, Manager1, Feed_from_the_storehouse, Building_area, Wall_material) VALUES (seq_hangar_id.NEXTVAL, 3, 'Автоматическое', 1500, 'Железный'); INSERT INTO Hangar (Building_id, Manager1, Feed_from_the_storehouse, Building_area, Wall_material) VALUES (seq_hangar_id.NEXTVAL, 4, 'Ручное', 1100, 'Кирпичный'); INSERT INTO Hangar_sector (Sector_id, Type1, Equipment, Responsible_worker, Hangar) VALUES (seq_hangar_sector_id.NEXTVAL, 'Прикорм', 1, 1, 1); INSERT INTO Hangar_sector (Sector_id, Type1, Equipment, Responsible_worker, Hangar) VALUES (seq_hangar_sector_id.NEXTVAL, 'Уборка', 2, 2, 2); INSERT INTO Hangar_sector (Sector_id, Type1, Equipment, Responsible_worker, Hangar) VALUES (seq_hangar_sector_id.NEXTVAL, 'Разведение', 3, 3, 3); INSERT INTO Hangar_sector (Sector_id, Type1, Equipment, Responsible_worker, Hangar) VALUES (seq_hangar_sector_id.NEXTVAL, 'Обслуживание', 4, 4, 4); INSERT INTO Hangar_sector (Sector_id, Type1, Equipment, Responsible_worker, Hangar) VALUES (seq_hangar_sector_id.NEXTVAL, 'Инспекция', 5, 5, 5); INSERT INTO Pigs (ID_Pigs, Breed, Date_of_birth, Weigh, Sector) VALUES (seq_pigs_id.NEXTVAL, 'Йоркширская', TO_DATE('2024-01-01', 'YYYY-MM-DD'), '100kg', 1); INSERT INTO Pigs (ID_Pigs, Breed, Date_of_birth, Weigh, Sector) VALUES (seq_pigs_id.NEXTVAL, 'Ландрас', TO_DATE('2024-02-02', 'YYYY-MM-DD'), '110kg', 2); INSERT INTO Pigs (ID_Pigs, Breed, Date_of_birth, Weigh, Sector) VALUES (seq_pigs_id.NEXTVAL, 'Дюрок', TO_DATE('2024-03-03', 'YYYY-MM-DD'), '120kg', 3); INSERT INTO Pigs (ID_Pigs, Breed, Date_of_birth, Weigh, Sector) VALUES (seq_pigs_id.NEXTVAL, 'Туклинская', TO_DATE('2024-05-05', 'YYYY-MM-DD'), '130kg', 4); INSERT INTO Pigs (ID_Pigs, Breed, Date_of_birth, Weigh, Sector) VALUES (seq_pigs_id.NEXTVAL, 'Вьетнамская вислобрюхая', TO_DATE('2024-06-06', 'YYYY-MM-DD'), '140kg', 5);