BPII - LAB4

/* 1. Iz bp Northwind prikazati ime i prezime zaposlenika (spojeno) sa ukupnim brojem narudzbi koje je uradio.
Listu sortirati od zaposlenika sa najvecim brojem uradjenih narudzbi.*/
USE Northwind
GO
SELECT E.FirstName + ' ' + E.LastName AS 'Ime i prezime',
    COUNT (O.EmployeeID) AS 'Broj narudzbi'
FROM Employees AS E
INNER JOIN Orders AS O ON E.EmployeeID=O.EmployeeID
GROUP BY E.FirstName, E.LastName
ORDER BY [Broj narudzbi] DESC

/*Modifikovati prethodni upit tako da se prikaze broj narudzbi koje su uradjene u 7 mjesecu 1997. godine, a prikazati
samo one zaposlenike koji su napravili 5 ili vise narudzbi.*/
SELECT E.FirstName + ' ' + E.LastName AS 'Ime i prezime',
    COUNT (O.EmployeeID) AS 'Broj narudzbi'
FROM Employees AS E
INNER JOIN Orders AS O ON E.EmployeeID=O.EmployeeID
WHERE DATEPART(MONTH, OrderDate)=7 AND DATEPART(YEAR, OrderDate)=1997
GROUP BY E.FirstName, E.LastName
HAVING COUNT (O.EmployeeID)>=5
ORDER BY [Broj narudzbi] DESC

/* 2.  Iz bp Northwind orikazati listu proizvoda kojih nema na zalihama. Takodjer, izlaz treba da sadrzi naziv dobavljaca,
broj telefon dobavljaca, broj proizvoda na zalihama te koliko je komada prodano.*/
SELECT s.CompanyName AS 'Dobavljac',
        s.Phone AS 'Telefon',
        p.ProductName AS 'Proizvod',
        p.UnitsInStock AS 'Zalihe',
        SUM(od.Quantity) AS Prodano
FROM Suppliers AS s
INNER JOIN Products AS p ON s.SupplierID=p.SupplierID
INNER JOIN [Order Details] AS od ON p.ProductID=od.ProductID
WHERE p.UnitsInStock=0
GROUP BY s.CompanyName, s.Phone, p.ProductName,p.UnitsInStock

/* 3. Iz bp Pubs prikazati prodaju knjiga po prodavnicama. Izlaz treba da sadrzi sljedece kolone:
naziv izdavaca, naziv prodavnice, naziv knjige i zaradu od prodaje.
Potrebo je prikazati prodaju knjiga izdavaca sa imenom 'New Moon Books'.*/
USE pubs
GO
SELECT p.pub_name AS Izdavac, st.stor_name AS Prodavnica, t.title AS Knjiga, SUM(s.qty*t.price) AS Zarada
FROM publishers AS p
INNER JOIN titles AS t ON p.pub_id=t.pub_id
INNER JOIN sales AS s ON s.title_id=t.title_id
INNER JOIN stores AS st ON st.stor_id=s.stor_id
WHERE p.pub_name='New Moon Books'
GROUP BY  p.pub_name, st.stor_name, t.title
ORDER BY Prodavnica, Knjiga

/* 4. Vasa firma zeli da sazna neke informacije od svojim kupcima. Svake godine se analizira drugi region.
Ove godine je dosao red na kupce iz United States. Izlaz treba da sadrzi:
ime i prezime kupca, ukupan broj narudzbi za prikazanog kupca, ukupnu kolicinu svih kupljenih proizvoda, region i grad iz
kojeg kupac dolazi. Uslovi su:
    -pored onih kupaca koji su nesto kupili, lista treba da sadrzi i one koji nisu uradili niti jednu narudzbu
    -ukoliko u izlazu postoji kolona u kojoj se pojavljju NULL vrijednosti iste je potrebno zamijneiti brojem 0
    -pored toga sto se traze kupci iz US, na listu je potrebno dodati i one koji dolaze iz grada 'Montreal'. (adv.)*/

USE AdventureWorks2014
GO
SELECT p.FirstName + ' ' + p.LastName AS Kupac,
        COUNT(soh.CustomerID) AS 'Broj narudzbi',
        ISNULL(SUM(sod.OrderQty), 0) AS 'Narucena kolicina',
        pa.City AS Grad
        --region
FROM Sales.Customer AS c
INNER JOIN Person.Person AS p ON p.BusinessEntityID=c.PersonID
LEFT JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID=soh.CustomerID
LEFT JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID=sod.SalesOrderID
INNER JOIN Sales. SalesTerritory AS st ON st.TerritoryID=c.TerritoryID
INNER JOIN Person.Address AS pa ON pa.AddressID=st.TerritoryID
WHERE pa.City='Montreal'
GROUP BY p.FirstName, p.LastName, pa.City


/* 5. Za kupca sa imenim i prezimenom 'Jordan Green' prikazati tipove i brojeve kartica koje posjeduje, te narudzbe koje je uradio
koristeci kartice (broj narudbe, datum narudzbe) (adv.)*/
SELECT p.FirstName, p.LastName, scc.CardType,  scc.CardNumber, soh.SalesOrderNumber, soh.OrderDate
FROM Sales.Customer AS sc
INNER JOIN Person.Person AS p ON sc.PersonID=p.BusinessEntityID
INNER JOIN Sales.PersonCreditCard AS pp ON p.BusinessEntityID=pp.BusinessEntityID
INNER JOIN Sales.CreditCard AS scc ON scc.CreditCardID=pp.CreditCardID
INNER JOIN Sales.SalesOrderHeader AS soh ON soh.CreditCardID=scc.CreditCardID
WHERE p.FirstName='Jordan'  AND p.LastName='Green'