sql-vjezba5

--1

USE AdventureWorks2014
GO
SELECT P.FirstName+' '+P.LastName AS [Vlasnik kartice],
       CC.CardType AS [Tip],
       CC.CardNumber AS [Broj kartice],
       CONVERT(VARCHAR,ROUND(SUM(SOH.TotalDue),2))+' KM' AS [Ukupno plać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 PCC.CreditCardID=SOH.CreditCardID
WHERE 20<(SELECT COUNT(SOH.SalesOrderID)
          FROM Sales.SalesOrderHeader AS SOH
          WHERE SOH.CreditCardID=CC.CreditCardID)
GROUP BY P.FirstName,P.LastName,CC.CardType,CC.CardNumber
ORDER BY [Vlasnik kartice]



--2



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



--3


USE AdventureWorks2014
GO
SELECT Kupac,
       CONVERT(VARCHAR,Ukupno)+' KM'
FROM (SELECT P.FirstName+' '+P.LastName AS Kupac,
             ROUND(SUM(SOH.TotalDue),2) AS Ukupno
      FROM Sales.Customer AS C
           INNER JOIN Sales.SalesOrderHeader AS SOH ON C.CustomerID=SOH.CustomerID
           INNER JOIN Person.Person AS P ON C.PersonID=P.BusinessEntityID
      WHERE MONTH(SOH.OrderDate)=7
      GROUP BY P.FirstName,P.LastName) AS T
WHERE Ukupno>200000
ORDER BY Ukupno DESC



--4


USE AdventureWorks2014
GO
SELECT T.Kupac,
       T.[Broj narudžbi]
FROM (SELECT P.FirstName+' '+P.LastName AS Kupac,
             (SELECT COUNT(SOH.SalesOrderID) FROM Sales.SalesOrderHeader AS SOH WHERE C.CustomerID=SOH.CustomerID) AS [Broj narudžbi]
      FROM Sales.Customer AS C
           INNER JOIN Person.Person AS P ON C.PersonID=P.BusinessEntityID)AS T
WHERE T.[Broj narudžbi]>20
ORDER BY T.[Broj narudžbi] DESC


--5



USE AdventureWorks2014
GO
SELECT T.[Ime proizvoda],
       T.Lokacija,
       T.[Količina na skladištu],
       T.[Prodana količina]
FROM (SELECT P.Name AS [Ime proizvoda],
             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 )AS T
WHERE T.[Količina na skladištu]<30



--6

USE AdventureWorks2014
GO
SELECT P.FirstName+' '+P.LastName AS Zaspolenik,
       RIGHT(E.LoginID,LEN(E.LoginID)-CHARINDEX('\',E.LoginID)) AS [Korisničko ime],
         REPLACE(SUBSTRING((REVERSE(RIGHT(E.LoginID,LEN(E.LoginID)-CHARINDEX('\',E.LoginID)))+REVERSE(E.JobTitle)),5,8),SUBSTRING(SUBSTRING((REVERSE(RIGHT(E.LoginID,LEN(E.LoginID)-CHARINDEX('\',E.LoginID)))+REVERSE(E.JobTitle)),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
WHERE E.Gender LIKE 'F' AND  DATEDIFF(YEAR,E.BirthDate,GETDATE())>50 AND DATEDIFF(YEAR,E.HireDate,GETDATE()) >5




--7


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