---

-- =========================
-- DELETE
-- =========================

-- 1
DELETE FROM ProjectAssignments
WHERE EmployeeID IN (
    SELECT EmployeeID
    FROM Employees
    WHERE IsActive = 0
);

DELETE FROM Employees
WHERE IsActive = 0;

-- 2
DELETE FROM ProjectAssignments
WHERE ProjectID IN (
    SELECT ProjectID
    FROM Projects
    WHERE DepartmentID IN (
        SELECT DepartmentID
        FROM Departments
        WHERE DepartmentName = 'Legal'
    )
);

DELETE FROM Projects
WHERE DepartmentID IN (
    SELECT DepartmentID
    FROM Departments
    WHERE DepartmentName = 'Legal'
);

DELETE FROM Employees
WHERE DepartmentID IN (
    SELECT DepartmentID
    FROM Departments
    WHERE DepartmentName = 'Legal'
);

DELETE FROM Departments
WHERE DepartmentName = 'Legal';

-- 3
DELETE FROM ProjectAssignments
WHERE ProjectID IN (
    SELECT ProjectID
    FROM Projects
    WHERE ProjectName = 'Тестирование ПО'
);

DELETE FROM Projects
WHERE ProjectName = 'Тестирование ПО';

-- 4
DELETE FROM ProjectAssignments
WHERE HoursWorked = 0;

-- 5
DELETE FROM ProjectAssignments
WHERE EmployeeID IN (
    SELECT EmployeeID
    FROM Employees
    WHERE Salary < 50000
);

DELETE FROM Employees
WHERE Salary < 50000;

-- 6
DELETE FROM ProjectAssignments
WHERE ProjectID IN (
    SELECT ProjectID
    FROM Projects
    WHERE DepartmentID IS NULL
);

-- 7
DELETE FROM Projects
WHERE ProjectID NOT IN (
    SELECT DISTINCT ProjectID
    FROM ProjectAssignments
);

-- 8
DELETE FROM ProjectAssignments
WHERE EmployeeID IN (
    SELECT EmployeeID
    FROM Employees
    WHERE Email LIKE '%example.com%'
    AND Salary > 90000
);

DELETE FROM Employees
WHERE Email LIKE '%example.com%'
AND Salary > 90000;

-- 9
DELETE FROM ProjectAssignments
WHERE ProjectID IN (
    SELECT ProjectID
    FROM Projects
    WHERE DepartmentID NOT IN (
        SELECT DISTINCT DepartmentID
        FROM Employees
        WHERE DepartmentID IS NOT NULL
    )
);

DELETE FROM Projects
WHERE DepartmentID NOT IN (
    SELECT DISTINCT DepartmentID
    FROM Employees
    WHERE DepartmentID IS NOT NULL
);

DELETE FROM Departments
WHERE DepartmentID NOT IN (
    SELECT DISTINCT DepartmentID
    FROM Employees
    WHERE DepartmentID IS NOT NULL
);

-- 10
DELETE FROM ProjectAssignments
WHERE Role = 'Junior Analyst';

-- 11
DELETE FROM ProjectAssignments
WHERE EmployeeID IN (
    SELECT pa.EmployeeID
    FROM ProjectAssignments pa
    JOIN Projects p
    ON pa.ProjectID = p.ProjectID
    WHERE p.ProjectName='Найм стажеров'
);

DELETE FROM Employees
WHERE EmployeeID IN (
    SELECT pa.EmployeeID
    FROM ProjectAssignments pa
    JOIN Projects p
    ON pa.ProjectID = p.ProjectID
    WHERE p.ProjectName='Найм стажеров'
);

-- 12
DELETE FROM ProjectAssignments
WHERE EmployeeID IN (
    SELECT EmployeeID
    FROM Employees
    WHERE DepartmentID = (
        SELECT DepartmentID
        FROM Departments
        WHERE DepartmentName='HR'
    )
);

-- 13
DELETE FROM ProjectAssignments
WHERE ProjectID IN (
    SELECT ProjectID
    FROM Projects
    WHERE DepartmentID IN (
        SELECT DepartmentID
        FROM Departments
        WHERE Budget < 85000
    )
);

DELETE FROM Projects
WHERE DepartmentID IN (
    SELECT DepartmentID
    FROM Departments
    WHERE Budget < 85000
);

-- 14
DELETE FROM ProjectAssignments
WHERE EmployeeID IN (
    SELECT EmployeeID
    FROM Employees
    WHERE Salary > (
        SELECT AVG(Salary)
        FROM Employees
    )
);

DELETE FROM Employees
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees
);

-- 15
DELETE FROM ProjectAssignments
WHERE HoursWorked < (
    SELECT AVG(HoursWorked)
    FROM ProjectAssignments
);

-- 16
UPDATE Employees
SET DepartmentID = 1
WHERE DepartmentID = (
    SELECT DepartmentID
    FROM Departments
    WHERE DepartmentName='Finance'
);

UPDATE Projects
SET DepartmentID = 1
WHERE DepartmentID = (
    SELECT DepartmentID
    FROM Departments
    WHERE DepartmentName='Finance'
);

DELETE FROM Departments
WHERE DepartmentName='Finance';

-- 17
UPDATE Departments
SET ManagerID = NULL
WHERE ManagerID = 1;

DELETE FROM HERE EmployeeID = 1;

DELETE FROM Employees
WHERE EmployeeID = 1;

-- 18
WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY Email
               ORDER BY EmployeeID
           ) AS rn
    FROM Employees
)
DELETE FROM CTE
WHERE rn > 1;

-- 19
DELETE FROM ProjectAssignments
WHERE ProjectID IN (
    SELECT ProjectID
    FROM Projects
    WHERE ProjectName LIKE 'Тест%'
);

DELETE FROM Projects
WHERE ProjectName LIKE 'Тест%';

-- 20
DELETE FROM ProjectAssignments
WHERE ProjectID NOT IN (1,2,3); ProjectAssignments
W