laba_2_BD_views

--1
CREATE VIEW fir_task AS
SELECT City, COUNT(Name) AS 'Кол-во компаний'
FROM Company
GROUP BY City

SELECT *
FROM fir_task

DROP VIEW fir_task

--2
CREATE VIEW sec_task AS
SELECT Cooperator.Name AS 'имя', Cooperator.Surname, Department.Name AS 'отдел', Company.Name AS 'компания'
FROM Cooperator, Department, Company
WHERE Company.Coefficent > 6 AND Cooperator.Dept_id = Department.Dept_id AND Department.Company_id = Company.Company_id

SELECT * FROM sec_task

DROP VIEW sec_task

--3
CREATE VIEW trd_task AS
SELECT Department.Name AS 'отдел', AVG(Evaluation.STATUS) AS 'среднее'
FROM Department --, Evaluation
JOIN Cooperator ON Cooperator.Dept_id = Department.Dept_id
JOIN Evaluation ON Evaluation.Cooperator_id = Cooperator.Cooperator_id
GROUP BY Department.Name
HAVING AVG(Evaluation.STATUS) > 3;

SELECT * FROM trd_task

DROP VIEW trd_task

--4
CREATE VIEW fourth_task AS
SELECT Cooperator.name, Cooperator.Surname, Cooperator.Salary
FROM Cooperator ,(SELECT AVG(salary)ss, Department.Dept_id
                    FROM Department, Cooperator
                    WHERE Department.Dept_id = Cooperator.Dept_id
                    GROUP BY Department.Dept_id) t
WHERE Cooperator.Dept_id = t.Dept_id AND Cooperator.Salary > t.ss

SELECT * FROM fourth_task

DROP VIEW fourth_task

--5
CREATE VIEW fifth_task AS
SELECT Cooperator.Name, Cooperator.Surname, Company.City
FROM Cooperator, Company, Department
WHERE Cooperator.City=Company.City AND Department.Company_id=Company.Company_id AND Cooperator.Dept_id = Department.Dept_id

SELECT * FROM fifth_task

DROP VIEW fifth_task

--6
CREATE VIEW sixth_task AS
SELECT Name, Surname
FROM Cooperator
JOIN Evaluation ON Cooperator.Cooperator_id = Evaluation.Cooperator_id
WHERE STATUS = 5 WITH CHECK OPTION

SELECT * FROM sixth_task

DROP VIEW sixth_task

--7
CREATE VIEW seventh_task AS
SELECT Name, Short_name AS 'аббревиатура', Coefficent
FROM Company
WHERE Coefficent <=5 AND Coefficent >=0 WITH CHECK OPTION

SELECT * FROM seventh_task

--INSERT INTO seventh_task (Name, аббревиатура, Coefficent) это проверка, при выполнении должна
--VALUES('Globe inc', 'Globe', 10)                          упасть с ошибкой

DROP VIEW seventh_task

-- zashita
-- task: вывести тех сотрудников у которых последняя оценка была 5
CREATE OR ALTER  VIEW viewpr
AS
SELECT Cooperator.Cooperator_id, Cooperator.Name, Evaluation.STATUS
FROM Cooperator, Evaluation, (SELECT Evaluation.DATE, Cooperator.Cooperator_id
                                FROM Cooperator, Evaluation
                                WHERE Cooperator.Cooperator_id = Evaluation.Cooperator_id) t
WHERE Cooperator.Cooperator_id = t.Cooperator_id AND  Evaluation.STATUS = 5
GROUP BY Evaluation.DATE, Cooperator.Name, Evaluation.STATUS, Cooperator.Cooperator_id
HAVING Evaluation.DATE = MAX(t.DATE)