boob
--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(month, ProductInventory.ModifiedDate, GETDATE()) AS [Modifed n Days ago]
FROM [Production].[ProductInventory]
JOIN Production.Location
ON Production.ProductInventory.LocationID = Production.Location.LocationID
WHERE DATEDIFF(month, ProductInventory.ModifiedDate, GETDATE()) BETWEEN 45 AND 53
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
HAVING SUM(SubTotal) > 1100000
--7
CREATE VIEW ViewName AS
SELECT * FROM Person.Person
/* When can a view be updated?
НЕ КОПИРУЙ, НАПИШИ ОДНУ-ДВЕ ПРИЧИНЫ, КОТОРЫЕ У ТЕБЯ ЕСТЬ
1. The view is defined based on one and only one table.
2. The view must include the PRIMARY KEY of the table based upon which the view has been created.
3. The view should not have any field made out of aggregate functions.
4. The view must not have any DISTINCT clause in its definition.
5. The view must not have any GROUP BY or HAVING clause in its definition.
6. The view must not have any SUBQUERIES in its definitions.
7. If the view you want to update is based upon another view, the later should be updatable.
8. Any of the selected output fields (of the view) must not use constants, strings or value expressions. */
--8 NationalIDNumber, JobTitle, FirstName, LastName, DepartmentName
SELECT FirstName, LastName, JobTitle, NationalIDNumber, Name AS [Department Name]
FROM Person.Person
JOIN HumanResources.Employee
ON Person.Person.BusinessEntityID = HumanResources.Employee.BusinessEntityID
JOIN HumanResources.EmployeeDepartmentHistory
ON HumanResources.Employee.BusinessEntityID = HumanResources.EmployeeDepartmentHistory.BusinessEntityID
JOIN HumanResources.Department
ON HumanResources.EmployeeDepartmentHistory.DepartmentID = HumanResources.Department.DepartmentID
WHERE HumanResources.Employee.BusinessEntityID <> All(SELECT BusinessEntityID FROM Sales.SalesPerson)
--9
SELECT Production.ProductCostHistory.ProductID, Product.Name,
Product.StandardCost, StartDate, EndDate,
MIN(Product.StandardCost) OVER(PARTITION BY Product.ProductID),
MAX(Product.StandardCost) OVER(PARTITION BY Product.ProductID)
FROM Production.ProductCostHistory
JOIN Production.Product ON Production.ProductCostHistory.ProductID = Product.ProductID