Bondarchuk_DBS_LABS2

-- Видалити базу даних "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', NULL),
(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 DISTINCT Gender FROM Employees;
-- SELECT * FROM Employees WHERE DepartmentId IN (3, 4);
-- SELECT * FROM Employees WHERE FullName LIKE 'E%';

-- SELECT * FROM Employees WHERE FullName LIKE '%n';

-- SELECT * FROM Employees WHERE FullName LIKE '%John%';

-- SELECT * FROM Employees WHERE SSN LIKE '%123%';

-- SELECT * FROM Departments WHERE DepartmentName LIKE 'Marketing%';

-- SELECT * FROM Employees WHERE FullName REGEXP 'o';

-- SELECT * FROM Employees WHERE SSN REGEXP '567';

-- SELECT * FROM Employees WHERE FullName REGEXP '^(M|D)';

-- SELECT * FROM Employees WHERE FullName REGEXP '^.{5,9}$';

-- SELECT * FROM Employees WHERE FullName REGEXP '^J.*e$';

-- SELECT * FROM Employees WHERE Experience IS NULL;

-- SELECT * FROM Departments
-- ORDER BY DepartmentName ASC;

-- SELECT * FROM Positions LIMIT 2;