test

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


Report Page