Vjezbe 5 Ekipno

use AdventureWorks2014
go

--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 P.FirstName+' '+P.LastName, COUNT(SOH.CreditCardID), CC.CardNumber,CC.CardType,SUM(SOH.TotalDue)
from Person.Person as P join Sales.PersonCreditCard as PCC on P.BusinessEntityID=PCC.BusinessEntityID
                        join Sales.CreditCard as CC on CC.CreditCardID=PCC.CreditCardID
                        join Sales.SalesOrderHeader as SOH on SOH.CreditCardID=CC.CreditCardID

group by P.FirstName,P.LastName,CC.CardNumber,CC.CardType
having COUNT(SOH.CreditCardID) > 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 C.CustomerID , SOH.OrderDate , PP.Name , SOD.OrderQty
from Sales.Customer as C join Sales.SalesOrderHeader as SOH on SOH.CustomerID=C.CustomerID
                        join Sales.SalesOrderDetail as SOD on SOH.SalesOrderID=SOD.SalesOrderID
                        join Production.Product as PP ON PP.ProductID = SOD.ProductID

where MONTH(SOH.OrderDate)=5 AND YEAR(SOH.OrderDate)=2014 and PP.Name='Front Brakes' and SOD.OrderQty>5



--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 C.CustomerID , P.FirstName+ ' '+P.LastName, SUM(SOH.TotalDue)
from Sales.Customer as C join Person.Person as P on P.BusinessEntityID=C.PersonID
                        join Sales.SalesOrderHeader AS SOH on SOH.CustomerID=C.CustomerID
where MONTH(SOH.OrderDate)=7
group by     C.CustomerID , P.FirstName,P.LastName
having SUM(SOH.TotalDue)>200000
order by SUM(SOH.TotalDue) desc



--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 P.FirstName+ ' ' + P.LastName , COUNT(SOH.SalesPersonID)
from HumanResources.Employee as E join Person.Person as P on P.BusinessEntityID=E.BusinessEntityID
                                    join Sales.SalesOrderHeader as SOH on P.BusinessEntityID=SOH.SalesPersonID
group by P.FirstName,P.LastName
having COUNT(SOH.SalesPersonID)>200
order by COUNT(SOH.SalesPersonID) 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,L.Name, SUM(PPI.Quantity),ISNULL(SUM(SOD.OrderQty),'0')
from Production.Product as PP join Production.ProductInventory as PPI on PPI.ProductID=PP.ProductID
                            join Production.Location as L on L.LocationID=PPI.LocationID
                            left outer join Sales.SalesOrderDetail as SOD on SOD.ProductID=PP.ProductID
group by PP.Name,L.Name
having SUM(PPI.Quantity) < 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 P.FirstName+ ' ' +P.LastName as 'Ime i prezime',SUBSTRING(LoginID,CHARINDEX('\',LoginID)+1,100)  as 'Korisnicko ime',
 STUFF(SUBSTRING(REVERSE(PP.PasswordHash),6,8),2,2,'X#') as 'Sifra najgora na svijetu',
 DATEDIFF(YEAR,E.BirthDate,GETDATE()) as 'Starost' ,
  DATEDIFF(YEAR,E.HireDate,GETDATE()) as 'Staz' ,
  E.Gender
from HumanResources.Employee as E join Person.Person as P on P.BusinessEntityID=E.BusinessEntityID
                                    join Person.[Password] as PP on PP.BusinessEntityID=P.BusinessEntityID
where DATEDIFF(YEAR,E.BirthDate,GETDATE()) > 50 AND
  DATEDIFF(YEAR,E.HireDate,GETDATE()) >5
  AND E.Gender='F'



  --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 P.Name, ST.Name, SUM(SOD.OrderQty),ROUND(SUM(SOD.OrderQty*SOD.UnitPrice-SOD.OrderQty*SOD.UnitPrice*SOD.UnitPriceDiscount),2) as Zarada
from Production.Product as P join Sales.SalesOrderDetail as SOD on SOD.ProductID=P.ProductID
                            join Sales.SalesOrderHeader as SOH on SOD.SalesOrderID=SOH.SalesOrderID
                            join Sales.SalesTerritory as ST ON ST.TerritoryID=SOH.TerritoryID
                            join  Sales.SpecialOffer as SO on SO.SpecialOfferID=SOD.SpecialOfferID
where SO.[Description]='Volume Discount 11 to 14'
group by P.Name,ST.Name
having SUM(SOD.OrderQty)>100
order by Zarada desc