SQL AdventureWorks

-- ZADANIE 1

SELECT d.Name, COUNT(*)
FROM HumanResources.Department AS d
 JOIN HumanResources.EmployeeDepartmentHistory AS edh
 ON d.DepartmentID = edh.DepartmentID
WHERE edh.EndDate IS NULL

GROUP BY d.Name;



-- ZADANIE 2

SELECT eph.BusinessEntityID,eph.Rate
FROM HumanResources.EmployeePayHistory AS eph
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON eph.BusinessEntityID = edh.BusinessEntityID
WHERE (eph.RateChangeDate = (SELECT MAX(RateChangeDate) FROM HumanResources.EmployeePayHistory AS eph2 WHERE eph.BusinessEntityID = eph2.BusinessEntityID))
        AND (edh.EndDate IS NULL)



-- ZADANIE 3

SELECT d.Name, AVG(Rate)
FROM HumanResources.EmployeePayHistory AS eph
JOIN HumanResources.EmployeeDepartmentHistory AS edh
    ON eph.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
    ON d.DepartmentID = edh.DepartmentID
GROUP BY d.Name;



-- ZADANIE 4

SELECT eph.BusinessEntityID,eph.Rate
FROM HumanResources.EmployeePayHistory AS eph
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON eph.BusinessEntityID = edh.BusinessEntityID
WHERE (eph.RateChangeDate = (SELECT MAX(RateChangeDate) FROM HumanResources.EmployeePayHistory AS eph2 WHERE eph.BusinessEntityID = eph2.BusinessEntityID))
        AND (edh.EndDate IS NULL) AND (eph.Rate > (SELECT AVG(Rate) FROM HumanResources.EmployeePayHistory))
ORDER BY eph.Rate DESC


-- ZADANIE 5

CREATE VIEW HumanResources.AverageRates (DepartmentID, AverageRate)
AS
SELECT DepartmentID, AVG(Rate)
FROM HumanResources.EmployeeDepartmentHistory AS edh
JOIN HumanResources.EmployeePayHistory AS eph
    ON eph.BusinessEntityID = edh.BusinessEntityID
GROUP BY DepartmentID

SELECT eph.BusinessEntityID,edh.DepartmentID,Rate
FROM HumanResources.EmployeePayHistory AS eph
JOIN HumanResources.EmployeeDepartmentHistory AS edh
    ON eph.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.AverageRates ON AverageRates.DepartmentID = edh.DepartmentID
WHERE (eph.RateChangeDate = (SELECT MAX(RateChangeDate) FROM HumanResources.EmployeePayHistory AS eph2 WHERE eph.BusinessEntityID = eph2.BusinessEntityID))
        AND (edh.EndDate IS NULL) AND (eph.Rate > HumanResources.AverageRates.AverageRate)

ORDER BY eph.BusinessEntityID



-- ZADANIE 6
SELECT eph.BusinessEntityID, Diff = (SELECT MAX(Rate) FROM HumanResources.EmployeePayHistory) - eph.Rate
FROM HumanResources.EmployeePayHistory AS eph
WHERE (eph.RateChangeDate = (SELECT MAX(RateChangeDate) FROM HumanResources.EmployeePayHistory AS eph2 WHERE eph.BusinessEntityID = eph2.BusinessEntityID ))