SQLize Online / PHPize Online  /  SQLtest Online

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 Storehouse_id_seq START WITH 1 INCREMENT BY 1; CREATE SEQUENCE ID_Equipment_seq START WITH 1 INCREMENT BY 1; CREATE SEQUENCE pass_id_seq START WITH 1 INCREMENT BY 1; CREATE SEQUENCE building_id_seq START WITH 1 INCREMENT BY 1; CREATE SEQUENCE sector_id_seq START WITH 1 INCREMENT BY 1; CREATE SEQUENCE id_pigs_seq START WITH 1 INCREMENT BY 1; INSERT INTO Storehouse (Storehouse_id, Pig_feed, Capacity_Store, Building_area, Number_of_floors) VALUES (Storehouse_id_seq.NEXTVAL, 'Жёлуди', 1000, 200, 1); INSERT INTO Storehouse (Storehouse_id, Pig_feed, Capacity_Store, Building_area, Number_of_floors) VALUES (Storehouse_id_seq.NEXTVAL, 'Отварная свёкла', 700, 150, 2); INSERT INTO Storehouse (Storehouse_id, Pig_feed, Capacity_Store, Building_area, Number_of_floors) VALUES (Storehouse_id_seq.NEXTVAL, 'Пшено', 1200, 250, 3); INSERT INTO Storehouse (Storehouse_id, Pig_feed, Capacity_Store, Building_area, Number_of_floors) VALUES (Storehouse_id_seq.NEXTVAL, 'Ячмень', 900, 180, 2); INSERT INTO Storehouse (Storehouse_id, Pig_feed, Capacity_Store, Building_area, Number_of_floors) VALUES (Storehouse_id_seq.NEXTVAL, 'Сухари', 800, 170, 1); -- Insert data into Equipment INSERT INTO Equipment (ID_Equipment, Storehouse, Destination, Price, Useful_life) VALUES (ID_Equipment_seq.NEXTVAL, 1, 'Система кормления', 5000, 10); INSERT INTO Equipment (ID_Equipment, Storehouse, Destination, Price, Useful_life) VALUES (ID_Equipment_seq.NEXTVAL, 2, 'Система водоснабжения', 3000, 8); INSERT INTO Equipment (ID_Equipment, Storehouse, Destination, Price, Useful_life) VALUES (ID_Equipment_seq.NEXTVAL, 3, 'Система охлаждения', 7000, 15); INSERT INTO Equipment (ID_Equipment, Storehouse, Destination, Price, Useful_life) VALUES (ID_Equipment_seq.NEXTVAL, 4, 'Тепловая лампа', 1500, 5); INSERT INTO Equipment (ID_Equipment, Storehouse, Destination, Price, Useful_life) VALUES (ID_Equipment_seq.NEXTVAL, 5, 'Вентиляционный вентилятор', 4000, 12); -----------------Farm_worker INSERT INTO Farm_worker (Pass_id, Full_name, Date_of_birth, Phone_number, Job1) VALUES (pass_id_seq.NEXTVAL, 'Виктор Валерьевич', TO_DATE('1975-01-01', 'YYYY-MM-DD'), '9384-234', 'Фермер'); INSERT INTO Farm_worker (Pass_id, Full_name, Date_of_birth, Phone_number, Job1) VALUES (pass_id_seq.NEXTVAL, 'Василий Михайлович', TO_DATE('1980-07-15', 'YYYY-MM-DD'), '2374-121', 'Менеджер'); INSERT INTO Farm_worker (Pass_id, Full_name, Date_of_birth, Phone_number, Job1) VALUES (pass_id_seq.NEXTVAL, 'Никита Сергеевич', TO_DATE('1997-03-20', 'YYYY-MM-DD'), '9292-929', 'Технический работник'); INSERT INTO Farm_worker (Pass_id, Full_name, Date_of_birth, Phone_number, Job1) VALUES (pass_id_seq.NEXTVAL, 'Валерия Викторовна', TO_DATE('1989-09-10', 'YYYY-MM-DD'), '9090-111', 'Ветеринар'); INSERT INTO Farm_worker (Pass_id, Full_name, Date_of_birth, Phone_number, Job1) VALUES (pass_id_seq.NEXTVAL, 'Кирилл Олегович', TO_DATE('1991-05-30', 'YYYY-MM-DD'), '5858-333', 'Работник склада'); -------------------Hangar INSERT INTO Hangar (Building_id, Manager1, Feed_from_the_storehouse, Building_area, Wall_material) VALUES (building_id_seq.NEXTVAL, 2, 'Ручное', 1000, 'Бетонный'); INSERT INTO Hangar (Building_id, Manager1, Feed_from_the_storehouse, Building_area, Wall_material) VALUES (building_id_seq.NEXTVAL, 2, 'Автоматическое', 1200, 'Железный'); INSERT INTO Hangar (Building_id, Manager1, Feed_from_the_storehouse, Building_area, Wall_material) VALUES (building_id_seq.NEXTVAL, 1, 'Ручное', 800, 'Оловянный'); INSERT INTO Hangar (Building_id, Manager1, Feed_from_the_storehouse, Building_area, Wall_material) VALUES (building_id_seq.NEXTVAL, 3, 'Автоматическое', 1500, 'Железный'); INSERT INTO Hangar (Building_id, Manager1, Feed_from_the_storehouse, Building_area, Wall_material) VALUES (building_id_seq.NEXTVAL, 4, 'Ручное', 1100, 'Кирпичный'); -----------------Hangar_sector INSERT INTO Hangar_sector (Sector_id, Type1, Equipment, Responsible_worker, Hangar) VALUES (sector_id_seq.NEXTVAL, 'Кормлешка', 1, 1, 1); INSERT INTO Hangar_sector (Sector_id, Type1, Equipment, Responsible_worker, Hangar) VALUES (sector_id_seq.NEXTVAL, 'Уборка', 2, 2, 2); INSERT INTO Hangar_sector (Sector_id, Type1, Equipment, Responsible_worker, Hangar) VALUES (sector_id_seq.NEXTVAL, 'Разведение', 3, 3, 3); INSERT INTO Hangar_sector (Sector_id, Type1, Equipment, Responsible_worker, Hangar) VALUES (sector_id_seq.NEXTVAL, 'Обслуживание', 4, 4, 4); INSERT INTO Hangar_sector (Sector_id, Type1, Equipment, Responsible_worker, Hangar) VALUES (sector_id_seq.NEXTVAL, 'Инспекция', 5, 5, 5); ----------------Pigs INSERT INTO Pigs (ID_Pigs, Breed, Date_of_birth, Weigh, Sector) VALUES (id_pigs_seq.NEXTVAL, 'Мангалица', TO_DATE('2021-05-01', 'YYYY-MM-DD'), '150kg', 1); INSERT INTO Pigs (ID_Pigs, Breed, Date_of_birth, Weigh, Sector) VALUES (id_pigs_seq.NEXTVAL, 'Пьетрен', TO_DATE('2021-01-05', 'YYYY-MM-DD'), '115kg', 2); INSERT INTO Pigs (ID_Pigs, Breed, Date_of_birth, Weigh, Sector) VALUES (id_pigs_seq.NEXTVAL, 'Дюрок', TO_DATE('2021-12-08', 'YYYY-MM-DD'), '120kg', 3); INSERT INTO Pigs (ID_Pigs, Breed, Date_of_birth, Weigh, Sector) VALUES (id_pigs_seq.NEXTVAL, 'Карликовая', TO_DATE('2021-12-12', 'YYYY-MM-DD'), '125kg', 4); INSERT INTO Pigs (ID_Pigs, Breed, Date_of_birth, Weigh, Sector) VALUES (id_pigs_seq.NEXTVAL, 'Гриц', TO_DATE('2021-09-05', 'YYYY-MM-DD'), '100kg', 5); alter table Pigs add (Pigs_fisrt_name varchar2(20)); alter table Pigs modify (Breed varchar2(50)); alter table Farm_worker add (Farm_worker_lastname varchar2(20)); update Farm_worker set Farm_worker_lastname = 'Капустин' where Pass_id = 1; update Farm_worker set Farm_worker_lastname = 'Завьялов' where Pass_id = 2; update Farm_worker set Farm_worker_lastname = 'Иванов' where Pass_id = 3; update Farm_worker set Farm_worker_lastname = 'Шимакова' where Pass_id = 4; update Farm_worker set Farm_worker_lastname = 'Соловьёв' where Pass_id = 5; alter table Pigs modify Weigh varchar2(20) update Pigs set Breed = 'Херефорд' where Sector = '1'; update Pigs set Breed = 'Ландрас' where Sector = '5'; drop TABLE Access_to_the_storehouse; drop TABLE Pigs; drop TABLE Hangar_sector; drop TABLE Hangar; drop TABLE Equipment; drop TABLE Storehouse; drop TABLE Farm_worker; drop SEQUENCE Storehouse_id_seq; drop SEQUENCE ID_Equipment_seq; drop SEQUENCE pass_id_seq; drop SEQUENCE building_id_seq; drop SEQUENCE sector_id_seq; drop SEQUENCE id_pigs_seq;