Bondarchuk_DBS_LABS

-- Видалити базу даних "AccountingDB", якщо вона існує
-- DROP DATABASE IF EXISTS AccountingDB;
-- Закоментовано створення бази, тому що редактор автоматично створив бд. Проект вже є базою
-- Створити нову базу даних "AccountingDB"
-- CREATE DATABASE AccountingDB;

-- Table "Departments"
CREATE TABLE Departments (
Id INT PRIMARY KEY,
DepartmentName VARCHAR(255) NOT NULL
);

-- Table "Positions"
CREATE TABLE Positions (
Id INT PRIMARY KEY,
PositionName VARCHAR(255) NOT NULL,
Salary DECIMAL(10, 2)
);

-- Table "Employees"
CREATE TABLE Employees (
EmployeeId INT PRIMARY KEY,
FullName VARCHAR(40) NOT NULL,
SSN VARCHAR(12) UNIQUE NOT NULL,
Gender CHAR(1) DEFAULT 'M' CHECK (Gender IN ('M', 'F')),
DateOfBirth DATE NOT NULL,
DepartmentId INT,
PositionId INT,
HireDate DATE,
Experience DECIMAL(5, 1) DEFAULT 0,
FOREIGN KEY (DepartmentId) REFERENCES Departments(Id) ON DELETE CASCADE,
FOREIGN KEY (PositionId) REFERENCES Positions(Id) ON DELETE CASCADE
);

-- Table "EmploymentRecords"
CREATE TABLE EmploymentRecords (
RecordId INT PRIMARY KEY,
EmployeeId INT,
DepartmentId INT,
PositionId INT,
HireDate DATE,
FOREIGN KEY (EmployeeId) REFERENCES Employees(EmployeeId) ON DELETE CASCADE,
FOREIGN KEY (DepartmentId) REFERENCES Departments(Id),
FOREIGN KEY (PositionId) REFERENCES Positions(Id)
);

-- Вставка даних в таблицю "Departments"
INSERT INTO Departments (Id, DepartmentName) VALUES
(3, 'Marketing Department'),
(4, 'Human Resources'),
(5, 'Finance Department');

-- Вставка даних в таблицю "Positions"
INSERT INTO Positions (Id, PositionName, Salary) VALUES
(3, 'Software Engineer', 60000.00),
(4, 'Sales Manager', 70000.00),
(5, 'Accountant', 55000.00);

-- Вставка даних в таблицю "Employees"
INSERT INTO Employees (EmployeeId, FullName, SSN, Gender, DateOfBirth, DepartmentId, PositionId, HireDate, Experience) VALUES
(103, 'John Doe', '123456789012', 'M', '1990-05-15', 3, 3, '2015-03-20', 5.5),
(104, 'Jane Smith', '987654321098', 'F', '1988-08-25', 4, 4, '2016-01-10', 4.2),
(105, 'Mike Johnson', '456789012345', 'M', '1992-11-03', 4, 3, '2017-06-15', 3.7),
(106, 'Emily Brown', '789012345678', 'F', '1995-02-20', 5, 4, '2018-08-02', 2.8),
(107, 'David Wilson', '234567890123', 'M', '1987-04-10', 3, 5, '2019-12-05', 6.1);

-- Вставка даних в таблицю "EmploymentRecords"
INSERT INTO EmploymentRecords (RecordId, EmployeeId, DepartmentId, PositionId, HireDate) VALUES
(1, 103, 3, 3, '2015-03-20'),
(2, 104, 4, 4, '2016-01-10'),
(3, 105, 5, 3, '2017-06-15'),
(4, 106, 4, 4, '2018-08-02'),
(5, 107, 3, 5, '2019-12-05');

-- Select all records from the "Departments" table
-- SELECT * FROM Departments;
-- SELECT * FROM Employees;
-- Example of adding a column to the "Employees" table
ALTER TABLE Employees ADD COLUMN Email VARCHAR(255);
INSERT INTO Employees (EmployeeId, FullName, SSN, Gender, DateOfBirth, DepartmentId, PositionId, HireDate, Experience, Email) VALUES
(110, 'Kekich Kekich', '123456789019', 'M', '1990-05-15', 3, 3, '2015-03-20', 5.5, '[email protected]');

-- Example of changing the data type of a column
ALTER TABLE Employees MODIFY COLUMN Experience DECIMAL(6, 2);

-- Example of dropping a column from the table
ALTER TABLE Employees DROP COLUMN Email;

-- Example of adding a CHECK constraint to the table
ALTER TABLE Employees ADD CONSTRAINT CheckExperience CHECK (Experience >= 0);
SELECT * FROM Employees;
-- Example of dropping a constraint
ALTER TABLE Employees DROP CONSTRAINT CheckExperience;

-- Update the experience for an employee with EmployeeId 104
UPDATE Employees SET Experience = 4 WHERE EmployeeId = 104;
SELECT * FROM Employees WHERE EmployeeId = 104;

-- Delete an employee with EmployeeId 103
DELETE FROM Employees WHERE EmployeeId = 103;

-- As we can see, the following code where we try to select the employee with ID 103 doesn't output any results because the employee has been deleted. The database is working as expected.
SELECT * FROM Employees;