BPII - Vjezba 5

/*1. Kreirati upit koji prikazuje kreditne kartice kojima je plaćeno više od 20 narudžbi.
U listu uključiti ime i prezime vlasnika kartice, tip kartice, broj kartice, ukupan iznos plaćen karticom.*/

SELECT pp.FirstName + ' ' + pp.LastName AS [Ime i prezime],
        scc.CardType AS [Tip kartice],
        scc.CardNumber AS [Broj kartice],
        SUM(ssoh.TotalDue) AS [Ukupan iznos]
FROM Sales.Customer AS sc
INNER JOIN Person.Person AS pp ON sc.PersonID=pp.BusinessEntityID
INNER JOIN Sales.PersonCreditCard AS spcc ON pp.BusinessEntityID=spcc.BusinessEntityID
INNER JOIN Sales.CreditCard AS scc ON spcc.CreditCardID=scc.CreditCardID
INNER JOIN Sales.SalesOrderHeader AS ssoh ON scc.CreditCardID=ssoh.CreditCardID
INNER JOIN Sales.SalesOrderDetail AS ssod ON ssoh.SalesOrderID=ssod.SalesOrderID
GROUP BY pp.FirstName, pp.LastName, scc.CardType, scc.CardNumber
HAVING SUM(ssod.OrderQty)>20

/*2. Kreirati upit koji prikazuje kupce koji su u maju mjesecu 2014. godine naručili proizvod „Front Brakes“ u količini većoj od 5 komada.*/

SELECT pp.FirstName + ' ' +pp.LastName AS [Kupac], ppro.Name AS [Ime proizvoda], ssod.OrderQty AS [Kolicina],
        CONVERT(varchar, ssoh.OrderDate, 104) [Datum narudzbe]
FROM Person.Person AS pp
INNER JOIN Sales.Customer AS sc ON pp.BusinessEntityID=sc.PersonID
INNER JOIN Sales.SalesOrderHeader AS ssoh ON ssoh.CustomerID=sc.CustomerID
INNER JOIN Sales.SalesOrderDetail AS ssod ON ssod.SalesOrderID=ssoh.SalesOrderID
INNER JOIN Production.Product AS ppro ON ppro.ProductID=ssod.ProductID
WHERE ppro.Name LIKE '%Front Brakes%' AND ssod.OrderQty>5 AND DATEPART(MONTH, ssoh.OrderDate)='5' AND DATEPART(YEAR, ssoh.OrderDate)='2014'


/*3. Kreirati upit koji prikazuje kupce koji su u 7. mjesecu utrošili više od 200.000 KM.
U listu uključiti ime i prezime kupca te ukupni utrošak. Izlaz sortirati prema utrošku opadajućim redoslijedom.*/

SELECT pp.FirstName + ' ' +pp.LastName AS [Ime i prezime kupca], SUM(soh.TotalDue) AS [Ukupni trosak]
FROM Person.Person AS pp
INNER JOIN Sales.Customer AS sc ON pp.BusinessEntityID=sc.PersonID
INNER JOIN Sales.SalesOrderHeader AS soh ON soh.CustomerID =sc.CustomerID
WHERE DATEPART(MONTH, soh.OrderDate)=7
GROUP BY pp.FirstName, pp.LastName
HAVING SUM(soh.TotalDue) > 200000

/*4. Kreirati upit koji prikazuje zaposlenike koji su uradili više od 200 narudžbi.
U listu uključiti ime i prezime zaposlenika te ukupan broj urađenih narudžbi.
Izlaz sortirati prema broju narudžbi opadajućim redoslijedom*/

SELECT pp.FirstName + ' ' +pp.LastName AS [Ime i prezime], SUM(ssod.OrderQty) AS [Broj narudzbi]
FROM Person.Person AS pp
INNER JOIN HumanResources.Employee AS hre ON pp.BusinessEntityID=hre.BusinessEntityID
INNER JOIN Sales.SalesPerson AS ssp ON ssp.BusinessEntityID=hre.BusinessEntityID
INNER JOIN Sales.SalesOrderHeader AS ssoh ON ssp.BusinessEntityID=ssoh.SalesPersonID
INNER JOIN Sales.SalesOrderDetail AS ssod ON ssod.SalesOrderID=ssoh.SalesOrderID
GROUP BY pp.FirstName, pp.LastName
HAVING SUM(ssod.OrderQty) >200
ORDER BY SUM(ssod.OrderQty) DESC

/*5. Kreirati upit koji prikazuje proizvode kojih na skladištu ima u količini manjoj od 30 komada.
Lista treba da sadrži naziv proizvoda, naziv skladišta (lokaciju), stanje na skladištu i ukupnu prodanu količinu.
U rezultate upita uključiti i one proizvode koji nikad nisu prodavani. Ukoliko je ukupna prodana količina
prikazana kao NULL vrijednost, izlaz formatirati brojem 0.*/

SELECT pp.Name AS [Ime proizvoda], pl.Name AS [Naziv skladista (lokacija)], COUNT(ppod.StockedQty) AS [Kolicina na skladistu],
        ISNULL(SUM(ppod.OrderQty),0) AS [Ukupna prodana kolicina]
FROM Production.Product AS pp
INNER JOIN Production.ProductInventory AS ppi ON pp.ProductID=ppi.ProductID
INNER JOIN Production.Location AS pl ON ppi.LocationID=pl.LocationID
INNER JOIN Purchasing.PurchaseOrderDetail AS ppod ON ppod.ProductID=pp.ProductID
GROUP BY pp.Name, pl.Name
HAVING COUNT(ppod.StockedQty)<30

/*6. Kreirati upit koji prikazuje podatke o zaposlenicima. Lista treba da sadrži sljedeće kolone:
ime i prezime zaposlenika (spojeno), korisničko ime, novu lozinku, starost i staž zaposlenika. Uslovi su sljedeći:

 Za korisničko ime potrebno je koristiti kolonu LoginID (tabela Employees).
Npr. LoginID zaposlenika sa imenom i prezimenom 'Mary Gibson' je adventure-works\mary0.
Korisničko ime zaposlenika je sve što se nalazi iza znaka \ (backslash) što je u ovom primjeru mary0,

 Nova lozinka se formira koristeći hešovanu lozinku zaposlenika na sljedeći način:
o Hešovanu lozinku potrebno je okrenuti obrnuto (npr. dbms2015 -> 5102smbd)
o Nakon toga preskačemo prvih 5 i uzimamo narednih 8 karaktera
o Sljedeći korak jeste da iz dobivenog stringa počevši od drugog karaktera naredna dva zamijenimo sa
 X# (npr. ako je dobiveni string dbms2015 izlaz će biti dX#s2015)

 Starost i staž se formiraju na osnovu kolona BirthDate i HireDate
Prikazati podatke samo za zaposlenike ženskog spola koji imaju više od 50 godina i više od 5 godina staža.*/

SELECT pp.FirstName + ' ' + pp.LastName AS [Ime i prezime zaposlenika],
        REPLACE(hre.LoginID, 'adventure-works\' ,'') AS [Korisnicko ime],
        SUBSTRING(REVERSE(hre.LoginID),5,8) AS [Nova loznika],
        STUFF(SUBSTRING(REVERSE(ppass.PasswordHash),6,8),2,2,'X#') AS [Izmjena passworda],
        DATEPART(YEAR,SYSDATETIME())-DATEPART(YEAR,hre.BirthDate) AS [Starost],
        DATEPART(YEAR, SYSDATETIME())-DATEPART(YEAR, hre.HireDate) AS [Staz]
FROM Person.Person AS pp
INNER JOIN HumanResources.Employee AS hre ON pp.BusinessEntityID=hre.BusinessEntityID
INNER JOIN Person.Password AS ppass ON pp.BusinessEntityID=ppass.BusinessEntityID
WHERE DATEPART(YEAR,SYSDATETIME())-DATEPART(YEAR,hre.BirthDate) >50 AND DATEPART(YEAR, SYSDATETIME())-DATEPART(YEAR, hre.HireDate)>5
    AND hre.Gender LIKE '%F%'


SELECT REPLACE(LoginID, 'adventure-works\' ,'')
FROM HumanResources.Employee
SELECT * FROM HumanResources.Employee WHERE Gender IS NOT NULL

/*7. Prikazati ukupnu količinu prodaje i ukupnu zaradu od prodaje svakog pojedinog proizvoda po teritoriji.
Uzeti u obzir samo prodaju u sklopu ponude pod nazivom “Volume Discount 11 to 14” i to samo gdje je količina prodaje veća od 100 komada.
Zaradu zaokružiti na dvije decimale, te izlaz sortirati po zaradi u opadajućem redoslijedu.*/

SELECT SUM(ssod.OrderQty) AS [Ukupna kolicina prodaje], ROUND(SUM(ssod.LineTotal),2) AS [Ukupna zarada],
    pl.Name AS [Lokacija]
FROM Production.Product AS pp
INNER JOIN Sales.SalesOrderDetail AS ssod ON pp.ProductID=ssod.ProductID
INNER JOIN Production.ProductInventory AS ppi ON pp.ProductID=ppi.ProductID
INNER JOIN Production.Location AS pl ON ppi.LocationID=pl.LocationID
WHERE ssod.SpecialOfferID=2
GROUP BY ssod.LineTotal, pl.Name
HAVING SUM(ssod.OrderQty)>100
ORDER BY ssod.LineTotal DESC

SELECT *
FROM Sales.SpecialOffer WHERE [Description] LIKE '%Volume Discount 11 to 14%'

/*8. Kreirati upit koji prikazuje četvrtu najveću platu u preduzeću (po visini primanja). Tabela EmployeePayHistory.*/
SELECT TOP 4 MAX(hreph.Rate)
FROM HumanResources.EmployeePayHistory AS hreph

/*9. Kreirati upit koji prikazuje naziv proizvoda, naziv lokacije, stanje zaliha na lokaciji,
ukupno stanje zaliha na svim lokacijama i ukupnu prodanu količinu. Uzeti u obzir prodaju samo u 2013. Godini.*/

SELECT pp.Name AS [Naziv proizvoda], pl.Name AS [Naziv lokacije], ssod.OrderQty AS [Stanje zaliha],
    SUM(ssod.OrderQty) AS [Ukupno stanje zaliha], CONVERT(varchar,ssoh.OrderDate,104) AS [Datum]
FROM Production.Product AS pp
INNER JOIN Production.ProductInventory AS ppi ON pp.ProductID=ppi.ProductID
INNER JOIN Production.Location AS pl ON ppi.LocationID=pl.LocationID
INNER JOIN Sales.SalesOrderDetail AS ssod ON ssod.ProductID=pp.ProductID
INNER JOIN Sales.SalesOrderHeader AS ssoh ON ssoh.SalesOrderID=ssod.SalesOrderID
WHERE DATEPART(YEAR, ssoh.OrderDate)=2013
GROUP BY pp.Name, pl.Name, ssod.OrderQty,ssoh.OrderDate