Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement