VJEZBE5.1

--1


USE AdventureWorks2014
GO
SELECT P.FirstName+' '+P.LastName AS Vlasnik,
       CC.CardType AS [Tip kartice],
       CC.CardNumber AS [Broj kartice],
       CONVERT(VARCHAR,ROUND(SUM(SOH.TotalDue),2))+' KM' AS [Ukupno utrošeno]
FROM Person.Person AS P
     INNER JOIN Sales.PersonCreditCard AS PCC ON P.BusinessEntityID=PCC.BusinessEntityID
     INNER JOIN Sales.CreditCard AS CC ON PCC.CreditCardID=CC.CreditCardID
     INNER JOIN Sales.SalesOrderHeader AS SOH ON CC.CreditCardID=SOH.CreditCardID
GROUP BY P.FirstName,P.LastName,CC.CardType,CC.CardNumber
HAVING COUNT(SOH.CreditCardID)>20



--2


USE AdventureWorks2014
GO
SELECT C.CustomerID AS Kupac
FROM Sales.Customer AS C
     INNER JOIN Sales.SalesOrderHeader AS SOH ON C.CustomerID=SOH.CustomerID
     INNER JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID=SOD.SalesOrderID
WHERE YEAR(SOH.OrderDate)=2014 AND MONTH(SOH.OrderDate)=5
      AND (SELECT SOD.OrderQty FROM Production.Product AS P
           WHERE SOD.ProductID=P.ProductID AND P.Name LIKE 'front brakes')>5


--3


USE AdventureWorks2014
GO
SELECT P.FirstName+' '+P.LastName AS [Kupac],
       ROUND(SUM(SOH.TotalDue),2) AS [Ukupno utrošeno]
FROM Sales.Customer AS C
     INNER JOIN Person.Person AS P ON C.PersonID=P.BusinessEntityID
     INNER JOIN Sales.SalesOrderHeader AS SOH ON C.CustomerID=SOH.CustomerID
WHERE MONTH(SOH.OrderDate)=7 --AND (SELECT SUM(SOH.TotalDue) FROM Sales.SalesOrderHeader AS SOH WHERE C.CustomerID=SOH.CustomerID)>200000
GROUP BY P.FirstName,P.LastName
HAVING ROUND(SUM(SOH.TotalDue),2)>200000
ORDER BY [Ukupno utrošeno] DESC



--4


USE AdventureWorks2014
GO
SELECT P.FirstName+' '+P.LastName AS Zaspolenik,
       COUNT(SOH.SalesOrderID) AS [Broj narudžbi]
FROM HumanResources.Employee AS E
     INNER JOIN Person.Person AS P ON E.BusinessEntityID=P.BusinessEntityID
     INNER JOIN Sales.SalesOrderHeader AS SOH ON E.BusinessEntityID=SOH.SalesPersonID
GROUP BY P.FirstName,P.LastName
HAVING COUNT(SOH.SalesOrderID)>200
ORDER BY [Broj narudžbi] DESC




--5


USE AdventureWorks2014
GO
SELECT P.Name AS Proizvod,
       L.Name AS Lokacija,
       SUM(PRI.Quantity) AS [Količina na skladištu],
       SUM(ISNULL(SOD.OrderQty,0)) AS [Prodana količina]
FROM Production.Product AS P
     INNER JOIN Production.ProductInventory AS PRI ON P.ProductID=PRI.ProductID
     INNER JOIN Production.Location AS L ON PRI.LocationID=L.LocationID
     LEFT JOIN Sales.SalesOrderDetail AS SOD ON P.ProductID=SOD.ProductID
GROUP BY P.Name,L.Name
HAVING SUM(PRI.Quantity)<30




--6



USE AdventureWorks2014
GO
SELECT P.FirstName+' '+P.LastName AS Zaposlenik,
       RIGHT(E.LoginID,LEN(E.LoginID)-CHARINDEX('\',E.LoginID)) AS [Korisničko ime],
       --REPLACE(SUBSTRING(REVERSE(PASS.PasswordHash),5,8),SUBSTRING(SUBSTRING(REVERSE(PASS.PasswordHash),5,8),2,2),'X#') AS Lozinka,
       STUFF(SUBSTRING(REVERSE(PASS.PasswordHash),5,8),2,2,'X#') AS Lozinka,
       DATEDIFF(YEAR,E.BirthDate,GETDATE()) AS Starost,
       DATEDIFF(YEAR,E.HireDate,GETDATE()) AS Staž
FROM HumanResources.Employee AS E
     INNER JOIN Person.Person AS P ON E.BusinessEntityID=P.BusinessEntityID
     INNER JOIN Person.Password AS PASS ON P.BusinessEntityID=PASS.BusinessEntityID
WHERE E.Gender LIKE 'F' AND  DATEDIFF(YEAR,E.BirthDate,GETDATE())>50 AND  DATEDIFF(YEAR,E.HireDate,GETDATE())>5



--7


USE AdventureWorks2014
GO
SELECT ST.Name AS Teritorija,
       SUM(SOD.OrderQty) AS [Ukupna prodana količina],
       ROUND(SUM(SOH.TotalDue),2) AS [Ukupna zarada]
FROM Sales.SalesOrderDetail AS SOD
     INNER JOIN Sales.SalesOrderHeader AS SOH ON SOD.SalesOrderID=SOH.SalesOrderID
     INNER JOIN Sales.SpecialOffer AS SO ON SOD.SpecialOfferID=SO.SpecialOfferID
     INNER JOIN Sales.SalesTerritory AS ST ON SOH.TerritoryID=ST.TerritoryID
WHERE SO.Description LIKE 'volume discount 11 to 14'
GROUP BY ST.Name



--8


USE AdventureWorks2014
GO
SELECT TOP 1 T.Plata
FROM (SELECT TOP 4 ROUND(EPH.Rate,2) AS Plata
      FROM HumanResources.EmployeePayHistory AS EPH
      ORDER BY Plata DESC) AS T
ORDER BY T.Plata



--9


USE AdventureWorks2014
GO
SELECT P.Name AS Proizvod,
       L.Name AS Lokacija,
       PRI.Quantity AS [Količina],
       (SELECT SUM(PRI.Quantity) FROM Production.ProductInventory AS PRI WHERE P.ProductID=PRI.ProductID) AS [Ukupna količina],
       (SELECT ISNULL(SUM(SOD.OrderQty),0)
         FROM Sales.SalesOrderDetail AS SOD
         INNER JOIN Sales.SalesOrderHeader AS SOH ON SOD.SalesOrderID=SOH.SalesOrderID
         WHERE P.ProductID=SOD.ProductID AND YEAR(SOH.OrderDate)=2013  ) AS [Ukupno prodano]
FROM Production.Product AS P
     INNER JOIN Production.ProductInventory AS PRI ON P.ProductID=PRI.ProductID
     INNER JOIN Production.Location AS L ON PRI.LocationID=L.LocationID


--10


USE AdventureWorks2014
GO
SELECT T.Kupac,T.[Tip kartice],T.[Broj kartice],T.[Ukupno utrošeno]
FROM(SELECT P.FirstName+' '+P.LastName Kupac,
       CC.CardType AS [Tip kartice],
       CC.CardNumber AS [Broj kartice],
       --(SELECT COUNT(PCC.CreditCardID) FROM Sales.PersonCreditCard AS PCC WHERE P.BusinessEntityID=PCC.BusinessEntityID) AS [Ukupno kartica],
       (SELECT ISNULL(ROUND(SUM(SOH.TotalDue),2),0) FROM Sales.SalesOrderHeader AS SOH WHERE C.CustomerID=SOH.CustomerID AND SOH.CreditCardID IS NOT NULL) AS [Ukupno utrošeno]
FROM Sales.Customer AS C
     INNER JOIN Person.Person AS P ON C.PersonID=P.BusinessEntityID
     INNER JOIN Sales.PersonCreditCard AS PCC ON P.BusinessEntityID=PCC.BusinessEntityID
     INNER JOIN Sales.CreditCard AS CC ON PCC.CreditCardID=CC.CreditCardID
     LEFT JOIN Sales.SalesOrderHeader AS SOH ON CC.CreditCardID=SOH.CreditCardID)AS T
--WHERE T.[Ukupno kartica]>1
ORDER BY T.Kupac,T.[Ukupno utrošeno] DESC