lecture 4 sql

--la zaba

/*

SELECT Name, Surname, Height, Ave
FROM Persons
JOIN
(
SELECT  Department_ID ,AVG(Height) AS ave
FROM persons
GROUP BY Department_ID
) AS xxx
ON Persons.Department_ID=xxx.Department_ID
WHERE Height > Ave

*/
/*
SELECT Department_name, Surname, Height, Ave
FROM Departments JOIN Persons
ON Persons.Department_ID = Persons.Department_ID
JOIN
(
SELECT  Department_ID ,AVG(Height) AS ave
FROM persons
GROUP BY Department_ID
) AS xxx
ON Persons.Department_ID=xxx.Department_ID
WHERE Height > Ave
*/
/*
SELECT Department_name, Surname, Height, Ave
FROM Departments JOIN
Persons
ON Persons.Department_ID = Departments.Department_ID
JOIN
(
SELECT  Department_ID ,AVG(Height) AS ave
FROM persons
GROUP BY Department_ID
) AS xxx
ON Persons.Department_ID=xxx.Department_ID
WHERE Height > Ave
ORDER BY Department_Name
*/

/*SELECT Department_name, Surname, Height, Ave
FROM Departments JOIN
Persons
ON Persons.Department_ID = Departments.Department_ID
JOIN
(
SELECT  Department_ID ,AVG(Height) AS ave
FROM persons
GROUP BY Department_ID
) AS xxx
ON Persons.Department_ID=xxx.Department_ID
AND Height = Ave
--WHERE Height > Ave
ORDER BY Department_Name
*/
/* (check it )
SELECT Surname, Name, AVG(salary) AS Ave, DepAve
FROM Salaries  JOIN Persons
ON Persons.Person_ID = Salaries.Person_ID
 JOIN
 (SELECT Department_ID, AVG(Salary) AS DepAve
FROM Persons JOIN Salaries
ON Persons.Person_ID = Salaries.Person_ID
GROUP BY Department_ID) As xxx
ON Persons.Department_ID = xxx.Department_ID
GROUP BY Surname, Name, DepAve
HAVING  AVG(salary) , DepAve
*/

/*
SELECT Surname, Department_Name, Name,  AVG(salary) AS Ave, DepAve
FROM Salaries  JOIN Persons
ON Persons.Person_ID = Salaries.Person_ID
 JOIN Departments
 ON Persons.Department_ID = Departments.Department_ID
 JOIN
 (SELECT Department_ID, AVG(Salary) AS DepAve
FROM Persons JOIN Salaries
ON Persons.Person_ID = Salaries.Person_ID
GROUP BY Department_ID) As xxx
ON Persons.Department_ID = xxx.Department_ID
GROUP BY  Department_Name,Surname, Name, DepAve
HAVING  AVG(salary) > DepAve
*/

/*
SELECT Surname, Department_Name, Name,  SUM(salary) AS EmpTotal , DepTotal,
(
SELECT SUM(Salary) As Total
FROM Salaries) AS TotalTotal

FROM Salaries  JOIN Persons
ON Persons.Person_ID = Salaries.Person_ID
 JOIN Departments
 ON Persons.Department_ID = Departments.Department_ID
 JOIN
 (SELECT Department_ID, AVG(Salary) AS DepTotal
FROM Persons JOIN Salaries
ON Persons.Person_ID = Salaries.Person_ID
GROUP BY Department_ID) As xxx
ON Persons.Department_ID = xxx.Department_ID
GROUP BY  Department_Name,Surname, Name, DepTotal
*/

SELECT Surname, Department_Name, Name,  SUM(salary) AS EmpTotal , DepTotal,
(
SELECT SUM(Salary) AS Total
FROM Salaries) AS TotalTotal

FROM Salaries  JOIN Persons
ON Persons.Person_ID = Salaries.Person_ID
 JOIN Departments
 ON Persons.Department_ID = Departments.Department_ID
 JOIN
 (SELECT Department_ID, AVG(Salary) AS DepTotal
FROM Persons JOIN Salaries
ON Persons.Person_ID = Salaries.Person_ID
GROUP BY Department_ID) AS xxx
ON Persons.Department_ID = xxx.Department_ID
GROUP BY  Department_Name,Surname, Name, DepTotal