SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Projects ( project_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, project_name VARCHAR2(255) NOT NULL, start_date DATE NOT NULL, end_date DATE, budget DECIMAL(15, 2), status VARCHAR2(50) NOT NULL ); CREATE TABLE Employees ( employee_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, first_name VARCHAR2(100) NOT NULL, last_name VARCHAR2(100) NOT NULL, position VARCHAR2(100) NOT NULL, hire_date DATE NOT NULL, project_id NUMBER, FOREIGN KEY (project_id) REFERENCES Projects(project_id) ); CREATE TABLE Materials ( material_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, material_name VARCHAR2(255) NOT NULL, quantity DECIMAL(15, 2) NOT NULL, unit_price DECIMAL(15, 2) NOT NULL, total_cost DECIMAL(15, 2) NOT NULL, project_id NUMBER, FOREIGN KEY (project_id) REFERENCES Projects(project_id) ); CREATE TABLE Tasks ( task_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, task_description VARCHAR2(1000) NOT NULL, assigned_to NUMBER, project_id NUMBER, start_date DATE NOT NULL, end_date DATE, status VARCHAR2(50) NOT NULL, FOREIGN KEY (assigned_to) REFERENCES Employees(employee_id), FOREIGN KEY (project_id) REFERENCES Projects(project_id) ); INSERT INTO Projects (project_name, start_date, end_date, budget, status) VALUES ('Строительство нового жилого комплекса', TO_DATE('2023-01-01', 'YYYY-MM-DD'), TO_DATE('2024-12-31', 'YYYY-MM-DD'), 50000000, 'В процессе'); INSERT INTO Projects (project_name, start_date, end_date, budget, status) VALUES ('Реконструкция старого здания', TO_DATE('2023-06-01', 'YYYY-MM-DD'), TO_DATE('2024-05-01', 'YYYY-MM-DD'), 15000000, 'Завершено'); INSERT INTO Projects (project_name, start_date, end_date, budget, status) VALUES ('Строительство моста', TO_DATE('2023-03-01', 'YYYY-MM-DD'), TO_DATE('2025-03-01', 'YYYY-MM-DD'), 20000000, 'В процессе'); INSERT INTO Projects (project_name, start_date, end_date, budget, status) VALUES ('Ремонт дорог', TO_DATE('2024-05-01', 'YYYY-MM-DD'), TO_DATE('2024-12-01', 'YYYY-MM-DD'), 3000000, 'В процессе'); INSERT INTO Employees (first_name, last_name, position, hire_date, project_id) VALUES ('Иван', 'Иванов', 'Прораб', TO_DATE('2015-04-20', 'YYYY-MM-DD'), 1); INSERT INTO Employees (first_name, last_name, position, hire_date, project_id) VALUES ('Сергей', 'Петров', 'Архитектор', TO_DATE('2016-08-15', 'YYYY-MM-DD'), 2); INSERT INTO Employees (first_name, last_name, position, hire_date, project_id) VALUES ('Алексей', 'Сидоров', 'Инженер', TO_DATE('2019-06-01', 'YYYY-MM-DD'), 3); INSERT INTO Employees (first_name, last_name, position, hire_date, project_id) VALUES ('Михаил', 'Коваленко', 'Строитель', TO_DATE('2020-03-30', 'YYYY-MM-DD'), 4); INSERT INTO Materials (material_name, quantity, unit_price, total_cost, project_id) VALUES ('Бетон', 10000, 150, 1500000, 1); INSERT INTO Materials (material_name, quantity, unit_price, total_cost, project_id) VALUES ('Кирпич', 20000, 30, 600000, 2); INSERT INTO Materials (material_name, quantity, unit_price, total_cost, project_id) VALUES ('Сталь', 5000, 250, 1250000, 3); INSERT INTO Materials (material_name, quantity, unit_price, total_cost, project_id) VALUES ('Асфальт', 5000, 50, 250000, 4); INSERT INTO Tasks (task_description, assigned_to, project_id, start_date, end_date, status) VALUES ('Устройство фундамента', 1, 1, TO_DATE('2023-01-01', 'YYYY-MM-DD'), TO_DATE('2023-06-01', 'YYYY-MM-DD'), 'В процессе'); INSERT INTO Tasks (task_description, assigned_to, project_id, start_date, end_date, status) VALUES ('Проектирование здания', 2, 2, TO_DATE('2023-06-01', 'YYYY-MM-DD'), TO_DATE('2024-01-01', 'YYYY-MM-DD'), 'Завершено'); INSERT INTO Tasks (task_description, assigned_to, project_id, start_date, end_date, status) VALUES ('Установка мостовых конструкций', 3, 3, TO_DATE('2023-03-01', 'YYYY-MM-DD'), TO_DATE('2024-03-01', 'YYYY-MM-DD'), 'В процессе'); INSERT INTO Tasks (task_description, assigned_to, project_id, start_date, end_date, status) VALUES ('Ремонт покрытия дороги', 4, 4, TO_DATE('2024-05-01', 'YYYY-MM-DD'), TO_DATE('2024-09-01', 'YYYY-MM-DD'), 'В процессе');