test
--1
SELECT DISTINCT FirstName FROM Person.Person
ORDER BY FirstName ASC
--2
SELECT * FROM Production.Product
WHERE ProductNumber LIKE 'F%'
ORDER BY ProductID DESC
--3
--Quantity, Shelf, modified between 1600 - 2700, location of productinv, days past since modif
--AS Names, order
SELECT Quantity, Shelf, Name, DATEDIFF(day, ProductInventory.ModifiedDate, GETDATE()) AS [Modifed n Days ago]
FROM [Production].[ProductInventory]
JOIN Production.Location
ON Production.ProductInventory.LocationID = Production.Location.LocationID
WHERE DATEDIFF(day, ProductInventory.ModifiedDate, GETDATE()) > 1600 AND DATEDIFF(day, ProductInventory.ModifiedDate, GETDATE()) < 2700
ORDER BY [Modifed n Days ago]
--4 Color, amount, total list price
SELECT Color, COUNT(ProductID) AS [Number of Products], SUM(ListPrice) AS [Total Price]
FROM Production.Product
GROUP BY Color
HAVING Color IS NOT NULL
ORDER BY [Number of Products] DESC
--5 EmployeeID, FirstName + LastName, Vacation Hours, AVG all emp hours, order by desc, AS names
SELECT HumanResources.Employee.BusinessEntityID AS [Employee ID], CONCAT(FirstName, LastName) AS [Full Name],
VacationHours AS [Vacation Hours], AVG(VacationHours) OVER() AS [Average Vacation Hours]
FROM Person.Person
JOIN HumanResources.Employee
ON HumanResources.Employee.BusinessEntityID = Person.BusinessEntityID
ORDER BY VacationHours DESC
--6 Sum of SubTotal, AVG Tax Amount, Max Freight Cost of Sales Orders
SELECT SUM(SubTotal) AS SubTotal, AVG(TaxAmt) AS [Average Tax], MAX(Freight) AS Freight, Name AS [Territory Name]
FROM Sales.SalesOrderHeader
JOIN Sales.SalesTerritory
ON Sales.SalesOrderHeader.TerritoryID = Sales.SalesTerritory.TerritoryID
WHERE OrderDate BETWEEN '2014-03-29 00:00:00' AND GETDATE()
GROUP BY Sales.SalesTerritory.Name