laba_1_BD

-- когда проверяли, сказали делать с join'ами, а я делал с помощью where, как я делал делать не надо
-- перепиисать тут легко
--1
SELECT Cooperator.Surname, Cooperator.Name, Cooperator.Bitrhday,
Cooperator.Salary, Cooperator.Phone_number, Cooperator.Category,
Department.Name, Evaluation.STATUS AS 'оценка'
    FROM Cooperator, Department, Evaluation
    WHERE Cooperator.Dept_id = Department.Dept_id AND Cooperator.Cooperator_id = Evaluation.Cooperator_id;


--2

SELECT Surname, Name, Dept_id
FROM Cooperator
WHERE NOT EXISTS (SELECT *
                 FROM Evaluation
                         WHERE Cooperator.Cooperator_id=Evaluation.Cooperator_id)
UNION
SELECT Surname, Name, Dept_id
FROM Cooperator
WHERE NOT EXISTS (SELECT *
                 FROM Evaluation
                         WHERE Cooperator.Cooperator_id=Evaluation.Cooperator_id) AND Cooperator.Dept_id = NULL


--3
SELECT Cooperator.Surname, Company.Name, Evaluation.STATUS AS 'оценка'
    FROM Cooperator, Department, Evaluation, Company
    WHERE Cooperator.Dept_id = Department.Dept_id AND Cooperator.Cooperator_id = Evaluation.Cooperator_id AND Company.Company_id = Department.Company_id
UNION
SELECT Cooperator.Surname, NULL, Evaluation.STATUS AS 'оценка'
    FROM Cooperator, Department, Evaluation, Company
    WHERE Cooperator.Dept_id IS NULL AND Cooperator.Cooperator_id = Evaluation.Cooperator_id;


--4
SELECT Cooperator.Surname, Department.Name, Evaluation.STATUS AS 'оценка'
    FROM Cooperator, Department, Evaluation, Company
    WHERE Cooperator.Dept_id = Department.Dept_id AND Cooperator.Cooperator_id = Evaluation.Cooperator_id AND Company.Company_id = Department.Company_id
    AND Evaluation.STATUS >= 4


--5
SELECT Cooperator.Surname, Department.Name, Evaluation.STATUS AS 'оценка'
    FROM Cooperator, Department, Evaluation, Company
    WHERE Cooperator.Dept_id = Department.Dept_id AND Cooperator.Cooperator_id = Evaluation.Cooperator_id AND Company.Company_id = Department.Company_id
    AND Evaluation.STATUS >= 4 AND Evaluation.Date>='2019-12-01' AND Evaluation.Date<'2020-01-01'


--6
SELECT Company.Name, MAX(Cooperator.salary) AS 'макс з/п'
    FROM Company JOIN Department ON Company.Company_id = Department.Company_id
    JOIN Cooperator ON Cooperator.Dept_id = Department.Dept_id
    WHERE Company.Coefficent > 8
    GROUP BY Company.Name

-- должно было не работать, но работает
SELECT Company.Name, MAX(Cooperator.salary) AS 'макс з/п'
    FROM Company, Cooperator, Department
    WHERE Company.Coefficent > 8 AND Cooperator.Dept_id = Department.Dept_id AND Company.Company_id = Department.Company_id
    GROUP BY Company.Name


--7
SELECT Cooperator.Surname, Company.Coefficent
FROM Cooperator, Company, Department
WHERE Cooperator.Dept_id = Department.Dept_id AND  Company.Company_id = Department.Company_id
UNION
SELECT Cooperator.Surname, NULL
FROM Cooperator
WHERE Cooperator.Dept_id IS NULL
GROUP BY Cooperator.Surname