Vjezba 4

USE AdventureWorks2014
GO

/*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.*/

 --0Zadatak 3 vjezbe 3 uradjen sa join-om


 SELECT st.TerritoryID ,count(c.CustomerID) AS Ukupno
 FROM Sales.Customer AS c INNER JOIN sales.SalesTerritory as st
      ON c.TerritoryID = st.TerritoryID
GROUP BY st.TerritoryID
HAVING count(c.CustomerID) >1000


/*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'.*/

--Zadatak 4 vjezba 3 uradjeno sa join-om


SELECT PM.ProductModelID , Count(p.ProductID), PM.Name
FROM  Production.Product AS P INNER JOIN Production.ProductModel AS PM
      ON p.ProductModelID = PM.ProductModelID
WHERE (PM.ProductModelID IS NOT NULL )  AND (p.Name LIKE 'S%')
GROUP BY PM.ProductModelID, PM.Name
HAVING  Count(p.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?*/

-- zadatak 5 vjezba 3 uradjen sa join-om

SELECT TOP 10 WITH TIES SOD.ProductID , SUM(SOD.OrderQty) AS 'Ukupna količina prodaje'
FROM Sales.SalesOrderDetail AS SOD INNER JOIN sales.SpecialOfferProduct AS SOP
    ON SOD.SpecialOfferID = SOP.SpecialOfferID AND SOD.ProductID = SOP.ProductID INNER JOIN Production.Product AS P
    ON SOP.ProductID = P.ProductID
GROUP BY SOD.ProductID
ORDER BY 2 DESC


/*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.*/


-- Zadatak 6 vjezbe 3 uradjen sa join-om

SELECT P.ProductID as 'ID Proizvoda',
       ROUND(SUM(SOD.UnitPrice * SOD.OrderQty),2) AS 'Ukupna zarada bez popusta',
       ROUND (SUM((SOD.UnitPrice * SOD.OrderQty)-(SOD.UnitPrice*SOD.OrderQty*SOD.UnitPriceDiscount)),2) AS 'Ukupna zarada sa popustom',
       SOD.UnitPriceDiscount AS Popust
FROM Sales.SalesOrderDetail AS SOD INNER JOIN Sales.SpecialOfferProduct AS SOP
    ON SOD.SpecialOfferID = SOP.SpecialOfferID AND SOD.ProductID = SOP.ProductID
    INNER JOIN Production.Product AS P
    ON SOP.ProductID = P.ProductID
WHERE SOD.UnitPriceDiscount>0
GROUP BY P.ProductID,SOD.UnitPriceDiscount
ORDER BY "Ukupna zarada bez popusta" 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*/

--Vjezba 3 zadatak 7 uradjen sa join-om

SELECT SOH.SalesOrderNumber AS 'Broj narudžbe',
       CONVERT(NVARCHAR(10),SOH.OrderDate,104) AS 'Datum narudžbe',
      CONVERT(NVARCHAR(10),SOH.DueDate,104) AS 'Datum isporuke'
FROM Sales.SalesOrderHeader AS SOH INNER JOIN Sales.SalesTerritory AS ST
    ON SOH.TerritoryID = ST.TerritoryID
WHERE ST.Name LIKE 'Canada' AND MONTH(DueDate) ='7'AND  YEAR(DueDate) = '2014' AND SOH.CreditCardID IS NULL


/*ZADATAK 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.*/

--1 Zadatak

SELECT TOP 10 P.FirstName AS Ime ,
       P.LastName AS Prezime,
       SUBSTRING(E.LoginID,17,10) AS 'Korisničko ime',
       LEN(SUBSTRING(E.LoginID,17,10)) AS 'Dužina korisničkog imena',
       P.Title,
       CONVERT(NVARCHAR(10),E.HireDate,104) AS 'Datum zaposlenja',
       DATEPART(YEAR,SYSDATETIME()) - DATEPART(YEAR,(E.BirthDate)) AS Starost,
       DATEPART(YEAR,SYSDATETIME()) - DATEPART(YEAR,(E.HireDate))  AS Staž,
       E.JobTitle AS 'Titula na poslu'
FROM HumanResources.Employee AS E INNER JOIN Person.Person AS P
    ON E.BusinessEntityID = P.BusinessEntityID
WHERE E.JobTitle LIKE '%Manager%'
ORDER BY E.BirthDate

/*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“.*/

--2 Zadatak

SELECT TOP 10  P.Name AS Ime ,
       CONVERT (nvarchar, SOD.OrderQty)+ ' kom' AS Količina,
       CONVERT (nvarchar,ROUND(SOD.UnitPrice,2))+ ' KM' AS Cijena ,
       CAST(ROUND((SOD.OrderQty * SOD.UnitPrice),2)AS nvarchar(15)) + ' KM' AS Iznos
FROM Sales.SalesOrderDetail AS SOD INNER JOIN Sales.SpecialOfferProduct AS SOP
    ON SOD.SpecialOfferID = SOP.SpecialOfferID AND SOD.ProductID = SOP.ProductID INNER JOIN
    Production.Product AS P
    ON SOP.ProductID = P.ProductID

--2* drugi nacin
SELECT TOP 10  P.Name AS Ime ,
       CONVERT (nvarchar, SOD.OrderQty)+ ' kom' AS Količina,
       CONVERT (nvarchar,ROUND(SOD.UnitPrice,2))+ ' KM' AS Cijena ,
       CAST(ROUND((SOD.OrderQty * SOD.UnitPrice),2)AS nvarchar(15)) + ' KM' AS Iznos
FROM Production.Product AS P INNER JOIN Sales.SalesOrderDetail AS SOD
    ON P.ProductID = SOD.ProductID
ORDER BY SOD.UnitPrice DESC


/*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.*/

--3 Zadatak


SELECT PM.Name , PD.Description ,
        PC.Name AS 'Jezik opisa'
FROM Production.ProductModel AS PM INNER JOIN Production.ProductModelProductDescriptionCulture AS PMPD
    ON PM.ProductModelID = PMPD.ProductModelID INNER JOIN Production.ProductDescription AS PD
    ON PMPD.ProductDescriptionID = PD.ProductDescriptionID INNER JOIN Production.Culture AS PC
    ON PMPD.CultureID = PC.CultureID
 WHERE PM.Name LIKE '%Mountain%' AND PC.CultureID = 'en'

 /*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.*/

--4 Zadatak

SELECT P.ProductNumber AS Broj , P.Name AS Naziv , P.ListPrice , PII.Quantity
FROM Production.Product AS P INNER JOIN Production.ProductInventory AS PII
    ON P.ProductID = PII.ProductID INNER JOIN Production.ProductSubcategory AS PS
    ON P.ProductSubcategoryID = PS.ProductSubcategoryID
WHERE PS.ProductCategoryID =1
ORDER BY Quantity DESC

--4 Zadatak nacin 2
SELECT P.ProductNumber AS Broj , P.Name AS Naziv , P.ListPrice AS Cijena, PII.Quantity AS Zalihe ,PL.Name AS Lokacija,
 PC.Name AS Kategorija
FROM Production.Product AS P JOIN Production.ProductInventory AS PII
    ON P.ProductID = PII.ProductID JOIN Production.Location AS PL
    ON PII.LocationID = PL.LocationID JOIN Production.ProductSubcategory AS PS
    ON P.ProductSubcategoryID = PS.ProductSubcategoryID JOIN Production.ProductCategory AS PC
    ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE PC.Name LIKE '%Bikes%'
ORDER BY PII.Quantity DESC
SELECT *
FROM Production.ProductCategory

/*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.*/

--5

SELECT P.FirstName AS Ime , P.LastName AS Prezime ,CONVERT(nvarchar(10),E.HireDate,104) AS 'Datum zaposlenja',
       EA.EmailAddress, ST.[Group] AS Teritorija,
       SUM(SOH.TotalDue) AS' Ukupna zarada', CONVERT(nvarchar(10),SOH.DueDate,104) AS 'Datum isporuke'
FROM HumanResources.Employee AS E INNER JOIN Person.Person AS P
    ON E.BusinessEntityID = P.BusinessEntityID INNER JOIN Sales.SalesPerson AS SP
    ON E.BusinessEntityID = SP.BusinessEntityID INNER JOIN Sales.SalesOrderHeader AS SOH
    ON SP.BusinessEntityID =SOH.SalesPersonID INNER JOIN Sales.SalesTerritory AS ST
    ON SP.TerritoryID = ST.TerritoryID INNER JOIN Person.EmailAddress AS EA
    ON P.BusinessEntityID = EA.BusinessEntityID
WHERE ST.[Group] LIKE  '%Europe%' AND DATEPART(Month,SOH.DueDate) = '01' AND DATEPART (Year ,SOH.DueDate) = '2014'
GROUP BY  P.FirstName ,P.LastName , CONVERT(nvarchar(10),E.HireDate,104) ,EA.EmailAddress , ST.[Group],
          CONVERT(nvarchar(10),SOH.DueDate,104)
ORDER BY SUM(SOH.TotalDue) DESC