/**Vezbe nad bazom AdventureWorks 2016**/
/*Rad sa NULL vrednostima***/
	SELECT Name, Weight, Color
	FROM Production.Product
	WHERE Weight < 10.00 OR Color IS NULL
	ORDER BY Name

	SELECT Description, DiscountPct, MinQty, MaxQty AS 'Max Quantity'	
     FROM Sales.SpecialOffer
/*******/
	SELECT Description, DiscountPct, MinQty, ISNULL(MaxQty, 0) AS 'Max Quantity'
	FROM Sales.SpecialOffer

/*****/
  SELECT ProductID, MakeFlag, FinishedGoodsFlag 
  FROM Production.Product WHERE ProductID < 10

 /****/   
 SELECT ProductID, MakeFlag, FinishedGoodsFlag,
 NULLIF(MakeFlag,FinishedGoodsFlag)AS 'Null ako su jednake'	
 FROM Production.Product WHERE ProductID < 10

 /****/
 SELECT Name, Class, Color, ProductNumber
 FROM Production.Product
 /*****/
 SELECT Name, Class, Color, ProductNumber,
 COALESCE(Class, Color, ProductNumber) AS PrviKojiNijeNull
 FROM Production.Product

 /**Sortiranje***/
 SELECT ProductID, ProductSubcategoryID, ListPrice
 FROM Production.Product
 ORDER BY ProductSubcategoryID DESC, ListPrice

 /***Eliminisanje duplikata***/
 SELECT JobTitle
 FROM HumanResources.Employee
 ORDER BY JobTitle

 SELECT DISTINCT JobTitle
 FROM HumanResources.Employee
 ORDER BY JobTitle

 /***Filtracija podataka nad bazom Northwind****/

SELECT TOP (5) orderid, customerid, orderdate
FROM Orders
ORDER BY orderdate DESC

SELECT TOP (5) WITH TIES orderid, customerid, orderdate
FROM Orders
ORDER BY orderdate DESC

SELECT TOP (10) PERCENT orderid, customerid, orderdate
FROM Orders
ORDER BY orderdate DESC;
GO

SELECT orderid, customerid, orderdate
FROM Orders
ORDER BY orderdate, orderid DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

SELECT orderid, customerid, orderdate
FROM Orders
ORDER BY orderdate DESC
OFFSET 0 ROWS FETCH FIRST 50 ROWS ONLY;

SELECT orderid, customerid, orderdate
FROM Orders
ORDER BY orderdate DESC
OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY;

/******Window funkcije za rad nad bazom Nortwind**/
SELECT productid, productname, unitprice,
	RANK() OVER(ORDER BY unitprice DESC) AS pricerank
FROM Products
ORDER BY pricerank

SELECT categoryid,productid, unitprice,
	RANK() OVER(PARTITION BY categoryid ORDER BY unitprice DESC) AS pricerank
FROM Products 
ORDER BY categoryid, pricerank;
 
SELECT o.CustomerID,
	month(o.[OrderDate]) as mesec,
	 od.[Quantity],
	SUM(od.[Quantity]) OVER ( PARTITION BY o.CustomerID ) AS ukupnonarcuneno
FROM [Order Details] od inner join [Orders] o on od.OrderID=o.OrderID;

SELECT o.customerid, month(o.[OrderDate]) as mesec, od.[Quantity],
	SUM(od.[Quantity]) OVER ( PARTITION BY o.customerid ) AS subtotal,
	CAST(100. * od.[Quantity]/SUM(od.[Quantity]) OVER ( PARTITION BY o.customerid )AS NUMERIC(8,2)) AS OfTotal
FROM [Order Details] od inner join [Orders] o on od.OrderID=o.OrderID;

/****AdventureWorks 2016***/
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity  
    ,DENSE_RANK() OVER   
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank  
FROM Production.ProductInventory AS i   
INNER JOIN Production.Product AS p   
    ON i.ProductID = p.ProductID  
WHERE i.LocationID BETWEEN 3 AND 4  
ORDER BY i.LocationID;  
GO  

SELECT p.FirstName, p.LastName  
    ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number"  
    ,RANK() OVER (ORDER BY a.PostalCode) AS Rank  
    ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank"  
    ,NTILE(4) OVER (ORDER BY a.PostalCode) AS Quartile  
    ,s.SalesYTD  
    ,a.PostalCode  
FROM Sales.SalesPerson AS s   
    INNER JOIN Person.Person AS p   
        ON s.BusinessEntityID = p.BusinessEntityID  
    INNER JOIN Person.Address AS a   
        ON a.AddressID = p.BusinessEntityID  
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;