BPII - Vjezba 4

/*Iz vježbe 3, zadatke od broja 3-7 uraditi primjenom JOIN operatora!*/

/*3. Kreirati upit koji prikazuje ukupan broj kupaca po teritoriji. Lista treba da sadrži sljedeće kolone:
ID teritorije, ukupan broj kupaca. Uzeti u obzir samo teritorije gdje ima više od 1000 kupaca.*/

SELECT COUNT (sc.CustomerID) AS [Ukupan broj kupaca], sc.TerritoryID AS [ID teritorije]
FROM Person.Person AS pp
INNER JOIN Sales.Customer AS sc ON pp.BusinessEntityID=sc.PersonID
GROUP BY sc.TerritoryID
HAVING COUNT (sc.CustomerID)>1000
ORDER BY COUNT (sc.CustomerID) DESC

/*4. Prikazati ukupan broj proizvoda po modelu. Lista treba da sadrži ID modela proizvoda i ukupan broj proizvoda.
Uslov je da proizvod pripada nekom modelu i da je ukupan broj proizvoda po modelu veći od 1.
Također, prebrojati samo proizvode čiji naziv počinje slovom 'S'.*/

SELECT COUNT (pp.ProductID) AS [Ukupan broj proizvoda], ppm.Name
FROM Production.Product AS pp
INNER JOIN Production.ProductModel AS ppm ON pp.ProductModelID=ppm.ProductModelID
WHERE pp.Name LIKE 'S%' AND pp.ProductModelID IS NOT NULL
GROUP BY ppm.Name
HAVING COUNT (pp.ProductID)>1

/*5. Kreirati upit koji prikazuje 10 najprodavanijih proizvoda. Lista treba da sadrži ID proizvoda i ukupnu količinu prodaje.
Provjeriti da li ima proizvoda sa istom količinom prodaje kao zapis pod rednim brojem 10?*/

SELECT TOP 10 WITH TIES pp.Name, SUM(ppod.OrderQty) AS [Ukupna kolicina prodaje], pp.ProductID
FROM Purchasing.PurchaseOrderDetail AS ppod
INNER JOIN Production.Product AS pp ON ppod.ProductID=pp.ProductID
GROUP BY pp.ProductID, pp.Name
ORDER BY pp.Name

/*6. Kreirati upit koji prikazuje zaradu od prodaje proizvoda. Lista treba da sadrži ID proizvoda, ukupnu zaradu bez uračunatog popusta
i ukupnu zaradu sa uračunatim popustom. Iznos zarade zaokružiti na dvije decimale. Uslov je da se prikaže zarada samo za stavke
gdje je bilo popusta. Listu sortirati po zaradi opadajućim redoslijedom.*/

SELECT pp.Name, ROUND(SUM(ssod.UnitPrice),2) AS [Zarada bez popusta],
        SUM(ssod.UnitPrice)-COUNT(ssod.OrderQty)*SUM(ssod.UnitPriceDiscount) AS [Zarada s popustom]
FROM Sales.SalesOrderDetail AS ssod
INNER JOIN Production.Product AS pp ON ssod.ProductID=pp.ProductID
WHERE ssod.UnitPriceDiscount>0
GROUP BY pp.Name
ORDER BY SUM(ssod.UnitPrice) DESC

/*7. Prikazati broj narudžbe, datum narudžbe i datum isporuke za narudžbe koje su isporučene u Kanadu u 7. mjesecu 2014. godine.
Uzeti u obzir samo narudžbe koje nisu plaćene kreditnom karticom. Datume formatirati u sljedećem obliku: dd.mm.yyyy*/

SELECT ssoh.SalesOrderID AS [Broj narudzbe], CONVERT(varchar,ssoh.OrderDate,104) AS [Datum narudzbe],
        CONVERT(varchar,ssoh.ShipDate,104) AS [Datum isporuke]
FROM Sales.SalesOrderHeader AS ssoh
INNER JOIN Sales.SalesTerritory AS sst ON ssoh.TerritoryID=sst.TerritoryID
WHERE MONTH(ssoh.ShipDate)=7 AND YEAR(ssoh.ShipDate)=2014 AND ssoh.CreditCardApprovalCode IS NOT NULL

--****************************************************************************************************************************

/*1. Kreirati upit koji prikazuje ime i prezime, korisničko ime (sve iza znaka „\“ u koloni LoginID), dužinu korisničkog imena, titulu,
datum zaposlenja (dd.MM.yyyy), starost i staž zaposlenika. Uslov je da se prikaže 10 najstarijih zaposlenika koji obavljaju bilo
koju ulogu menadžera.*/

SELECT TOP 10 pp.FirstName + ' ' + pp.LastName AS [Ime i prezime],
        SUBSTRING(hre.LoginID,17,10) AS [LoginID],
        LEN(SUBSTRING(hre.LoginID, 17,10)) AS [Duzina korisnckog imena],
        hre.JobTitle AS [Titula],
        CONVERT(varchar,hre.HireDate,104) AS [Datum zaposlenja],
        DATEPART(YEAR,SYSDATETIME())-DATEPART(YEAR,hre.BirthDate) AS [Satrost],
        DATEPART(YEAR, SYSDATETIME())-DATEPART(YEAR, hre.HireDate) AS [Staz zaposlenika]
FROM Person.Person AS pp
INNER JOIN HumanResources.Employee AS hre ON pp.BusinessEntityID=hre.BusinessEntityID
WHERE hre.JobTitle LIKE '%Manager%'
ORDER BY DATEPART(YEAR,SYSDATETIME())-DATEPART(YEAR,hre.BirthDate) DESC

/*2. Kreirati upit koji prikazuje 10 najskupljih stavki prodaje (detalji narudžbe) i to sljedeće kolone:
naziv proizvoda, količina, cijena, iznos. Cijenu i iznos zaokružiti na dvije decimale. Također, količinu prikazati u formatu „10 kom.“,
a cijenu i iznos u formatu „1000 KM“.*/

SELECT TOP 10 pp.Name AS [Naziv proizvoda],
            CONVERT(nvarchar,ssod.OrderQty) + ' kom' AS [Kolicina],
            CONVERT(nvarchar,ROUND([UnitPrice],2))+' KM' AS [Cijena],
            CONVERT(nvarchar,ROUND([ListPrice],2)) + ' KM' AS [Iznos]
FROM Production.Product AS pp
INNER JOIN Sales.SalesOrderDetail AS ssod ON pp.ProductID=ssod.ProductID

/*3. Kreirati upit koji prikazuje naziv modela i opis modela proizvoda. Uslov je da naziv modela sadrži riječ „Mountain“,
dok je opis potrebno prikazati samo na engleskom jeziku.*/

SELECT ppm.Name AS [Naziv modela], ppd.Description AS [Opis modela proizvoda]
FROM Production.ProductModel AS ppm
INNER JOIN Production.ProductModelProductDescriptionCulture AS ppmpdc ON ppm.ProductModelID=ppmpdc.ProductModelID
INNER JOIN Production.ProductDescription AS ppd ON ppmpdc.ProductDescriptionID=ppd.ProductDescriptionID
INNER JOIN Production.Culture AS pc ON ppmpdc.CultureID=pc.CultureID
WHERE ppm.Name LIKE '%Mountain%' AND pc.Name='English'

SELECT *
FROM Production.Culture


/*4. Kreirati upit koji prikazuje broj, naziv i cijenu proizvoda, te stanje zaliha po lokacijama.
Uzeti u obzir samo proizvode koji pripadaju kategoriji „Bikes“. Izlaz sortirati po stanju zaliha u opadajućem redoslijedu.*/

SELECT pp.ProductNumber AS [Broj],
        pp.ListPrice AS [Cijena],
        pp.Name AS [Ime],
        SUM(ppi.Quantity) AS [Stanje zaliha],
        pl.Name AS [Lokacija]
FROM Production.Product AS pp
INNER JOIN Production.ProductSubcategory AS pps ON pp.ProductSubcategoryID=pps.ProductSubcategoryID
INNER JOIN Production.ProductCategory AS ppc ON pps.ProductCategoryID=ppc.ProductCategoryID
INNER JOIN Production.ProductInventory AS ppi ON pp.ProductID=ppi.ProductID
INNER JOIN Production.Location AS pl ON ppi.LocationID=pl.LocationID
WHERE ppc.Name LIKE '%Bikes%'
GROUP BY pp.ProductNumber, pp.ListPrice, pp.Name, pl.Name
ORDER BY SUM(ppi.Quantity) DESC


/*5. Kreirati upit koji prikazuje ukupno ostvarenu zaradu po zaposleniku, na području Evrope, u januaru mjesecu 2014. godine.
Lista treba da sadrži ime i prezime zaposlenika, datum zaposlenja (dd.MM.yyyy), mail adresu, te ukupnu ostvarenu zaradu zaokruženu na
dvije decimale. Izlaz sortirati po zaradi u opadajućem redoslijedu.*/

SELECT pp.FirstName + ' ' +pp.LastName AS [Ime i prezime],
        CONVERT(varchar,hre.HireDate, 104) AS [Datum zaposlenja],
        pea.EmailAddress AS [email],
        ROUND(SUM(ssp.SalesYTD),2) AS [Ukupna ostvarena zarada]
FROM HumanResources.Employee AS hre
INNER JOIN Person.Person AS pp ON hre.BusinessEntityID=pp.BusinessEntityID
INNER JOIN Person.EmailAddress AS pea ON pp.BusinessEntityID=pea.BusinessEntityID
INNER JOIN Sales.SalesPerson AS ssp ON hre.BusinessEntityID=ssp.BusinessEntityID
INNER JOIN Sales.SalesTerritory AS sst ON ssp.TerritoryID=sst.TerritoryID
INNER JOIN Sales.SalesOrderHeader AS ssoh ON ssp.BusinessEntityID=ssoh.SalesPersonID
WHERE sst.[Group] LIKE '%Europe%' AND DATEPART(YEAR, ssoh.DueDate)='2014' AND DATEPART(MONTH, ssoh.DueDate)='01'
GROUP BY pp.FirstName, pp.LastName, pea.EmailAddress, CONVERT(varchar,hre.HireDate, 104)
ORDER BY ROUND(SUM(ssp.SalesYTD),2) DESC