SHARE
TWEET

Untitled

a guest Jan 19th, 2020 68 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ----1. QUERY
  2.  
  3. SELECT FirstName,LastName, VacationHours, SickLeaveHours, (VacationHours+SickLeaveHours) AS SUM_OF_HOURS,
  4. DATEDIFF(YEAR,BirthDate,CAST(GETDATE() AS DATE)) AS YEARS_OLD
  5. FROM Person.Person INNER JOIN HumanResources.Employee
  6. ON Person.Person.BusinessEntityID=HumanResources.Employee.BusinessEntityID
  7. ORDER BY YEARS_OLD ASC;
  8.  
  9.  
  10. ----2. QUERY
  11.  
  12. SELECT FirstName, LastName, VacationHours, SickLeaveHours,
  13. RANK() OVER (ORDER BY VacationHours+SickLeaveHours DESC) HOURS_RANK, OrganizationLevel
  14. FROM Person.Person INNER JOIN HumanResources.Employee
  15. ON Person.Person.BusinessEntityID=HumanResources.Employee.BusinessEntityID;
  16.  
  17.  
  18. ----3. QUERY
  19.  
  20. SELECT DATENAME(dw,OrderDate), AVG(SubTotal) AS AVG_SubTotal, AVG(TaxAmt) AS AVG_TaxAmt, AVG(Freight) AS AVG_Freight
  21. FROM Sales.SalesOrderHeader
  22. GROUP BY DATENAME(dw,OrderDate),DATEPART(dw,OrderDate)
  23. ORDER BY DATEPART(dw,OrderDate);
  24.  
  25.  
  26. ----4. QUERY
  27.  
  28. SELECT (FirstName+', '+ COALESCE(MiddleName,'/')+', '+LastName) AS FULL_NAME,
  29. Sales.SalesTerritory.Name, AVG(SubTotal) AS AVG_SUBTOTAL, AVG(TaxAmt) AS AVG_TAX, AVG(Freight) AS AVG_FREIGHT
  30. FROM Person.Person INNER JOIN HumanResources.Employee
  31. ON Person.Person.BusinessEntityID=HumanResources.Employee.BusinessEntityID LEFT JOIN Sales.SalesOrderHeader
  32. ON HumanResources.Employee.BusinessEntityID=Sales.SalesOrderHeader.SalesPersonID LEFT JOIN Sales.SalesTerritory
  33. ON Sales.SalesOrderHeader.TerritoryID=Sales.SalesTerritory.TerritoryID
  34. WHERE Freight>(SELECT AVG(Freight) FROM Sales.SalesOrderHeader)
  35. GROUP BY (FirstName+', '+ COALESCE(MiddleName,'/')+', '+LastName),Sales.SalesTerritory.Name
  36. ORDER BY (FirstName+', '+ COALESCE(MiddleName,'/')+', '+LastName),Sales.SalesTerritory.Name
  37.  
  38. ----5. QUERY
  39.  
  40.  
  41. SELECT AVG(SubTotal) AS AVG_SUBTOTAL, AVG(TaxAmt) AS AVG_TAX,
  42. AVG(Freight) AS AVG_FREIGHT
  43. FROM Sales.SalesOrderHeader LEFT JOIN Sales.SalesPerson
  44. ON Sales.SalesOrderHeader.SalesPersonID=Sales.SalesPerson.BusinessEntityID LEFT JOIN Person.BusinessEntityAddress
  45. ON Sales.SalesPerson.BusinessEntityID=Person.BusinessEntityAddress.BusinessEntityID LEFT JOIN Person.Address
  46. ON Person.BusinessEntityAddress.AddressID=Person.Address.AddressID LEFT JOIN Person.StateProvince
  47. ON Person.Address.StateProvinceID=Person.StateProvince.StateProvinceID
  48. GROUP BY Sales.SalesOrderHeader.TerritoryID
  49. UNION
  50. SELECT AVG(SubTotal) AS AVG_SUBTOTAL, AVG(TaxAmt) AS AVG_TAX,
  51. AVG(Freight) AS AVG_FREIGHT
  52. FROM Sales.SalesOrderHeader LEFT JOIN Sales.SalesPerson
  53. ON Sales.SalesOrderHeader.SalesPersonID=Sales.SalesPerson.BusinessEntityID LEFT JOIN Person.BusinessEntityAddress
  54. ON Sales.SalesPerson.BusinessEntityID=Person.BusinessEntityAddress.BusinessEntityID LEFT JOIN Person.Address
  55. ON Person.BusinessEntityAddress.AddressID=Person.Address.AddressID LEFT JOIN Person.StateProvince
  56. ON Person.Address.StateProvinceID=Person.StateProvince.StateProvinceID
  57. GROUP BY Sales.SalesOrderHeader.TerritoryID,Person.StateProvince.StateProvinceID
  58. UNION
  59. SELECT AVG(SubTotal) AS AVG_SUBTOTAL, AVG(TaxAmt) AS AVG_TAX,
  60. AVG(Freight) AS AVG_FREIGHT
  61. FROM Sales.SalesOrderHeader LEFT JOIN Sales.SalesPerson
  62. ON Sales.SalesOrderHeader.SalesPersonID=Sales.SalesPerson.BusinessEntityID LEFT JOIN Person.BusinessEntityAddress
  63. ON Sales.SalesPerson.BusinessEntityID=Person.BusinessEntityAddress.BusinessEntityID LEFT JOIN Person.Address
  64. ON Person.BusinessEntityAddress.AddressID=Person.Address.AddressID LEFT JOIN Person.StateProvince
  65. ON Person.Address.StateProvinceID=Person.StateProvince.StateProvinceID
  66. GROUP BY Person.StateProvince.StateProvinceID,Person.StateProvince.CountryRegionCode
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top