BPI - LAB5

/* 1. Prijem se radi na Northwind bazi podataka.
Menadzment firme zeli da nagradi kupca koji je utrošio najviše novca u februaru mjesecu. Zbog ograničenih sredstava ovaj put će biti
nadrađen samo kupac koji dolazi iz Londona. Prikazati kupca sa sl podacima:
ime i prezime (ContactName), adresa, telefon i ukupno potroseno.
Kolonu ukupno utroseno fomatirati na sl nacin: 1000KM.*/
SELECT TOP 1 c.ContactName AS Kupac, c.Address, c.Phone, SUM(od.UnitPrice*od.Quantity) AS 'Ukupno utroseno'
FROM Customers AS c
INNER JOIN Orders AS o ON c.CustomerID=o.CustomerID
INNER JOIN [Order Details] AS od ON o.OrderID=od.OrderID
WHERE DATEPART(MONTH, o.OrderDate)=2 AND c.City='London'
GROUP BY c.ContactName, c.Address, c.Phone
ORDER BY [Ukupno utroseno] DESC

/* 2. Koristeci bp pubs, prikazati listu zaposlenika (ime i prezime) sa sl podacima za svakog zaposlenog:
minimalna, makismalna, srednja i ukupna kolicina prodatih artikala (knjiga). Uslovi su:
ukupna prodaja treba biti veca od 100, a srednja izmedju 20 i 25 ukljucujuci granicne vrijednosti.*/
USE pubs
GO
SELECT e.fname + ' ' + e.lname AS 'Ime i prezime',
        MIN(s.qty) AS Minimalna,
        MAX(s.qty) AS Maksimalna,
        AVG(s.qty) AS Prosjecna,
        SUM(s.qty) AS Ukupno
FROM employee AS e
INNER JOIN publishers AS p ON e.pub_id=p.pub_id
INNER JOIN titles AS t ON p.pub_id=t.pub_id
INNER JOIN sales AS s ON t.title_id=s.title_id
GROUP BY e.fname, e.lname
HAVING SUM(s.qty)>100 AND AVG(s.qty) BETWEEN 20 AND 25

/* 3. Primjer radi na adv2014 bp.
Potrebno je prikazati informacije od kreditnim karticama kupaca. Izlaz treba da sadrži sl kolone:
ime i prezime kupca, email, tip kartice, broj kartice i godinu isteka kartice. Uslovi su sl:
    -prikazati samo vista kartice koje isticu u 2008. godini
    -na kistu ukljuciti i kuoce koji nisu uradili niti jednu narudzbu koristeci crticu
    -eliminisati duplakte u rezultatima upita
    -izlaz sortirati abecedno po prezimenu kupca
*/
USE AdventureWorks2014
GO
SELECT DISTINCT pp.FirstName + ' ' + pp.LastName AS 'Ime i prezime',
        pea.EmailAddress,
        scc.CardType,
        scc.CardNumber,
        scc.ExpYear
FROM Sales.Customer AS sc
INNER JOIN Person.Person AS pp ON sc.PersonID=pp.BusinessEntityID
INNER JOIN Person.EmailAddress AS pea ON pp.BusinessEntityID=pea.BusinessEntityID
INNER JOIN Sales.PersonCreditCard AS pcc ON pp.BusinessEntityID=pcc.BusinessEntityID
INNER JOIN Sales.CreditCard AS scc ON pcc.CreditCardID=scc.CreditCardID
LEFT OUTER JOIN Sales.SalesOrderHeader AS soh ON scc.CreditCardID=soh.CreditCardID
WHERE scc.CardType='Vista' AND scc.ExpYear=2008
ORDER BY 2