lr6_db

--1
SELECT *
FROM Evaluation
WHERE Cooperator_id IN (
    SELECT Cooperator_id
    FROM Cooperator
    WHERE Surname = 'Иванов');


--2
SELECT Name
FROM Cooperator
WHERE Cooperator.Dept_id = 5 AND Cooperator_id IN (
    SELECT Cooperator_id
    FROM Evaluation
    WHERE Status > (SELECT AVG(Status)
                   FROM Evaluation))


--3
SELECT Name
FROM Cooperator
WHERE Dept_id = 7 AND Cooperator_id IN (
        SELECT Cooperator_id
        FROM Evaluation
        WHERE Status < (
            SELECT AVG(Status)
            FROM Evaluation));


--4
select Dept_id /*count(*)*/ as amount_of_Deps
from Department D
where (select min(Stat_count)
    from (select count(Status) as Stat_count
        from Evaluation Ev
        where Cooperator_id in (select Cooperator_id
                         from Cooperator
                         where Dept_id = D.Dept_id)
        group by Cooperator_id) B) >3;


--5
SELECT C1.Surname, C1.Name, C1.Dept_id
FROM Cooperator AS C1
WHERE C1.Salary = (
    SELECT MAX(C2.Salary)
    FROM Cooperator AS C2
    WHERE C1.Dept_id = C2.Dept_id);


--6
SELECT Surname, Name
FROM Cooperator
WHERE City NOT IN (
      SELECT DISTINCT City
      FROM Company)


--7
SELECT Coop.Cooperator_id, Coop.Name, Coop.Surname, Coop.City, Comn.City as City_of_comp
FROM Cooperator AS Coop, Department AS Dept, Company AS Comn
WHERE Coop.Dept_id = Dept.Dept_id
    AND Comn.Company_id = Dept.Company_id
        AND Coop.City != Comn.City;

SELECT Cooperator_id, Name, Surname, City
FROM Cooperator AS C
WHERE C.Dept_id IN (
    SELECT Dept_id
    FROM Department
    WHERE Company_id IN (
        SELECT Company_id
        FROM Company
        WHERE City != C.City));



--------------------------------------------------------------------------------------
--8
SELECT DISTINCT Cooperator.Cooperator_id, Surname, Name, City, Salary
FROM Cooperator
INNER JOIN Evaluation ON Cooperator.Cooperator_id = Evaluation.Cooperator_id
WHERE EXISTS (SELECT Cooperator_id
              FROM Evaluation
              WHERE Cooperator.Cooperator_id = Evaluation.Cooperator_id
              GROUP BY Cooperator_id
              HAVING MIN(Status) > 3);

select *
from Evaluation
--9
select distinct Cp.Cooperator_id, Cp.Surname, Cp.Name, City, Salary
from Cooperator Cp, Evaluation Ev
where Cp.Cooperator_id = Ev.Cooperator_id and 3 <
                                            (select min(Status)
                                            from Evaluation
                                            where Cooperator_id = Cp.Cooperator_id);



--10
SELECT *
FROM Cooperator c
WHERE EXISTS (
    SELECT *
    FROM Department d
    WHERE d.Dept_id = c.Dept_id
    AND c.City = (
        SELECT City
        FROM Company
        WHERE d.Company_id = Company_id));


--11
SELECT d.Full_name
FROM DEPARTMENT d, Cooperator c
WHERE d.Dept_id = c.Dept_id AND EXISTS (
    SELECT *
    FROM Evaluation e
    WHERE e.Cooperator_id = c.Cooperator_id)
GROUP BY d.Full_name
HAVING COUNT(DISTINCT c.Cooperator_id) > 1;