--1

USE NORTHWND
GO
SELECT TOP 1 C.ContactName, C.Address, C.Phone,CONVERT(nvarchar, (OD.Quantity*(OD.UnitPrice - OD.UnitPrice*OD.Discount))) + ' KM' AS 'Ukupno Utroseno', C.City
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 C.City = 'London'
ORDER BY (OD.Quantity*(OD.UnitPrice - OD.UnitPrice*OD.Discount)) DESC

--Admirovo


SELECT TOP 1 C.ContactName, C.Address, C.Phone, SUM(OD.Quantity*OD.UnitPrice) AS 'Ukupno Utroseno', C.City
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 C.City = 'London' AND DATEPART(MONTH, O.OrderDate)=2
GROUP BY  C.ContactName, C.Address, C.Phone, C.City
ORDER BY 4 DESC


--2

USE pubs
GO
SELECT E.fname + ' ' + E.lname AS 'Ime i Prezime', MIN(S.qty) AS Minimalna, MAX(S.qty) AS Maximalna, AVG(S.qty) AS Srednja, 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

USE AdventureWorks2017
GO
SELECT P.FirstName, P.LastName, EA.EmailAddress, CC.CardType, CC.CardNumber, CC.ExpYear, SOH.SalesOrderID
FROM Person.Person AS P INNER JOIN Person.EmailAddress AS EA
    ON P.BusinessEntityID = EA.BusinessEntityID INNER JOIN Sales.PersonCreditCard AS PCC
    ON P.BusinessEntityID = PCC.BusinessEntityID INNER JOIN Sales.CreditCard AS CC
    ON PCC.CreditCardID = CC.CreditCardID LEFT JOIN Sales.SalesOrderHeader AS SOH
    ON CC.CreditCardID = SOH.CreditCardID INNER JOIN Sales.Customer AS C
    ON SOH.CustomerID = C.CustomerID
WHERE CC.CardType = 'Vista' AND CC.ExpYear = 2008
ORDER BY P.LastName ASC

--Admirovo

USE AdventureWorks2017
GO
SELECT DISTINCT P.FirstName, P.LastName, EA.EmailAddress, CC.CardType, CC.CardNumber, CC.ExpYear
FROM Sales.Customer AS C JOIN Person.Person AS P
    ON C.PersonID = P.BusinessEntityID JOIN Person.EmailAddress AS EA
    ON P.BusinessEntityID = EA.BusinessEntityID JOIN Sales.PersonCreditCard AS PCC
    ON P.BusinessEntityID = PCC.BusinessEntityID JOIN Sales.CreditCard AS CC
    ON PCC.CreditCardID = CC.CreditCardID LEFT JOIN Sales.SalesOrderHeader AS SOH
    ON CC.CreditCardID = SOH.CreditCardID
WHERE CC.CardType = 'Vista' AND CC.ExpYear = 2008
ORDER BY P.LastName ASC