SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Employees ( 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, payment_type VARCHAR2(50) NOT NULL, hire_date DATE NOT NULL ); CREATE TABLE WorkTime ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, employee_id NUMBER, work_date DATE NOT NULL, hours_worked NUMBER NOT NULL, tasks_completed NUMBER, FOREIGN KEY (employee_id) REFERENCES Employees(id) ); CREATE TABLE Salaries ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, employee_id NUMBER, salary_date DATE NOT NULL, base_salary DECIMAL(10, 2) NOT NULL, bonuses DECIMAL(10, 2), deductions DECIMAL(10, 2), total_salary DECIMAL(10, 2) NOT NULL, FOREIGN KEY (employee_id) REFERENCES Employees(id) ); CREATE TABLE Departments ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR2(100) NOT NULL UNIQUE, manager_id NUMBER, FOREIGN KEY (manager_id) REFERENCES Employees(id) ); -- Insert into Employees table INSERT INTO Employees (first_name, last_name, position, payment_type, hire_date) VALUES ('Ivan', 'Ivanov', 'Слесарь', 'Hourly', TO_DATE('2020-01-01', 'YYYY-MM-DD')); INSERT INTO Employees (first_name, last_name, position, payment_type, hire_date) VALUES ('Sergey', 'Petrov', 'Монтажник', 'Monthly', TO_DATE('2021-06-15', 'YYYY-MM-DD')); INSERT INTO Employees (first_name, last_name, position, payment_type, hire_date) VALUES ('Alexei', 'Sidorov', 'Уборщик', 'Hourly', TO_DATE('2019-03-25', 'YYYY-MM-DD')); INSERT INTO Employees (first_name, last_name, position, payment_type, hire_date) VALUES ('Mikhail', 'Kovalenko', 'Директор', 'Monthly', TO_DATE('2018-11-10', 'YYYY-MM-DD')); -- Insert into WorkTime table INSERT INTO WorkTime (employee_id, work_date, hours_worked, tasks_completed) VALUES (1, TO_DATE('2024-12-18', 'YYYY-MM-DD'), 8, 5); INSERT INTO WorkTime (employee_id, work_date, hours_worked, tasks_completed) VALUES (2, TO_DATE('2024-12-18', 'YYYY-MM-DD'), 7, 3); INSERT INTO WorkTime (employee_id, work_date, hours_worked, tasks_completed) VALUES (3, TO_DATE('2024-12-18', 'YYYY-MM-DD'), 6, 4); INSERT INTO WorkTime (employee_id, work_date, hours_worked, tasks_completed) VALUES (4, TO_DATE('2024-12-18', 'YYYY-MM-DD'), 8, 6); -- Insert into Salaries table INSERT INTO Salaries (employee_id, salary_date, base_salary, bonuses, deductions, total_salary) VALUES (1, TO_DATE('2024-12-18', 'YYYY-MM-DD'), 3000.00, 500.00, 100.00, 3400.00); INSERT INTO Salaries (employee_id, salary_date, base_salary, bonuses, deductions, total_salary) VALUES (2, TO_DATE('2024-12-18', 'YYYY-MM-DD'), 4000.00, 700.00, 200.00, 4500.00); INSERT INTO Salaries (employee_id, salary_date, base_salary, bonuses, deductions, total_salary) VALUES (3, TO_DATE('2024-12-18', 'YYYY-MM-DD'), 2000.00, 200.00, 50.00, 2150.00); INSERT INTO Salaries (employee_id, salary_date, base_salary, bonuses, deductions, total_salary) VALUES (4, TO_DATE('2024-12-18', 'YYYY-MM-DD'), 5000.00, 1000.00, 300.00, 5700.00); -- Insert into Departments table INSERT INTO Departments (name, manager_id) VALUES ('Engineering', 4); INSERT INTO Departments (name, manager_id) VALUES ('Cleaning', 3); INSERT INTO Departments (name, manager_id) VALUES ('HR', 2); INSERT INTO Departments (name, manager_id) VALUES ('IT', 1); -- Создание пользователей CREATE USER alice IDENTIFIED BY alice_password; CREATE USER bob IDENTIFIED BY bob_password; -- Создание роли CREATE ROLE manager_role; -- Назначение роли пользователю GRANT manager_role TO alice; -- Предоставление привилегий на таблицы GRANT SELECT, INSERT, UPDATE ON employees TO alice; -- Проверка ролей, назначенных пользователю Alice SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'ALICE'; -- Проверка привилегий на таблицу employees SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'ALICE';