----1. QUERY

SELECT FirstName,LastName, VacationHours, SickLeaveHours, (VacationHours+SickLeaveHours) AS SUM_OF_HOURS,
DATEDIFF(YEAR,BirthDate,CAST(GETDATE() AS DATE)) AS YEARS_OLD
FROM Person.Person INNER JOIN HumanResources.Employee
ON Person.Person.BusinessEntityID=HumanResources.Employee.BusinessEntityID
ORDER BY YEARS_OLD ASC;


----2. QUERY

SELECT FirstName, LastName, VacationHours, SickLeaveHours,
RANK() OVER (ORDER BY VacationHours+SickLeaveHours DESC) HOURS_RANK, OrganizationLevel
FROM Person.Person INNER JOIN HumanResources.Employee
ON Person.Person.BusinessEntityID=HumanResources.Employee.BusinessEntityID;


----3. QUERY

SELECT DATENAME(dw,OrderDate), AVG(SubTotal) AS AVG_SubTotal, AVG(TaxAmt) AS AVG_TaxAmt, AVG(Freight) AS AVG_Freight
FROM Sales.SalesOrderHeader
GROUP BY DATENAME(dw,OrderDate),DATEPART(dw,OrderDate)
ORDER BY DATEPART(dw,OrderDate);


----4. QUERY

SELECT (FirstName+', '+ COALESCE(MiddleName,'/')+', '+LastName) AS FULL_NAME,
Sales.SalesTerritory.Name, AVG(SubTotal) AS AVG_SUBTOTAL, AVG(TaxAmt) AS AVG_TAX, AVG(Freight) AS AVG_FREIGHT
FROM Person.Person INNER JOIN HumanResources.Employee
ON Person.Person.BusinessEntityID=HumanResources.Employee.BusinessEntityID LEFT JOIN Sales.SalesOrderHeader
ON HumanResources.Employee.BusinessEntityID=Sales.SalesOrderHeader.SalesPersonID LEFT JOIN Sales.SalesTerritory
ON Sales.SalesOrderHeader.TerritoryID=Sales.SalesTerritory.TerritoryID
WHERE Freight>(SELECT AVG(Freight) FROM Sales.SalesOrderHeader)
GROUP BY (FirstName+', '+ COALESCE(MiddleName,'/')+', '+LastName),Sales.SalesTerritory.Name
ORDER BY (FirstName+', '+ COALESCE(MiddleName,'/')+', '+LastName),Sales.SalesTerritory.Name

----5. QUERY


SELECT AVG(SubTotal) AS AVG_SUBTOTAL, AVG(TaxAmt) AS AVG_TAX,
AVG(Freight) AS AVG_FREIGHT
FROM Sales.SalesOrderHeader LEFT JOIN Sales.SalesPerson
ON Sales.SalesOrderHeader.SalesPersonID=Sales.SalesPerson.BusinessEntityID LEFT JOIN Person.BusinessEntityAddress
ON Sales.SalesPerson.BusinessEntityID=Person.BusinessEntityAddress.BusinessEntityID LEFT JOIN Person.Address
ON Person.BusinessEntityAddress.AddressID=Person.Address.AddressID LEFT JOIN Person.StateProvince
ON Person.Address.StateProvinceID=Person.StateProvince.StateProvinceID
GROUP BY Sales.SalesOrderHeader.TerritoryID
UNION
SELECT AVG(SubTotal) AS AVG_SUBTOTAL, AVG(TaxAmt) AS AVG_TAX,
AVG(Freight) AS AVG_FREIGHT
FROM Sales.SalesOrderHeader LEFT JOIN Sales.SalesPerson
ON Sales.SalesOrderHeader.SalesPersonID=Sales.SalesPerson.BusinessEntityID LEFT JOIN Person.BusinessEntityAddress
ON Sales.SalesPerson.BusinessEntityID=Person.BusinessEntityAddress.BusinessEntityID LEFT JOIN Person.Address
ON Person.BusinessEntityAddress.AddressID=Person.Address.AddressID LEFT JOIN Person.StateProvince
ON Person.Address.StateProvinceID=Person.StateProvince.StateProvinceID
GROUP BY Sales.SalesOrderHeader.TerritoryID,Person.StateProvince.StateProvinceID
UNION
SELECT AVG(SubTotal) AS AVG_SUBTOTAL, AVG(TaxAmt) AS AVG_TAX,
AVG(Freight) AS AVG_FREIGHT
FROM Sales.SalesOrderHeader LEFT JOIN Sales.SalesPerson
ON Sales.SalesOrderHeader.SalesPersonID=Sales.SalesPerson.BusinessEntityID LEFT JOIN Person.BusinessEntityAddress
ON Sales.SalesPerson.BusinessEntityID=Person.BusinessEntityAddress.BusinessEntityID LEFT JOIN Person.Address
ON Person.BusinessEntityAddress.AddressID=Person.Address.AddressID LEFT JOIN Person.StateProvince
ON Person.Address.StateProvinceID=Person.StateProvince.StateProvinceID
GROUP BY Person.StateProvince.StateProvinceID,Person.StateProvince.CountryRegionCode