[BP II] zad

USE NORTHWND
GO

--1.    Prikazati sve kupce koji dolaze iz Njemačke ili Švedske, a imaju unesen fax.

SELECT CustomerID, Country, Fax
FROM Customers
WHERE Country IN ('Germany','Sweden') AND Fax IS NOT NULL

--2.    Prikazati kupce kojima CompanyName počinje slovom 'M' ili ContactName počinje slovom 'M'.

SELECT CompanyName, ContactName
FROM Customers
WHERE CompanyName LIKE 'M%' OR ContactName LIKE 'M%'


--3.    Prikazati sve narudžbe koje su obavljene u 7 mjesecu 1997 godine.

SELECT OrderID, OrderDate
FROM Orders
WHERE DATEPART(MONTH,OrderDate)=7 AND DATEPART(YEAR,OrderDate)=1997


--4.    Prikazati sve proizvode koji su skuplji od 20 KM.

SELECT ProductID, ProductName, UnitPrice
FROM Products
WHERE UnitPrice > 20


--5.    Koliko je svaki zaposlenik uradio narudžbi?

SELECT E.EmployeeID, COUNT(O.OrderID)
FROM Employees AS E INNER JOIN Orders AS O
ON E.EmployeeID=O.EmployeeID
GROUP BY E.EmployeeID


--6.    Prikazati 5 kupaca koji su imali najviše narudžbi.

SELECT TOP 5 C.CustomerID, COUNT(O.OrderID)
FROM Customers AS C INNER JOIN Orders  AS O
ON C.CustomerID=O.CustomerID
GROUP BY C.CustomerID
ORDER BY 2 DESC


--7.    Prikazati kupca koji je utrošio najviše novca u 7 mjesecu 1997. godine.


SELECT TOP 1 C.ContactName,SUM(O.Freight) AS 'Novac' , O.OrderDate
FROM Customers AS C INNER JOIN Orders AS O
ON C.CustomerID=O.CustomerID
WHERE DATEPART(MONTH,O.OrderDate)=7 AND DATEPART(YEAR,O.OrderDate)=1997
GROUP BY C.ContactName,O.OrderDate
ORDER BY 2 DESC

--8.    Prikazati navjernijeg kupca koji dolazi iz Njemačke.
--Najvjerniji kupac je onaj koji je utrošio najviše novca.


SELECT TOP 1 C.ContactName, SUM(O.Freight) AS 'Novac', C.Country
FROM Customers AS C INNER JOIN Orders AS O
ON C.CustomerID=O.CustomerID
WHERE C.Country = 'Germany'
GROUP BY C.ContactName, C.Country
ORDER BY 2 DESC


--9.    Prikazati dobavljace koji dolaze iz Japana i ukupan broj proizvoda koji se naručuje od njih.


SELECT S.CompanyName, S.Country, COUNT(P.UnitsOnOrder) AS 'Broj proizvoda'
FROM Suppliers AS S
INNER JOIN Products AS P ON S.SupplierID=P.SupplierID
WHERE S.Country='Japan'
GROUP BY S.CompanyName, S.Country


--10.   Kreirati upit koji nam prikazuje kategorije proizvoda i proizvode zajedno sa
--cijenom pojedinog proizvoda po komadu. Uslovi su: kategorija proizvoda u svome
--nazivu posjeduje dio rijeći „food“ ili ime proizvoda počinje sa slovom T.
--Takoðer, cijena proizvoda po komadu treba biti veća od 60.


SELECT C.CategoryName,P.ProductName,P.UnitPrice
FROM Categories AS C
INNER JOIN Products AS P ON C.CategoryID=P.CategoryID
WHERE (C.CategoryName LIKE '%food%' OR P.ProductName LIKE 'T%') AND P.UnitPrice>60


/*11.   Kreirati upit koji će prikazati proizvode koji pripadaju kategoriji „Confections“.
 Takoðer, upit treba da prikaže podatke o dobavljaču: naziv dobavljača, adresa i broj telefona.
 Uslovi koji se trebaju zadovoljiti su:
a)  Stanje proizvoda na zalihama manje od 30 komada, i
b)  Dobavljač dolazi iz Manchestera ili Berlina. */


SELECT C.CategoryName, S.CompanyName,S.Address,S.Phone,P.UnitsInStock, S.City
FROM Categories AS C
INNER JOIN Products AS P ON C.CategoryID=P.CategoryID
INNER JOIN Suppliers AS S ON S.SupplierID=P.SupplierID
WHERE C.CategoryName LIKE '%confections%' AND P.UnitsInStock < 30 AND S.City IN ('Manchester','Berlin')



--12.   Kreirati upit koji prikazuje ukupan iznos popusta koji je dat za svaki pojedini proizvod.


SELECT P.ProductName, OD.Discount
FROM Products AS P
INNER JOIN [Order Details] AS OD ON P.ProductID=OD.ProductID


/*13.   Prikazati ime i prezime zaposlenika (spojeno), te broj narudžbi koje su napravili.
Uslovi su sljedeći:
a)  Zaposlenici dolaze iz Londona, ili
b)  Broj narudžbi manji od 100.*/

SELECT E.FirstName+' ' +E.LastName AS 'Ime i prezime', COUNT(O.OrderID) AS 'Broj narudzbi'
FROM Employees AS E
INNER JOIN Orders AS O ON  E.EmployeeID=O.EmployeeID
WHERE E.City LIKE 'London'
GROUP BY E.FirstName,E.LastName
HAVING COUNT(O.OrderID) < 100